Oracle Pragma RESTRICT_REFERENCES

Oracle Pragma RESTRICT_REFERENCES

The pragma was introduced to control and preserve the database state. It worked as a watchdog for the packaged function so that they should not modify the package or database state. It restricted the creation of the package, if its member function violated the specified purity level.

The pragma checks purity level of the function in four states

WNPS –Modification restricted on package variables

RNDS – SELECT query restricted on the database tables

RNPS – Restriction on the selection of package variables

Pragma RESTRICT_REFERENCES Syntax

While the Pragma must use minimum one restricted state in its specification, it may contain all the four too.

Pragma RESTRICT_REFERENCES Example Code

Let's take a look at the usage of pragma and impact of purity level in the below snapshot illustrations.

A package function F_CHANGE_LOC is created in the package PKS_REF_REF with WNPS level check. This implies that it cannot modify the package variable L_LOC.

Impact: Package PKS_RES_REF is created with compilation errors.

The function is created in RNDS purity check. The function would not allow the querying of database tables within its declare section or executable body section.

Impact: Package PKS_RES_REF is created with compilation errors.

The function F_UPD_SAL is created in RNDS purity level specification. The function would not allow the DML operations on the database table within its body.

Impact: Package PKS_RES_REF is created with compilation errors.