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².
Also, in order to give a name to an unnamed error, check out the following code snippet. Make sure to declare a valid error number for this.
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 V_DATE DATE;
3 INVALID_DATE_FORMAT EXCEPTION;
4 PRAGMA EXCEPTION_INIT(INVALID_DATE_FORMAT, -1830);
5 BEGIN
6
7 V_DATE := TO_DATE('08/08/2022', 'DD/YYYY');
8
9 EXCEPTION
10 WHEN INVALID_DATE_FORMAT THEN
11 DBMS_OUTPUT.PUT_LINE('INVALID_DATE_FORMAT');
12 END;
13 /
INVALID_DATE_FORMAT
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
I demonstrate three different examples in order to show how FORALL works with exceptions. In all three cases you will see how the result changes.
SQL> create table t_test
2 (
3 c1 number(2)
4 );
Table created.
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 type t_arr is table of number;
3 arr t_arr := t_arr();
4 BEGIN
5
6 for i in 1..10
7 loop
8 arr.extend;
9 arr(arr.last) := i;
10 end loop;
11
12 arr(5) := 200;
13 arr(6) := 300;
14
15 forall ind in 1..arr.count
16 insert into t_test values(arr(ind));
17
18 END;
19 /
DECLARE
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
ORA-06512: at line 15
SQL> select * from t_test;
no rows selected
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 type t_arr is table of number;
3 arr t_arr := t_arr();
4 BEGIN
5
6 for i in 1..10
7 loop
8 arr.extend;
9 arr(arr.last) := i;
10 end loop;
11
12 arr(5) := 200;
13 arr(6) := 300;
14
15 forall ind in 1..arr.count
16 insert into t_test values(arr(ind));
17
18 EXCEPTION
19 when others then
20 dbms_output.put_line(DBMS_UTILITY.FORMAT_ERROR_STACK);
21 dbms_output.put_line(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
22 END;
23 /
ORA-01438: value larger than specified precision allowed for this column
ORA-06512: at line 15
PL/SQL procedure successfully completed.
SQL> select * from t_test;
C1
----------
1
2
3
4
SQL> rollback;
Rollback complete.
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 type t_arr is table of number;
3 arr t_arr := t_arr();
4
5 bulk_errors EXCEPTION;
6 PRAGMA EXCEPTION_INIT(bulk_errors, -24381);
7 BEGIN
8
9 for i in 1..10
10 loop
11 arr.extend;
12 arr(arr.last) := i;
13 end loop;
14
15 arr(5) := 200;
16 arr(6) := 300;
17
18 forall ind in 1..arr.count
19 save exceptions
20 insert into t_test values(arr(ind));
21
22 EXCEPTION
23 WHEN bulk_errors THEN
24 FOR ind IN 1..SQL%BULK_EXCEPTIONS.COUNT
25 LOOP
26 DBMS_OUTPUT.PUT_LINE(SQLERRM(-1 * SQL%BULK_EXCEPTIONS(ind).ERROR_CODE)||' '||SQL%BULK_EXCEPTIONS(ind).ERROR_CODE);
27 DBMS_OUTPUT.PUT_LINE('Error at index '||SQL%BULK_EXCEPTIONS(ind).ERROR_INDEX);
28 END LOOP;
29 end;
30 /
ORA-01438: value larger than specified precision allowed for this column 1438
Error at index 5
ORA-01438: value larger than specified precision allowed for this column 1438
Error at index 6
PL/SQL procedure successfully completed.
SQL> select * from t_test;
C1
----------
1
2
3
4
7
8
9
10
8 rows selected.
SQL> rollback;
Rollback complete.
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.