Oracle PL/SQL EXCEPTION

Test Code:

1. DECLARE
2. BEGIN
4. <Test0>
5. -- Most normal way to handle exception.
6. DECLARE
7. Except_test0 EXCEPTION;
8. BEGIN
9. RAISE effect_test0;
10. EXCEPTION
11. WHEN effect_test0 THEN
12. Dbms_output.put_line ('Test0 effect_test0: SQLCODE ='| SQLCODE |
13. ', SQLERRM ='| SQLERRM );
14. WHEN OTHERS THEN
15. Dbms_output.put_line ('Test0 OTHERS: SQLCODE ='| SQLCODE |
16. ', SQLERRM ='| SQLERRM );
17. END;
19. <Test1>
20. -- Custom exception error number.
21. DECLARE
22. Except_test1 EXCEPTION;
23. -- Suggested error number range:-20, NNN.
24. PRAGMA EXCEPTION_INIT (effect_test1,-20001 );
25. BEGIN
26. RAISE effect_test1;
27. EXCEPTION
28. WHEN OTHERS THEN
29. Dbms_output.put_line ('Test1: SQLCODE ='| SQLCODE |', SQLERRM ='|
30. SQLERRM );
31. END;
33. <Test2>
34. -- Custom exception error number and error message.
35. BEGIN
36. Raise_application_error (-20002,'Couldn t test 2');
37. EXCEPTION
38. WHEN OTHERS THEN
39. If SQL code =-20002
40. THEN
41. Dbms_output.put_line ('Test2a: SQLCODE ='| SQLCODE |', SQLERRM ='|
42. SQLERRM );
43. Dbms_output.put_line ('Test2b: SQLCODE ='| SQLCODE |', SQLERRM ='|
44. SQLERRM );
45. ELSE
46. Dbms_output.put_line ('Test2c: SQLCODE ='| SQLCODE |', SQLERRM ='|
47. SQLERRM );
48. End if;
49. END;
51. -- SQLCODE and SQLERRM will be re evaluated after EXCEPTION handled.
52. Dbms_output.put_line ('Test2d: SQLCODE ='| SQLCODE |', SQLERRM ='|
53. SQLERRM );
55. <Test3>
56. -- Custom exception error number and error message, more readable.
57. DECLARE
58. Except_test3 EXCEPTION;
59. PRAGMA EXCEPTION_INIT (effect_test3,-20001 );
60. BEGIN
61. Raise_application_error (-20001,'Couldn t test 3');
62. EXCEPTION
63. WHEN effect_test3 THEN
64. Dbms_output.put_line ('Test3 effect_test3: SQLCODE ='| SQLCODE |
65. ', SQLERRM ='| SQLERRM );
66. WHEN OTHERS THEN
67. Dbms_output.put_line ('Test3 OTHERS: SQLCODE ='| SQLCODE |
68. ', SQLERRM ='| SQLERRM );
69. END;
71. <Test4>
72. -- Exception can be re raised.
73. BEGIN
74. RAISE no_data_found;
75. EXCEPTION
76. WHEN OTHERS THEN
77. Dbms_output.put_line ('Test4: SQLCODE ='| SQLCODE |', SQLERRM ='|
78. SQLERRM );
79. RAISE;
80. END;
82. EXCEPTION
83. WHEN OTHERS THEN
84. Dbms_output.put_line ('Outer: SQLCODE ='| SQLCODE |', SQLERRM ='|
85. SQLERRM );
86. END;

Output:

1. Test0 effect_test0: SQLCODE = 1, SQLERRM = User-Defined Exception
2. Test1: SQLCODE =-20001, SQLERRM = ORA-20001:
3. Test2A: SQLCODE =-20002, SQLERRM = ORA-20002: Counter T test 2
4. Test2B: SQLCODE =-20002, SQLERRM = ORA-20002: Counter T test 2
5. Test2D: SQLCODE = 0, SQLERRM = ORA-0000: normal, successful completion
6. Test3 effect_test3: SQLCODE =-20001, SQLERRM = ORA-20001: couldn t test 3
7. Test4: SQLCODE = 100, SQLERRM = ORA-01403: no data found
8. Outer: SQLCODE = 100, SQLERRM = ORA-01403: no data found