RESTRICT_REFERENCES Pragma

To be callable from SQL statements, a stored function must obey certain "purity" rules, which are meant to control side effects. (See "Controlling Side Effects of PL/SQL Subprograms".) If any SQL statement inside the function body violates a rule, you get an error at run time (when the statement is parsed). To check for violations of the rules, you can use the pragma (compiler directive) RESTRICT_REFERENCES. The pragma asserts that a function does not read and/or write database tables and/or package variables. For more information, see Oracle9i Application Developer's Guide - Fundamentals.

Syntax

Text description of the illustration restrict_references_pragma.gif

Keyword and Parameter Description

DEFAULT

This specifies that the pragma applies to all functions in the package spec or object type spec. You can still declare the pragma for individual functions. Such pragmas override the default pragma.

function_name

This identifies a user-defined function.

PRAGMA

This keyword signifies that the statement is a pragma (compiler directive). Pragmas are processed at compile time, not at run time. They do not affect the meaning of a program; they simply convey information to the compiler.

RNDS

This asserts that the function reads no database state (does not query database tables).

RNPS

This asserts that the function reads no package state (does not reference the values of packaged variables)

TRUST

This asserts that the function can be trusted not to violate one or more rules.

WNDS

This asserts that the function writes no database state (does not modify database tables).

WNPS

This asserts that the function writes no package state (does not change the values of packaged variables).

Usage Notes

You can declare the pragma RESTRICT_REFERENCES only in a package spec or object type spec. You can specify up to four constraints (RNDS, RNPS, WNDS, WNPS) in any order. To call the function from parallel queries, you must specify all four constraints. No constraint implies another. For example, WNPS does not imply RNPS.

When you specify TRUST, the function body is not checked for violations of the constraints listed in the pragma. The function is trusted not to violate them.

If you specify DEFAULT instead of a function name, the pragma applies to all functions in the package spec or object type spec (including, in the latter case, the system-defined constructor). You can still declare the pragma for individual functions. Such pragmas override the default pragma.

A RESTRICT_REFERENCES pragma can apply to only one function declaration. So, a pragma that references the name of overloaded functions always applies to the nearest foregoing function declaration.

Examples

The following pragma asserts that packaged function balance writes no database state (WNDS) and reads no package state (RNPS):

CREATE PACKAGE loans AS
   ...
   FUNCTION balance RETURN NUMBER;
   PRAGMA RESTRICT_REFERENCES (balance, WNDS, RNPS);
END loans;

A pragma that references the name of overloaded functions always applies to the nearest foregoing function declaration. So, in the following example, the pragma applies to the second declaration of credit_ok:

CREATE PACKAGE loans AS
   FUNCTION credit_ok (amount_limit NUMBER) RETURN BOOLEAN;
   FUNCTION credit_ok (time_limit DATE) RETURN BOOLEAN;
   PRAGMA RESTRICT_REFERENCES (credit_ok, WNDS);
   ...
END loans;

Related Topics

AUTONOMOUS_TRANSACTION Pragma, EXCEPTION_INIT Pragma, SERIALLY_REUSABLE Pragma