17.4 Restrictions on PLSQL Functions in SQL

Chapter 17
Calling PL/SQL Functions in SQL

 

---

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.