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.
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_FOUNDexception 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_INITpragma. You can also assign a number and description to the exception using
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:
To raise exception that you’ve defined you use the
RAISE statement as follows:
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.