Home » SQL & PL/SQL » SQL & PL/SQL » Exception : ORA-06502: PL/SQL: numeric or value error (oracle 10g)
Exception : ORA-06502: PL/SQL: numeric or value error [message #442182] Fri, 05 February 2010 03:57 Go to next message
sskuser
Messages: 3
Registered: February 2010
Location: BANGALORE
Junior Member
Dear All,

Please help me to resolve the error : ORA-06502: PL/SQL: numeric or value error

Following is the block of PL/SQL :

DECLARE
TYPE REPORT_ID IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
R REPORT_ID;
reportIdInt number;
REPORT_STRUCTURE_CUR SYS_REFCURSOR;
REPORT_CONFIG_CUR SYS_REFCURSOR;
METADATA_ID NUMBER;
STRUCT_REC REPORT_STRUCTURE%ROWTYPE;
CONFIG_REC REPORT_CONFIGS%ROWTYPE;
PAPERFORMAT_REC REPORT_REQUEST_DETAIL%ROWTYPE;
REPORT_LOGS CLOB;
PAPER_FORMAT VARCHAR2(100);
CONFIG_VALUE VARCHAR2(15);
-- REPORT_LOGS REPORT_LOG_DETAILS := REPORT_LOG_DETAILS();
BEGIN
R(1) := 41;
/* R(2) := 51;
R(3) := 72;
R(4) := 81;*/
FOR i in R.FIRST..R.LAST
LOOP
REPORT_LOGS := 'Report Id : ' ;
OPEN REPORT_STRUCTURE_CUR FOR
SELECT REPORT_ID,REPORT_ELEMENT_ID FROM REPORT_STRUCTURE WHERE REPORT_ID = R(i);
REPORT_LOGS := REPORT_LOGS || ', Report Structure : [';
LOOP

FETCH REPORT_STRUCTURE_CUR INTO STRUCT_REC;
EXIT WHEN REPORT_STRUCTURE_CUR%NOTFOUND;

LOOP

REPORT_LOGS := REPORT_LOGS || STRUCT_REC.REPORT_ELEMENT_ID || ', ';

IF REPORT_STRUCTURE_CUR%NOTFOUND THEN

REPORT_LOGS := REPORT_LOGS || ']' || ', ReportConfig:[(';



END IF;


END LOOP;
CLOSE REPORT_STRUCTURE_CUR;
END LOOP;
OPEN REPORT_CONFIG_CUR FOR
SELECT REPORT_ID,CONFIG_ID,
(CASE
WHEN COLOR_VAL IS NOT NULL THEN COLOR_VAL
WHEN NUMERIC_VAL IS NOT NULL THEN TO_CHAR(NUMERIC_VAL)
WHEN DATE_VAL IS NOT NULL THEN TO_CHAR(DATE_VAL)
WHEN STRING_VAL IS NOT NULL THEN STRING_VAL
END) AS CONFIG_VALUE
FROM REPORT_CONFIGS RC WHERE REPORT_ID= R(i);

SELECT (CASE
WHEN COLOR_VAL IS NOT NULL THEN COLOR_VAL
WHEN NUMERIC_VAL IS NOT NULL THEN TO_CHAR(NUMERIC_VAL)
WHEN DATE_VAL IS NOT NULL THEN TO_CHAR(DATE_VAL)
WHEN STRING_VAL IS NOT NULL THEN STRING_VAL
END) AS CONFIG_VALUE INTO CONFIG_VALUE
FROM REPORT_CONFIGS RC WHERE REPORT_ID= R(i);

LOOP

FETCH REPORT_CONFIG_CUR INTO CONFIG_REC;

EXIT WHEN REPORT_CONFIG_CUR%NOTFOUND;

LOOP

IF CONFIG_REC.CONFIG_ID IS NOT NULL AND CONFIG_VALUE IS NOT NULL THEN
REPORT_LOGS := REPORT_LOGS || CONFIG_REC.CONFIG_ID || ',' || CONFIG_VALUE || ')';


REPORT_LOGS := REPORT_LOGS || '(';
END IF;

IF REPORT_STRUCTURE_CUR%NOTFOUND THEN

REPORT_LOGS := REPORT_LOGS || ']' || '], Paper Format : ';

END IF;

END LOOP;
CLOSE REPORT_CONFIG_CUR;
END LOOP;

SELECT METADATA_ID INTO METADATA_ID FROM REPORT_METADATA WHERE METADATA_NAME='PAPER_FORMAT';

SELECT METADATA_VALUE INTO PAPER_FORMAT FROM REPORT_REQUEST_DETAIL
WHERE REPORT_ID = R(i) AND
METADATA_ID = METADATA_ID;

REPORT_LOGS := REPORT_LOGS || PAPER_FORMAT;

DBMS_OUTPUT.PUT_LINE('RESULT : ' || REPORT_LOGS);

END LOOP;

EXCEPTION
WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;



Re: Exception : ORA-06502: PL/SQL: numeric or value error [message #442186 is a reply to message #442182] Fri, 05 February 2010 04:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Remove the silly WHEN OTHERS clause then you will from which the error comes.

2/ Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

3/ Repost when you will achieve point 1/ and retry (without forgetting to apply point 2/).

Regards
Michel
Re: Exception : ORA-06502: PL/SQL: numeric or value error [message #442187 is a reply to message #442182] Fri, 05 February 2010 04:13 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
That code is unreadable, please repost using code tags - see the orafaq forum guide if you're not sure how - and use indentation.

Also remove the pointless exception handler and you'll get the line number at which the error actually occurs. The solution will probably be obvious at that point.
Re: Exception : ORA-06502: PL/SQL: numeric or value error [message #442188 is a reply to message #442182] Fri, 05 February 2010 04:20 Go to previous messageGo to next message
sskuser
Messages: 3
Registered: February 2010
Location: BANGALORE
Junior Member
Dear All,

I have formatted the PL/SQL block. Please suggest the solution

DECLARE
TYPE report_id IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
R REPORT_ID;
reportidint NUMBER;
report_structure_cur SYS_REFCURSOR;
report_config_cur SYS_REFCURSOR;
metadata_id NUMBER;
struct_rec report_structure%ROWTYPE;
config_rec report_configs%ROWTYPE;
paperformat_rec report_request_detail%ROWTYPE;
report_logs CLOB;
paper_format VARCHAR2(100);
config_value VARCHAR2(15);
BEGIN
R(1) := 41;

FOR i IN r.FIRST.. r.LAST LOOP
report_logs := 'Report Id : ';

OPEN report_structure_cur FOR
SELECT report_id,
report_element_id
FROM report_structure
WHERE report_id = R(i);

report_logs := report_logs
||', Report Structure : [';

LOOP
FETCH report_structure_cur INTO struct_rec;

EXIT WHEN report_structure_cur%NOTFOUND;

LOOP
report_logs := report_logs
||struct_rec.report_element_id
||', ';

IF report_structure_cur%NOTFOUND THEN
report_logs := report_logs
||']'
||', ReportConfig:[(';
END IF;
END LOOP;

CLOSE report_structure_cur;
END LOOP;

OPEN report_config_cur FOR
SELECT report_id,
config_id,
(CASE
WHEN color_val IS NOT NULL
THEN color_val
WHEN numeric_val IS NOT NULL
THEN To_char(numeric_val)
WHEN date_val IS NOT NULL
THEN To_char(date_val)
WHEN string_val IS NOT NULL
THEN string_val
END) AS config_value
FROM report_configs rc
WHERE report_id = R(i);

SELECT (CASE
WHEN color_val IS NOT NULL
THEN color_val
WHEN numeric_val IS NOT NULL
THEN To_char(numeric_val)
WHEN date_val IS NOT NULL
THEN To_char(date_val)
WHEN string_val IS NOT NULL
THEN string_val
END) AS config_value
INTO config_value
FROM report_configs rc
WHERE report_id = R(i);

LOOP
FETCH report_config_cur INTO config_rec;

EXIT WHEN report_config_cur%NOTFOUND;

LOOP
IF config_rec.config_id IS NOT NULL
AND config_value IS NOT NULL THEN
report_logs := report_logs
||config_rec.config_id
||','
||config_value
||')';

report_logs := report_logs
||'(';
END IF;

IF report_structure_cur%NOTFOUND THEN
report_logs := report_logs
||']'
||'], Paper Format : ';
END IF;
END LOOP;

CLOSE report_config_cur;
END LOOP;

SELECT metadata_id
INTO metadata_id
FROM report_metadata
WHERE metadata_name = 'PAPER_FORMAT';

SELECT metadata_value
INTO paper_format
FROM report_request_detail
WHERE report_id = R(i)
AND metadata_id = metadata_id;

report_logs := report_logs
||paper_format;

dbms_output.Put_line('RESULT : '
||report_logs);
END LOOP;
EXCEPTION
dbms_output.Put_line(sqlerrm);
END;

Thanks & Regards,
S.Sendilkumar
Re: Exception : ORA-06502: PL/SQL: numeric or value error [message #442189 is a reply to message #442188] Fri, 05 February 2010 04:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Fri, 05 February 2010 11:11
1/ Remove the silly WHEN OTHERS clause then you will from which the error comes.

2/ Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

3/ Repost when you will achieve point 1/ and retry (without forgetting to apply point 2/).

Regards
Michel


Please read and follow it.
Re: Exception : ORA-06502: PL/SQL: numeric or value error [message #442190 is a reply to message #442182] Fri, 05 February 2010 04:26 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
That's not formatted at all. Formatting means using indentation and code tags.
So this bit:

LOOP
FETCH report_structure_cur INTO struct_rec;

EXIT WHEN report_structure_cur%NOTFOUND;

LOOP
report_logs := report_logs
||struct_rec.report_element_id
||', ';

would look like this:
LOOP
  FETCH report_structure_cur INTO struct_rec;

  EXIT WHEN report_structure_cur%NOTFOUND;

  LOOP
    report_logs := report_logs
                   ||struct_rec.report_element_id
                   ||', ';


And you still need to remove the exception handler, which is a bug, so we can get the line number the error occured at.
Re: Exception : ORA-06502: PL/SQL: numeric or value error [message #442191 is a reply to message #442187] Fri, 05 February 2010 04:26 Go to previous messageGo to next message
sskuser
Messages: 3
Registered: February 2010
Location: BANGALORE
Junior Member
Dear All,

I have formated the above PL/SQL block and removed the exception blocks. I Found the line no which exception happens. I will correct the mistake when i post the message again. Now i am able to correct the errors. Thank you very much for your suggestion.

The corrected and formatted PL/SQL blocks :

   DECLARE
  TYPE report_id IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  R                     REPORT_ID;
  reportidint           NUMBER;
  report_structure_cur  SYS_REFCURSOR;
  report_config_cur     SYS_REFCURSOR;
  metadata_id           NUMBER;
  struct_rec            report_structure%ROWTYPE;
  config_rec            report_configs%ROWTYPE;
  paperformat_rec       report_request_detail%ROWTYPE;
  report_logs           CLOB;
  paper_format          VARCHAR2(100);
  config_value          VARCHAR2(15);
BEGIN
  R(1) := 41;
 
  FOR i IN r.FIRST.. r.LAST LOOP
    report_logs := 'Report Id : ';
    
    OPEN report_structure_cur FOR
      SELECT report_id,
             report_element_id
      FROM   report_structure
      WHERE  report_id = R(i);
    
    report_logs := report_logs
                   ||', Report Structure : [';
    
    LOOP
      FETCH report_structure_cur INTO struct_rec;
      
      EXIT WHEN report_structure_cur%NOTFOUND;
      
      LOOP
        report_logs := report_logs
                       || to_char(struct_rec.report_element_id)
                       ||', ';
        
        IF report_structure_cur%NOTFOUND THEN
          report_logs := report_logs
                         ||']'
                         ||', ReportConfig:[(';
        END IF;
      END LOOP;
      
      CLOSE report_structure_cur;
    END LOOP;
    
    OPEN report_config_cur FOR
      SELECT report_id,
             config_id,
             (CASE 
                WHEN color_val IS NOT NULL
                THEN color_val
                WHEN numeric_val IS NOT NULL
                THEN To_char(numeric_val)
                WHEN date_val IS NOT NULL
                THEN To_char(date_val)
                WHEN string_val IS NOT NULL
                THEN string_val
              END) AS config_value
      FROM   report_configs rc
      WHERE  report_id = R(i);
    
    SELECT (CASE 
              WHEN color_val IS NOT NULL
              THEN color_val
              WHEN numeric_val IS NOT NULL
              THEN To_char(numeric_val)
              WHEN date_val IS NOT NULL
              THEN To_char(date_val)
              WHEN string_val IS NOT NULL
              THEN string_val
            END) AS config_value
    INTO   config_value
    FROM   report_configs rc
    WHERE  report_id = R(i);
    
    LOOP
      FETCH report_config_cur INTO config_rec;
      
      EXIT WHEN report_config_cur%NOTFOUND;
      
      LOOP
        IF config_rec.config_id IS NOT NULL 
           AND config_value IS NOT NULL THEN
          report_logs := report_logs
                         ||config_rec.config_id
                         ||','
                         ||config_value
                         ||')';
          
          report_logs := report_logs
                         ||'(';
        END IF;
        
        IF report_structure_cur%NOTFOUND THEN
          report_logs := report_logs
                         ||']'
                         ||'], Paper Format : ';
        END IF;
      END LOOP;
      
      CLOSE report_config_cur;
    END LOOP;
    
    SELECT metadata_id
    INTO   metadata_id
    FROM   report_metadata
    WHERE  metadata_name = 'PAPER_FORMAT';
    
    SELECT metadata_value
    INTO   paper_format
    FROM   report_request_detail
    WHERE  report_id = R(i)
           AND metadata_id = metadata_id;
    
    report_logs := report_logs
                   ||paper_format;
    
    dbms_output.Put_line('RESULT : '
                         ||report_logs);
  END LOOP;
END;


Thanks & Regards,
S.Sendilkumar

[You need to enclose formatted code in CODE taqs]

[Updated on: Fri, 05 February 2010 04:32] by Moderator

Report message to a moderator

Re: Exception : ORA-06502: PL/SQL: numeric or value error [message #442194 is a reply to message #442191] Fri, 05 February 2010 04:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
have formated the above PL/SQL block and removed the exception blocks. I Found the line no which exception happens. I will correct the mistake when i post the message again. Now i am able to correct the errors. Thank you very much for your suggestion.

And you don't think it should be useful to post us which line is on error?

And this is still not formatted.
Read and follow the guide, I gave you the link, read it and follow it.

Regards
Michel
Re: Exception : ORA-06502: PL/SQL: numeric or value error [message #442196 is a reply to message #442182] Fri, 05 February 2010 04:46 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Some other issues in the code:
1) both close statements are in the wrong place, should be after the END LOOPs they're currently before. If either of those loops ever goes round a second time you'll get an error as you try to fetch a closed cursor.
2) Really don't see the point of this select:
SELECT (CASE 
              WHEN color_val IS NOT NULL
              THEN color_val
              WHEN numeric_val IS NOT NULL
              THEN To_char(numeric_val)
              WHEN date_val IS NOT NULL
              THEN To_char(date_val)
              WHEN string_val IS NOT NULL
              THEN string_val
            END) AS config_value
    INTO   config_value
    FROM   report_configs rc
    WHERE  report_id = R(i);

Since the ref cursor before it fetches the exact same data.
3) This loop:
LOOP
        report_logs := report_logs
                       || to_char(struct_rec.report_element_id)
                       ||', ';
        
        IF report_structure_cur%NOTFOUND THEN
          report_logs := report_logs
                         ||']'
                         ||', ReportConfig:[(';
        END IF;
      END LOOP;

Is an inifinite loop, as is the equivalent lower down.
Re: Exception : ORA-06502: PL/SQL: numeric or value error [message #442197 is a reply to message #442182] Fri, 05 February 2010 04:47 Go to previous message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
In fact I bet the 6502 was caused by the infinite loop.
Previous Topic: YTD Calculation using analytical functions
Next Topic: can i remove pragma autonomous_transaction in this scenario
Goto Forum:
  


Current Time: Sun Dec 11 06:28:19 CST 2016

Total time taken to generate the page: 0.07012 seconds