Home » SQL & PL/SQL » SQL & PL/SQL » ORA-06502: PL/SQL: numeric or value error while using CLOB (Oracle 10g)
ORA-06502: PL/SQL: numeric or value error while using CLOB [message #415758] Wed, 29 July 2009 03:00 Go to next message
Azure_Sky
Messages: 3
Registered: December 2005
Junior Member
I am using a clob for keeping log while running a proc.
We append logging information to this clob. Something like this:

clob_log := clob_log ||'To be updated for: '||tab_id(j)||' and '||tab_cd(j)||'^'

And at the end of the proc we write it to a table.

But sometimes for some runs I get an error
ORA-06502: PL/SQL: numeric or value error

The procedure is breaking due to the line where we are inserting data to the clob.

Any idea why this may be happening. Could it be because the log info is exceeding the size of the CLOB.

Thanks!!
Re: ORA-06502: PL/SQL: numeric or value error while using CLOB [message #415762 is a reply to message #415758] Wed, 29 July 2009 03:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Without the rest of the code it is hard to say.
First try with:
clob_log := /* clob_log || */'To be updated for: '||tab_id(j)||' and '||tab_cd(j)||'^'
Then you will know if it is a CLOB length problem (which limit is 4GB).

Regards
Michel

Re: ORA-06502: PL/SQL: numeric or value error while using CLOB [message #416171 is a reply to message #415758] Thu, 30 July 2009 23:58 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
try something like this too

declare
   clob_v clob := ' ';
   i number;
begin
   clob_v := 'x';
   i := 0;
   loop
      i := i + 1;
      clob_v := clob_v||'x';
      if i > 1024*1024*1024*4 then exit; end if;
   end loop;
exception when others then raise_application_error(-20999,'i='||i);
end;
/

This should fail when you reach your limit (i will actually be the last successful value I think). I suspect the issue is how plsql may be treating your clob. I think the basic limit in plsql for a clob using simple string operations is 32K not 4G and that to work with clobs larger than that you need to use the dbms_lob functions.

Then again maybe you are using 11g and there were clob improvements I have not worked with yet. Or it may be that not having worked directly with clobs for a year now I have forgotten some details and got it wrong. My mind grows weak with age.

Good luck, Kevin
Previous Topic: procedure probleam
Next Topic: Need Help for better Performance of the Package
Goto Forum:
  


Current Time: Tue Dec 06 04:15:08 CST 2016

Total time taken to generate the page: 0.22953 seconds