value_error EXCEPTION;In other words, we declare an exception with the same name as an exception defined in the STANDARD package of Oracle (and thus can be referenced without a package qualifier name, as in STANDARD.VALUE_ERROR). By doing so, if Oracle raises its VALUE_ERROR exception, a WHEN clause like
WHEN VALUE_ERRORwill not trap the exception. Tricky, yes....and at least one player had some comments on it, so take a look...
Hi Finn:
ReplyDeleteThis was really very tricky and intelligent quiz.
I never tried this and came to know now that when the name of predefined exception is used to declare user-defined exception, it actually masks the effect of predefined exception. That means it takes precedence over predefined exception. This is obvious but never thought and tried declaring user-defined exception with the name of predefined exception.
The code runs fine if you comment the line "value_error exception" or use "others" exception handler in exception section.
I tried to enclose FOR LOOP in it's own block with it's own value_error exception using exception_init and the result was as I expected.
Here is the code that I tried:
====================================================================
DECLARE
value_error EXCEPTION;
TYPE dual_tt IS TABLE OF SYS.dual%ROWTYPE;
l_rows dual_tt;
BEGIN
SELECT *
BULK COLLECT INTO l_rows
FROM SYS.dual
WHERE 1 = 2;
DECLARE
value_error EXCEPTION;
PRAGMA EXCEPTION_INIT(value_error, -6502);
BEGIN
FOR indx IN l_rows.FIRST .. l_rows.LAST
LOOP
DBMS_OUTPUT.put_line (l_rows (indx).dummy);
END LOOP;
EXCEPTION
WHEN value_error
THEN
DBMS_OUTPUT.put_line ('Exception trapped in FOR LOOP!');
END;
EXCEPTION
WHEN value_error
THEN
DBMS_OUTPUT.put_line ('Exception trapped!');
END;
/
Exception trapped in FOR LOOP!
PL/SQL procedure successfully completed.
====================================================================
Also, was curious why value_error is encountering and got to know it's because l_rows.FIRST and l_rows.LAST have values NULL. I didn't came across a situation where the index of a collection is NULL, so didn't have an idea that if index is NULL value_error is thrown.
Tried the following code and it threw "Subscript beyond count" error as expected.
====================================================================
DECLARE
-- value_error EXCEPTION;
TYPE dual_tt IS TABLE OF SYS.dual%ROWTYPE;
l_rows dual_tt;
BEGIN
SELECT *
BULK COLLECT INTO l_rows
FROM SYS.dual
WHERE 1 = 2;
FOR indx IN NVL(l_rows.FIRST, 1) .. NVL(l_rows.LAST, 1)
LOOP
DBMS_OUTPUT.put_line (l_rows (indx).dummy);
END LOOP;
EXCEPTION
WHEN value_error
THEN
DBMS_OUTPUT.put_line ('Exception trapped!');
END;
/
DECLARE
*
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at line 13
====================================================================
Tried to convert the TYPE to associative array and got the error "no data found", again as expected.
====================================================================
DECLARE
-- value_error EXCEPTION;
TYPE dual_tt IS TABLE OF SYS.dual%ROWTYPE INDEX BY BINARY_INTEGER;
l_rows dual_tt;
BEGIN
SELECT *
BULK COLLECT INTO l_rows
FROM SYS.dual
WHERE 1 = 2;
FOR indx IN NVL(l_rows.FIRST, 1) .. NVL(l_rows.LAST, 1)
LOOP
DBMS_OUTPUT.put_line (l_rows (indx).dummy);
END LOOP;
EXCEPTION
WHEN value_error
THEN
DBMS_OUTPUT.put_line ('Exception trapped!');
END;
/
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 12
====================================================================
Very good question indeed. Kudos to you.
Best regards,
Ramesh