User defined exceptions in Oracle

User defined exceptions in Oracle – Learn how user can define his own Exceptions depends on the requirements of business logic.

User defined exceptions in Oracle

This is an error that is defined by the programmer.
This exceptions defined to handle the business situations during the execution of PL/SQL block.

Declaring PL/SQL Exceptions

DECLARE
   Excepion_Name EXCEPTION;

• Exceptions can be declared only in the declarative part of a PL/SQL block, subprogram, or package.
• Declare an exception by introducing its name, followed by the keyword EXCEPTION. Exception and variable declarations are similar.
• Remember, an exception is an error condition, not a data item. Unlike variables, exceptions cannot appear in assignment statements or SQL statements.

Techniques used for User Defined Exceptions.

1. RAISE
2. RAIS_APPLICATION_ERROR

RAISE

RAISE statement transfert the control of the block from the execution part of the PL/SQL block to the Exception Hanling part of the block.

Steps to handle Exception.

1. Declare Exception
2. Raise in Executable section explicitly using
   RAISE
3. Handle the raised exception.

Syntax

DECLARE
   Exception_name EXCEPTION;
   
BEGIN
   ...
   IF......THEN
      RAISE Exception_name ;
   END IF;
EXCEPTION
   WHEN Exception_name THEN
      -- handle the error
END;

• In the above syntax, the keyword RAISE is used in the execution part followed by exception “exception_name”.
• This will raise this particular exception at the time of execution, and this needs to be handled or raised further.

Example Program

DECLARE
  A                     NUMBER :=20;
  B                     NUMBER :=0;
  C                     NUMBER;
  ZERO_DIVIDE_EXVEPTION EXCEPTION;
BEGIN
  IF B=0 THEN
    RAISE ZERO_DIVIDE_EXVEPTION;
  END IF;
  C:=A/B;
EXCEPTION
WHEN ZERO_DIVIDE_EXVEPTION THEN
  DBMS_OUTPUT.PUT_LINE('Number Cannot  Divide by Zero');
END;

Output
Number Cannot Divide by Zero

Reraising the Exception

Sometimes, you want to reraise an exception, that is, handle it locally, then pass it to an enclosing block.

For example, you might want to roll back a transaction in the current block, then log the error in an enclosing block.

Syntax

BEGIN
   ...
   DECLARE  ---------- sub-block begins
      Exception_Name EXCEPTION;
   BEGIN
      ...
      IF ... THEN
         RAISE Exception_Name ;
      END IF;
 EXCEPTION
    WHEN Exception_Name THEN
         -- handle the error
         RAISE;  -- reraise the current exception
   END;  ------------ sub-block ends  
EXCEPTION
   ...
   WHEN OTHERS THEN
      ROLLBACK;
END;

To reraise an exception, simply place a RAISE statement in the local handler, as shown in the following example:

Example Program

DECLARE
  A                     NUMBER :=20;
  B                     NUMBER :=0;
  C                     NUMBER;
  ZERO_DIVIDE_EXVEPTION EXCEPTION;
BEGIN
  BEGIN
    IF B=0 THEN
      RAISE ZERO_DIVIDE_EXVEPTION; --RAISING EXCEPTION
    END IF;
    C:=A/B;
  EXCEPTION
  WHEN ZERO_DIVIDE_EXVEPTION THEN
    DBMS_OUTPUT.PUT_LINE('Number Cannot  Divide by Zero');
    RAISE; -- RE RAISING EXCEPTION
  END;
EXCEPTION
WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('RE RAISING THE EXCEPTION');
END;

Output

Number Cannot Divide by Zero
RE RAISING THE EXCEPTION

If you want two or more exceptions to execute the same sequence of statements, list the exception names in the WHEN clause, separating them by the keyword OR, as follows:

EXCEPTION
   WHEN Excepiton_1 OR Excepiton_2 OR Exception_3 THEN
      -- handle the error

Omitting the exception name in a RAISE statement–allowed only in an exception handler–reraises the current exception.

Handling Raised PL/SQL Exceptions

When an exception is raised, normal execution of your PL/SQL block or subprogram stops and control transfers to its exception-handling part, which is formatted as follows:

EXCEPTION
   WHEN exception_name1 THEN  -- handler
      sequence_of_statements1
   WHEN exception_name2 THEN  -- another handler
      sequence_of_statements2
   ...
   WHEN OTHERS THEN           -- optional handler
      sequence_of_statements3
END;

RAISE_APPLICATION_ERROR

The procedure RAISE_APPLICATION_ERROR lets you issue user-defined ORA- error messages from stored subprograms.

That way, you can report errors to your application and avoid returning unhandled exceptions.

To call RAISE_APPLICATION_ERROR, use the syntax

RAISE_APPLICATION_ERROR(error_number, 'ERROR MESSAGE');

• This  built in procedure is used to create your own error message, which can be more descriptive than the name exceptions.
• It is used to communicate a predefined exception interactively by returning a non standard error code and error message.
• Using this procedure we can report error to application and avoid returning unhandled exception.

Notes

• Error number must exists between -20,000 and -20,999.
• Error_message is the text associate with this error, and keep_errors is Boolean value.
• The error_message parameter must be less than 512 characters.

SQLCODE FUNCTION

• It return the current error code
• for a user defined exception it returns 1,+100 NO_DATA_FOUND exception.

SQLERRM

• It returns the current error message text.
• SQLERRM returns the error message associated with the error number.
• The maximum length of a message returned by the SQLERRM is 512 bytes.

Trapping Non-Predefined oracle server errors

• we can associate a named exception with a particular oracle error.
• The Non-predefined oracle server error is trapped by declaring it first or by using the OTHERS exception Handle.
• The declare Exception is RAISED implicitly by the oracle server.
• The PL/SQL PRAGMA EXCEPTION_INIT() can be used for associating EXCEPTION Name with oracle error Number.
• The PRAGMA EXCEPTION_INIT() tells the PL/SQL engine completely to associate an EXCEPTION name with an oracle Number.
• The PRAGMA EXCEPTION_INIT() allows programmer to refer to any internal Exception by the name and associate that to specific handles.
• Pragma is a directive of compiler which tells the compiler to associate error no with user declared exception at compile time.

STEPS TO HANDLE PRAGMA EXCEPTION

1.  Declare Exception.
2. Associate Exception with oracle Number using
   PRAGMA EXCEPTION_INIT(Exception_name, oracle_error_number);
3. Handle the raised exception.

Exception_name is the name of an exception declare prior to the pragma.
Oracle_error_number is the desired error code to associate with this named_exception.

Syntax

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

Important points to note in Exception

• In function, an exception should always either return value or raise the exception further. else Oracle will throw ‘Function returned without a value’ error at run-time.
• Transaction control statements can be given at exception handling block.
• SQLERRM and SQLCODE are the in-built functions that will give the exception message and code.
• If an exception is not handled then by default all the active transaction in that session will be rolled back.
• RAISE_APPLICATION_ERROR (-<error_code>, <error_message>) can be used instead of RAISE to raise the error with user code and message. Error code should be
  greater than 20000 and prefixed with ‘-‘.