17.7.1 Manual Application of Pragmas

You must manually apply RESTRICT_REFERENCES pragmas to all of your code -- and you have to figure out where all those pragmas need to go. This process is often similar to a Sherlock Holmes plot. You compile a package and get a pragma violation error. This can happen because your program breaks a rule (like trying to change data) or because it calls other programs which break a rule. You notice in this case that your function calls five or six other functions or procedures, so you must apply pragmas to each of these. By doing so, you assert purity levels where none had been asserted before, raising more errors and in some cases significant architectural issues.

For example, suppose that you suddenly have to apply a pragma to a procedure in package X and that package has an initialization section; you must then also pragma-tize the initialization section. A common practice in this section is to set up a PL/SQL table for in-memory manipulation of data. If you use any PL/SQL table methods to do this initialization, your pragma will fail.

This can be a very frustrating exercise, at times leading to abandoning the effort to enable your function for execution in SQL. In my experience, you will want to identify in advance (as much as possible) those areas of your application which you will want to call in SQL. You will then strive to keep this code very "clean" and focused, with limited entanglements with other packages, and with an absolutely minimal use of built-in packaged functionality. Neither an easy nor a particularly desirable task.

17.7.2 Read Consistency Model Complications

Yes, it is hard to believe, but quite true: unless you take special precautions, it is quite possible that your SQL query will violate the read consistency model of the Oracle RDBMS, which has been sacrosanct territory for years at Oracle. To understand this issue, consider the following query and the function it calls:

SELECT name, total_sales (account_id)
  FROM account
 WHERE status = 'ACTIVE';

FUNCTION total_sales (id_in IN account.account_id%TYPE)
   RETURN NUMBER
IS
   CURSOR tot_cur
   IS
      SELECT SUM (sales) total
        FROM orders
       WHERE account_id = id_in
         AND year = TO_NUMBER (TO_CHAR (SYSDATE, 'YYYY'));
   tot_rec tot_cur%ROWTYPE;
BEGIN
   OPEN tot_cur;
   FETCH tot_cur INTO tot_rec;
   RETURN tot_rec.total;
END;

The account table has five million active rows in it (a very successful enterprise!). The orders table has 20 million rows. I start the query at 11 a.m.; it takes about an hour to complete. At 10:45 a.m., somebody with the proper authority comes along, deletes all rows from the orders table and performs a commit. According to the read consistency model of Oracle, the session running the query should see all those deleted rows until the query completes. But the next time the total_sales function executes from within the query, it finds no order rows and returns NULL -- and will do so until the query completes.

So if you are executing queries inside functions which are called inside SQL, you need to be acutely aware of read-consistency issues. If these functions are called in long-running queries or transactions, you will probably need to issue the following command to enforce read-consistency between SQL statements in the current transaction:

SET TRANSACTION READ ONLY

You will find more information about this command in Chapter 6, Database Interaction and Cursors.

Working with functions in SQL is more difficult and more complicated than you might first imagine. Big surprise. You can say that about almost every aspect of Oracle technology, especially the newer additions to the stable. I hope that over time Oracle will make our lives easier (there are definitely some improvements in Oracle 8.0). Ultimately we need a utility that allows a developer to "point" to a function and request, "make that function usable in SQL." And that utility will then apply all the pragmas or at least generate a report of the steps necessary to get the job done.

We can dream, can't we?