PL/SQL Exception Handling in All Details

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.

Leave a Reply

Your email address will not be published.