Exception Handling Methods

 Exceptions can occur due to

1. Some unhandled conditions or data issues which result in oracle throwing an exception(example - query fetching more than the requested number of rows in the into clause)

2. They may be due to some functional misbehavior due to which you want to handle the exception gracefully and throw the user an appropriate message

3. Some business validation needs to be addressed. Example -  If the premium payment from a customer is overdue, you may want to stop further processing for that customer. In that case you can raise the exception and in the exception block this exception can be suitably handled.

These exceptions that occur, can be put in an exception block and handled suitably.

Below are some of the different ways of handling exceptions in oracle.

Example 1

Selecting a row from a table where no data exists for the particular record will result in the below error.

ORA-01403: no data found
ORA-06512: at line 10
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.

This can be suitably handled in an exception block as shown below.

DECLARE
v_employee_id           NUMBER; 
v_employee_name         VARCHAR2(100);
v_job_id                VARCHAR2(10);

BEGIN
  v_employee_id := 1025;

  SELECT FIRST_NAME || ' ' || LAST_NAME, JOB_ID
  INTO v_employee_name, v_job_id
  FROM EMPLOYEES WHERE EMPLOYEE_ID = v_employee_id;

EXCEPTION
  WHEN NO_DATA_FOUND
  THEN
    dbms_output.put_line('Data does not exist in EMPLOYEES table for employee_id :' || v_employee_id);
END;

Example 2 Another way of handling an exception would be to raise an user defined exception as below.

DECLARE
book_overdue       EXCEPTION;
 
v_issue_date    DATE;
v_due_date       DATE;
BEGIN
  v_due_date := TO_DATE('28-DEC-2016','DD-MON-RRRR');
  v_issue_date := SYSDATE - 15;
  
  IF SYSDATE > v_due_date
  THEN
      RAISE book_overdue;
  END IF;

EXCEPTION
  WHEN book_overdue
  THEN
    dbms_output.put_line('Book is overdue');
END;

Example 3 For Oracle exceptions that do not have a pre defined exception name, you can use the OTHERS exception or give your own exception name. We can do this using EXCEPTION_INIT.

Below is an example of mapping an Oracle error code to an exception name. Consider the case of the exact fetch error.

ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 10
01422. 00000 -  "exact fetch returns more than requested number of rows"
*Cause:    The number specified in exact fetch is less than the rows returned.
*Action:   Rewrite the query or change number of rows requested

The oracle code for this error is -1422. To associate a name to this particular error code, we need to declare the exception as follows

exact_fetch_error               	EXCEPTION; -- define the exception
PRAGMA                              EXCEPTION_INIT(exact_fetch_error, -1422);   -- associate a code to this exception using EXCEPTION_INIT

Sample Code is shown below

DECLARE
exact_fetch_error               EXCEPTION;
PRAGMA                          EXCEPTION_INIT(exact_fetch_error, -1422);  
v_job_id               VARCHAR2(100);

BEGIN
  
   SELECT
          JOB_ID
    INTO
          v_job_id
    FROM HR.JOB_HISTORY
    WHERE EMPLOYEE_ID = 101;
  

EXCEPTION
  WHEN exact_fetch_error
  THEN
    dbms_output.put_line('Exact fetch returned more than requested number or row.');
END;

Similarly, user defined exceptions can be associated with user defined exception codes. These codes should be in the range of -20000 and -20999. Example - you want to handle a divide by zero condition

divide_by_zero          EXCEPTION;
PRAGMA                  EXCEPTION_INIT(divide_by_zero, -20101);

The procedure RAISE_APPLICATTION_ERROR lets you propogate an exception caught in a PL/SQL block gracefully to the application thus avoiding any unhandled exceptions. RAISE_APPLICATION_ERROR is also useful in propogating the error up the hierarchy of PL/SQL functions/procedures where it can be suitably handled. 

Below is an example of propogating an exception in a sub procedure P2 to the calling block in P1 and to the final exception block in P1 where it can be suitably handled. Suitable comments are provided in the code to explain the error propogation.

create or replace PROCEDURE P2
AS

v_error_msg  VARCHAR2(500);
v_return NUMBER;
BEGIN
  BEGIN
    v_return := 1/0;
  EXCEPTION
    WHEN OTHERS THEN
--Propogate the exception to the calling exception block    
      v_error_msg := 'Exception in P2: Propogate the message to calling block ';
      RAISE_APPLICATION_ERROR(-20002, v_error_msg || ' : ' || SQLERRM);
  END;  
  
END;

create or replace PROCEDURE P1
AS

v_error_msg    VARCHAR2(500);
v_p_flag            VARCHAR2(1) := 1;
v_return NUMBER;

BEGIN
  v_p_flag := 2;
  IF v_p_flag = 1
  THEN
    v_return := 1/0;  
  END IF;
  
  IF v_p_flag = 2
  THEN
      BEGIN
       P2;   
      EXCEPTION
        WHEN OTHERS THEN
--Propogate the exception to main exception block
          v_error_msg := 'Propogate the message to the main block ';
          RAISE_APPLICATION_ERROR(-20001, v_error_msg || ' : ' || SQLERRM);
        ROLLBACK; 
      END;
  END IF;
  
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    v_error_msg := 'Exception IN P1 ';
-- All error messages can be logged in this final exception block.    
    dbms_output.put_line( v_error_msg || SQLCODE || ' :-' || SQLERRM);
END;

Output exception displayed Exception in P1 -20001: ORA -20001: Propogate the message to the main block ORA-20002: Exception in P2: Propogate the message to calling block : ORA-01476: divisor is equal to zero. RAISE_APPLICATION_ERROR has a third parameter which takes input as TRUE/FALSE. The default value for this parameter is false. If this value is set to TRUE, the error is added to the top of the stack of previous errors. If it is false(the default), the last error replaces the previous errors raised. But it has the entire error stack appended in a single line as shown above. 

Some common pre defined error messages are

DUP_VAL_ON_INDEX - 00001 - when you attempt to store a record in a table with a column that is having an unique index
INVALID_NUMBER   - 01722 - the conversion of a character sting into a number fails because the string variable does not contain a valid number
NO_DATA_FOUND    - 01403 - when a select from the database does not fetch any records
TOO_MANY_ROWS    - 01422 - a select into returns more than one row
ZERO_DIVIDE      - 01476 - when a numeric expression has a value 0 in the divisor.
CASE_NOT_FOUND   - ORA-06592 - When a particular case condition is not handled and there is no ELSE condition for default values

References 

https://docs.oracle.com/database/121/TTPLS/exceptions.htm#TTPLS350
https://www.tutorialspoint.com/plsql/plsql_exceptions.htm

Thought for the day

Don’t be afraid of failure. This is the way to succeed

--Lebron James