ORA-06502: PL/SQL: numeric or value error
I am getting 'ORA-06502: PL/SQL: numeric or value error' in the following statement:
ls_message_body2 := ls_message_body2 ||
lr_log_dtl.object_id || CHR(9) ||
lr_log_dtl.field1 || CHR(9) ||
lr_log_dtl.field2 || CHR(9) ||
lr_log_dtl.field3 || CHR(9) ||
lr_log_dtl.field4 || CHR(9) ||
lr_log_dtl.field5 || CHR(13)
Datatype of ls_message_body2 is LONG. All the others are VARCHAR2.
And this error is specifically caused by 'lr_log_dtl.object_id || CHR(9) ||'! If I comment out this part of the statement, the job runs fine.
Any ideas what might be causing the problem?
Oracle version: 22.214.171.124.3
And this code is in a stored procedure.
I have dug up the Oracle manual and this is what it says about this error.
I don't have any idea how to fix it though!
ORA-06502, "PL/SQL: numeric or value error"
Cause: The valid range for the threshold argument to the Similar( ) function is from 0.0 to 100.0.
Action: Correct the statement and try again.
This is usually overflow.
Check lengths of all the lr_log_dtl.* variables.
The maximum size of a LONG variable is 32760 bytes.
The whole difference between a little boy and an adult man is the price of toys
Are you sure is lr_log_dtl.object_id of VARCHAR2?
or what is the size of fields? field1 to field5. sum it up (field1+2+3+4+5+ object_id), it might be too large and because of that it is overflowing..
2 ls_message_body2 long DEFAULT ' Hello Long ' ;
3 object_id varchar2(10) DEFAULT ' Object Id' ;
4 field1 varchar2(10) DEFAULT ' f1 ' ;
5 field2 varchar2(10) DEFAULT ' f2 ' ;
6 field3 varchar2(10) DEFAULT ' f3 ' ;
7 field4 varchar2(10) DEFAULT ' f4 ' ;
8 field5 varchar2(10) DEFAULT ' f5 ' ;
9 nCnt number default 0;
12 ls_message_body2 := ls_message_body2 ||
13 object_id || CHR(9) ||
14 field1 || CHR(9) ||
15 field2 || CHR(9) ||
16 field3 || CHR(9) ||
17 field4 || CHR(9) ||
18 field5 || CHR(13);
20 if ncnt = 3 then
23 ncnt := ncnt + 1;
25 end if;
26 end loop;
Hello Long Object Id f1 f2 f3 f4 f5
Object Id f1 f2 f3 f4
Object Id f1 f2 f3 f4 f5
Object Id f1 f2 f3 f4 f5
PL/SQL procedure successfully completed.
Last edited by Sameer; 11-27-2002 at 06:19 AM.
Are you sure you want chr(9) concatenated, or are you looking for chr(10) - the line return?
thanks for those of you gave me the hints, and i m sorry i m posting a bit here on this!
i did found out the cause of the error, which was that 'ls_message_body2' is of type LONG, and it was getting populated in a loop, with each record putting its data into the variable. and well, the limit of LONG is 32760 in PL/SQL. so because of much data the max limit was getting reached.
for now, i have put a bux fix and later i m thinking of using a CLOB.
Again, did you really mean to use chr(9)?
SQL> select 'what is '||chr(9)||'?' from dual;
what is ?
Last edited by stecal; 12-03-2002 at 01:14 PM.
Click Here to Expand Forum to Full Width