Exception Handling in Oracle

Exception Handling

PL/SQL provides a feature to handle the Exceptions which occur in a PL/SQL Block known as exception Handling.

Using Exception Handling we can test the code and avoid it from exiting abruptly

PL/SQL Exception message consists of three parts.
1) Type of Exception
2) An Error Code
3) A message

 DECLARE

   Declaration section

 BEGIN

   Exception section

 EXCEPTION

 WHEN ex_name1 THEN

    -Error handling statements

 WHEN ex_name2 THEN

    -Error handling statements

 WHEN Others THEN

   -Error handling statements

END;

When an exception is raised, Oracle searches for an appropriate exception handler in the exception section

There are 3 types of Exceptions.
a) Named System Exceptions
b) Unnamed System Exceptions
c) User-defined Exceptions

Named System Exceptions :

CURSOR_ALREADY_OPEN

INVALID_CURSOR--Your program attempts an illegal cursor operation such as closing an unopened cursor

INVALID_NUMBER---in convertion functions it will raise       

NO_DATA_FOUND---No rows returned

TOO_MANY_ROWS--multiple values selected

ZERO_DIVIDE

STORAGE_ERROR---out of memory

DUP_VAL_ON_INDEX

LOGIN_DENIED

CASE_NOT_FOUND---None of the choices in the WHEN clauses of a CASE statement is selected, and there is no ELSE clause

PROGRAM_ERROR ---PL/SQL has an internal problem.

SYS_INVALID_ROWID--The conversion of a character string into a universal rowid fails because the character string does not represent a valid rowid.

VALUE_ERROR---value too large error

NOT_LOGGED_ON--Your program issues a database call without being connected to Oracle.

Unnamed System Exceptions :

Those system exception for which oracle does not provide a name is known as unamed system exception.

These exception do not occur frequently. These Exceptions have a code and an associated message.

There are two ways to handle unnamed sysyem exceptions:
1. By using the WHEN OTHERS exception handler, or
2. By associating the exception code to a name and using it as a named exception.

We can assign a name to unnamed system exceptions using a Pragma called EXCEPTION_INIT.
EXCEPTION_INIT will associate a predefined Oracle error number to a programmer_defined exception name.

DECLARE

  Child_rec_exception EXCEPTION;

  PRAGMA

   EXCEPTION_INIT (Child_rec_exception, -2292);

BEGIN

  Delete FROM product where product_id= 104;

EXCEPTION

   WHEN Child_rec_exception

   THEN Dbms_output.put_line('Child records are present for this product_id.');

END;

 User-defined Exceptions :

Apart from sytem exceptions we can explicity define exceptions based on business rules. These are known as user-defined exceptions

DECLARE

  huge_quantity EXCEPTION;

  CURSOR product_quantity is

  SELECT p.product_name as name, sum(o.total_units) as units

  FROM order_tems o, product p

  WHERE o.product_id = p.product_id;

  quantity order_tems.total_units%type;

  up_limit CONSTANT order_tems.total_units%type := 20;

  message VARCHAR2(50);

BEGIN

  FOR product_rec in product_quantity LOOP

    quantity := product_rec.units;

     IF quantity > up_limit THEN

      message := 'The number of units of product ' || product_rec.name || 

                 ' is more than 20. Special discounts should be provided.

                                            Rest of the records are skipped. '

     RAISE huge_quantity;

     ELSIF quantity < up_limit THEN

      v_message:= 'The number of unit is below the discount limit.';

     END IF;

     dbms_output.put_line (message);

  END LOOP;

 EXCEPTION

   WHEN huge_quantity THEN

     dbms_output.put_line (message);

 END;

/

CREATE OR REPLACE FUNCTION is_ssn (string_in VARCHAR2)
RETURN VARCHAR2 IS
-- validating ###-##-#### format
delim VARCHAR2(1);
part1 NUMBER(3,0);
part2 NUMBER(2,0);
part3 NUMBER(4,0);

too_long EXCEPTION;
too_short EXCEPTION;
delimiter EXCEPTION;
BEGIN
IF LENGTH(string_in) > 11 THEN
RAISE too_long;
ELSIF LENGTH(string_in) < 11 THEN
RAISE too_short;
END IF;

part1 := TO_NUMBER(SUBSTR(string_in,1,3),'999');

delim := SUBSTR(string_in,4,1);
IF delim <> '-' THEN
RAISE delimiter;
END IF;

part2 := TO_NUMBER(SUBSTR(string_in,5,2),'99');

delim := SUBSTR(string_in,7,1);
IF delim <> '-' THEN
RAISE delimiter;
END IF;

part3 := TO_NUMBER(SUBSTR(string_in,8,4),'9999');

RETURN 'TRUE';
EXCEPTION
WHEN too_long THEN
dbms_output.put_line('More Than 11 Characters');
RETURN 'FALSE';
WHEN too_short THEN
dbms_output.put_line('Less Than 11 Characters');
RETURN 'FALSE';
WHEN delimiter THEN
dbms_output.put_line('Incorrect Delimiter');
RETURN 'FALSE';
WHEN OTHERS THEN
dbms_output.put_line('Some Other Issue');
RETURN 'FALSE';
END is_ssn;

RAISE_APPLICATION_ERROR ( ):

RAISE_APPLICATION_ERROR is a built-in procedure in oracle which is used to display the user-defined error messages

 along with the error number whose range is in between -20000 and -20999.

Whenever a message is displayed using RAISE_APPLICATION_ERROR, all previous transactions which are not

 committed within the PL/SQL Block are rolled back automatically (i.e. change due to INSERT, UPDATE, or DELETE statements

The General Syntax to use this procedure is:

RAISE_APPLICATION_ERROR (error_number, error_message);

DECLARE

  huge_quantity EXCEPTION;

  CURSOR product_quantity is

  SELECT p.product_name as name, sum(o.total_units) as units

  FROM order_tems o, product p

  WHERE o.product_id = p.product_id;

  quantity order_tems.total_units%type;

  up_limit CONSTANT order_tems.total_units%type := 20;

  message VARCHAR2(50);

BEGIN

  FOR product_rec in product_quantity LOOP

    quantity := product_rec.units;

     IF quantity > up_limit THEN

        RAISE huge_quantity;

     ELSIF quantity < up_limit THEN

      v_message:= 'The number of unit is below the discount limit.';

     END IF;

     Dbms_output.put_line (message);

  END LOOP;

 EXCEPTION

   WHEN huge_quantity THEN

                     raise_application_error(-2100, 'The number of unit is above the discount limit.');

 END;

You cannot use SQLCODE or SQLERRM directly in a SQL statement. Instead, you must assign their values to local variables,

  then use the variables in the SQL statement, as shown in the following example:

DECLARE

   err_num NUMBER;

   err_msg VARCHAR2(100);

BEGIN

   ...

EXCEPTION

   WHEN OTHERS THEN

      err_num := SQLCODE;

      err_msg := SUBSTR(SQLERRM, 1, 100);

      INSERT INTO errors VALUES (err_num, err_msg);

END;

Pragma

Pragma is a keyword in Oracle PL/SQL that is used to provide an instruction to the compiler.

There are 4 types of Pragma..

Pragma autonomous_Transaction--Autonomous transactions allow you to leave the context of the calling transaction, perform an independant transaction.

Pragma Exception_init---We can assign a name to unnamed system exceptions using a Pragma called EXCEPTION_INIT

Pragma restrict_references--Defines the purity level of a packaged program

Pragma serially_reusable

Pragma restrict_references

If any SQL statement inside the function body violates a rule, you get an error at run time (when the statement is parsed). To check for violations of the rules at compile time, you can use the compiler directive PRAGMA RESTRICT_REFERENCES. This pragma asserts that a function does not read and/or write database tables and/or package variables. Functions that do any of these read or write operations are difficult to optimize, because any call might produce different results or encounter errors.

Ur's

AmarAlam