17.5.1 The RESTRICT_REFERENCES Pragma

As I've mentioned, a pragma is a special directive to the PL/SQL compiler. If you have ever created a programmer-defined, named exception, you have already encountered your first pragma. In the case of the RESTRICT_REFERENCES pragma, you are telling the compiler the purity level you believe your function meets or exceeds.

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 (you do not specify the pragma in the package body).

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 different 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.

Notice that only the WNDS level is mandatory in the pragma. That is consistent with the restriction that stored functions in SQL may not execute an UPDATE, INSERT, or DELETE statement. All other states are optional. You can list them in any order, but you must include the WNDS argument. No one argument implies another argument. I can write to the database without reading from it. I can read a package variable without writing to a package variable.

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

PACKAGE company_financials
IS
   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 of the functions. Oracle Corporation recommends that you assert the highest possible purity levels so that the compiler will never reject the function unnecessarily.

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

17.5.2 Asserting Purity Level with Package Initialization Section

If your package contains an initialization section (executable statements after a BEGIN statement in the package body), you must also assert the purity level of that section. The initialization section is executed automatically the first time any package object is referenced. So if a packaged function is used in a SQL statement, it will trigger execution of that code. If the initialization section modifies package variables or database information, the compiler needs to know about that through the pragma.

You can assert the purity level of the initialization section either explicitly or implicitly. To make an explicit assertion, use the following variation of the pragma RESTRICT_REFERENCES:

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

Instead of specifying the name of the function, you include the name of the package itself, followed by all the applicable state arguments. In the following argument I assert only WNDS and WNPS because the initialization section reads data from the configuration table and also reads the value of a global variable from another package (session_pkg.user_id).

PACKAGE configure
IS
   PRAGMA RESTRICT_REFERENCES (configure, WNDS, WNPS);
   user_name VARCHAR2(100);
END configure;

PACKAGE BODY configure
IS
BEGIN
   SELECT lname || ', ' || fname INTO user_name
     FROM user_table
    WHERE user_id = session_pkg.user_id;
END configure;

Why can I assert the WNPS even though I do write to the user_name package variable? The answer is that it's a variable from this same package, so the action is not considered a side effect.

You can also implicitly assert the purity level of the package's initialization section by allowing the compiler to infer that level from the purity level(s) of all the pragmas for individual functions in the package. In the following version of the company package, the two pragmas for the functions allow the Oracle Server to infer a combined purity level of RNDS and WNPS for the initialization section. This means that the initialization section cannot read from the database and cannot write to a package variable.

PACKAGE company
IS
   FUNCTION get_company (company_id_in IN VARCHAR2)
      RETURN company%ROWTYPE;

   FUNCTION deactivate_company (company_id_in IN company.company_id%TYPE)
      RETURN VARCHAR2;

   PRAGMA RESTRICT_REFERENCES (get_company, RNDS, WNPS);
   PRAGMA RESTRICT_REFERENCES (deactivate_name, WNPS);
END company;

Generally, you are probably better off providing an explicit purity level assertion for the initialization section. This makes it easier for those responsible for maintaining the package to understand both your intentions and your understanding of the package.