DBMS Exceptions

Exceptions

An Exception is an error situation, which arises during program execution. When an error occurs exception is raised, normal execution is stopped and control transfers to exceptionhandling part.
 Exception handlers are routines written to handle the exception. The exceptions can be internally defined (system-defined or pre-defined) or User-defined exception.

Syntax:

EXCEPTION  
        WHEN <ExceptionName> THEN 
        <User Defined Action To Be Carried Out> 

Predefined exception :

Predefined exception is raised automatically whenever there is a violation of Oracle coding rules. Predefined exceptions are those like ZERO_DIVIDE, which is raised automatically when we try to divide a number by zero. Other built-in exceptions are given below. You can handle unexpected Oracle errors using OTHERS handler. It can handle all raised exceptions that are not handled by any other handler. It must always be written as the last handler in exception block.
 

Exception Raised when....
DUP_VAL_ON_INDEX When you try to insert a duplicate value into a unique column.
INVALID_CURSOR It occurs when we try accessing an invalid cursor.
INVALID_NUMBER On usage of something other than number in place of number value.
LOGIN_DENIED At the time when user login is denied.
TOO_MANY_ROWS When a select query returns more than one row and the destination variable can take only single value.
VALUE_ERROR When an arithmetic, value conversion, truncation, or constraint error occurs.
CURSOR_ALREADY_OPEN Raised when we try to open an already open cursor.


Predefined exception handlers are declared globally in package STANDARD. Hence we need not have to define them rather just use them.
The biggest advantage of exception handling is it improves readability and reliability of the code. Errors from many statements of code can be handles with a single handler. Instead of checking for an error at every point we can just add an exception handler and if any exception is raised it is handled by that.
For checking errors at a specific spot it is always better to have those statements in a separate begin – end block.

Example:

DECLARE
  N number;
 BEGIN
  N:=10/0;
  EXCEPTION WHEN ZERO_DIVIDE THEN
  DBMS_OUTPUT.PUT_LINE('divide by zero error occures..');
 END;
 /

Output:

Run SQL Command Line
SQL>set serveroutput on
SQL>start D://d.sql
divide by zero error occures..
PL/SQL procedure successfully completed.

User-defined Exceptions :

The technique that is used is to bind a numbered exception handler to a name using Pragma Exception_init (). This binding of a numbered exception handler, to a name (i.e. a String), is done in the Declare section of a PL/SQL block.
The Pragma action word is a call to a pre-compiler, which immediately binds the numbered exception handler to a name when encountered.
The function Exception_init() takes two parameters the first is the user defined exception name the second is the Oracle engine's exception number. These lines will be included in the Declare section of the PL/SQL block.
The user defined exception name must be the statement that immediately precedes the Pragma Exception_init() statement.

Syntax:

DECLARE 
     < ExceptionName > EXCEPTION ; 
      PRAGMA EXCEPTION_INIT (< ExceptionName >, <ErrorCodeNo>); 
 BEGIN

Using this technique it is possible to bind appropriate numbered exception handlers to names and use these names in the Exception section of a PL/SQL block. When this is done the default exception handling code of the exception handler is overridden and the user-defined exception handling code is executed .

Syntax:

DECLARE 
     < ExceptionName > EXCEPTION ; 
      PRAGMA EXCEPTION_INIT (< ExceptionName >, <ErrorCodeNo>); 
 BEGIN
    .  .  .  .  
 EXCEPTION 
   WHEN < ExceptionName > THEN 
     < Action > 
 END; 

Example:

Create table named emp have column like id with notnull constraint,name and etc.

 DECLARE 
    e_MissingNull EXCEPTION; 
        PRAGMA EXCEPTION_INIT(e_MissingNull, -1400); 
  BEGIN
       INSERT INTO emp(id) VALUES (NULL);
  EXCEPTION
          WHEN e_MissingNull then 
        DBMS_OUTPUT.put_line('ORA-1400 occurred'); 
  END;
  /    

Output:

Run SQL Command Line
SQL>set serveroutput on
SQL>start D://e.sql
ORA-1400 occurred
PL/SQL procedure successfully completed.

User Defined Exception Handling :

To trap business rules being violated the technique of raising user-defined exceptions and then handling them, is used.
User-defined error conditions must be declared in the declarative part of any PL/SQL block. In the executable part, a check for the condition that needs special attention is made. If that condition exists, the call to the user-defined exception is made using a RAISE statement. The exception once raised is then handled in the Exception handling section of the PL/SQL code block.

Syntax:

DECLARE 
     < ExceptionName > EXCEPTION ;  
 BEGIN
     <SQL Sentence >;
     IF < Condition > THEN 
       RAISE <ExceptionName>; 
     END IF;  
 EXCEPTION 
     WHEN <ExceptionName>THEN {User Defined Action To Be Taken}; 
  END;  

Example:

 DECLARE 
    ex EXCEPTION;  
  BEGIN
      IF TO_CHAR(SYSDATE,'DY')=='SUN' THEN
         RAISE ex;
      END IF;
  EXCEPTION
          WHEN ex then 
        DBMS_OUTPUT.put_line('No Transcations Today'); 
  END;
  /   

Output:

Run SQL Command Line
SQL>set serveroutput on
SQL>start D://ex.sql
No Transactions Today
PL/SQL procedure successfully completed.


Share This Page to Download:

Share to Download

Share This Page on:


Ask Question