ORA-06502: PL/SQL: numeric or value error
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

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

  1. #1
    Join Date
    Jul 2001
    Tokyo, Japan.

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

    Rhitu Thakur.

  2. #2
    Join Date
    Jul 2001
    Tokyo, Japan.

    Error message.

    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.

  3. #3
    Join Date
    Apr 2001
    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

  4. #4
    Join Date
    Sep 2001
    Düsseldorf, Germany.
    Are you sure is lr_log_dtl.object_id of VARCHAR2?

    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);
     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  /
    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.
    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..
    Last edited by Sameer; 11-27-2002 at 06:19 AM.

  5. #5
    Join Date
    May 2002
    Are you sure you want chr(9) concatenated, or are you looking for chr(10) - the line return?

  6. #6
    Join Date
    Jul 2001
    Tokyo, Japan.

    the bug!


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

  7. #7
    Join Date
    May 2002
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width