Limitation of substr function
How to use SUBSTR function for special characters, so that it will give the CORRECT result. As special character holds extra memory than normal charcter, simply using substr function gives wrong result.
Here is the example which will clear my doubt:
Line: ------------
Code:
Line: ------------
Initial values:
Variables
v_message_substr VARCHAR2(2000);
v_message VARCHAR(32767); -- this variable gets the data from external source
v_line NUMBER := 1;
v_id NUMBER := 1;
Table test_table (column1 e_id(varchar2(1), column2 message(varchar2(2000))
........
1) v_message_substr := SUBSTR(v_message,(1+((v_line-1)*2000)),2000);
2) DBMS_OUTPUT.PUT_LINE('email message string'||v_message_substr);
3) INSERT INTO test_table VALUES (v_id, vl_message_substr);
Line: ------------
The above code will work properly, even if v_message have data more than values 2000, however if v_message have special like Ë , substr will pass data more than 2000 character which will cause the code to fail at line1 as v_message_substr have max length of 2000 and substr function is passing data of length 2001.
Please help me how to use substr function efficiently so that it should not pass data more than it should pass.