Oracle PL/SQL Programming: Guide to Oracle8i Features

In Oracle8 i , Oracle has made changes in the way you define programs and assert their purity levels , or lack of side effects.

10.2.1 RESTRICT_REFERENCES Pragma

Back in Oracle 7.3, PL/SQL offered a new pragma (compiler directive) called RESTRICT_REFERENCES. This pragma was used to assert to the PL/SQL compiler the purity level of a packaged procedure or function. The RESTRICT_REFERENCES pragma had to be included in the package specification if you were to use that program inside a SQL statement (directly or indirectly).

This pragma has always been a major headache for PL/SQL developers, and as of Oracle 8.1 it is no longer required. The SQL engine will determine at the time you run your SQL statement whether or not the function call is valid. Many of the restrictions on how and when you can use functions in SQL have been relaxed , but the following rules still apply:

• Named notation is not supported; you can only use positional notation. However, you cannot provide actual parameters for formal parameters with default values.

• A function called from a query or DML statement may not end the current transaction, commit or roll back to a savepoint, or ALTER the system or session.

• A function called from a SELECT statement or from a parallelized DML statement may not execute a DML statement or otherwise modify the database.

• A function called from a DML statement may not read or modify the particular table being modified by that DML statement.

The last three restrictions can be avoided if you take advantage of PL/SQL 8.1's autonomous transaction feature (described in Chapter 2 ) in your function. With this approach, the function executes in its own transaction space, so a commit or DML statement has no impact on the "calling" SQL statement.

Oracle 8.1 also offers much better error messages. Instead of the double-negative, mind-bending:

ORA-06571: Function TOTCOMP does not guarantee not to update database

you will see this much more sensible message:

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

You can still include the PRAGMA RESTRICT_REFERENCES if you wish. You might, for example, want to use the PL/SQL compiler as a verification mechanism. It will tell you if your function has the side effects that you would expect, informing you of potential complications in underlying layers of code.

10.2.2 Related New Developments

Oracle has added several new options to the way you define programs and assert their purity levels to make it easier to deploy stored code in the new world of integration with Java and C, as well as with parallel databases.

10.2.2.1 The TRUST option

You no longer need to use PRAGMA RESTRICT_REFERENCES to enable PL/SQL programs for use in SQL; the runtime engine will figure it out all by itself. However, what if you want to call Java or C routines from within SQL? You can now build wrappers around code written in those languages to make them accessible in PL/SQL. But the SQL engine cannot analyze those programs to determine freedom from side effects.

Oracle has, therefore, added a new option in the PRAGMA RESTRICT_REFERENCES statement: TRUST. The complete syntax for this pragma is now as follows :

PRAGMA RESTRICT_REFERENCES ( function_name , WNDS [, WNPS] [, RNDS] [, RNPS] [, TRUST] );

The TRUST option makes it easier to call Java and C routines from functions that have a RESTRICT_REFERENCES pragma. When TRUST is used in the pragma, the restrictions listed in that pragma are not actually enforced. They are, instead, simply trusted to be true. Now that's flexibility!

You can deploy the TRUST option on the top-level program that then calls other programs, or you can use TRUST with each of the lower-level programs, allowing you to then assert whatever purity levels are necessary on routines that call them. Let's look at some examples.

When calling from a section of code that is using pragmas to a section that is not, there are two likely usage styles. One is to place a pragma on the routine to be called, for example, on a call specification for a Java method. Calls from PL/SQL to this method will then complain if the method is less restricted than the calling function.

The following package declares two programs. The maxsal function is based on a Java method; it uses the TRUST option to assert WNDS ( writes no database state) and RNDS (reads no database state). The analyze_compensation procedure, which needs to assert WNDS so that it can be called from within a SQL query (indirectly), can then call maxsal. Note that if I wanted to assert WNPS or use this procedure in a function that is called in a WHERE clause, it would be rejected because the underlying pragma does not assert this purity level:

CREATE OR REPLACE PACKAGE personnel IS FUNCTION maxsal (ssn_in IN VARCHAR2) RETURN NUMBER IS LANGUAGE JAVA NAME 'Person.max_salary (Java.lang.String) return long'; PRAGMA RESTRICT_REFERENCES (maxsal, WNDS, RNDS, TRUST); PROCEDURE analyze_compensation (ssn_in IN VARCHAR2); PRAGMA RESTRICT_REFERENCES (analyze_compensation, WNDS); END;

In the next example, I have rewritten the personnel package specification to demonstrate the second usage style. Here, the top-level program, analyze_compensation, is the only program with a RESTRICT_REFERENCES pragma. I still won't have any problem calling maxsal and deploying it inside SQL, because I have added the TRUST option:

CREATE OR REPLACE PACKAGE personnel IS FUNCTION maxsal (ssn_in IN VARCHAR2) RETURN NUMBER IS LANGUAGE JAVA NAME 'Person.max_salary (Java.lang.String) return long'; PROCEDURE analyze_compensation (ssn_in IN VARCHAR2); PRAGMA RESTRICT_REFERENCES ( analyze_compensation, WNDS, WNPS, TRUST); END;

The SQL runtime engine will not even check the purity level of maxsal; it has been instructed to "trust" the program.

10.2.2.2 Deterministic functions

What, you might ask, is a deterministic function ? It's a program that doesn't have any free will. If you pass it X and Y values for its arguments, it always returns the same value(s). According to Oracle documentation:

A function that is dependent solely on the values passed into it as arguments, and does not meaningfully reference or modify the contents of package variables or the database, or have any other side-effects, is termed deterministic.

Here is an example of a deterministic function:

CREATE FUNCTION ceo_compensation ( stock_increase IN NUMBER, layoffs IN NUMBER) RETURN NUMBER IS BEGIN RETURN 1000000 * (layoffs + stock_increase); END;

Even if the company's assembly line killed three workers due to faulty wiring, as long as the stock price goes up and the CEO lays off a bunch of people, he receives a huge paycheck.

What's the big deal about this sort of function? If Oracle can be sure that the function will return the same value for a given set of arguments, then it can avoid re-executing the function if it remembers the value from the last execution with those arguments.

If you tell Oracle that a function is deterministic, this declaration becomes an optimization hint. The Oracle8 i engine then knows that this function does not need to be called repetitively for the same arguments. It can instead cache the results and then simply use those results again and again.

This feature is especially useful within a parallelized query or parallelized DML statement. In addition, two new features in Oracle8 i require that any function used with them be declared deterministic:

• Any function used in a function-based index is required to be deterministic.

• Any function used in a materialized view must be deterministic if that view is to be marked ENABLE QUERY REWRITE.

How do you declare a function to have this property? Simply place the DETERMINISTIC keyword after the return value type in the header of your function. In the following block, I have redefined the ceo_compensation function to be deterministic:

CREATE FUNCTION ceo_compensation ( stock_increase IN NUMBER, layoffs IN NUMBER) RETURN NUMBER DETERMINISTIC IS BEGIN RETURN 1000000 * (layoffs + stock_increase); END;

You can add this DETERMINISTIC clause in a header of any of the following:

• A standalone function

• A packaged function

• An object type function

Do not place the DETERMINISTIC keyword in the header of the function in the package body or object type body. You can also declare a procedure to be deterministic. There are several situations in which deterministic functions are not required but are strongly recommended by Oracle:

• Any materialized view or snapshot that is declared REFRESH FAST should only call deterministic functions.

• If your function is called in a WHERE, ORDER BY, or GROUP BY clause, or is a MAP or ORDER method of a SQL type, or in any other way helps determine whether or where a row should appear in a result set, that function should be deterministic. (This characteristic was specified through the RESTRICT_REFERENCES pragma in earlier versions of Oracle with the WNDS and WNPS purity levels.)

10.2.2.3 PARALLEL_ENABLE functions

The execution of a SQL statement can involve many distinct actions (for example, updating multiple indexes on an INSERT). Oracle's parallel execution feature allows these multiple operations to be executed simultaneously on different processes. If a function is called in a SQL statement that is run in parallel, then Oracle may make and run a separate copy of the function in each process.

Oracle's parallel execution feature divides the work of executing a SQL statement across multiple processes. Functions called from a SQL statement that is run in parallel may have a separate copy run in each of these processes, with each copy called for only the subset of rows handled by that process. Each process has its own copy of package data structures, as well as Java STATIC class attributes. Oracle cannot assume that it is safe to parallelize the execution of user -defined functions if they might possibly modify any of those variables.

Prior to Oracle8 i , a packaged function could be run in a parallel DML statement if its RESTRICT_REFERENCES pragma asserted all purity levels: WNDS, RNDS, WNPS, and RNPS. Those same purity levels also needed to be applicable to a standalone function if it were to be called in parallelized SQL. A parallel query required RNPS and WNPS, in addition to WNDS.

In Oracle8 i , since RESTRICT_REFERENCES is no longer required and parallel function execution is still desired, a new and optional keyword, PARALLEL_ENABLE, has been added for the program header. This keyword is placed before the IS or AS keyword as shown in this example:

CREATE FUNCTION ceo_compensation ( stock_increase IN NUMBER, layoffs IN NUMBER) RETURN NUMBER PARALLEL_ENABLE IS BEGIN RETURN 1000000 * (layoffs + stock_increase); END; /

You can use this keyword in a function defined in a CREATE FUNCTION statement, in a function's declaration in a CREATE PACKAGE statement, or on a method's declaration in a CREATE TYPE statement. You do not repeat this keyword in the function's or method's body in a CREATE PACKAGE BODY or CREATE TYPE BODY statement.

You don't have to use the PARALLEL_ENABLE keyword to identify functions that are eligible for parallel execution; Oracle suggests, however, that you use it, rather than a RESTRICT_REFERENCES pragma, when you need to achieve this effect. Here are some things to keep in mind:

• If you define a standalone function with the CREATE FUNCTION statement, it may still be eligible for parallel execution if the SQL runtime engine can determine that the function neither reads nor writes package variables, nor calls any function that might do so.

• A Java method or C function can never be seen by Oracle as safe to run in parallel unless the programmer explicitly indicates PARALLEL_ENABLE on the call specification or provides a PRAGMA RESTRICT_REFERENCES indicating that the function is pure enough for the operation.

• If your function executes in parallel as part of a parallelized DML statement, it is not permitted to execute another DML statement. This function is, in fact, subject to the same restrictions enforced on functions that are run inside a query.

• You can combine PARALLEL_ENABLE and DETERMINISTIC in the same program header.