buffer overflow - ora 06502 in plsql function when returning a clob
728719Oct 19 2009 — edited Oct 20 2009hi,
im really stuck with a plsql function i wrote, and hope somebody is having any new ideas to help me!
the function's structure is roughly that one:
create or replace function x (y in varchar2, z in varchar2)
return clob is
retstr clob;
begin
--> add values to retstr, up to around 6000 bytes
return retstr;
end;
the function works fine, as long as i return less than 4000 bytes, everything above that gives me the error:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1
"select x('abc','def') from dual" gives me always perfect results, no matter which size the return variable is..
the problems start when i try to build the function's return value in into a select statement
I know that SQL has different limits on variables than PLSQL has.
But as far as i know a clob is also larger than 4000 bytes in sql..
is there any buffer that might need to be reset?