Error or Warning of any kind is called as Exception in Oracle which can be predefined or user-defined. Predefined exceptions are raised implicitly by the system. User-defined exceptions must be raised explicitly by RAISE statements. To handle raised exceptions, we write separate routines called exception handlers

There are four kinds of exceptions in PL/SQL they are

1)Named system exceptions.

2)Named programmer-defined exceptions.

3)Unnamed system exceptions.

4)Unnamed programmer-defined exceptions.

1)Named system exceptions.

Exceptions that have been given names by PL/SQL and raised as a result of an error in PL/SQL.For example, PL/SQL raises the predefined exception NO_DATA_FOUND if a SELECT INTO statement returns no rows.These Exceptions are raised automatically when an error condition occurs.

Following are few examples of Named system Exceptions

Exception Raised when

NO_DATA_FOUND A SELECT INTO statement returns no rows, or your program references a deleted element in a nested table or an uninitialized

element in an index-by table.

TOO_MANY_ROWS A SELECT INTO statement returns more than one row.

ZERO_DIVIDE A program attempts to divide a number by zero.

Here is an example of a procedure that uses a Named System Exception:

CREATE OR REPLACE PROCEDURE Employee_Details(pempno in Number) IS

-- declare record variable that represents a row fetched from the employees table

employee_rec emp%ROWTYPE;

BEGIN

SELECT ename,job,sal INTO employee_rec.ename,employee_rec.job,employee_rec.sal from emp WHERE empno=pempno;

Exception

when TOO_MANY_ROWS then

DBMS_OUTPUT.PUT_LINE('The following employee has Duplicate Records'||pempno);

when others then

DBMS_OUTPUT.PUT_LINE('Some other Error occured');

END;

/

In this example, we are trapping the Named System Exception TOO_MANY_ROWS . We are also using the WHEN OTHERS clause to trap all remaining exceptions.

2)Named programmer-defined exceptions.

Sometimes, it is necessary for us to name and trap our own exceptions – ones that aren’t defined already by PL/SQL. These are called Named Programmer-Defined Exceptions.

These Exceptions have to be declared in Declaration section of PL/SQL block.

Here is an example of a procedure that uses a Named Programmer-Defined Exception:

CREATE OR REPLACE PROCEDURE add_new_order
(order_id_in IN NUMBER, sales_in IN NUMBER)
IS
no_sales EXCEPTION;

BEGIN
IF sales_in = 0 THEN
RAISE no_sales;

ELSE
INSERT INTO orders (order_id, total_sales )
VALUES ( order_id_in, sales_in );
END IF;

EXCEPTION
WHEN no_sales THEN
raise_application_error (-20001,'You must have sales in order to submit the order.');

WHEN OTHERS THEN
raise_application_error (-20002,'An error has occurred inserting an order.');

END;

In this example, we have declared a Named Programmer-Defined Exception called no_sales in our declaration statement with the following code:

no_sales EXCEPTION;

We’ve then raised the exception in the executable section of the code:

IF sales_in = 0 THEN
RAISE no_sales;

Now if the sales_in variable contains a zero, our code will jump directly to the Named Programmer-Defined Exception called no_sales.

Finally, we tell our procedure what to do when the no_sales exception is encountered by including code in the WHEN clause:

WHEN no_sales THEN
raise_application_error (-20001,’You must have sales in order to submit the order.’);

We are also using the when others clause to trap all remaining exceptions:

WHEN OTHERS THEN
raise_application_error (-20002,’An error has occurred inserting an order.’);

3)Unnamed system exceptions.

Exceptions that are raised as a result of an error in PL/SQL or RDBMS processing but have not been given names by PL/SQL. Only the most common errors are so named; the rest have numbers and can be assigned names with the special PRAGMA EXCEPTION_INIT syntax.

4)Unnamed programmer-defined exceptions.

Exceptions that are defined and raised in the server by the programmer. In this case, the programmer provides both an error number (between -20000 and -20999) and an error message, and raises that exception with a call to RAISE_APPLICATION_ERROR. That error, along with its message, is propagated back to the client-side application.