11.4 Restrictions on Calling PL/SQL from SQL

Although 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 INNER JOIN supplier s

  ON 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

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

Does the function read from database tables?
Does the function reference any global package variables?
Does the function write to any database tables?
Does the function modify any global package variables?

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. For a function to be called from a 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 a 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, and the compiler checks to see if you are telling it the truth.

Beginning with Oracle8i, you are no longer required to specify the purity level of functions in the package specification. All functions called from queries, whether stand-alone or within packages, are checked at runtime to ensure that there are no side effects. Nevertheless, you may want to consider using the RESTRICT_REFERENCES pragma so that you avoid any surprises later on.

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

Treat the function as if it satisfies the pragma without actually checking the code.
Treat any functions or procedures called from the function that have the TRUST keyword as if they satisfy the pragma as well.

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;

11.4.3 Other Restrictions

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

The function can't end the current transaction using COMMIT or ROLLBACK.
The function can't alter a transaction by creating savepoints or rolling back to a previously defined savepoint.
The function can't issue an ALTER SYSTEM or ALTER SESSION statement.
All parameter types, including the return type, must be standard SQL types such as VARCHAR2, NUMBER, and DATE. PL/SQL types such as BOOLEAN and RECORD are not allowed.
All parameters must be IN parameters. OUT and IN OUT parameters are not allowed.

The first three restrictions are designed to protect against changes that could alter the operational environment of the parent query. The fourth and fifth restrictions ensure that the data being returned from the PL/SQL function can be handled by the SQL statement.