Blog

DECLARE 

   <declarations section> 

BEGIN 

   <executable command(s)> 

EXCEPTION 

   <exception handling goes 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;

Example

Let us write a code to illustrate the concept. We will be using the CUSTOMERS table we had created and used in the previous chapters −

DECLARE 

   c_id customers.id%type := 8; 

   c_name customerS.Name%type; 

   c_addr customers.address%type; 

BEGIN 

   SELECT  name, address INTO  c_name, c_addr 

   FROM customers 

   WHERE id = c_id;  

   DBMS_OUTPUT.PUT_LINE ('Name: '||  c_name); 

   DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr); 

EXCEPTION 

   WHEN no_data_found THEN 

      dbms_output.put_line('No such customer!'); 

   WHEN others THEN 

      dbms_output.put_line('Error!'); 

END; 

/

When the above code is executed at the SQL prompt, it produces the following result −

No such customer!  

PL/SQL procedure successfully completed. 

The above program displays the name and address of a customer whose ID is given. Since there is no customer with ID value 8 in our database, the program raises the run-time exception NO_DATA_FOUND, which is captured in the EXCEPTION block.

Raising Exceptions

Exceptions are raised by the database server automatically whenever there is any internal database error, but exceptions can be raised explicitly by the programmer by using the command RAISE. Following is the simple syntax for raising an exception −

DECLARE 

   exception_name EXCEPTION; 

BEGIN 

   IF condition THEN 

      RAISE exception_name; 

   END IF; 

EXCEPTION 

   WHEN exception_name THEN 

   statement; 

END; 

You can use the above syntax in raising the Oracle standard exception or any user-defined exception. In the next section, we will give you an example on raising a user-defined exception. You can raise the Oracle standard exceptions in a similar way.

User-defined Exceptions

PL/SQL allows you to define your own exceptions according to the need of your program. A user-defined exception must be declared and then raised explicitly, using either a RAISE statement or the procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR.

The syntax for declaring an exception is −

DECLARE 

   my-exception EXCEPTION; 

Example

The following example illustrates the concept. This program asks for a customer ID, when the user enters an invalid ID, the exception invalid_id is raised.

DECLARE 

   c_id customers.id%type := &cc_id; 

   c_name customerS.Name%type; 

   c_addr customers.address%type;  

   -- user defined exception 

   ex_invalid_id  EXCEPTION; 

BEGIN 

   IF c_id <= 0 THEN 

      RAISE ex_invalid_id; 

   ELSE 

      SELECT  name, address INTO  c_name, c_addr 

      FROM customers 

      WHERE id = c_id;

      DBMS_OUTPUT.PUT_LINE ('Name: '||  c_name);  

      DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr); 

   END IF; 

EXCEPTION 

   WHEN ex_invalid_id THEN 

      dbms_output.put_line('ID must be greater than zero!'); 

   WHEN no_data_found THEN 

      dbms_output.put_line('No such customer!'); 

   WHEN others THEN 

      dbms_output.put_line('Error!');  

END; 

/

When the above code is executed at the SQL prompt, it produces the following result −

Enter value for cc_id: -6 (let's enter a value -6) 

old  2: c_id customers.id%type := &cc_id; 

new  2: c_id customers.id%type := -6; 

ID must be greater than zero! 

PL/SQL procedure successfully completed.