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.