17.4 Restrictions on PL/SQL Functions in SQL

Stored functions in SQL offer tremendous power. As you might expect, however, power introduces the possibility of abuse and the need for responsible action. In the context of SQL, abuse of power involves the rippling impact of side effects in a function. Consider the following function:

FUNCTION total_comp    (salary_in IN employee.salary%TYPE, bonus_in IN employee.bonus%TYPE)    RETURN NUMBER IS BEGIN    UPDATE employee SET salary = salary_in / 2;    RETURN salary_in + NVL (bonus_in, 0); END;

This simple little calculation, introduced at the beginning of the chapter, now also updates the salary of all employees to half of the specified value. This action affects the results of the query from which total_comp might originate; even worse, it affects any other SQL statement in this session.

Along with modification of database tables, modification of package variables is another side effect of stored functions in SQL. Package variables act as globals within a particular session. A function that changes a package variable could have an impact on another stored function or procedure, which in turn could affect a SQL statement using that stored function.

A PL/SQL function could also cause a side effect in the WHERE clause of a query. The query optimizer can reorder the evaluation of predicates in the WHERE clause to minimize the number of rows processed. A function executing in this clause could therefore subvert the query optimization process.

My general recommendation for a function is that it should be narrowly focused on computing and returning a value. But a recommendation is not enough when it comes to database integrity: in order to guard against nasty side effects and upredictable behavior, the Oracle Server makes it impossible for your stored function in SQL to take any of the following actions:

• The stored function may not modify database tables. It cannot execute an INSERT, DELETE, or UPDATE statement.

• A stored function that is called remotely or through a parallelized action may not read or write the values of package variables. The Oracle Server does not support side effects that cross user sessions.

• A stored function can update the values of package variables only if that function is called in a SELECT, VALUES, or SET clause. If the store function is called in the WHERE or GROUP BY clause, it cannot write package variables.

• Prior to Oracle8, you can call very few built-in packaged programs inside a function which will be used in SQL. This means that your PL/SQL function cannot contain calls to DBMS_OUTPUT.PUT_LINE, DBMS_PIPE, and DBMS_SQL, to name just a few. In some cases, this makes perfect sense. If you are not allowed to perform an UPDATE, you certainly shouldn't be able to use DBMS_SQL to sneak that UPDATE by "the censors." But with other packages, the restriction is unnecessary and present only because Oracle did not enable those programs. In Oracle8, some of these restrictions are removed. You can call DBMS_OUTPUT.PUT_LINE from within a function called in SQL. You can even send information to database pipes.[ 1 ]

  [1] My book, Oracle Built-in Packages , contains a comprehensive discussion of the packaged functions which are available for use in SQL.

• Prior to Oracle8, you cannot call RAISE_APPLICATION_ERROR from within the stored function.

• In Oracle Server 7.3, you cannot apply PL/SQL table methods (COUNT, FIRST, LAST, NEXT, PRIOR, etc.) in a stored function which is used in SQL (this is a "known bug" fixed in Oracle8). For example, if your function contains the following code, it cannot be used in SQL:

  DECLARE    TYPE emptabtype IS TABLE of emp%ROWTYPE INDEX BY BINARY_INTEGER;    emptab emptabtype; BEGIN    IF emptab.COUNT > 0 THEN -- Causes rejection inside SQL

• The stored function may not call another module (stored procedure or function) that breaks any of the above rules. A function is only as pure as the most impure of any modules it, in turn, calls.

• The stored function may not reference a view that breaks any of the above rules. A view is a stored SELECT statement; that view's SELECT may use stored functions.

If your function violates any of these rules or is a function defined in a package and is missing its RESTRICT_REFERENCES pragma, you will receive the dreaded ORA-06571 error:

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

As discussed in Section 17.7, "Realities: Calling PL/SQL Functions in SQL" , it can be very difficult at times (and sometimes impossible) to avoid this error. In other situations, however, there is an easy resolution (certainly do check the above list of restrictions).