Exception Handling - II

RAISE / FORMAT_ERROR_STACK / FORMAT_ERROR_BACKTRACE

RAISE will get you the right error message only if that was the last program executed; assume a scenario where the RAISE is placed in a subprogram which does not handle exception: you will lose the error message, FORMAT_ERROR_STACK and FORMAT_ERROR_BACKTRACE will help there

Sample

CREATE OR REPLACE PROCEDURE p1 IS

BEGIN

  dbms_output.put_line('in p1');

  RAISE no_data_found;

EXCEPTION

  WHEN OTHERS THEN

    dbms_output.put_line('From p1:' || 'ERROR' || SQLCODE || ' ' ||

                         substr(SQLERRM, 1, 200));

END;

CREATE OR REPLACE PROCEDURE p2 IS

BEGIN

  dbms_output.put_line('in p2 b4 Calling P1');

  p1;

  dbms_output.put_line('in p2 after Calling P1');

END;

CREATE OR REPLACE PROCEDURE p3 IS

BEGIN

  dbms_output.put_line('in p3 b4 Calling P2');

  p2;

  dbms_output.put_line('in p3 b4 Calling P2');

EXCEPTION

  WHEN OTHERS THEN

    dbms_output.put_line('From P3:' || 'ERROR' || SQLCODE || ' ' ||

                         substr(SQLERRM, 1, 200)); 

END;

Output

in p3 b4 Calling P2

in p2 b4 Calling P1

in p1

From p1:ERROR100 ORA-01403: no data found

in p2 after Calling P1

in p3 b4 Calling P2

When the control comes to P3 we do not know what error happened, Adding a RAISE statement will help.

CREATE OR REPLACE PROCEDURE p1 IS

BEGIN

  dbms_output.put_line('in p1');

  RAISE no_data_found;

EXCEPTION

  WHEN OTHERS THEN

    dbms_output.put_line('From p1:' || 'ERROR' || SQLCODE || ' ' ||

                         substr(SQLERRM, 1, 200));

    dbms_output.put_line(dbms_utility.format_error_stack);

    RAISE;

END;

CREATE OR REPLACE PROCEDURE p2 IS

BEGIN

  dbms_output.put_line('in p2 b4 Calling P1');

  p1;

  dbms_output.put_line('in p2 after Calling P1');

END;

CREATE OR REPLACE PROCEDURE p3 IS

BEGIN

  dbms_output.put_line('in p3 b4 Calling P2');

  p2;

  dbms_output.put_line('in p3 b4 Calling P2');

EXCEPTION

  WHEN OTHERS THEN

    dbms_output.put_line('From P3:' || 'ERROR' || SQLCODE || ' ' ||

                         substr(SQLERRM, 1, 200));

    dbms_output.put_line(dbms_utility.format_error_stack);   

 

END;

Output

in p3 b4 Calling P2

in p2 b4 Calling P1

in p1

From p1:ERROR100 ORA-01403: no data found

ORA-01403: no data found

From P3:ERROR100 ORA-01403: no data found

ORA-01403: no data found

RAISE throws the error. Now FORMAT_ERROR_STACK does not give much information more than SQLERRM, but FORMAT_ERROR_BACKTRACE will give you more information than ERROR_STACK, the output will be similar to the output of the SQLERRM function.

 

CREATE OR REPLACE PROCEDURE p1 IS

BEGIN

  dbms_output.put_line('in p1');

  RAISE no_data_found;

EXCEPTION

  WHEN OTHERS THEN

    dbms_output.put_line('From p1:' || 'ERROR' || SQLCODE || ' ' ||

                         substr(SQLERRM, 1, 200));

    dbms_output.put_line(dbms_utility.format_error_stack);

    RAISE;

END;

CREATE OR REPLACE PROCEDURE p2 IS

BEGIN

  dbms_output.put_line('in p2 b4 Calling P1');

  p1;

  dbms_output.put_line('in p2 after Calling P1');

END;

CREATE OR REPLACE PROCEDURE p3 IS

BEGIN

  dbms_output.put_line('in p3 b4 Calling P2');

  p2;

  dbms_output.put_line('in p3 b4 Calling P2');

EXCEPTION

  WHEN OTHERS THEN

    dbms_output.put_line('From P3:' || 'ERROR' || SQLCODE || ' ' ||

                         substr(SQLERRM, 1, 200));

    dbms_output.put_line(dbms_utility.format_error_stack);

    dbms_output.put_line(dbms_utility.format_error_backtrace);

 

END;

Output

in p3 b4 Calling P2

in p2 b4 Calling P1

in p1

From p1:ERROR100 ORA-01403: no data found

ORA-01403: no data found

From P3:ERROR100 ORA-01403: no data found

ORA-01403: no data found

ORA-06512: at "SCOTT.P1", line 10

ORA-06512: at "SCOTT.P2", line 4

ORA-06512: at "SCOTT.P3", line 4