Home

Scope of an exception in PL/SQL

Wed, 2007.05.09 - 10:34 — müzso
The built-in functions SQLCODE and SQLERRM provide information inside an exception handler about the exception itself that is being dealt with. The question is when does the life of an exception end ... or put it the other way around: when does SQLCODE "forget" the error code of the exception and return "ORA-00000" ("normal, successful completion")?

To test that, run the following block:
BEGIN
  BEGIN
    raise_application_error(-20501, '');
  EXCEPTION WHEN OTHERS THEN
    dbms_output.put_line('SQLCODE: ' || SQLCODE || ', SQLERRM: ' || SQLERRM);
    BEGIN
      dbms_output.put_line('SQLCODE: ' || SQLCODE || ', SQLERRM: ' || SQLERRM);
      raise_application_error(-20502, '');
    EXCEPTION WHEN OTHERS THEN
      dbms_output.put_line('SQLCODE: ' || SQLCODE || ', SQLERRM: ' || SQLERRM);
    END;
    dbms_output.put_line('SQLCODE: ' || SQLCODE || ', SQLERRM: ' || SQLERRM);
  END;
  dbms_output.put_line('SQLCODE: ' || SQLCODE || ', SQLERRM: ' || SQLERRM);
END;

The result should be:
SQLCODE: -20501, SQLERRM: ORA-20501:
SQLCODE: -20501, SQLERRM: ORA-20501:
SQLCODE: -20502, SQLERRM: ORA-20502:
SQLCODE: 0, SQLERRM: ORA-0000: normal, successful completion
SQLCODE: 0, SQLERRM: ORA-0000: normal, successful completion

So the answer is: SQLCODE returns the error code of the last exception and is reset to zero upon leaving an exception handler block. Actually I'd better like if the next to the last line in the output of my example whould still show the -20501 error code that is handled in that exception handler. Sad Nobody's perfect. :-/