Home » SQL & PL/SQL » SQL & PL/SQL » Store big data into variable (CLOB with error ORA-06502)
Store big data into variable [message #436760] Sun, 27 December 2009 22:37 Go to next message
meim
Messages: 35
Registered: July 2009
Member
Hi,

I am using CLOB to store some data to be send via email.
i keep getting error msg ->> ORA-06502 :Text: PL/SQL: numeric or value error

As found this happen "when you try to assign a value to a numeric variable but the value is larger than the variable can handle"*

the data i set is really too big, you can say the info of 200 employee.

my questions:
1- is there any datatype is bigger that CLOB ?
2- is there any way to find out the capacity of the CLOB variable during the run time so i can fill in another variable ?
3- any suggestion to solve this ?

well, currently am thinking about send the info as attachment, this can be good idea for the first time i run the procedure when i will have big number, however, the next time will may have not more than 20-50 employee which can be handle by CLOB.


Regards,
Meim

Re: Store big data into variable [message #436761 is a reply to message #436760] Sun, 27 December 2009 22:44 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>i keep getting error msg ->> ORA-06502 :Text: PL/SQL: numeric or value error
>As found this happen "when you try to assign a value to a numeric variable but the value is larger than the variable can handle"*

WRONG!
ORA-6502 results from 1 of 2 possible problems with version less than V10 ( as in V7, V8, or V9)
1) by assigning non-numeric to a NUMBER
2) by assigning a character string which is longer than the VARCHAR2 length.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

Use CUT & PASTE to show us exactly what you do & how Oracle responds
Re: Store big data into variable [message #436764 is a reply to message #436761] Sun, 27 December 2009 23:36 Go to previous messageGo to next message
meim
Messages: 35
Registered: July 2009
Member
It is not the first time i post a topic here, and i don't think am opposing the rules. if you think my info is not enough that can be clarify with discussion - as am think my case is clear!

i didn't post my procedure, because i don't want to fill the page with a code of 490 lines, and i have privet info that i don't want to show.

my case so simple
1- I have big data ( i mean big something can exceed the limit of the variable)
2- I assign it to clob variable
3- i got this error:

ORA-06502: PL/SQL: numeric or value error

i search for the reason and found this
http://dbaspot.com/ora-06502-pl-sql-numeric-or-value-error.html
which look so reasonable to me because i do two tests
1- i assigned part of the data, and no error is showing
2- i assigned the whole data, i got the error

the idea i think is one which is assigning a data bigger than the length of the variable whether it is number, or varchar.

I am not sure if i have the whole info about the system, but i think we are using here v10.

I wish this is clear and am not opposing the rules.

Regards,
Meim.



Re: Store big data into variable [message #436766 is a reply to message #436764] Sun, 27 December 2009 23:58 Go to previous messageGo to next message
meim
Messages: 35
Registered: July 2009
Member
i tried to get the length of the variable after each assign ( since i do that via loop)

the last length i got is 181419 before error
ORA-06502: PL/SQL: numeric or value error

as far as i know CLOB = 4Gig , and 181419 byte still didnt reach the limit, am i right? Sad
Re: Store big data into variable [message #436768 is a reply to message #436766] Mon, 28 December 2009 01:39 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
If i understood your post properly....
Quote:
I am using CLOB to store some data to be send via email

1)Is that the same method you are using in your previous post.
2)
Quote:
1- i assigned part of the data, and no error is showing
2- i assigned the whole data, i got the error

the idea i think is one which is assigning a data bigger than the length of the variable whether it is number, or varchar.

Yes exactly...If you are using utl_raw.cast_to_raw

In plsql the max size for raw datatype is 32767 bytes only.

use this to know the length of the clob you are using ...DBMS_LOB.getlength

see this

Data Types

Let me know if i miss read any data in your post.


Sriram Smile

[Updated on: Mon, 28 December 2009 01:49]

Report message to a moderator

Re: Store big data into variable [message #436771 is a reply to message #436768] Mon, 28 December 2009 02:29 Go to previous messageGo to next message
meim
Messages: 35
Registered: July 2009
Member
Hi ramoradba Smile

1- Yes it is the same, and just found that this procedure is causing the problem while i was thinking it is bcz the CLOB that i assign my data to.

2- is that mean i can't pass large data with utl_raw.cast_to_raw ?

I checked the links you post, thanks a lot, but is the first one gives the solution to pass clob/large data ? i couldn't get the idea of the 2nd procedure there. sorry for this,it is the first time i deal with clob thats why i feel confused a bit.

thanks ramoradba , i will check again with all things you post.

Re: Store big data into variable [message #436772 is a reply to message #436771] Mon, 28 December 2009 03:08 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
This willhelp you

sriram Smile
Re: Store big data into variable [message #436873 is a reply to message #436772] Tue, 29 December 2009 05:17 Go to previous message
meim
Messages: 35
Registered: July 2009
Member
Thanks a lot sriram for your help.

Since utl_raw.cast_to_raw cant carry more than 32 KB, so i just divide my data into two variables and repeat utl_raw.cast_to_raw statement twice to carry the two variables.


Regards.

Previous Topic: Execute UNIX script from PL/SQL procedure
Next Topic: Check Constraints with Date Ranges !!! URGENT
Goto Forum:
  


Current Time: Thu Sep 29 07:20:31 CDT 2016

Total time taken to generate the page: 0.13111 seconds