4 Replies Latest reply on Jul 26, 2012 8:42 AM by BluShadow

    Defining error code for qualified exception using exception_init pragma

    sudher
      Hi, 

      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.
        • 1. Re: Defining error code for qualified exception using exception_init pragma
          EdStevens
          948744 wrote:
          Hi, 

          I have been experimenting on exception handling in oracle plsql. During my experimentation I made the following anonymous plsql block.

          <snip>

          Did you notice the name of this particular forum?

          Please close this thread and ask your question in SQL and PL/SQL
          • 2. Re: Defining error code for qualified exception using exception_init pragma
            BluShadow
            Question now moved to Oracle Discussion Forums » Oracle Database » SQL and PL/SQL
            • 3. Re: Defining error code for qualified exception using exception_init pragma
              BluShadow
              The problem in your block 2 code is that you've not just used the same exception name, but you've assigned them with the same exception code. If the code is different then it will work...
              SQL> set serverout on
              SQL> ed
              Wrote file afiedt.buf
              
                1  <<outer_block>>
                2  declare
                3      exc exception;
                4      pragma exception_init(exc,-20001);
                5  begin
                6      <<inner_block>>
                7      declare
                8          exc exception;
                9          pragma exception_init(exc,-20002);
               10      begin
               11          raise_application_error(-20002,'Error raised');
               12      exception
               13          when outer_block.exc then
               14              dbms_output.put_line('outer Exception caught ' );
               15          when inner_block.exc then
               16              dbms_output.put_line('Inner Exception caught ' );
               17      end;
               18* end;
              SQL> /
              Inner Exception caught
              
              PL/SQL procedure successfully completed.
              
              SQL> ed
              Wrote file afiedt.buf
              
                1  <<outer_block>>
                2  declare
                3      exc exception;
                4      pragma exception_init(exc,-20001);
                5  begin
                6      <<inner_block>>
                7      declare
                8          exc exception;
                9          pragma exception_init(exc,-20002);
               10      begin
               11          raise_application_error(-20001,'Error raised');
               12      exception
               13          when outer_block.exc then
               14              dbms_output.put_line('outer Exception caught ' );
               15          when inner_block.exc then
               16              dbms_output.put_line('Inner Exception caught ' );
               17      end;
               18* end;
              SQL> /
              outer Exception caught
              
              PL/SQL procedure successfully completed.
              • 4. Re: Defining error code for qualified exception using exception_init pragma
                BluShadow
                Oh, and the reason for that is described here...

                http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/errors.htm#sthref2000

                PRAGMA is a compiler directive, not a run time directive, so you can't re-assign the same code to different exceptions on-the-fly during execution of your code, hence why the code was complaining because it was considering your outer EXC and inner EXC to be the same code and hence you were effectively testing for the same exception value twice, regardless of you prefixing the name with the outer/inner labels.

                You can certainly re-use exception names at run time and that follows standard variable scoping rules, but if you assign exception names to actual error code values, those need to be either unique or, if you've assigned multiple exception names to the same code you need to ensure you only test for one of them within your exception handlers.
                1 person found this helpful