PL/SQL Exception Handlings

Exceptions (PL/SQL runtime errors) can arise from design faults, coding mistakes, hardware failures, and many other sources. We cannot anticipate all possible exceptions, but can write exception handlers that let your program to continue to operate in their presence.

Any PL/SQL block can have an exception-handling part, which can have one or more exception handlers.For example, an exception-handling part could have this syntax:

EXCEPTION

  WHEN exception_name_1 THEN

                   statements_1                 -- Exception handler

  WHEN exception_name_2 OR ex_name_3 THEN

                   statements_2                 -- Exception handler

  WHEN OTHERS THEN

                   statements_3                -- Exception handler

END;

When an exception is raised in the executable part of the block, the executable part stops and control transfers to the exception-handling part. If ex_name_1 was raised, then statements_1 run. If either ex_name_2 or ex_name_3 was raised, then statements_2 run. If any other exception was raised, then statements_3 run.After an exception handler runs, control transfers to the next statement of the enclosing block. If there is no enclosing block, then:

• If the exception handler is in a subprogram, then control returns to the invoker, at the statement after the invocation.
• If the exception handler is in an anonymous block, then control transfers to the host environment (for example, SQL*Plus)

Types of Exceptions

Predefined PLSQL Exceptions:

An internal exception is raised implicitly whenever your PL/SQL program violates an Oracle rule or exceeds a system-dependent limit. Every Oracle error has a number, but exceptions must be handled by name. So, PL/SQL predefines some common Oracle errors as exceptions. For example, PL/SQL raises the predefined exception NO_DATA_FOUND if a SELECT INTO statement returns no rows.

Frequently found exceptions are

Exception Name	Error number	Error code	Reason
CURSOR_ALREADY_OPEN	ORA-06511	-6511	Your program attempts to open an already open cursor. A cursor must be closed before it can be reopened.
INVALID_CURSOR	ORA-01001	-1001	Your program attempts an illegal cursor operation such as closing an unopened cursor
INVALID_NUMBER	ORA-01722	-1722	In a SQL statement, the conversion of a character string into a number fails because the string does not represent a valid number
NO_DATA_FOUND	ORA-01403	100	when select ,,,into ,,, from table statement returns no rows found from the table
PROGRAM_ERROR	ORA-06501	-6501	PL/SQL has an internal problem.
TOO_MANY_ROWS	ORA-01422	-1422	A SELECT INTO statement returns more than one row
VALUE_ERROR	ORA-06502	-6502	An arithmetic, conversion, truncation, or size-constraint error occurs
ZERO_DIVIDE	ORA-01476	-1476	Your program attempts to divide a number by zero

Example : NO DATA FOUND & TOO MANY ROWS

DECLARE
   lv_name   VARCHAR2 (30);
BEGIN
   SELECT NAME
     INTO lv_name
     FROM student_table
    WHERE marks > 100;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.put_line ('NO DATA FOUND');
END;

 output : NO DATA FOUND

DECLARE
   lv_name   VARCHAR2 (30);
BEGIN
   SELECT NAME
     INTO lv_name
     FROM student_table
    WHERE marks < 100; -- it extract more than one rows
EXCEPTION
   WHEN TOO_MANY_ROWS
   THEN
      DBMS_OUTPUT.put_line ('TOO MANY ROWS FOUND');
END;

output : TOO MANY ROWS FOUND

Pragma EXCEPTION_INIT

To handle error conditions (typically ORA- messages) that have no predefined name, you must use the OTHERS handler or the pragma EXCEPTION_INIT. A pragma is a compiler directive that is processed at compile time, not at run time.
In PL/SQL, the pragma EXCEPTION_INIT tells the compiler to associate an exception name with an Oracle error number. That lets you refer to any internal exception by name and to write a specific handler for it. When you see an error stack, or sequence of error messages, the one on top is the one that you can trap and handle.

The pragma EXCEPTION_INIT in the declarative part of a PL/SQL block, subprogram, or package using the syntax

PRAGMA EXCEPTION_INIT(exception_name, -Oracle_error_number);

where exception_name is the name of a previously declared exception and the number is a negative value corresponding to an ORA- error number. The pragma must appear somewhere after the exception declaration in the same declarative section, as shown in the following example:

DECLARE
   deadlock_detected EXCEPTION;
   PRAGMA EXCEPTION_INIT(deadlock_detected, -60);
BEGIN
   ... -- Some operation that causes an ORA-00060 error
EXCEPTION
   WHEN deadlock_detected THEN
      -- handle the error
END;

User-defined Exception :

User can  caught their own exceptions in the declarative part of any PL/SQL anonymous block, subprogram, or package. You must raise user-defined exceptions explicitly.

For example, you might declare an exception named insufficient_funds to flag overdrawn bank accounts.

Raising Exceptions Explicitly

To raise an exception explicitly, use either the RAISE statement or RAISE_APPLICATION_ERROR procedure.

RAISE Statement

The RAISE statement explicitly raises an exception. Outside an exception handler, you must specify the exception name. Inside an exception handler, if you omit the exception name, the RAISE statement reraises the current exception.

Declaring, Raising, and Handling User-Defined Exception

CREATE PROCEDURE account_status (
  due_date DATE,
  today    DATE
) AUTHID DEFINER
IS
  past_due  EXCEPTION;  -- declare exception
BEGIN
  IF due_date < today THEN
    RAISE past_due;  -- explicitly raise exception
  END IF;
EXCEPTION
  WHEN past_due THEN  -- handle exception
    DBMS_OUTPUT.PUT_LINE ('Account past due.');
END;
/
BEGIN
  account_status ('1-JUL-10', '9-JUL-10');
END;
/

Result:

Account past due.

RAISE_APPLICATION_ERROR Procedure

You can invoke the RAISE_APPLICATION_ERROR procedure (defined in the DBMS_STANDARD package) only from a stored subprogram or method. Typically, you invoke this procedure to raise a user-defined exception and return its error code and error message to the invoker.

To invoke RAISE_APPLICATION_ERROR, use this syntax:

RAISE_APPLICATION_ERROR (error_code, message[, {TRUE | FALSE}]);

You must have assigned error_code to the user-defined exception with the EXCEPTION_INIT pragma. The syntax is:

PRAGMA EXCEPTION_INIT (exception_name, error_code)

The error_code is an integer in the range -20000..-20999 and the message is a character string of at most 2048 bytes.

Raising User-Defined Exception with RAISE_APPLICATION_ERROR

CREATE PROCEDURE account_status (
  due_date DATE,
  today    DATE
) AUTHID DEFINER
IS
BEGIN
  IF due_date < today THEN                   -- explicitly raise exception
    RAISE_APPLICATION_ERROR(-20000, 'Account past due.');
  END IF;
END;
/

DECLARE
  past_due  EXCEPTION;                       -- declare exception
  PRAGMA EXCEPTION_INIT (past_due, -20000);  -- assign error code to exception
BEGIN
  account_status ('1-JUL-10', '9-JUL-10');   -- invoke procedure
EXCEPTION
  WHEN past_due THEN                         -- handle exception
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQLERRM(-20000)));
END;
/

Result:

ORA-20000: Account past due.