#

| Chapter 17 Calling PL/SQL Functions in SQL |

## 17.7 Realities: Calling PL/SQL Functions in SQL

The ability to call PL/SQL functions in SQL has been around since Release 2.1, but in many ways (at least until Oracle8) it can still be considered "bleeding edge" technology. Why?

- 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 described in a subsection below.

- Functions execute outside of the read consistency model of the Oracle database (!). This issue is also explored below in a subsection below.

- The overhead of calling a function from SQL remains high. The exact price you pay to call a function from within SQL (compared to, say, executing in-line SQL code) can be hard to pin down. It varies from computer to computer and even by instance or by the function being called; I have heard reports that range from an extra half-second to an astonishing additional 50 seconds (in that case, I suggested that they do some more analysis and debugging). Whatever the specific amount of time, the delay can be noticeable and you need to factor it into your design and test plans.

- Tuning mechanisms such as EXPLAIN PLAN do not take into account the SQL that may be called inside functions called in your SQL statement. PL/SQL functions are ignored by the EXPLAIN PLAN facility. This makes it very difficult to come up with a comprehensive understanding of performance bottlenecks and tuning needs.

- So much Oracle technology, especially that found in built-in packages, is declared "off limits" to functions in SQL. Just consider DBMS_OUTPUT. You can't use it in functions called in SQL. But ideally you will want to use those same functions in SQL and PL/SQL. Another issue may be that your standard debugging technique is to insert calls to a trace program at the beginning of each of your functions and procedures. Chances are that the trace program relies on DBMS_OUTPUT (or UTL_FILE or DBMS_PIPE or take your pick, they're all -- at least until Oracle8 -- off limits). Sorry! You will not be able to use that function in SQL. So you have to pull out some of the code from the function. As a result, you can end up with two versions of your code: one for PL/SQL and one for SQL. That is one nasty scenario for software developers.

Let's examine two of these issues in more detail.

### 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?

17.6 Column/Function Name Precedence |  | 17.8 Examples of Embedded PL/SQL

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