Exception management is one of the most important part of all programming languages. Having a good understanding of this topic will help you a lot in debugging. In this thread, I will show you all important points that will make your work easier. (Note that all examples have been implemented in Oracle 19c)
First, let’s see how exception handling works. Basically, all errors between BEGIN and END block will be thrown in EXCEPTION section (excluding DECLARE part).
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 V_NUM NUMBER;
3 BEGIN
4
5 V_NUM := 5 / 0;
6
7 EXCEPTION
8 WHEN ZERO_DIVIDE THEN
9 DBMS_OUTPUT.PUT_LINE('Error 1');
10
11 END;
12 /
Error 1
PL/SQL yordam² ba■ar²yla tamamland².
As you can see from the above example, when an exception is raised in the DECLARE section, it cannot catch by the current block.
Predefined Exceptions
Predefined exceptions are the most common errors that developers can encounter. So, PL/SQL declares globally in the package STANDARD. Also, you can redeclare predefined exceptions.
In case of using multiple exceptions, OTHERS exception should be placed at the end of the exception list in exception handling section. Otherwise, you will encounter PLS-00370 error. It includes all exceptions.
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 V_NUM NUMBER;
3 BEGIN
4
5 V_NUM := 5 / 0;
6
7 EXCEPTION
8 WHEN ACCESS_INTO_NULL THEN
9 DBMS_OUTPUT.PUT_LINE('ACCESS_INTO_NULL');
10
11 WHEN CASE_NOT_FOUND THEN
12 DBMS_OUTPUT.PUT_LINE('CASE_NOT_FOUND');
13
14 WHEN COLLECTION_IS_NULL THEN
15 DBMS_OUTPUT.PUT_LINE('COLLECTION_IS_NULL');
16
17 WHEN CURSOR_ALREADY_OPEN THEN
18 DBMS_OUTPUT.PUT_LINE('CURSOR_ALREADY_OPEN');
19
20 WHEN DUP_VAL_ON_INDEX THEN
21 DBMS_OUTPUT.PUT_LINE('DUP_VAL_ON_INDEX');
22
23 WHEN INVALID_CURSOR THEN
24 DBMS_OUTPUT.PUT_LINE('INVALID_CURSOR');
25
26 WHEN INVALID_NUMBER THEN
27 DBMS_OUTPUT.PUT_LINE('INVALID_NUMBER');
28
29 WHEN LOGIN_DENIED THEN
30 DBMS_OUTPUT.PUT_LINE('LOGIN_DENIED');
31
32 WHEN NO_DATA_FOUND THEN
33 DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND');
34
35 WHEN NO_DATA_NEEDED THEN
36 DBMS_OUTPUT.PUT_LINE('NO_DATA_NEEDED');
37
38 WHEN NOT_LOGGED_ON THEN
39 DBMS_OUTPUT.PUT_LINE('NOT_LOGGED_ON');
40
41 WHEN PROGRAM_ERROR THEN
42 DBMS_OUTPUT.PUT_LINE('PROGRAM_ERROR');
43
44 WHEN ROWTYPE_MISMATCH THEN
45 DBMS_OUTPUT.PUT_LINE('ROWTYPE_MISMATCH');
46
47 WHEN SELF_IS_NULL THEN
48 DBMS_OUTPUT.PUT_LINE('SELF_IS_NULL');
49
50 WHEN STORAGE_ERROR THEN
51 DBMS_OUTPUT.PUT_LINE('STORAGE_ERROR');
52
53 WHEN SUBSCRIPT_BEYOND_COUNT THEN
54 DBMS_OUTPUT.PUT_LINE('SUBSCRIPT_BEYOND_COUNT');
55
56 WHEN SUBSCRIPT_OUTSIDE_LIMIT THEN
57 DBMS_OUTPUT.PUT_LINE('SUBSCRIPT_OUTSIDE_LIMIT');
58
59 WHEN SYS_INVALID_ROWID THEN
60 DBMS_OUTPUT.PUT_LINE('SYS_INVALID_ROWID');
61
62 WHEN TIMEOUT_ON_RESOURCE THEN
63 DBMS_OUTPUT.PUT_LINE('TIMEOUT_ON_RESOURCE');
64
65 WHEN TOO_MANY_ROWS THEN
66 DBMS_OUTPUT.PUT_LINE('TOO_MANY_ROWS');
67
68 WHEN VALUE_ERROR THEN
69 DBMS_OUTPUT.PUT_LINE('VALUE_ERROR');
70
71 WHEN ZERO_DIVIDE THEN
72 DBMS_OUTPUT.PUT_LINE('ZERO_DIVIDE');
73
74 WHEN OTHERS THEN
75 DBMS_OUTPUT.PUT_LINE('OTHERS');
76
77 END;
78 /
ZERO_DIVIDE
PL/SQL yordam² ba■ar²yla tamamland².
User-Defined Exceptions
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 CURSOR C1 IS
3 SELECT LEVEL NUM FROM DUAL CONNECT BY LEVEL < 10;
4
5 E_NUMBER_EQUAL_FIVE EXCEPTION;
6 BEGIN
7
8 FOR REC IN C1
9 LOOP
10 IF REC.NUM = 5 THEN
11 RAISE E_NUMBER_EQUAL_FIVE;
12 ELSE
13 DBMS_OUTPUT.PUT_LINE(REC.NUM);
14 END IF;
15 END LOOP;
16
17 EXCEPTION
18 WHEN E_NUMBER_EQUAL_FIVE THEN
19 DBMS_OUTPUT.PUT_LINE('User defined exception has been thrown');
20
21 END;
22 /
1
2
3
4
User defined exception has been thrown
PL/SQL yordam² ba■ar²yla tamamland².
Besides, you can use RAISE_APPLICATION_ERROR procedure for user defined error codes between -20000 and -20999. EXCEPTION_INIT pragma can be used for exception error code assignment.
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 CURSOR C1 IS
3 SELECT LEVEL NUM FROM DUAL CONNECT BY LEVEL < 10;
4
5 E_NUMBER_EQUAL_FIVE EXCEPTION;
6 PRAGMA EXCEPTION_INIT(E_NUMBER_EQUAL_FIVE, -20001);
7
8 BEGIN
9
10 FOR REC IN C1
11 LOOP
12 IF REC.NUM = 5 THEN
13 RAISE_APPLICATION_ERROR(-20001, 'This is new exception');
14 ELSE
15 DBMS_OUTPUT.PUT_LINE(REC.NUM);
16 END IF;
17 END LOOP;
18
19 EXCEPTION
20 WHEN E_NUMBER_EQUAL_FIVE THEN
21 DBMS_OUTPUT.PUT_LINE('User defined error -20001');
22
23 END;
24 /
1
2
3
4
User defined error -20001
PL/SQL yordam² ba■ar²yla tamamland².
If you don’t catch the exception, it is shown as if system generated error.
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 CURSOR C1 IS
3 SELECT LEVEL NUM FROM DUAL CONNECT BY LEVEL < 10;
4
5 BEGIN
6
7 FOR REC IN C1
8 LOOP
9 IF REC.NUM = 5 THEN
10 RAISE_APPLICATION_ERROR(-20001, 'This is new exception');
11 ELSE
12 DBMS_OUTPUT.PUT_LINE(REC.NUM);
13 END IF;
14 END LOOP;
15
16 END;
17 /
1
2
3
4
DECLARE
*
1 sat²r²nda HATA:
ORA-20001: This is new exception
ORA-06512: konum sat²r 10
ORA-06512: konum sat²r 10
Also, you can handle the code inside OTHERS with SQLCODE. As it can be seen from the below example, PL/SQL block has been executed without giving error.
SQL> SET SERVEROUTPUT ON;
SQL> BEGIN
2
3 RAISE_APPLICATION_ERROR(-20001, 'This is new exception');
4
5 EXCEPTION
6 WHEN OTHERS THEN
7 IF SQLCODE = -20001 THEN
8 DBMS_OUTPUT.PUT_LINE('Error code: '||SQLCODE);
9 DBMS_OUTPUT.PUT_LINE('Error Message: '||SQLERRM);
10 ELSE
11 DBMS_OUTPUT.PUT_LINE('Others');
12 END IF;
13 END;
14 /
Error code: -20001
Error Message: ORA-20001: This is new exception
PL/SQL yordam² ba■ar²yla tamamland².
FORALL with SAVE EXCEPTIONS
CREATE TABLE T_TEST
(
C1 NUMBER
);
DECLARE
TYPE arr_t IS TABLE OF NUMBER;
arr arr_t := arr_t(1, 2, 3, 4, 5);
bulk_errors EXCEPTION;
PRAGMA EXCEPTION_INIT(bulk_errors, -24381);
BEGIN
FORALL ind IN 1..arr.count
SAVE EXCEPTIONS
INSERT INTO T_TEST VALUES(arr(ind));
commit;
EXCEPTION
WHEN bulk_errors THEN
FOR ind IN 1..SQL%BULK_EXCEPTIONS.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(SQL%BULK_EXCEPTIONS(ind).ERROR_CODE);
END LOOP;
END;
Tracing PL/SQL code
In this section, you can find a basic error logging package demonstration. PL/SQL provides a number of functions which give additional information when the exception is raised. Therefore, instead of using SQLCODE and SQLERRM, you can use DBMS_UTILITY.FORMAT_ERROR_STACK and DBMS_UTILITY.FORMAT_ERROR_BACKTRACE functions.
CREATE TABLE T_LOG
(
ID NUMBER,
PROC_NAME VARCHAR2(500),
ERR_MSG VARCHAR2(4000),
CREATEUSER VARCHAR2(500),
CREATEDATE DATE
);
CREATE SEQUENCE SEQ_LOG
START WITH 1
MAXVALUE 9999999999999999999999999999
MINVALUE 0
NOCYCLE
NOCACHE
NOORDER;
CREATE TRIGGER TRI_LOG
BEFORE INSERT ON T_LOG REFERENCING OLD AS "OLD" NEW AS "NEW"
FOR EACH ROW
DECLARE
BEGIN
:NEW.ID := SEQ_LOG.NEXTVAL;
:NEW.CREATEDATE := SYSDATE;
:NEW.CREATEUSER := USER;
END;
/
CREATE OR REPLACE PACKAGE PKG_DEMO
IS
PROCEDURE DOLOG(P_PROC_NAME VARCHAR2, P_MSG VARCHAR2);
PROCEDURE P1;
PROCEDURE P2;
END;
/
CREATE OR REPLACE PACKAGE BODY PKG_DEMO
IS
PROCEDURE DOLOG(P_PROC_NAME VARCHAR2, P_MSG VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
V_ERRTXT VARCHAR2 (4000) := '';
BEGIN
INSERT INTO T_LOG(ERR_MSG, PROC_NAME) VALUES (DOLOG.P_MSG, DOLOG.P_PROC_NAME);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
V_ERRTXT := ' ERRMSG = ' ||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE||CHR(13)||DBMS_UTILITY.FORMAT_ERROR_STACK;
INSERT INTO T_LOG(PROC_NAME, ERR_MSG) VALUES ('DOLOG', V_ERRTXT);
COMMIT;
END DOLOG;
PROCEDURE P1
IS
V_STR VARCHAR2(4000);
BEGIN
SELECT C_TXT INTO V_STR FROM (
SELECT 'Test 1' C_TXT FROM DUAL
UNION ALL
SELECT 'Test 2' FROM DUAL
);
EXCEPTION
WHEN OTHERS THEN
DOLOG(
P_PROC_NAME => DBMS_UTILITY.FORMAT_CALL_STACK,
P_MSG => DBMS_UTILITY.FORMAT_ERROR_BACKTRACE||CHR(13)||DBMS_UTILITY.FORMAT_ERROR_STACK
);
END P1;
PROCEDURE P2
IS
BEGIN
P1;
EXCEPTION
WHEN OTHERS THEN
DOLOG(
P_PROC_NAME => DBMS_UTILITY.FORMAT_CALL_STACK,
P_MSG => DBMS_UTILITY.FORMAT_ERROR_BACKTRACE||CHR(13)||DBMS_UTILITY.FORMAT_ERROR_STACK
);
END P2;
END;
/
Let’s execute our demo package.
SQL> BEGIN
2 PKG_DEMO.P1;
3 END;
4 /
PL/SQL yordam² ba■ar²yla tamamland².
SQL> SET LINESIZE 200
SQL> COLUMN PROC_NAME FORMAT A100
SQL> COLUMN ERR_MSG FORMAT A100
SQL>
SQL> SELECT PROC_NAME FROM T_LOG;
PROC_NAME
----------------------------------------------------------------------------------------------------
----- PL/SQL Call Stack -----
object line object
handle number name
0xc8aec3d0 34 package body TEST_USER.PKG_DEMO.P1
0x4fa6d9f70 2 anonymous block
SQL>
SQL> SELECT ERR_MSG FROM T_LOG;
ERR_MSG
----------------------------------------------------------------------------------------------------
ORA-06512: konum "TEST_USER.PKG_DEMO", sat²r 26
ORA-01422: tam okuma istenilenden daha fazla say²da sat²r d÷nd³r³r
So, we can view all errors from our T_LOG table. It becomes much easier to find bugs in the application code.