11.4 Restrictions on Calling PL/SQL from SQL

While calling stored functions from SQL is a powerful feature, it is important to understand how doing so might have unintended consequences. For example, imagine that one of your co-workers has written a stored function that, given a part number, returns the number of times that part is included in all open orders. The function is contained in a utilities package such as the following:

CREATE OR REPLACE PACKAGE pkg_util AS
  FUNCTION get_part_order_qty(pno IN VARCHAR2) RETURN NUMBER;
END pkg_util;

You have been tasked with generating a weekly inventory report, and you would like to make use of the function in one of your queries, as in:

SELECT p.part_nbr, p.name, s.name, p.inventory_qty,
  pkg_util.get_part_order_qty(p.part_nbr) open_order_qty
FROM part p, supplier s
WHERE p.supplier_id = s.supplier_id
ORDER BY s.name, p.part_nbr;

When you run the query, however, you are surprised to see the following error:

ORA-14551: cannot perform a DML operation inside a query

Upon checking the package body, you find that the get_part_order_qty function, along with calculating the number of times a part is included in all open orders, generates a request to restock the part by inserting a record into the part_order table if the calculated value exceeds the number in inventory. Had Oracle allowed your statement to be executed, your query would have resulted in changes to the database without your knowledge or consent.

11.4.1 Purity Level

In order to determine whether a stored function might have unintended consequences when called from an SQL statement, Oracle assigns a purity level to the function that answers the following four questions:

For each negative response to these questions, a designation is added to the purity level, as shown in Table 11-1.

Table 11-1. Purity level designations

Question #	Designation	Description
1	RNDS	Reads no database state
2	RNPS	Reads no package state
3	WNDS	Writes no database state
4	WNPS	Writes no package state

Therefore, a function with a purity level of {WNPS, WNDS} is guaranteed not to write to the database or modify package variables, but it may reference package variables and/or read from database tables. In order for a function to be called from an SQL statement, its purity level must at a minimum include the WNDS designation.

When using packaged functions in Oracle versions prior to release 8.1, it was required that the purity level be specified prior to calling a function from an SQL statement. This is accomplished by adding a pragma, or compiler directive, to the package specification. The RESTRICT_REFERENCES pragma follows the function declaration in the package specification, as demonstrated here:

CREATE OR REPLACE PACKAGE my_pkg AS
  FUNCTION my_func(arg1 IN NUMBER) RETURN VARCHAR2;
  PRAGMA RESTRICT_REFERENCES(my_func, RNPS, WNPS, WNDS);
END my_pkg;

When the package body is compiled, the code is checked against the designations listed in the RESTRICT_REFERENCES pragma. If the code does not meet the purity level asserted in the pragma, compilation fails with the following error:

PLS-00452: Subprogram 'MY_FUNC' violates its associated pragma

Therefore, you tell the compiler what your function will and won't do via the RESTRICT_REFERENCES pragma, the compiler checks that you are telling it the truth, and you are then free to call the function in any way supported by the function's purity level without further intervention from Oracle. If, on the other hand, your function was not included in a package, the Oracle engine would have no way to check the function's purity level prior to it being called, and Oracle would be forced to check the function's logic for side effects every time it is called.

The ability to assert a purity level is another reason to use packages for all your PL/SQL programming needs. Purity levels cannot be asserted for standalone procedures and functions.

Beginning with Oracle8i, you are no longer required to specify the purity level of functions in the package specification. If you choose not to, your functions will be checked each time they are called from SQL statements to ensure that they meet the minimum requirements. Whenever possible, however, you should include the pragma in your package specification so that the code can be examined at compile time rather than each time it is executed.

11.4.2 Trust Me...

One of the reasons Oracle has relaxed the requirement that the purity level be asserted at compile time is that PL/SQL can make calls to functions written in C and Java, which have no mechanisms similar to PL/SQL's PRAGMA for asserting purity. In order to allow functions written in different languages to call each other, Oracle introduced the TRUST keyword in Oracle8i. Adding TRUST to the RESTRICT_REFERENCES pragma for a function causes Oracle to:

Thus, a stored function whose RESTRICT_REFERENCES pragma includes WNDS and TRUST could make calls to other PL/SQL functions that do not specify RESTRICT_REFERENCES pragmas and/or external C and Java functions and still be callable from SQL statements. In the case of external C or Java calls, you will need to include the TRUST designation in your function's RESTRICT_REFERENCES pragma if you want to call the function from SQL, since the C or Java source code is not available to the server for inspection.

To use TRUST, simply append it to the end of the purity designation list, as in:

CREATE OR REPLACE PACKAGE my_pkg AS
  FUNCTION my_func(arg1 IN NUMBER) RETURN VARCHAR2;
  PRAGMA RESTRICT_REFERENCES(my_func, RNPS, WNPS, WNDS, TRUST);
END my_pkg;

While you may be tempted to always use TRUST when asserting the purity level of your functions, this is a feature that should be used sparingly. Once you add the TRUST designation to your pragma, future changes to your function or any downstream functions that violate WNDS will not be caught at either compilation or runtime, causing your queries to have unintended consequences.

11.4.3 Other Restrictions

In addition to the WNDS requirement, Oracle checks that each function invoked from an SQL statement abides by the following rules:

The first three restrictions are designed to protect against changes that could alter the operational environment of the parent query. The fourth restriction might be relaxed in a future release of the Oracle server, but it's a bit of a stretch to imagine how calling a function that returns a nested table of objects would add value to a SELECT statement.^[4]

^[4] Unless it is wrapped in a TABLE expression in the FROM clause.

11.4.4 Consistency Issues

All of the restrictions detailed earlier must be met in order to call a stored function from a query. There is one additional topic, however, that is not so much a restriction as a pitfall: queries executed by stored functions will see the effects of transactions committed since the parent query began execution, while the parent query will not. Whether this is due to a design flaw is open to debate. Depending on the database environment and length of your queries, the impact could range from nonexistent to severe.

For example, if you are running reports at 2 P.M. against a data-mart that is loaded between 2 and 4 A.M., your stored functions will see the same data as the parent query as long as the query finishes execution before the next data load. On the other hand, a long-running query executed against an OLTP database during peak activity might yield severe inconsistencies between the results returned by the parent query and those returned by the stored functions. Therefore, you should carefully consider your operating environment and the expected query runtimes before including stored function calls in your SQL statements.