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: 8.1.7.0.3
And this code is in a stored procedure.

:confused:

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.

Are you sure is lr_log_dtl.object_id of VARCHAR2?

Code:

---

SQL> declare
  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;
 10  begin
 11  loop
 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);
 19 
 20  if ncnt = 3 then
 21  exit;
 22  else
 23    ncnt := ncnt + 1;
 24    dbms_output.put_line(ncnt);
 25  end if;
 26  end loop;
 27  dbms_output.put_line(ls_message_body2);
 28  end;
 29  /
1
2
3
Hello Long  Object Id    f1      f2      f3      f4      f5
 Object Id  f1      f2      f3      f4   
f5
 Object Id  f1      f2      f3      f4      f5
 Object Id  f1      f2      f3      f4      f5


PL/SQL procedure successfully completed.

SQL>

---

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..

Are you sure you want chr(9) concatenated, or are you looking for chr(10) - the line return?

well,

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)?

Code:

---

SQL> select 'what is '||chr(9)||'?' from dual;

'WHATIS'||
----------
what is      ?

---