Ever had a trouble while you were trying to print a variable, got an ORA-06502 error?
ORA-06502: PL/SQL: numeric or value error string
Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2).
Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.
If your variable is a CLOB it might be confusing as it was for me..
However, you ‘ll figure out the reason soon enough: Those string parts that you were collecting are bigger than 32K
And you ‘ll realize that you should tell it what to do.
Here is what I came across to. A big and special thanks to oracle forums, of course :)
PROCEDURE chunk_clob(p_clob CLOB) IS
v_offset PLS_INTEGER DEFAULT 1;
v_clob CLOB := p_clob;
v_chunk := dbms_lob.getchunksize(v_clob);
v_len := dbms_lob.getlength(v_clob);
WHILE v_offset v_chunk
THEN v_size := v_chunk;
ELSE v_size := v_len - (v_offset - 1);
v_buf_size := NULL;
dbms_lob.READ(v_clob, v_size, v_offset, v_buf_size);
v_offset := v_offset + v_size;
– OKAY what is this and what am I going to do with this?
It ‘ll take care of your oversized strings so you get to see what your VAR has in it.