PL/SQL Exception

Summary: in this tutorial, you will learn how to handle PL/SQL exception appropriately. In addition, you’ll also learn how to define your own exception and raise it in your code.

Introducing to PL/SQL Exception

In PL/SQL, any kind of errors are treated as exceptions. An exception is defined as a special condition that change the program execution flow. The PL/SQL provides you with a flexible and powerful way to handle such exceptions.

PL/SQL catches and handles exceptions by using exception handler architecture. Whenever an exception occurs, it is raised. The current PL/SQL block execution halts and control is passed to a separated section called exception section.

In the exception section, you can check what kind of exception has been occurred and handle it appropriately. This exception handler architecture enables separating the business logic and exception handling code hence make the program easier to read and maintain.

PL/SQL Exception

There are two types of exceptions:

• System exception: the system exception is raised by PL/SQL run-time when it detect an error. For example NO_DATA_FOUND exception is raised if you select a non-existing record from database.
• Programmer-defined exception: the programmer exception is defined by you in a specific application. You can map exception names with specific Oracle errors using the EXCEPTION_INIT pragma. You can also assign a number and description to the exception using RAISE_APPLICATION_ERROR.

Defining PL/SQL Exception

An exception must be defined before it can be raised. Oracle provides many predefined exceptions in the STANDARD package. To define an exception you use EXCEPTION keyword as below:

EXCEPTION_NAME EXCEPTION;

To raise exception that you’ve defined you use the RAISE statement as follows:

RAISE EXCEPTION_NAME;

In the exception handler section, you use can handle the exception as usual. The following example illustrates the programmer-defined exceptions. We get the salary of an employee and check it with the job’s salary range. If the salary is below the range, we raise exception BELOW_SALARY_RANGE. If the salary is above the range, we raise exception ABOVE_SALARY_RANGE, just make it simple for demonstration.

SET SERVEROUTPUT ON SIZE 100000;
DECLARE
  -- define exceptions
  BELOW_SALARY_RANGE EXCEPTION;
  ABOVE_SALARY_RANGE EXCEPTION;
  -- salary variables
  n_salary employees.salary%TYPE;
  n_min_salary employees.salary%TYPE;
  n_max_salary employees.salary%TYPE;
  -- input employee id
  n_emp_id employees.employee_id%TYPE := &emp_id;
BEGIN
  SELECT salary,
         min_salary,
         max_salary
  INTO n_salary,
       n_min_salary,
       n_max_salary
  FROM employees
  INNER JOIN jobs ON jobs.job_id = employees.job_id
  WHERE employee_id = n_emp_id;

  IF n_salary < n_min_salary THEN
     RAISE BELOW_SALARY_RANGE;
  ELSIF n_salary > n_max_salary THEN
      RAISE ABOVE_SALARY_RANGE;
  END IF;

  dbms_output.put_line('Employee ' || n_emp_id ||
                               ' has salary $' || n_salary ); 

  EXCEPTION
    WHEN BELOW_SALARY_RANGE THEN
      dbms_output.put_line('Employee ' || n_emp_id ||
			  ' has salary below the salary range');
    WHEN ABOVE_SALARY_RANGE THEN
      dbms_output.put_line('Employee ' || n_emp_id ||
			  ' has salary above the salary range');
    WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Employee ' || n_emp_id || ' not found');
END;
/

In this tutorial, you’ve learned how to define your own PL/SQL exception, raise and handle it in exception handler section of PL/SQL block.