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.
| 
No comments:
Post a Comment