Purity Levels Of Function - PRAGMA RESTRICT_REFERENCES

An Oracle database cannot determine the work done by a packaged function when the function is executed from inside a DML statement. Therefore, if packaged functions are to be executed from within a DML statement, developers must use a PRAGMA to define a purity level for functions defined as part of the package spec. A PRAGMA is a compiler directive that instructs the compiler to handle code in a specific manner. To define a purity level for a packaged function, the PRAGMA RESTRICT_REFERENCES is used.

A purity level defined within a package spec instructs Oracle about the kinds of operations that the function performs. Below are the four purity levels that can be defined for a function.

WNDS	Write No Database State. The function doesn’t alter the contents of any database table.
RNDS	Read No Database State. The function doesn’t read the contents of any database table.
WNPS	Write No Package State. The function doesn’t alter any variables within another package.
RNPS	Read No Package State. The function doesn’t read any variables within another package.

The following example shows, how a purity level could mentioned for a function, while defining the package specification. The example makes the function get_id to the WNDS purity level.

CREATE OR REPLACE PACKAGE P_TEST

FUNCTION GET_ID RETURN NUMBER;

PRAGME RESTRICT_REFERENCES(GET_ID,WNDS);

END;

Note: The purity level for the functions can also be defined for the functions used in an Oracle object type.