Test Code:

DECLARE BEGIN <<test0>> -- most normal way to handle exception. DECLARE except_test0 EXCEPTION; BEGIN RAISE except_test0; EXCEPTION WHEN except_test0 THEN dbms_output.put_line('test0 except_test0: SQLCODE=' || SQLCODE || ', SQLERRM=' || SQLERRM); WHEN OTHERS THEN dbms_output.put_line('test0 OTHERS: SQLCODE=' || SQLCODE || ', SQLERRM=' || SQLERRM); END; <<test1>> -- custom exception error number. DECLARE except_test1 EXCEPTION; -- suggested error number range: -20,NNN. PRAGMA EXCEPTION_INIT(except_test1, -20001); BEGIN RAISE except_test1; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('test1: SQLCODE=' || SQLCODE || ', SQLERRM=' || SQLERRM); END; <<test2>> -- custom exception error number and error message. BEGIN raise_application_error(-20002, 'except test 2'); EXCEPTION WHEN OTHERS THEN IF SQLCODE = -20002 THEN dbms_output.put_line('test2A: SQLCODE=' || SQLCODE || ', SQLERRM=' || SQLERRM); dbms_output.put_line('test2B: SQLCODE=' || SQLCODE || ', SQLERRM=' || SQLERRM); ELSE dbms_output.put_line('test2C: SQLCODE=' || SQLCODE || ', SQLERRM=' || SQLERRM); END IF; END; -- SQLCODE and SQLERRM will be re evaluated after EXCEPTION handled. dbms_output.put_line('test2D: SQLCODE=' || SQLCODE || ', SQLERRM=' || SQLERRM); <<test3>> -- custom exception error number and error message, more readable. DECLARE except_test3 EXCEPTION; PRAGMA EXCEPTION_INIT(except_test3, -20001); BEGIN raise_application_error(-20001, 'except test 3'); EXCEPTION WHEN except_test3 THEN dbms_output.put_line('test3 except_test3: SQLCODE=' || SQLCODE || ', SQLERRM=' || SQLERRM); WHEN OTHERS THEN dbms_output.put_line('test3 OTHERS: SQLCODE=' || SQLCODE || ', SQLERRM=' || SQLERRM); END; <<test4>> -- exception can be re raised. BEGIN RAISE no_data_found; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('test4: SQLCODE=' || SQLCODE || ', SQLERRM=' || SQLERRM); RAISE; END; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('outer: SQLCODE=' || SQLCODE || ', SQLERRM=' || SQLERRM); END;

 

Output:

test0 except_test0: SQLCODE=1, SQLERRM=User-Defined Exception test1: SQLCODE=-20001, SQLERRM=ORA-20001: test2A: SQLCODE=-20002, SQLERRM=ORA-20002: except test 2 test2B: SQLCODE=-20002, SQLERRM=ORA-20002: except test 2 test2D: SQLCODE=0, SQLERRM=ORA-0000: normal, successful completion test3 except_test3: SQLCODE=-20001, SQLERRM=ORA-20001: except test 3 test4: SQLCODE=100, SQLERRM=ORA-01403: no data found outer: SQLCODE=100, SQLERRM=ORA-01403: no data found