#

| Chapter 17 Calling PL/SQL Functions in SQL |

## 17.5 Calling Packaged Functions in SQL

As I describe in Chapter 16, Packages, the specification and body of a package are distinct; a specification can (and must) exist before its body has been defined. This feature of packages makes life complicated when it comes to calling functions in SQL. When a SELECT statement calls a packaged function, the only information available to it is the package specification. Yet it is the contents of the package body which determine whether that function is valid for execution in SQL. The consequence of this structure is that you will have to add code to your package specification in order to enable a packaged function for calling in SQL.

To use the official lingo, you must explicitly "assert" the purity level (the extent to which a function is free of side effects) of a stored function in a package specification. The Oracle Server can then determine when the package body is compiled whether the function violates that purity level. If so, an error will be raised and you then face the sometimes daunting task of figuring out where and how the violation occurs.

You assert a purity level for a function with the RESTRICT_REFERENCES pragma, explored in the next section.

### 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.1.1 Pragma violation errors

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

>     CREATE OR REPLACE PACKAGE BODY company_financials
>     IS
>        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 breaking 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.

17.4 Restrictions on PL/SQL Functions in SQL |  | 17.6 Column/Function Name Precedence

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.