PL/SQL - Exceptions

Exceptions:

An error condition during a program excution is called an exception in PL/SQL. We can catch such errors 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:

DECLARE

<declarations section>

BEGIN

<executable commands>

EXCEPTION

WHEN exception1 THEN

exception1-handling-statements

WHEN exception2 THEN

exception2-handling-statements

WHEN exception3 THEN

exception3-handling-statements

.......

WHEN others THEN

exception-handling-statements

END;

Exceptions example:

Fetch customer details based on customer id. There are 3 possibilities to get the responses.

1. Successfully fetched 1 customer 

2. Error with no customer 

3. Many customers records found.

CREATE OR REPLACE PROCEDURE GET_CUSTOMER

(

c_id IN NUMBER

)

AS

c_name customer.first_name%type;

c_country customer.country%type;

BEGIN

SELECT first_name, country INTO c_name, c_country FROM customer WHERE customer_id = c_id;

dbms_output.put_line('Name: '|| c_name);

dbms_output.put_line('Country: '|| c_country);

EXCEPTION

WHEN no_data_found THEN

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

WHEN too_many_rows THEN

dbms_output.put_line('You got more than 1 row!');

WHEN others THEN

dbms_output.put_line('Error!');

END;

/

Run the procedure:

Execute get_customer(10);

User defined exceptions:

PL/SQL allows us to define our own exceptions according to the need of our program. A user defined exception must be declared and then raised explicitly, using either a RAISE.

CREATE OR REPLACE PROCEDURE GET_CUSTOMER

(

c_id IN NUMBER

)

AS

c_name customer.first_name%type;

c_country customer.country%type;

ex_customer_id EXCEPTION;

BEGIN

IF c_id <=0 THEN

RAISE ex_customer_id;

SELECT first_name, country INTO c_name, c_country FROM customer WHERE customer_id = c_id;

dbms_output.put_line('Name: '|| c_name);

dbms_output.put_line('Country: '|| c_country);

EXCEPTION

WHEN ex_customer_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 too_many_rows THEN

dbms_output.put_line('You got more than 1 row!');

WHEN others THEN

dbms_output.put_line('Error!');

END;

/

System defined Exception List:

An internal exception is raised implicitly whenever your PL/SQL program violates an Oracle rule or exceeds a system dependent limit. 

PL/SQL declares predefined exceptions globally in package STANDARD  which defines the PL/SQL environment. So we need not to declare them again and just to reuse them.

Few of them are below:

ZERO_DIVIDE : when your program attempts to divide a number by zero

VALUE_ERROR : when an arithmatic, conversion, truncation or size-constraint error occurs.

STORAGE_ERROR: PL/SQL runs out of memory ot memory has been corrupted.

ROWTYPE_MISMATCH : the host cursor variable and PL/SQL cursor variable involved in an assignment have incompatible return types.