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

 

PRAGMA RESTRICT_REFERENCES

 

PRAGMA RESTRICT_REFERENCES:

PRAGMA RESTRICT_REFERENCES uses to control the side effects of PL/SQL Subprograms. Every PL/SQL Subprograms must follow some rules in terms of transaction control and security. 

The RESTRICT REFERENCES Pragma asserts that a user-defined subprogram does not read or write database tables or package variables.

syntax:

PRAGMA RESTRICT_REFERENCES ( [SUBPROGRAM_NAME/DEFAULT] , [RNDS, WNDS, RNPS, WNPS, TRUST]);

DEFAULT

Specifies that the pragma applies to all subprograms in the package specification or object type specification (including the system-defined constructor for object types).

You can still declare the pragma for individual subprograms, overriding the DEFAULT pragma.

RNDS

Asserts that the subprogram reads no database state (does not query database tables).

WNDS

Asserts that the subprogram writes no database state (does not modify tables).

RNPS

Asserts that the subprogram reads no package state (does not reference the values of packaged variables)

You cannot specify RNPS if the subprogram invokes the SQLCODE or SQLERRM function.

WNPS

Asserts that the subprogram writes no package state (does not change the values of packaged variables).

You cannot specify WNPS if the subprogram invokes the SQLCODE or SQLERRM function.

TRUST

Asserts that the subprogram can be trusted not to violate one or more rules.

When you specify TRUST, the subprogram body is not checked for violations of the constraints listed in the pragma. The subprogram is trusted not to violate them. Skipping these checks can improve performance. TRUST is needed for functions written in C or Java that are invoked from PL/SQL, since PL/SQL cannot verify them at run time.

About It:

• The name of a user-defined subprogram, usually a function.
• If subprogram_name is overloaded, the pragma applies only to the most recent subprogram declaration.
• A RESTRICT_REFERENCES pragma can appear only in a package specification or object type specification. Typically, this pragma is specified for functions. If a function calls procedures, specify the pragma for those procedures also.
• To invoke a subprogram from parallel queries, you must specify all four constraints—RNDS, WNDS, RNPS, and WNPS. No constraint implies another.
• However, by definition, autonomous routines never violate the rules read no database state (RNDS) and write no database state (WNDS) no matter what they do. This can be useful

 

Example: declaring restrict reference with autonomous transaction:

When you invoke the packaged function log_msg from a query, it inserts a message into database table debug_output without violating the rule write no database state.

— create the debug table
CREATE TABLE debug_output (msg VARCHAR2(200));

— create the package spec
CREATE PACKAGE debugging AS
   FUNCTION log_msg (msg VARCHAR2) RETURN VARCHAR2;
   PRAGMA RESTRICT_REFERENCES(log_msg, WNDS, RNDS);
END debugging;
/
— create the package body
CREATE PACKAGE BODY debugging AS
   FUNCTION log_msg (msg VARCHAR2) RETURN VARCHAR2 IS
   PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
      — the following insert does not violate the constraint
      — WNDS because this is an autonomous routine
      INSERT INTO debug_output VALUES (msg);
      COMMIT;
      RETURN msg;
   END;
END debugging;
/
— invoke the packaged function from a query
DECLARE
   my_emp_id    NUMBER(6);
   my_last_name VARCHAR2(25);
   my_count     NUMBER;
BEGIN
   my_emp_id := 120;
   SELECT debugging.log_msg(last_name)
   INTO my_last_name FROM employees
   WHERE employee_id = my_emp_id;
— even if you roll back in this scope, the insert into ‘debug_output’ remains
— committed because it is part of an autonomous transaction
   ROLLBACK;
END;
/

example for different state:

RNDS:

CREATE OR REPLACE PACKAGE debugging AS
   FUNCTION log_msg (msg VARCHAR2) RETURN VARCHAR2;
   PRAGMA RESTRICT_REFERENCES(log_msg, RNDS); — (read no database state)
END debugging;
/

CREATE OR REPLACE PACKAGE BODY debugging AS
str VARCHAR2(10);
   FUNCTION log_msg (msg VARCHAR2) RETURN VARCHAR2 IS

   BEGIN
      SELECT msg INTO debugging.str FROM debug_output WHERE msg=’chinu’; –this statement will cause error
      INSERT INTO debug_output VALUES (msg);
      COMMIT;
      RETURN msg;
   END;

WNDS:

CREATE OR REPLACE PACKAGE debugging AS
   FUNCTION log_msg (msg VARCHAR2) RETURN VARCHAR2;
   PRAGMA RESTRICT_REFERENCES(log_msg, WNDS); — (read no database state)
END debugging;
/

CREATE OR REPLACE PACKAGE BODY debugging AS
str VARCHAR2(10);
   FUNCTION log_msg (msg VARCHAR2) RETURN VARCHAR2 IS

   BEGIN
      SELECT msg INTO debugging.str FROM debug_output WHERE msg=’chinu’;
      INSERT INTO debug_output VALUES (msg); –this statement will cause error
      COMMIT;
      RETURN msg;
   END;

RNPS:

 CREATE OR REPLACE PACKAGE debugging AS
   FUNCTION log_msg (msg VARCHAR2) RETURN VARCHAR2;
   PRAGMA RESTRICT_REFERENCES(log_msg, RNPS);
END debugging;
/

CREATE OR REPLACE PACKAGE BODY debugging AS
str VARCHAR2(10);
   FUNCTION log_msg (msg VARCHAR2) RETURN VARCHAR2 IS

   BEGIN
      IF str=’hi’ THEN       –this statement will cause error
        dbms_output.put_line(str); END IF;     
      SELECT msg INTO debugging.str FROM debug_output WHERE msg=’chinu’;
      INSERT INTO debug_output VALUES (msg);
      COMMIT;
      RETURN msg;
   END;
END debugging;
/

WNPS:

CREATE OR REPLACE PACKAGE debugging AS
   FUNCTION log_msg (msg VARCHAR2) RETURN VARCHAR2;
   PRAGMA RESTRICT_REFERENCES(log_msg, WNPS);
END debugging;
/

CREATE OR REPLACE PACKAGE BODY debugging AS
str VARCHAR2(10);
   FUNCTION log_msg (msg VARCHAR2) RETURN VARCHAR2 IS

   BEGIN
      IF str=’hi’ THEN
        dbms_output.put_line(str); END IF;     
      SELECT msg INTO debugging.str FROM debug_output WHERE msg=’chinu’; –this statement will cause error
      INSERT INTO debug_output VALUES (msg);
      COMMIT;
      RETURN msg;
   END;
END debugging;
/

TRUST: This state is something, which is not checking the subprogram body whether it is violating the constraint imposed by pragma or not. generally it is used when a function (written in C or java) is calling from the PL/SQL code.

Example: (Try the below program which imposed “TRUST” pragma state.)

CREATE OR REPLACE PACKAGE BODY debugging AS
   str VARCHAR2(10);
   FUNCTION log_msg (msg VARCHAR2) RETURN VARCHAR2 IS

   BEGIN
      IF str=’hi’ THEN
      dbms_output.put_line(str);  END IF;     
      SELECT msg INTO debugging.str FROM debug_output WHERE msg=’chinu’;
      INSERT INTO debug_output VALUES (msg);
      COMMIT;
      RETURN msg;
   END;
END debugging;
/

 for other pragma states except “TRUST” will throw error in above subprogram.