Defining error code for qualified exception using exception_init pragma
sudherJul 25 2012 — edited Jul 26 2012Hi,
I have been experimenting on exception handling in oracle plsql. During my experimentation I made the following anonymous plsql block.
+<<outer_block>>+
declare
+ exc exception; +
begin
+ <<inner_block>>+
+ declare+
+ exc exception; +
+ begin+
+ raise outer_block.exc;+
+ exception +
+ when outer_block.exc then+
+ dbms_output.put_line('outer Exception caught ' );+
+ when inner_block.exc then+
+ dbms_output.put_line('Inner Exception caught ' );+
+ end;+
end;
When I executed the code, I got the output "outer Exception caught ".
------------------------------------------------- PLSQL Block 2 -------------------------------------------
I changed the code a little bit differently by assigning error codes to the exceptions.
+<<outer_block>>+
declare
+ exc exception;+
+ pragma exception_init(exc,-20001);+
begin
+ <<inner_block>>+
+ declare+
+ exc exception;+
+ pragma exception_init(exc,-20001);+
+ begin+
+ raise_application_error(-20001,'Error raised');+
+ exception +
+ when outer_block.exc then+
+ dbms_output.put_line('outer Exception caught ' );+
+ when inner_block.exc then+
+ dbms_output.put_line('Inner Exception caught ' );+
+ end;+
end;
When I executed the above code, I got the following error.
Error at line 1
ORA-06550: line 15, column 9:
PLS-00484: redundant exceptions 'EXC' and 'EXC' must appear in same exception handler
ORA-06550: line 5, column 1:
PL/SQL: Statement ignored
Script Terminated on line 21.
------------------------------------------------- PLSQL Block 3 -------------------------------------------
To avoid the error, I modified the code again by qualifying the exceptions with their block names. This time, I got a different error.
+<<outer_block>>+
declare
+ exc exception;+
+ pragma exception_init(outer_block.exc,-20001);+
begin
+ <<inner_block>>+
+ declare+
+ exc exception;+
+ pragma exception_init(inner_block.exc,-20001);+
+ begin+
+ raise_application_error(-20001,'Error raised');+
+ exception +
+ when outer_block.exc then+
+ dbms_output.put_line('outer Exception caught ' );+
+ when inner_block.exc then+
+ dbms_output.put_line('Inner Exception caught ' );+
+ end;+
end;
Error at line 1
ORA-06550: line 4, column 38:
PLS-00103: Encountered the symbol "." when expecting one of the following:
) , =>
The symbol ", was inserted before "." to continue.
ORA-06550: line 9, column 42:
PLS-00103: Encountered the symbol "." when expecting one of the following:
) , =>
The symbol ", was inserted before "." to continue.
Question:
Could multiple exceptions with same exception name defined across nested plsql blocks be assigned Error codes using EXCEPTION_INIT pragma? If there are errors in PLSQL blocks 2 and 3, kindly suggest.
If the same could be accomplished by some other methods, kindly explain.