CODEWITHSUNDEEP
oracle-databaseplsql
user3701737
user3701737

Reputation: 11

Can we raise an exception within an exception?

Can we raise an exception within an exception, like RAISE my_exception within an WHEN OTHERS exception?

Additionally, I am trying to understand the output in the following 2 scenarios: (Note that the only difference is the ordering of the exceptions)

Scenario 1

DECLARE
     lc_exception Exception;
     var1 number;
BEGIN
     select 1
     into var1
     from dual
     where 1=2;
EXCEPTION
     when lc_exception then
       dbms_output.put_line('This is my exception');

     when no_data_found then
       raise lc_exception;

     when others then
        dbms_output.put_line('There could be some other exception');
END;

Scenario 2

DECLARE
     lc_exception Exception;
     var1 number;
BEGIN
     select 1
     into var1
     from dual
     where 1=2;
EXCEPTION
     when no_data_found then
       raise lc_exception; 

     when lc_exception then
       dbms_output.put_line('This is my exception');

     when others then
        dbms_output.put_line('There could be some other exception');
END;

Upvotes: 1

Views: 3371

Answers (2)

TommCatt
TommCatt

Reputation: 5636

You just need an exception handler to catch your local exception. If you raise an exception in an exception handler, it cannot be caught in the same exception hander.

DECLARE
  lc_exception EXCEPTION;
  var1 number;
BEGIN
  BEGIN
    SELECT 1
    INTO var1
    FROM dual
    WHERE 1=2;
  EXCEPTION -- this is where the exception will be raised
    WHEN no_data_found THEN 
      raise lc_exception;
  END;

  dbms_output.put_line( 'This will never be executed.' );

EXCEPTION -- this is where the exception will be caught
  WHEN lc_exception THEN
    dbms_output.put_line( 'This is my exception' );

  WHEN others THEN
    dbms_output.put_line( 'There could be some other exception' );
END;

However, you may want to consider a slightly different method.

DECLARE
  lc_exception EXCEPTION;
  PRAGMA EXCEPTION_INIT( lc_exception, -20222 );
  var1 NUMBER;
BEGIN
  BEGIN
    SELECT 1
    INTO var1
    FROM dual
    WHERE 1=2;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN 
    RAISE_APPLICATION_ERROR( -20222, 'This is my exception' );
  END;

  DBMS_OUTPUT.PUT_LINE( 'This will never be executed.' );

EXCEPTION
    WHEN lc_exception THEN
      DBMS_OUTPUT.PUT_LINE( SQLERRM );

    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE( 'Some other exception: ' || SQLERRM );
END;

Upvotes: 1

user272735
user272735

Reputation: 10648

Both scenarios have the similar error stack:

ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at line 20
ORA-01403: no data found

The only difference is the exact location (line 20 vs. 18) where the user defined exception is raised.

  1. The select matches no rows thus no_data_found is raised by Oracle - ORA-01403: no data found in the error stack.
  2. The exception is caught by the block's exception handler block.
  3. In the exception handler an user defined exception lc_exception is raised.
  4. Because the program has no other exception handlers the user defined exception is leaked to host environment (in my case that was sqlplus client) - ORA-06510: PL/SQL: unhandled user-defined exception and program terminates.

The exception propagation is similar in both scenarios because in both cases the same initial exception no_data_found is raised and there is only one exception handling block. Therefore the order of no_data_found and lc_exception handlers doesn't matter.

More detailed explanation about exception handling/propagation is found from: PL/SQL Error Handling in Oracle documentation.

Upvotes: 2

Related Questions