Oracle PL/SQL Programming, 2nd Edition

Oracle PL/SQL Programming, 2nd EditionSearch this book
Previous: 17.3 Requirements for Stored Functions in SQLChapter 17
Calling PL/SQL Functions in SQL
Next: 17.5 Calling Packaged 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:

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


Previous: 17.3 Requirements for Stored Functions in SQLOracle PL/SQL Programming, 2nd EditionNext: 17.5 Calling Packaged Functions in SQL
17.3 Requirements for Stored Functions in SQLBook Index17.5 Calling Packaged Functions in SQL

The Oracle Library Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference