PL/SQL : Pragma Restrict References

Introduction 

In my packages functions and procedure, sometimes I have some rules to control purity rules in my database like reading from tables only, not query the database, ...... etc 

So I will  use PRAGMA RESTRICT_REFERENCES to control and preserve the database state in database packages.

Syntax of PRAGMA RESTRICT_REFERENCES

PRAGMA RESTRICT_REFERENCES(SUBPROGRAM_NAME, IDENTIFIER)

SUBPROGRAM_NAME can be default which will affect all the subprograms in packages or subprogram name(function or procedure) in package

IDENTIFIER can be RNDS, WNDS, RNPS, WNPS and TRUST. I will explain every one separately.

1- RNDS

select query is restricted against any of database tables

2- WNDS

DML operations are restricted against any of database tables 

3- RNPS

Selection of package variables is restricted 

4- WNPS

Modification in packages variables is restricted

5- TRUST

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

Demo

Test WNPS 

let's create packages XXX_PKG contains GN$USER package variables and SET_USER function to set GN$USER and I will use WNPS purity check in SET_USER function

 CREATE OR REPLACE PACKAGE XXX_PKG  
 AS  
   GN$USER  VARCHAR2 (100);  


PROCEDURE SET_USER;  
   

   PRAGMA RESTRICT_REFERENCES (SET_USER, WNPS);  

 END;  

ATE OR REPLACE PACKAGE BODY XXX_PKG  
 AS
CR
E  
   PROCEDURE SET_USER  
   IS  

. El-Sayed
BEGIN  
    GN$USER := 'Mahmoud 
A';  
   END;  


END;  

When compile the package body compiler will raise the below error

PLS-00452: Subprogram 'SET_USER' violates its associated pragma

Test RNDS

let's create packages XXX_PKG contains GET_EMPLOYEE_NAME function which query from EMP database table.

 CREATE OR REPLACE PACKAGE XXX_PKG  
 AS  

CTION GET_EMPLOYEE_NAME (IN_EMPLOYEE_ID NUMBER)  
    R
FU
NETURN VARCHAR2;  
   

PRAGMA RESTRICT_REFERENCES (GET_EMPLOYEE_NAME, RNDS);  

END;  


TE OR REPLACE PACKAGE BODY XXX_PKG  
 AS 
CRE
A 

FUNCTION GET_EMPLOYEE_NAME (IN_EMPLOYEE_ID NUMBER)  
  
RETURN VARCHAR2  
   IS  

2 (100);  
   BEGIN  
    SELECT ENAME 
LV$EMPLOYEE_NAME  VARCHA
R 
     INTO LV$EMPLOYEE_NAME  
     FROM EMP  

URN LV$EMPLOYEE_NAME;  
   END;  
 E
WHERE EMPNO = IN_EMPLOYEE_ID;  
   
    RE

TND;  

When compile the package body compiler will raise the below error

PLS-00452: Subprogram 'GET_EMPLOYEE_NAME' violates its associated pragma

You can test the others purity checks, Just I made two examples to illustrate the idea.

Thanks

Sekhar