Exception Handling

An exception handler is a process of trapping an error that occurs while processing a PL/SQL code. Run time error may occur for various reasons like incorrect design, coding mistakes or other kind of hardware failures. You many not anticipate every possible errors but you can plan to handle the error in a user-friendly manner and allows the application to continue without stalling.

Many programming languages display run time errors like stack overflow, division by zero, no_data_found stops the normal processing and returns the control to the operating system. PL/SQL uses a mechanism called Exception Handling that lets you bulletproof your program so that the application can continue with stopping. The EXCEPTION section handles either Oracle Raised Error or a user-defined error.

The examples below will use dept_temp table like my other articles. This table has 4 records, see below the data stored in dept_temp.

Table: dept_temp

    DEPTNO DNAME          LOC       
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK   
        20 RESEARCH       DALLAS     
        30 SALES          CHICAGO     
        40 OPERATIONS     BOSTON     

PRE-DEFINED ORACLE ERROR

Oracle has some common errors referenced as pre-defined errors. These errors have associated exception name within Oracle Database. Oracle supplies a set of predefined exceptions or names associated with common Oracle error. These predefined exceptions are defined in a standard oracle package which makes them available globally on Oracle server.

This example demonstrate how the application stops when the application is not design to handle the error correctly. The dept_temp table does not contain the dept no 27, therefore the application throws an error "no data found" when you run a SELECT statement for deptno 27.

DECLARE
    dept_no NUMBER(4) := 0;
BEGIN
   SELECT deptno INTO dept_no FROM dept_temp
    WHERE  deptno = 27;
END;
/

Error report -
ORA-01403: no data found
ORA-06512: at line 4
01403. 00000 -  "no data found"
*Cause:    No data was found from the objects.
*Action:   There was no data from the objects which may be due to end of fetch.

The above PL/SQL block does not handle the error therefore it terminates and spits out Oracle error code and message. Let's re-write the above code to handle the error that we already know. The code below checks for no data found and too many rows error and handle the appropriately.

Example using pre-defined Oracle error no data found.

SET Serveroutput ON;
DECLARE
  dept_no NUMBER(4) := 270;
BEGIN
  SELECT deptno INTO dept_no FROM dept_temp WHERE deptno = 27;
EXCEPTION
WHEN NO_DATA_FOUND THEN
  DBMS_OUTPUT.PUT_LINE('Record Not Found');
WHEN TOO_MANY_ROWS THEN
  DBMS_OUTPUT.PUT_LINE('Too Many Records Found');
END;
/

Output:

PL/SQL procedure successfully completed.

No Record Found

Example using oracle predefined error too many rows.

DECLARE
 dept_no NUMBER(4) := 0;
BEGIN
  SELECT deptno INTO dept_no FROM dept_temp WHERE deptno IN (20,30);
  IF SQL%FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Records Found, Your Record is: ' || dept_no);
  END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
  DBMS_OUTPUT.PUT_LINE('No Record Found');
WHEN TOO_MANY_ROWS THEN
  DBMS_OUTPUT.PUT_LINE('Too Many Records Found');
END;
/

Output:
PL/SQL procedure successfully completed.

Too Many Records Found

There are tons of predefined Oracle error but some of the most popular ones listed below. You can visit doc.oracle.com for a complete list of predefined errors.

1. DATA_NOT_FOUND
2. TOO_MANY_ROWS
3. ZERO_DIVIDE
4. .... ETC.

NON-PREDEFINED ORACLE ERROR:

There are thousands of Oracle errors and not all errors are predefined exceptions. How do you handle Oracle exceptions that are not predefined?  In such case an exception needs to be declared and an Oracle error number needs to be associated with this exception. Let's see this in an example.

We have a dept_temp with a PK on employee number. This means the table can't accept duplicate employee number for another candidate. The dept_temp table contains dept no 40. See the top of the article to see the record contained on the sample table. You should be guessing already on what error you get when we try to insert a record with dept no that already exists.

Error starting at line : 1 in command -
INSERT INTO dept_temp
  (deptno, dname, loc
  ) VALUES
  (40,'CS', 'St Louis'
  )
Error report -
SQL Error: ORA-00001: unique constraint (PRABIN.DEPTNO_PK) violated
00001. 00000 -  "unique constraint (%s.%s) violated"
*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
           For Trusted Oracle configured in DBMS MAC mode, you may see
           this message if a duplicate entry exists at a different level.
*Action:   Either remove the unique restriction or do not insert the key.


We got SQL Error ORA-00001. i.e. Unique Constraint Violation. This is not on a list of predefined exceptions. The examples below illustrates how you can catch errors that are not part of  predefined exceptions.

Example of non predefined exception handling with PRAGMA statement.

 set serveroutput on;
 DECLARE
 primary_key_violation EXCEPTION;
 PRAGMA EXCEPTION_INIT(primary_key_violation, -00001);
 BEGIN
 INSERT INTO dept_temp
  (deptno, dname, loc
  ) VALUES
  (40,'CS', 'St Louis'
  );
 Exception
 WHEN primary_key_violation THEN
 DBMS_OUTPUT.PUT_LINE('PK violation, please check your data!');
 END;
 /

Output:
PL/SQL procedure successfully completed.
PK violation, please check your data!

Example of non predefined exception handling without PRAGMA statement.

SET serveroutput ON;
DECLARE
  table_update_exception EXCEPTION;
BEGIN
  UPDATE dept_temp SET deptno = 40 WHERE deptno = 51;
  IF SQL%NOTFOUND THEN
    RAISE table_update_exception;
  END IF;
EXCEPTION
WHEN table_update_exception THEN
  DBMS_OUTPUT.PUT_LINE('No Data found for update!');
END;
/

Output:
PL/SQL procedure successfully completed.
No Data found for update!

WHEN OTHER EXCEPTION

Oracle is a complex relational database management system therefore it has tons of errors defined. Not all errors are predefined but have error code and message. A developer cannot remember all the predefined errors therefore the use of WHEN OTHER exception is very helpful as it provides the error code and the message. The WHEN OTHER clause traps all the remaining exceptions that have not been handled by predefined and programmer defined exceptions. I recommend this on all your PL/SQL for handling errors along with pre-defined and user defined exception.

Example Using WHEN OTHERS along with no record found

DECLARE
  dept_no NUMBER(4) := 0;
  ecode   NUMBER;
  emesg   VARCHAR2(200);
BEGIN
  SELECT deptno INTO dept_no FROM dept_temp WHERE deptno IN (20,30);
  IF SQL%FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Records Found, Your Record is: ' || dept_no);
  END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
  DBMS_OUTPUT.PUT_LINE('No Record Found');
WHEN OTHERS THEN
  ecode := SQLCODE;
  emesg := SQLERRM;
  dbms_output.put_line(TO_CHAR(ecode) || '-' || emesg);
END;
/

Output:
PL/SQL procedure successfully completed.
-1422-ORA-01422: exact fetch returns more than requested number of rows


Example using WHEN OTHERS with too many rows.

DECLARE
  dept_no NUMBER(4) := 0;
  ecode   NUMBER;
  emesg   VARCHAR2(200);
BEGIN
  SELECT deptno INTO dept_no FROM dept_temp WHERE deptno IN (200);
  IF SQL%FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Records Found, Your Record is: ' || dept_no);
  END IF;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
  DBMS_OUTPUT.PUT_LINE('No Record Found');
WHEN OTHERS THEN
  ecode := SQLCODE;
  emesg := SQLERRM;
  dbms_output.put_line(TO_CHAR(ecode) || '-' || emesg);
END;
/

Output:
PL/SQL procedure successfully completed.

100-ORA-01403: no data found

With this article, I showed you how to catch and handle the PL/SQL errors. I hope you find this article  helpful I recommend using exception handling where possible to become a better developer.