PRAGMA EXCEPTION_INIT

 

PRAGMA EXCEPTION_INIT:

The EXCEPTION_INIT pragma associates a user-defined exception name with an Oracle Database error number. You can intercept any Oracle Database error number and write an exception handler for it, instead of using the OTHERS handler.

Syntax: PRAGMA EXCEPTION_INIT(exception_name, -error_number);

Where to use :To handle error conditions (typically ORA-n 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.

About: In PL/SQL, the pragma EXCEPTION_INIT tells the compiler to associate an exception name with an Oracle Database 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.

For Example: Let’s consider the product table and order_items table from sql joins.

Here product_id is a primary key in product table and a foreign key in order_items table.
If we try to delete a product_id from the product table when it has child records in order_id table an exception will be thrown with oracle code number -2292.
We can provide a name to this exception and handle it in the exception section as given below.

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;
/