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 is treated as exceptions. An exception is defined as a special condition that changes 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, control is passed to a separate 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.

There are two types of exceptions:

• System exception: the system exception is raised by PL/SQL run-time when it detects an error. For example, NO_DATA_FOUNDexception is raised if you select a non-existing record from the database.
• Programmer-defined exception: the programmer-defined exception is defined by you in a specific application. You can map exception names with specific Oracle errors using the EXCEPTION_INITpragma. 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 STANDARDpackage. To define an exception you use EXCEPTIONkeyword as below:

EXCEPTION_NAME EXCEPTION;Code language: SQL (Structured Query Language) (sql)

To raise an exception that you’ve defined you use the RAISEstatement as follows:

RAISE EXCEPTION_NAME;Code language: SQL (Structured Query Language) (sql)

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 an exception BELOW_SALARY_RANGE. If the salary is above the range, we raise the 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;
/Code language: SQL (Structured Query Language) (sql)

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.