Exception Handling in PL/SQL

Lets take a look at EXCEPTION Handling in PL/SQL:

Exception handling in any general programming language would be:
When we talk about defensive coding style we expect that due to some reasons ( like application data, unexpected data) errors may come in the program, and when errors come we have 2 options:
1. Leave it to the environment to deal with the exception.
2. Handle the error within your program and give user an easily understood message, rather than the environment message which may be tricky and technical for the users to understand.

PL/SQL exception handling:

1. The exceptions will be handled in a block of program named exception, so if your program handles exception then the structure of the program will look something like

DECLARE
BEGIN
EXCEPTION
WHEN <name_of_the_exception> THEN <define_action>
END;

2. Oracle provides 2 functions SQLERRM and SQLCODE
SQLCODE: This function returns the error code that is associated with the error that came in the program.
SQLERRM: This function returns the code along with the error message,here the code will also be appended with ORA

3. There are some list of predefined exceptions given by Oracle where the exceptions can be addressed by the name.

Example: Run the script in HR schema:

SET SERVEROUTPUT ON;
DECLARE
job_name varchar2(256);
BEGIN
SELECT job_id INTO job_name FROM employees;
EXCEPTION WHEN TOO_MANY_ROWS
THEN
DBMS_OUTPUT.PUT_LINE('Inside the exception block with code'||SQLCODE);
DBMS_OUTPUT.PUT_LINE('Inside the exception block with Error message'||SQLERRM);
END;

Few points about the above code:

1. The select statement gave error in the code as job_name was a scalar variable and the code returned multiple values.
2. As soon as the error occurred exception is raised and oracle checked for the exception block, when it find the exception block, it executed the print statements.
3. SQLCODE returned only code and SQLERRM gave error message also.
4. TOO_MANY_ROWS is a predefined exception with error code value : -01422.


USER DEFINED EXCEPTION:

Sometimes the exceptions that come in our application are specific to the business logic that we write for our applications, in this case we cannot rely on the generic exceptions that are provided by Oracle.
In that case we need to define our own exceptions.

Few points about user defined exception:
1. The default SQLCODE for user defined exception is 1.
2. The allowed limit for mapping user defined exception to error code is -20,000 to -20,999. Anything out of this range will map to -21000.

There are 2 ways for user defined exception:
1. DECLARE the exception as a variable of type EXCEPTION in the declare block.
2. Dynamically define the exception.

Example . Run the script in HR schema:
scenario: We will look at the salary of an employee and if the salary is less than 5000, the message "Salary too low" to be displayed.

Using the first approach, declaring the exception in the DECLARE block.

set serveroutput on ;
DECLARE
salary_amount number;
SALARY_TOO_LOW EXCEPTION; -- USER defined exception declaration.
BEGIN
SELECT salary into salary_amount from employees where employee_id=105;
IF(salary_amount<5000)
THEN
RAISE SALARY_TOO_LOW;
END IF;
EXCEPTION
WHEN SALARY_TOO_LOW THEN
DBMS_OUTPUT.PUT_LINE('Salary of the employee is less then 5000 '||SQLCODE);
DBMS_OUTPUT.PUT_LINE('Salary of the employee is less then 5000 '||SQLERRM);
END;
Output:
Salary of the employee is less then 5000 1
Salary of the employee is less then 5000 User-Defined Exception

Few points about the above code:
1. Exception was declared as a variable in the Declare block and was RAISED in the execution block using the RAISE keyword.
2. SQLCODE for user defined exception is 1 by default, and error message is User-Defined exception by default.

Now the requirement comes where we want to associate some user defined error code with the exception.

set serveroutput on ;
DECLARE
salary_amount number;
SALARY_TOO_LOW EXCEPTION; -- USER defined exception declaration.
PRAGMA EXCEPTION_INIT(SALARY_TOO_LOW,-20001);--Mapped to some code
BEGIN
SELECT salary into salary_amount from employees where employee_id=105;
IF(salary_amount<5000)
THEN
RAISE SALARY_TOO_LOW;
END IF;
EXCEPTION
WHEN SALARY_TOO_LOW THEN
DBMS_OUTPUT.PUT_LINE('Salary of the employee is less then 5000 '||SQLCODE);
DBMS_OUTPUT.PUT_LINE('Salary of the employee is less then 5000 '||SQLERRM);
END;
--output:
Salary of the employee is less then 5000 -20001
Salary of the employee is less then 5000 ORA-20001:

Few points about the above code:
1. A compiler directive PRAGMA EXCEPTION_INIT is used to map the exception code with the variable.
2. The excpetion is raised with the RAISE Keyword

Now the requirement comes where we want to associate some user defined error code and error message with the exception.

set serveroutput on ;
DECLARE
salary_amount number;
SALARY_TOO_LOW EXCEPTION; -- USER defined exception declaration.
PRAGMA EXCEPTION_INIT(SALARY_TOO_LOW,-20001);--Mapped to some code
BEGIN
SELECT salary into salary_amount from employees where employee_id=105;
IF(salary_amount<5000)
THEN
RAISE_APPLICATION_ERROR(-20001,'Employee Salary is very low');-- The ERROR code is mapped to the ERROR MESSAGE
END IF;
EXCEPTION
WHEN SALARY_TOO_LOW THEN
DBMS_OUTPUT.PUT_LINE('Salary of the employee is less then 5000 '||SQLCODE);
DBMS_OUTPUT.PUT_LINE('Salary of the employee is less then 5000 '||SQLERRM);
END;

Few Points about the above code
1. Using EXCEPTION_INIT PRAGMA we have mapped exception variable with a user defined code in the range -20000 to 20999.
2. The RAISE_APPLICATION_ERROR is used to map the same code with the Exception variable code.

Finally if we just want to dynamically define the exception in the execution block, then the following will work.

set serveroutput on ;
DECLARE
salary_amount number;
BEGIN
SELECT salary into salary_amount from employees where employee_id=105;
IF(salary_amount<5000)
THEN
RAISE_APPLICATION_ERROR(-20001,'Employee Salary is very low');-- The ERROR code is mapped to the ERROR MESSAGE
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Salary of the employee is less then 5000 '||SQLCODE);
DBMS_OUTPUT.PUT_LINE('Salary of the employee is less then 5000 '||SQLERRM);
END;

output:
Salary of the employee is less then 5000 -20001
Salary of the employee is less then 5000 ORA-20001: Employee Salary is very low

Few points about the above code:
1. Here an exception variable is not included explicitly.
2. The Exception is raised dynamically using RAISE_APPLICATION_ERROR with error code and error message.
3. Rather than using the name of the exception we used the generic keyword which is WHEN OTHERS.