PRAGMA RESTRICT_REFERENCES is an instruction or a hint to the
compiler which is processed at compile time, not at runtime, it used to control
the side effects of PL/SQL Subprograms. Every PL/SQL Subprograms must follow
some rules in terms of transaction control and security.
PRAGMA
RESTRICT_REFERENCES mainly used in
PL/SQL Package and it ensures that a subprogram does not read or write database tables or package variables.
PRAGMA RESTRICT_REFERENCES 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.
From
Oracle 11g, PRAGMA RESTRICT REFERENCES is deprecated. Oracle recommends
using DETERMINISTIC and PARALLEL_ENABLE instead of RESTRICT REFERENCES.
- RNDS – Read No Database State.
function not to read or query tables
- RNPS – Read No Package
State. function not to read or reference package variables
- WNDS – Write No Database State.
function not modify database tables
- WNPS – Write No Package State.
function not modify package variables
- TRUST – function can be trusted
not to violate one or more rules. Used only when C or JAVA routines are
called from PL/SQL.
Without using pragma in
package, package successfully complied but it may result runtime error, and
developers need to change the code.
ORA-14551: cannot perform a DML operation inside a query
Using pragma in package,
package will be successfully complied with compile time error, which leads
developers need to change the code.
PLS-00452: Subprogram ‘send_emp_alloc’ violates
its associated pragma
CREATE OR REPLACE
PACKAGE pkg_transfergoods
IS
function
send_emp_alloc(p_empno integer) return number;
PRAGMA
restrict_references(send_emp_alloc, RNDS, RNPS, WNDS, WNPS);
END pkg_transfergoods;
/
To apply PRAGMA
restrict_references functionalities on all package functions – sub programs by
using Keyword = DEFAULT to all of the sub programs inside the package. It
applies (WNDS – Write No Database State to all package not modify database
tables) to all database tables which are used in package.
CREATE OR REPLACE
PACKAGE pkg_emp_alloc
IS
function
send_emp_alloc(p_empno integer) return number;
function get_emp_name(p_empno
integer) return varchar2;
PRAGMA
restrict_references(DEFAULT, WNDS);
END pkg_emp_alloc;
/
