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;  
   
 CREATE OR REPLACE PACKAGE BODY XXX_PKG  
 AS  
   PROCEDURE SET_USER  
   IS  
   BEGIN  
    GN$USER := 'Mahmoud A. El-Sayed';  
   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  
   FUNCTION GET_EMPLOYEE_NAME (IN_EMPLOYEE_ID NUMBER)  
    RETURN VARCHAR2;  
   
   PRAGMA RESTRICT_REFERENCES (GET_EMPLOYEE_NAME, RNDS);  
 END;  
   
 CREATE OR REPLACE PACKAGE BODY XXX_PKG  
 AS  
   FUNCTION GET_EMPLOYEE_NAME (IN_EMPLOYEE_ID NUMBER)  
    RETURN VARCHAR2  
   IS  
    LV$EMPLOYEE_NAME  VARCHAR2 (100);  
   BEGIN  
    SELECT ENAME  
     INTO LV$EMPLOYEE_NAME  
     FROM EMP  
     WHERE EMPNO = IN_EMPLOYEE_ID;  
   
    RETURN LV$EMPLOYEE_NAME;  
   END;  
 END;  

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
Mahmoud A. El-Sayed