Pragma Introduction
In my package's functions and procedure,
sometimes I have some rules to control purity rules of the 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.
one more easy example to check :
CREATE OR REPLACE PACKAGE rnds_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 rnds_PKG
AS
FUNCTION GET_EMPLOYEE_NAME (IN_EMPLOYEE_ID NUMBER)
RETURN VARCHAR2
IS
v_EMPLOYEE_NAME VARCHAR2 (100);
BEGIN
SELECT ENAME
INTO v_EMPLOYEE_NAME
FROM EMP
WHERE EMPNO = IN_EMPLOYEE_ID;
RETURN v_EMPLOYEE_NAME;
END;
END;
select rnds_pkg.get_employee_name(7369) from dual;
Comments