Skip to content
Advertisements

DBMS/U3 Topic 6 PL/SQL: Exceptional Handling

An exception is an error condition during a program execution. PL/SQL supports programmers to catch such conditions using EXCEPTION block in the program and an appropriate action is taken against the error condition. There are two types of exceptions −

  • System-defined exceptions
  • User-defined exceptions

Syntax for Exception Handling

The general syntax for exception handling is as follows. Here you can list down as many exceptions as you can handle. The default exception will be handled using WHEN others THEN −

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.

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.

 

Advertisements

1 Comment »

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: