substr function fails
hi there, can anyone tell me why a simple substr would throw an error if the target varchar assignment isn't three characters larger than the length provided to the substr function?
can anyone tell me why this:
declare
v_legal varchar2(200);
v_disp_trans_sid integer := 850500;
begin
v_legal := SUBSTR(Ttls_Common.build_parcel_string(v_disp_trans_sid),1,200);
dbms_output.put_line('v_legal is '||length(v_legal)||' characters.');
end;
/
causes this even though the assignment is substr to 200:
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 7
yet once i increase the size of the target varchar by three characters it works:
declare
v_legal varchar2(203);
v_disp_trans_sid integer := 850500;
begin
v_legal := SUBSTR(Ttls_Common.build_parcel_string(v_disp_trans_sid),1,200);
dbms_output.put_line('v_legal is '||length(v_legal)||' characters.');
end;
/
causes:
v_legal is 200 characters.
PL/SQL procedure successfully completed.