Exception in PL/SQL

  • An error occurred during the execution of program is called exception in PL/SQL.
  • PL/SQL provides the facility to catch errors by declaring conditions in exception block in the program and necessary action to be taken to rectify the error.
  • Exception can be user defined (these are logical error defined by user) or internally defined.
    For example: The division by zero error.
  • Internal exceptions are raised automatically by the runtime system.
  • User defined exception should be raised explicitly by RAISE statements.

Syntax

DECLARE  
   <declaration section>  
BEGIN  
   <executable commands>  
EXCEPTION  
   <define exception handling here >  
   WHEN exception1 THEN   
       exception1-handling-statements   
   WHEN exception2  THEN   
      exception2-handling-statements   
   WHEN exception3 THEN   
      exception3-handling-statements  
   ........  
   WHEN others THEN  
      exception3-handling-statements  
END;

Advantages of PL/SQL Exceptions

  • It is possible to handle potential errors from many statements by using a single exception handler.
  • If user needs to check for error at every point, it is solved by adding an exception handler to PL/SQL block in the program.
  • To check error at a specific spot is possible by enclosing a single statement or a group of statements inside its own exception handler.
  • Isolating error handling method makes the rest of the program easier to read and understand.
Example: Illustration of exception handling.

Consider the following table titled 'Employee'.

IdNameDesignationSalary
1AlbertProgrammer50000
2AnnaHR25000
3MarkAnalyst55000
4JasonContent writer20000
5AndrewProgrammer90000

Write a PL/SQL program to give message that no such a employee is present in a table.

DECLARE  
   c_id employee.id%type := 9;  
   c_name  employee.name%type;  
   c_addr employee.designation%type;  
BEGIN  
   SELECT  name, designation INTO  c_name, c_addr  
   FROM Employees
   WHERE id = c_id;  
dbms_output.put_line ('Name: '| |   c_name);  
dbms_output.put_line ('Designation: ' | |  c_addr);  
EXCEPTION  
   WHEN no_data_found THEN  
      dbms_output.put_line('No such Employee exist!');  
   WHEN others THEN  
      dbms_output.put_line('Error!');   
END;  
/


Output:
No such Employee exist!

Note:
  • In the above example, the given input value for employee id= 9, which is not present in the database.
  • If we provide input value for id=3, the output will be:
    Name: Mark
    Designation: Analyst

Raising Exceptions

  • The Oracle engine provides a procedure RAISE_APPLICATION_ERROR  that allows user to issue user-defined error message.
  • When an error makes it undesirable or impossible to finish processing, the PL/SQL block and subprogram raise an error.
  • User can write raise statements for a given exception anywhere within the scope of that exception.
Syntax:
RAISE_APPLICATION_ERROR(<ErrorNumber>,<Message>);

Where,
ErrorNumber is a negative integer in the range -2000 to -20999 & Message is a character string upto 2048 bytes in length.

Example: Raising exception by using RAISE statement.

DECLARE
   out_of_stock   EXCEPTION;
   number_on_hand NUMBER := 0;
BEGIN
   IF number_on_hand < 1 THEN
      RAISE out_of_stock; -- raise an exception that we defined
   END IF;
EXCEPTION
   WHEN out_of_stock THEN
      -- handle the error
      dbms_output.put_line('Encountered out-of-stock error.');
END;
/


Output:
Encountered out-of-stock error.
▲