Exception : ORA-06502: PL/SQL: numeric or value error [message #442182] |
Fri, 05 February 2010 03:57  |
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 #442187 is a reply to message #442182] |
Fri, 05 February 2010 04:13   |
cookiemonster
Messages: 13965 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   |
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 #442190 is a reply to message #442182] |
Fri, 05 February 2010 04:26   |
cookiemonster
Messages: 13965 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   |
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 #442196 is a reply to message #442182] |
Fri, 05 February 2010 04:46   |
cookiemonster
Messages: 13965 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.
|
|
|
|