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
    Location
    Tokyo, Japan.
    Posts
    3

    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.

    Rhitu Thakur.

  2. #2
    Join Date
    Jul 2001
    Location
    Tokyo, Japan.
    Posts
    3

    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
    Location
    Czechia
    Posts
    712
    This is usually overflow.
    Check lengths of all the lr_log_dtl.* variables.
    The maximum size of a LONG variable is 32760 bytes.
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  4. #4
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    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..
    Last edited by Sameer; 11-27-2002 at 07:19 AM.

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

  6. #6
    Join Date
    Jul 2001
    Location
    Tokyo, Japan.
    Posts
    3

    the bug!

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

  7. #7
    Join Date
    May 2002
    Posts
    2,645
    Again, did you really mean to use chr(9)?

    Code:
    SQL> select 'what is '||chr(9)||'?' from dual;
    
    'WHATIS'||
    ----------
    what is      ?
    Last edited by stecal; 12-03-2002 at 02: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