|PL/SQL User's Guide and Reference
Release 2 (9.2)
Part Number A96624-01
PL/SQL Language Elements, 40 of 52
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.
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.
This identifies a user-defined function.
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.
This asserts that the function reads no database state (does not query database tables).
This asserts that the function reads no package state (does not reference the values of packaged variables)
This asserts that the function can be trusted not to violate one or more rules.
This asserts that the function writes no database state (does not modify database tables).
This asserts that the function writes no package state (does not change the values of packaged variables).
You can declare the pragma
RESTRICT_REFERENCES only in a package spec or object type spec. You can specify up to four constraints (
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
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.
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.
The following pragma asserts that packaged function
balance writes no database state (
WNDS) and reads no package state (
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
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;