Exception Handling in Oracle

Generally, in PL/SQL while executing a program we might encounter an error. They can occurr during execution and are called an “EXCEPTION”. It will disrupt the normal flow of the program’s execution.

Exception Handling

PL/SQL provides a feature called “EXCEPTION HANDLING” to handle the exception that occurs in the PL/SQL block. When an error occurs an exception is raised, normal execution is stopped and control transfers to the exception handling code.

A PL/SQL Exception consists of the following three parts:

  1. Type of Exception
  2. An Error code
  3. A Message

The following shows the general syntax of Exception Handling:

DECLARE

    <Declaration section>

 BEGIN

    <Executable command>

 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;

The following are the types of exceptions to be handled:

Types of Exception

1. PRE-DEFINED / UNNAMED EXCEPTION

A Pre-defined Exception is also called a Named System Exception. They are the one to whom the names are already assigned by the PL/SQL and declared in the STANDARD package. There is no need to declare them in our own program. For example: the pre-defined exception NO_DATA_FOUND is raised when a SELECT INTO statement returns on rows.

Some of the pre-defined exceptions are as follows:

  1. NO_DATA_FOUND
    SQL CODE : +100
    ORACLE ERROR : ORA 01403
     
  2. INVALID_NUMBER
    SQL CODE : -1722
    ORACLE ERROR : ORA 01722
     
  3. INVALID_CURSOR
    SQL CODE : -1001
    ORACLE ERROR : ORA 01001
     
  4. TOO_MANY_ROW
    SQL CODE : -1422
    ORACLE ERROR : ORA 01422
     
  5. CURSOR_ALREADY_OPEN
    SQL CODE : -6511
    ORACLE ERROR : ORA 06511
     
  6. LOGIN_DENIED
    SQL CODE : -1017
    ORACLE ERROR : ORA 01017

Example

DECLARE

TEMP NUMBER; 

Cust_name Cust_cname%type; 

BEGIN 

SELECT CNAME INTO CUST_name FROM CUST WHERE CUSTNUM=1; 

DBMS_OUTPUT.PUT_LINE(CUSTNUM 1 EXIST); 

EXCEPTION 

WHEN NO_DATA_FOUND THEN 

DBMS_OUTPUT.PUT_LINE(CUST_NUM 1 DOES NOT EXIST…’); 

WHEN ROWTYPE_MISMATCH THEN 

DBMS_OUTPUT.PUT_LINE(CUST_NUM 1 DOES NOT EXIST…’); 

END;

2. NON PRE-DEFINED / UNNAMED EXCEPTION

Non pre-defined errors are the ones that are not pre-named, but have a number in place of a name. These errors are RAISED automatically by the system, because they are system errors and can be handled using PRAGMA EXCEPTION_INIT. They do not occur frequently therefore Oracle has not provided the names to them.

Example

DECLARE

   exception_name EXCEPTION;

   PRAGMA

   EXCEPTION_INIT (exception_name, Err_code);

BEGIN

   Execution section

EXCEPTION

   WHEN exception_name THEN

     Handle the exception

END;

PRAGMA: It is a compiler directive that is processed at compile time not at runtime.
EXCEPTION_INIT: It is a built in sub-program used to provide an exception name to the constraint violation error number.

3. USER DEFINED EXCEPTION

Apart from a system exception, PL/SQL allows you to create your own exception where we can explicitly define exceptions based on a business rule, also using either a RAISE statement or the procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR.

Declaring Exception

 

DECLARE

myexception EXCEPTION;

BEGIN

------

 

Raising Exception:

 

BEGIN

RAISE myexception;

-------

 

Handling Exception

 

BEGIN

------

----

EXCEPTION

WHEN myexception THEN

Statements;

END;

 

Example

The following example illustrates the concept. This program asks for a Student ID. When the user enters an invalid ID, the exception INVALID_ID is RAISED.

DECLARE

   Stu_id customers.id%type := &Stu_id;

   Stu_FirstName  Students.FirstName%type;

   Stu_LastName  Students.LastName%type;

   Stu_Addr Students.Address%type;

 

   -- user defined exception

   ex_invalid_id  EXCEPTION;

BEGIN

   IF Stu_id <= 0 THEN

      RAISE ex_invalid_id;

   ELSE

      SELECT  FirstName, LastName, Address INTO  Stu_FirstName, Stu_LastName, Stu_Addr

      FROM Students

      WHERE id = Stu_id;

             

      DBMS_OUTPUT.PUT_LINE ('FirstName: '||  Stu_FirstName);

              DBMS_OUTPUT.PUT_LINE ('LastName: '||  Stu_LastName);

      DBMS_OUTPUT.PUT_LINE ('Address: ' || Stu_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 Student!');

   WHEN others THEN

      dbms_output.put_line('Error!');

END;