Exception Handling in Oracle

What is Exception Handling?

PL/SQL provides a feature to handle the Exceptions which occur in a PL/SQL Block known as exception Handling. Using Exception Handling we can test the code and avoid it from exiting abruptly.
An exception is an identifier in PL/SQL that is raised during the execution of a block that terminates its main body of actions. A block always terminates when PL/SQL raises an exception, but can you specify an exception handler to perform final actions.

Types of Exception

There are 3 types of Exceptions.
a) Named System Exceptions
b) Unnamed System Exceptions
c) User-defined Exceptions
Named System Exceptions (or Predefined Oracle Server Exceptions) and Unnamed System Exceptions (or Nonpredefined Oracle Server Exceptions) are implicitly raised.
User-defined Exceptions are explicitly raised.

Structure of Exception Handling

DECLARE Declaration section BEGIN Exception section EXCEPTION WHEN ex_name1 THEN -Error handling statements WHEN ex_name2 THEN -Error handling statements WHEN Others THEN -Error handling statements END;

DECLARE    Declaration section BEGIN    Exception section EXCEPTION   WHEN ex_name1 THEN      -Error handling statements   WHEN ex_name2 THEN      -Error handling statements   WHEN Others THEN      -Error handling statements END;

You can trap any error by including a corresponding routine within the exception handling section of the PL/SQL block. Each handler consists of a WHEN clause, which specifies an exception, followed by a sequence of statements to be executed when that exception is raised.
The exception-handling section traps only those exceptions that are specified; any other exceptions are not trapped unless you use the OTHERS exception handler.

Exceptions Trapping Rules:

• Begin the exception-handling section of the block with the EXCEPTION keyword.
• You can define several exception handlers, each with its own set of actions, for the block.
• When an exception occurs, PL/SQL processes only one handler before leaving the block.
• Place the OTHERS clause after all other exception-handling clauses.
• WHEN OTHERS is the last clause and you can have only one OTHERS clause.

a) Named System Exceptions
System exceptions are automatically raised by Oracle, when a program violates a RDBMS rule. There are some system exceptions which are raised frequently, so they are pre-defined and given a name in Oracle which are known as Named System Exceptions.
Note: PL/SQL declares predefined exceptions in the STANDARD package.
Few Predefined Exceptions:

• NO_DATA_FOUND (ORA-01403) — When a SELECT…INTO clause does not return any row from a table.
• TOO_MANY_ROWS (ORA-01422) — When you SELECT or fetch more than one row into a record or variable.
• ZERO_DIVIDE (ORA-01476) — When you attempt to divide a number by zero.
• CURSOR_ALREADY_OPEN (ORA-06511) — You tried to open a cursor that is already open.
• INVALID_CURSOR (ORA-01001) — Illegal cursor operation occurred. You tried to reference a cursor that does not yet exist. This may have happened because you’ve executed a FETCH cursor or CLOSE cursor before Opening the cursor.
• INVALID_NUMBER (ORA-01722) — You tried to execute an SQL statement that tried to convert a string to a number, but it was unsuccessful.
• DUP_VAL_ON_INDEX (ORA-00001) — Attempted to insert a duplicate value.
• LOGIN_DENIED (ORA-01017) — You tried to log into Oracle with an invalid username/password combination.
• NOT_LOGGED_ON (ORA-01012) — You tried to execute a call to Oracle before logging in.
• VALUE_ERROR (ORA-06502) — You tried to perform an operation and there was an error on a conversion, truncation, or invalid constraining of numeric or character data.

Named system exceptions are:

• Not declared explicitly.
• Raised implicitly when a predefined Oracle error occurs.
• Caught by referencing the standard name within an exception-handling routine.

For Example:

BEGIN Execution section EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line ('A SELECT...INTO did not return any row.'); END;

BEGIN   Execution section EXCEPTION   WHEN NO_DATA_FOUND THEN   dbms_output.put_line ('A SELECT...INTO did not return any row.'); END;

b) Unnamed System Exceptions
Those system exception for which oracle does not provide a name is known as unnamed system exception. These exceptions do not occur frequently. These Exceptions have a code and an associated message.
There are two ways to handle unnamed system exceptions:
1. By using the WHEN OTHERS exception handler, or
2. By associating the exception code to a name and using it as a named exception.
We can assign a name to unnamed system exceptions using a Pragma called EXCEPTION_INIT. EXCEPTION_INIT will associate a predefined Oracle error number to a programmer defined exception name.
Steps to be followed to use unnamed system exceptions are

• They are raised implicitly.
• If they are not handled in WHEN Others they must be handled explicitly.
• To handle the exception explicitly, they must be declared using Pragma EXCEPTION_INIT as given above and handled referencing the user-defined exception name in the exception section.

The general syntax to declare unnamed system exception using EXCEPTION_INIT is:

DECLARE exception_name EXCEPTION; PRAGMA EXCEPTION_INIT (exception_name, Err_code); BEGIN Execution section EXCEPTION WHEN exception_name THEN Handle the exception END;

DECLARE    exception_name EXCEPTION;    PRAGMA    EXCEPTION_INIT (exception_name, Err_code); BEGIN    Execution section EXCEPTION    WHEN exception_name THEN      Handle the exception END;

For Example:
Let’s trap for Oracle server error number –2292, which is an integrity constraint violation.
1] Declare the name for the exception within the declarative section.
exception EXCEPTION;
Where: exception is the name of the exception.
2.] Associate the declared exception with the standard Oracle server error number using the
PRAGMA EXCEPTION_INIT statement.
PRAGMA EXCEPTION_INIT(exception, error_number);
Where: exception is the previously declared exception.
error_number is a standard Oracle Server error number.
3] Reference the declared exception within the corresponding exception-handling routine.

DEFINE p_deptno = 10 DECLARE e_emps_remaining EXCEPTION; PRAGMA EXCEPTION_INIT(e_emps_remaining, -2292); BEGIN DELETE FROM departments WHERE department_id = &p_deptno; COMMIT; EXCEPTION WHEN e_emps_remaining THEN DBMS_OUTPUT.PUT_LINE ('Cannot remove dept ' || TO_CHAR(&p_deptno) || '. Employees exist. '); END;

DEFINE p_deptno = 10 DECLARE      e_emps_remaining EXCEPTION;      PRAGMA EXCEPTION_INIT(e_emps_remaining, -2292); BEGIN      DELETE FROM departments      WHERE department_id = &p_deptno;      COMMIT; EXCEPTION      WHEN e_emps_remaining THEN      DBMS_OUTPUT.PUT_LINE ('Cannot remove dept ' ||      TO_CHAR(&p_deptno) || '. Employees exist. '); END;

c) User-defined Exceptions
Apart from system exceptions we can explicitly define exceptions based on business rules. These are known as user-defined exceptions.
Steps to be followed to use user-defined exceptions:
• They should be explicitly declared in the declaration section.
• They should be explicitly raised in the Execution Section.
• They should be handled by referencing the user-defined exception name in the exception section.
For Example:

DECLARE e_invalid_department EXCEPTION; BEGIN UPDATE departments SET department_name = '&p_department_desc' WHERE department_id = &p_department_number; IF SQL%NOTFOUND THEN RAISE e_invalid_department; END IF; COMMIT; EXCEPTION WHEN e_invalid_department THEN DBMS_OUTPUT.PUT_LINE('No such department id.'); END;

DECLARE      e_invalid_department EXCEPTION; BEGIN     UPDATE departments     SET department_name = '&p_department_desc'     WHERE department_id = &p_department_number;     IF SQL%NOTFOUND THEN       RAISE e_invalid_department;     END IF;     COMMIT; EXCEPTION     WHEN e_invalid_department THEN     DBMS_OUTPUT.PUT_LINE('No such department id.'); END;

Functions for Trapping Exceptions:
When an exception occurs, you can identify the associated error code or error message by using two functions. Based on the values of the code or message, you can decide which subsequent action to take based on the error.
• SQLCODE: Returns the numeric value for the error code. 
• SQLERRM: Returns the message associated with the error number.
You cannot use SQLCODE or SQLERRM directly in a SQL statement. Instead, you must assign their values to local variables, then use the variables in the SQL statement, as shown in the following example:

DECLARE err_num NUMBER; err_msg VARCHAR2(100); BEGIN ... EXCEPTION ... WHEN OTHERS THEN err_num := SQLCODE; err_msg := SUBSTR(SQLERRM, 1, 100); INSERT INTO errors VALUES (err_num, err_msg); END;

DECLARE     err_num NUMBER;     err_msg VARCHAR2(100); BEGIN     ... EXCEPTION     ...   WHEN OTHERS THEN     err_num := SQLCODE;     err_msg := SUBSTR(SQLERRM, 1, 100);     INSERT INTO errors VALUES (err_num, err_msg); END;

RAISE_APPLICATION_ERROR ( ) :
RAISE_APPLICATION_ERROR is a built-in procedure in oracle which is used to display the user-defined error messages along with the error number whose range is in between -20000 and -20999.
Whenever a message is displayed using RAISE_APPLICATION_ERROR, all previous transactions which are not committed within the PL/SQL Block are rolled back automatically (i.e. change due to INSERT, UPDATE, or DELETE statements).
RAISE_APPLICATION_ERROR raises an exception but does not handle it.
RAISE_APPLICATION_ERROR is used for the following reasons,
a) to create a unique id for an user-defined exception.
b) to make the user-defined exception look like an Oracle error.
The General Syntax to use this procedure is:
RAISE_APPLICATION_ERROR (error_number, error_message);
• The Error number must be between -20000 and -20999
• The Error_message is the message you want to display when the error occurs.
RAISE_APPLICATION_ERROR can be used in either (or both) the executable section and the exception section of a PL/SQL program. The returned error is consistent with how the Oracle server produces a predefined, nonpredefined, or user-defined error. The error number and message is displayed to the user.
Executable section:

BEGIN ... DELETE FROM employees WHERE manager_id = v_mgr; IF SQL%NOTFOUND THEN RAISE_APPLICATION_ERROR (-20202,'This is not a valid manager'); END IF; ...

BEGIN ... DELETE FROM employees WHERE manager_id = v_mgr; IF SQL%NOTFOUND THEN RAISE_APPLICATION_ERROR (-20202,'This is not a valid manager'); END IF; ...

Exception section:

... EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR (-20201,'Manager is not a valid employee.'); END;

... EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR (-20201,'Manager is not a valid employee.'); END;

What is Exception propagation in Oracle? 
Exceptions which are not handled in a sub block get propagated to the outer block. When an exception occurs, it terminates from the line where the exception occurs and the control goes to the calling program or the next outer block. If not handled in the outer block, it terminates that block and propagates to the next outer block and so on. And, if exception occurs in the outermost block, then the whole program gets terminated.