Pages

OracleEBSpro is purely for knowledge sharing and learning purpose, with the main focus on Oracle E-Business Suite Product and other related Oracle Technologies.

I'm NOT responsible for any damages in whatever form caused by the usage of the content of this blog.

I share my Oracle knowledge through this blog. All my posts in this blog are based on my experience, reading oracle websites, books, forums and other blogs. I invite people to read and suggest ways to improve this blog.


Sunday, February 24, 2013

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

1 comment:

  1. Thank you for providing good information to the students it will be very helpful to them for career in future. Oracle R12 Financials Training in Ameerpet

    ReplyDelete