The PRAGMA RESTRICT_REFERENCES (Oracle8 and earlier)

Prior to Oracle8i, if you wanted to invoke within SQL a function that was defined inside a package specification, you would have had to provide a RESTRICT_REFERENCES pragma (a compiler directive or instruction) for that function. This pragma asserts the "purity level" of the function, in essence promising Oracle that the function has the specified side effects (or, more to the point, lack thereof).

Working with the RESTRICT_REFERENCES pragma can be very frustrating, so it was a great relief to many a PL/SQL developer when in Oracle8i this pragma was made unnecessary. However, this section talks briefly about some of the rules associated with this pragma for those still using Oracle8 and earlier.

You need a separate PRAGMA statement for each packaged function you wish to use in a SQL statement, and it must come after the function declaration in the package specification. To assert a purity level with the pragma, use the following syntax:

PRAGMA RESTRICT_REFERENCES (function_name, WNDS [, WNPS] [, RNDS] [, RNPS])

where function_name is the name of the function whose purity level you wish to assert, and the four codes have the following meanings:

WNDS

Writes No Database State. Asserts that the function does not modify any database tables.

WNPS

Writes No Package State. Asserts that the function does not modify any package variables.

RNDS

Reads No Database State. Asserts that the function does not read any database tables.

RNPS

Reads No Package State. Asserts that the function does not read any package variables.

Here is an example of two different purity-level assertions for functions in the company_financials package:

PACKAGE company_financialsIS FUNCTION company_type (type_code_in IN VARCHAR2) RETURN VARCHAR2; FUNCTION company_name ( company_id_in IN company.company_id%TYPE) RETURN VARCHAR2; PRAGMA RESTRICT_REFERENCES ( company_type, WNDS, RNDS, WNPS, RNPS); PRAGMA RESTRICT_REFERENCES (company_name, WNDS, WNPS, RNPS);END company_financials;

In this package, the company_name function reads from the database to obtain the name for the specified company. Notice that I placed both pragmas together at the bottom of the package specification; the pragma does not need to immediately follow the function specification. I also went to the trouble of specifying the WNPS and RNPS arguments for both functions. Oracle recommends that you assert the highest possible purity levels so that the compiler will never reject the function unnecessarily.

If a function you want to call in SQL calls a procedure or function in a package, you must also provide a RESTRICT_REFERENCES pragma for that program. You cannot call a procedure directly in SQL, but if it is going to be executed indirectly from within SQL, it still must follow the rules.

If your function violates its pragma, you will receive the PLS-00452 error: subprogram `program' violates its associated pragma. Suppose, for example, that the body of the company_financials package looks like this:

CREATE OR REPLACE PACKAGE BODY company_financialsIS FUNCTION company_type (type_code_in IN VARCHAR2) RETURN VARCHAR2 IS v_sal NUMBER; BEGIN SELECT sal INTO v_sal FROM emp WHERE empno = 1; RETURN 'bigone'; END; FUNCTION company_name (company_id_in IN company.company_id%TYPE) RETURN VARCHAR2 IS BEGIN UPDATE emp SET sal = 0; RETURN 'bigone'; END;END company_financials;

When I attempt to compile this package body I will get the following error:

3/4 PLS-00452: Subprogram 'COMPANY_TYPE' violates its associated pragma

because the company_type function reads from the database and I have asserted the RNDS purity level. If I remove that silly SELECT statement, I will then receive this error:

11/4 PLS-00452: Subprogram 'COMPANY_NAME' violates its associated pragma

because the company_name function updates the database and I have asserted the WNDS level. You will sometimes look at your function and say, "Hey, I absolutely do not violate my purity level. There is no UPDATE, DELETE, or UPDATE around." Maybe not. But there is a good chance that you are calling a built-in package or in some other way breakingthe rules. Again, if you are running Oracle8i and above, you no longer need to deal with RESTRICT_REFERENCES. The runtime engine will automatically check your code for any violations.