PRAGMA RESTRICT_REFERENCES Oracle

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.     

What is PRAGMA

PRAGMA is an instruction or a hint or information to the compiler. Pragmas are processed at compile time, not at run time.

Syntax :-

CREATE OR REPLACE PACKAGE pkg_salary
IS
function get_emp_salary(p_empno integer) return number;    
PRAGMA restrict_references(get_emp_salary, RNDS, RNPS, WNDS, WNPS);
END pkg_salary;

RNDS – Read No Database State. Asserts that the function not to read or query tables
RNDS – Read No Package State. Asserts that the function not to read or reference package variables
WNDS – Write No Database State. Asserts that the function not modify database tables
WNPS – Write No Package State. Asserts that the function not modify package variables
TRUST – Asserts that the function can be trusted not to violate one or more rules. Used only when C or JAVA routines are called from PL/SQL.

Explanation :-

In the above example Function get_emp_salary is associated with PRAGMA restrict_references. Oracle conveying the compiler to follow four rules WNDS, WNPS, RNDS, RNPS. When the package body compiles and find any rules which violates any of the rules (RNDS, RNPS, WNDS, WNPS, TRUST) it will raise a compilation error. Note that if there is a PRAGMA derivative and violates any rules it will NOT raise any compiler error and it might raise run time error.

Consider the below examples

1) Function to raise employee salary 

CREATE OR REPLACE PACKAGE pkg_salary
IS
function get_emp_salary(p_empno integer) return number;
END pkg_salary;
/

CREATE OR REPLACE PACKAGE body pkg_salary
IS

function get_emp_salary(p_empno integer) return number
is
n_sal number;
Begin
update emp set salary = salary + salary * 0.1 where empno = p_empno
returning salary into n_sal;
commit;
return n_sal;
End get_emp_salary;

END pkg_salary;
/

Compiling……..

SQL> ../pkg_salary.sql;

Package created.

Package body created.

Executing………

SQL> select pkg_salary.get_emp_salary(10) from dual;
select pkg_salary.get_emp_salary(10) from dual
*
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query

So package specification and body compiled properly but unable to use it as it raise error while executing.

2) Function to raise employee salary – With PRAGMA restrict_references

CREATE OR REPLACE PACKAGE pkg_salary
IS
function get_emp_salary(p_empno integer) return number;
PRAGMA restrict_references(get_emp_salary, WNDS);
END pkg_salary;
/

CREATE OR REPLACE PACKAGE body pkg_salary
IS
function get_emp_salary(p_empno integer) return number
is
n_sal number;
Begin
update emp set salary = salary + salary * 0.1 where empno = p_empno
returning salary into n_sal;
commit;
return n_sal;
End get_emp_salary;

END pkg_salary;
/

Compiling……..

SQL> ../pkg_salary.sql;

Package created.

Warning: Package Body created with compilation errors.

SQL> show err
Errors for PACKAGE BODY PKG_SALARY:

LINE/COL ERROR
——– —————————————————————–
4/1      PLS-00452: Subprogram ‘GET_EMP_SALARY’ violates its associated
pragma

When we use PRAGMA restrict_references it raises a compiler error which helps developer to re-write his code.
Generally PRAGMA restrict_references are used with functions.

Note:-

DEFAULT key word applies PRAGMA restrict_references to all of the sub programs in side the package
See below example

CREATE OR REPLACE PACKAGE pkg_salary
IS
function get_emp_salary(p_empno integer) return number;
function get_emp_name(p_empno integer) return varchar2;
PRAGMA restrict_references(DEFAULT, WNDS);
END pkg_salary;
/

Package created.

Note:- Normally functions are not created for DML related processes and PRAGMA restrict_references are not necessary.