Exception Handling in Oracle

In this tutorial we will see how to handle different types of exceptions in oracle PL/ SQl which can be use to trap the system or user errors.
This is a very basic of the exceptions.

We've categorized exception handling into the following topics:

1)                 Named System Exception

2)                 Named Programmer-Defined Exception

3)                 When Others Exception

·                   What is a named system exception?

Named system exceptions are exceptions that have been given names by PL/SQL And do not need to be defined by the programmer.

Oracle has a standard set of exceptions already named as follows:

Oracle Exception Name	Oracle Error	Explanation
DUP_VAL_ON_INDEX	ORA-00001	You tried to execute an INSERT or UPDATE statement that has created a duplicate value in a field restricted by a unique index.
TIMEOUT_ON_RESOURCE	ORA-00051	You were waiting for a resource and you timed out.
TRANSACTION_BACKED_OUT	ORA-00061	The remote portion of a transaction has rolled back.
INVALID_CURSOR	ORA-01001	You tried to reference a cursor that does not yet exist. This may have happened because you've executed a FETCH cursor or CLOSE cursor before Opening the cursor.
NOT_LOGGED_ON	ORA-01012	You tried to execute a call to Oracle before logging in.
LOGIN_DENIED	ORA-01017	You tried to log into Oracle with an invalid username/password combination.
NO_DATA_FOUND	ORA-01403	You tried one of the following: 1.        You executed a SELECT INTO statement and no rows were returned. 2.        You referenced an uninitialized row in a table. 3.        You read past the end of file with the UTL_FILE package.
TOO_MANY_ROWS	ORA-01422	You tried to execute a SELECT INTO statement and more than one row was returned.
ZERO_DIVIDE	ORA-01476	You tried to divide a number by zero.
INVALID_NUMBER	ORA-01722	You tried to execute an SQL statement that tried to convert a string to a number, but it was unsuccessful.
STORAGE_ERROR	ORA-06500	You ran out of memory or memory was corrupted.
PROGRAM_ERROR	ORA-06501	This is a generic "Contact Oracle support" message because an internal problem was encountered.
VALUE_ERROR	ORA-06502	You tried to perform an operation and there was a error on a conversion, truncation, or invalid constraining of numeric or character data.
CURSOR_ALREADY_OPEN	ORA-06511	You tried to open a cursor that is already open.

The syntax for the Named System Exception in a procedure is:
CREATE [OR REPLACE] PROCEDURE procedure_name
    [parameter ]
IS
    [declaration_section]
BEGIN
    executable_section

EXCEPTION
    WHEN DUP_VAL_ON_INDEX

THEN
        [Do Something]
    WHEN INVALID_CURSOR
THEN
        [Do Something]

    WHEN  NO_DATA_FOUND

THEN
        [Do Something]
    WHEN OTHERS THEN
        [Do Something]
END [procedure_name];

The syntax for the Named System Exception in a function is:
CREATE [OR REPLACE] FUNCTION function_name
    [parameter]
    RETURN return_datatype
IS | AS
    [declaration_section]
BEGIN
    executable_section

EXCEPTION
    WHEN DUP_VAL_ON_INDEX

THEN
        [Do Something]
    WHEN INVALID_CURSOR
THEN
        [Do Something]

    WHEN  NO_DATA_FOUND

THEN
        [Do Something]
    WHEN OTHERS THEN
        [Do Something]
END [function_name];

 Let’s Have A Look At The Example:-

1.      Create a table named TRAN Like Below (Tran is short form of transaction):

1)      Create a Procedure for inserting Records in Tran table:

2)     Insert Records In Table Like Below:

While inserting duplicate transaction id system has thrown the error that you define in the code. Using this kind of login you can put message to the user to enter the different value

·                   What is a named programmer-defined exception?

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

The syntax for the Named Programmer-Defined Exception in a procedure is:

CREATE [OR REPLACE] PROCEDURE procedure_name
    [paramete]
IS
    [declaration_section]

exception_name EXCEPTION;

BEGIN
    executable_section

 RAISE exception_name ;

EXCEPTION
WHEN exception_name THEN
                 [statements]

    WHEN OTHERS THEN
        [statements]

END [procedure_name];

The syntax for the Named Programmer-Defined Exception in a function is:

CREATE [OR REPLACE] FUNCTION function_name
    [parameter]
    RETURN return_datatype
IS | AS
    [declaration_section]

    exception_name EXCEPTION;

BEGIN
    executable_section

    RAISE exception_name ;

EXCEPTION
WHEN exception_name THEN
        [statements]

    WHEN OTHERS THEN
        [statements]

END [function_name];

Let’s Have A Look At The Example:-

1)     Create a table :

2)     Create a Procedure :

3)      Execute the Procedure :

If you would been not handle the exception then the error would be system define.

·                    What is a WHEN OTHERS clause?

The WHEN OTHERS clause is used to trap all remaining exceptions that have not been handled by your Named System Define Exception and Programmer-define Exception.

The syntax for the WHEN OTHERS clause in a procedure is:

CREATE [OR REPLACE] PROCEDURE procedure_name
    [parameter]
IS
    [declaration_section]
BEGIN
    executable_section

EXCEPTION
    WHEN exception_name1 THEN
        [statements]

    WHEN exception_name2 THEN
        [statements]

    WHEN exception_name_n THEN
        [statements]

    WHEN OTHERS THEN

           [statements]

END [procedure_name];

The syntax for the WHEN OTHERS clause in a function is:

CREATE [OR REPLACE] FUNCTION function_name
    [parameter]
    RETURN return_datatype
IS | AS
    [declaration_section]
BEGIN
    executable_section

EXCEPTION
    WHEN exception_name1 THEN
        [statements]

    WHEN exception_name2 THEN
        [statements]

    WHEN exception_name_n THEN
        [statements]

    WHEN OTHERS THEN
                  [statements]

END [function_name];

Let’s Have A Look At The Example:-

1)     Create a table :

2)     Create a Procedure :

If an exception is encountered that is not a DUP_VAL_ON_INDEX or a NO_TRAN, it will be trapped by the WHEN OTHERS clause.