I have a procedure in package returned a ora-06502 error message.
it goes something like this:
for i in (select * from table) loop
msg := msg || ' Found column : :'||table.column;
I checked and double checked msg is declared as CLOB; The procedure stopped at this statement and when the size of msg exceeded 32767.
However, I wrote a debug procedure in the same package. It worked OK; It uses the same variable in the package, i.e. msg.
procedure debug_test is
msg := msg||lpad('x', 5000, '0');
IF dbms_lob.getlength(msg) > 100000 THEN
I know by using a CAST(XXX as CLOB) will solve the problem but I am puzzled why one procedure works and not the other. Can anybody see the difference? What am I doing wrong?
Thanks in advance.