Section 2.4. Using Cursors for More Than Queries

2.4. Using Cursors for More Than Queries

As the Oracle database has evolved over time, so has the humble cursor. In addition to providing the performance improvements described in earlier sections, cursor functionality now extends beyond queries, and it is integral to the design and building of applications. This section describes a variety of additional cursor capabilities not explored in previous sections.

Bulk fetching, REF cursors, cursor parameters, and cursor expressions are especially handy tools for DBAs who need ways to investigate and improve the performance of applications. The features discussed here are particularly helpful if you are working with very busy databases where keeping the number of records touched to an absolute minimum is very important. For example, REF cursors can be used to control data access from client applications that may not even be aware of the way that tables are structured. Cursor parameters allow data access to be spread out. (Chapter 3 discusses additional tools for accomplishing this goal.) And cursor expressions (nested cursors) go a long way toward ensuring that only the work that needs to be done actually is done.

2.4.1. Bulking Up

If you fetch records , one by one, via a PL/SQL loop, you will incur the overhead of context switching between SQL and PL/SQL once per record. This will dramatically increase the elapsed time if you are processing a large number of records. You can reduce the number of context switches by using a bulk fetch (BULK COLLECT INTO) to query records in sets or all at once.

First, here is an example of what I mean by fetching one record at a time:

     CREATE OR REPLACE PROCEDURE one_at_a_time AS       CURSOR curs_get_ord IS       SELECT order_number,              order_date         FROM orders       ORDER BY order_number;       v_order_number NUMBER;       v_order_date   DATE;     BEGIN       FOR v_order_rec IN curs_get_ord LOOP         do_something;       END LOOP;     END;

If the ORDERS table contains 100 records, then 100 context switches would occur. Here is the bulk fetch version of the code.

     CREATE OR REPLACE PROCEDURE all_at_once AS       CURSOR curs_get_ord IS       SELECT order_number,              order_date         FROM orders       ORDER BY order_number;       -- local collections to hold bulk fetched values       TYPE v_number_t IS TABLE OF NUMBER;       TYPE v_date_t   IS TABLE OF DATE;       v_order_number v_number_t;       v_order_date   v_date_t;     BEGIN       -- get all orders at once       OPEN curs_get_ord;       FETCH curs_get_ord BULK COLLECT INTO v_order_number, v_order_date;       CLOSE curs_get_ord;       -- if any orders were found then loop through the local       -- collections to process them       IF NVL(v_order_number.COUNT,0) > 0 THEN         FOR counter IN v_order_number.FIRST..v_order_number.LAST LOOP           do_something;         END LOOP;       END IF;     END;

For large record sets, the performance gain can be huge, so I highly recommend that you use this option whenever you can.

There is also another, less obvious advantage to performing bulk fetches: the database does not have to maintain a read-consistent view of the data while the records it retrieves are processed. Let's look again at the previous example. If the mythical DO_SOMETHING procedure took five seconds to process each of the 100 records retrieved from the ORDERS table, Oracle would have to maintain a read-consistent copy of the records in the result set for more than eight minutes. If the ORDERS table is busy with many other DML operations, then the database's rollback segments will be busy keeping a view of the data in synch for the long operation.

In this case, there is a potential snag resulting from switching to a bulk fetch method: the DO_SOMETHING procedure will have to handle situations where the orders it wants to process no longer exist because they were deleted after the bulk fetch occurred.

The alternative is to query all records into memory right away with a bulk fetch and then process them. This operation also drastically reduces the chances of getting the troublesome ORA-01555 - Snapshot Too Old (Rollback Segment Too Small) error.

Because the bulk fetch feature brings records into session memory, a balance must be struck with session memory limits. If session memory is a concern for your application, then you can use the LIMIT clause to restrict the number of entries queried at one time. For example:

     OPEN curs_get_ord;     LOOP       -- get next 1,000 orders       FETCH curs_get_ord BULK COLLECT INTO v_order_number, v_order_date LIMIT 1000;       -- if any more orders found then loop through them       IF NVL(v_order_number.COUNT,0) > 0 THEN         FOR counter IN v_order_number.FIRST..v_order_number.LAST LOOP           do_something;         END LOOP;       ELSE         EXIT;       END IF;     END LOOP;     CLOSE curs_get_ord;

I make frequent use of the bulk fetch feature when querying Oracle's performance (V$) tables because the last thing I want is for the database to do extra work just so I can see, for example, how many reads and writes each session did. Here is the algorithm I follow:

     BEGIN       bulk fetch current sessions from V$SESSION       for each session         query session stats for reads and writes       end if     END;

I recommend using this feature frequently when querying from the busier Oracle performance views.

2.4.2. REF Cursors

REF cursors provide placeholders for eventual real cursors. Using REF cursors, a program may utilize Oracle's cursor features without being terribly explicit about what data is to be accessed until runtime. Here is a really simple example:

     CREATE OR REPLACE PROCEDURE ref_curs AS       v_curs SYS_REFCURSOR;     BEGIN       OPEN v_curs FOR 'SELECT order_number ' ||                        ' FROM orders';       CLOSE v_curs;     END;

At compile time, Oracle has no idea what the query text will beall it sees is a string variable. But the REF cursor tells it to be ready to provide cursor functionality in some manner.

The most useful application of REF cursors is to provide "black box" data access to other applications with functions building and returning REF cursors as shown here:

     CREATE OR REPLACE FUNCTION all_orders ( p_id NUMBER )                       RETURN SYS_REFCURSOR  IS       v_curs SYS_REFCURSOR;     BEGIN       OPEN v_curs FOR 'SELECT * ' ||                        ' FROM orders ' ||                       ' WHERE order_number = ' || p_id;       RETURN v_curs;     END;

The calling program simply passes an order_number value to the function and is returned access to the underlying data without having to know anything about it beforehand. External applications, such as Microsoft's .NET, can interrogate the returned REF cursor to determine attributes such as column names and datatypes to decide how to display them.

Here is how the all_orders function might be issued from PL/SQL:

     DECLARE       v_curs      SYS_REFCURSOR;       v_order_rec ORDERS%ROWTYPE;     BEGIN       v_curs := all_orders(1);       FETCH v_curs INTO v_order_rec;       IF v_curs%FOUND THEN         DBMS_OUTPUT.PUT_LINE('Found It');       END IF;       CLOSE v_curs;     END;

2.4.2.1. Strong vs. weak REF cursors

There are two types of REF cursors, strongly typed and weakly typed. The difference is that weakly typed REF cursors have no idea up front what data set they will be returning, while strongly typed ones are told explicitly what their return set will look like.

The SYS_REFCURSOR datatype shown in the two previous examples became available in Oracle9i Database, allowing for the quick definition of weakly typed REF cursors. In previous versions, they were declared like this:

DECLARE   TYPE v_curs_t IS REF_CURSOR;   v_curs v_curs_t;

Weakly typed REF cursors can be reused by almost any query because they are not tied to an explicit return structure.

     DECLARE       v_curs SYS_REFCURSOR;     BEGIN       OPEN v_curs FOR 'SELECT order_number ' ||                        ' FROM orders';       CLOSE v_curs;       OPEN v_curs FOR 'SELECT * ' ||                        ' FROM orders';       CLOSE v_curs;     END;

The actual query provided for the REF cursor winds up being validated, parsed, and held in the System Global Area just like any other cursor.

     SQL_TEXT                       PARSE_CALLS EXECUTIONS     ------------------------------ ----------- ----------     SELECT *  FROM orders                    1          1     SELECT order_number  FROM orde           1          1     rs

Note, however, that REF cursors are not soft-closed, so they cannot take advantage of being opened extra-super-duper quickly later on. Thus, REF cursors will not work as fast as normal cursors.

Weakly typed REF cursors will also incur overhead when Oracle figures out the structure of the return set on the fly. So, for the best performance, strongly typed REF cursors should be used whenever possible. Here are some examples of strongly typed REF cursors:

     DECLARE       -- type for order records       TYPE v_order_curs IS REF CURSOR RETURN orders%ROWTYPE;       v_oc v_order_curs;       -- type for order numbers only       TYPE v_order_number_t IS RECORD ( order_number orders.order_number%TYPE );       TYPE v_order_number_curs IS REF CURSOR RETURN v_order_number_t;       v_ocn v_order_number_curs;

Attempts to use a REF cursor with a non-matching return data set will be met with the rather generic ORA-06550 message.

     OPEN v_ocn FOR SELECT * FROM ORDERS;                      *     ERROR at line 10:     ORA-06550: line 10, column 18:     PLS-00382: expression is of wrong type

2.4.2.2. REF cursor attributes

REF cursors have the same full suite of attributes as explicit cursors, as shown in this example:

     DECLARE       v_curs SYS_REFCURSOR;       v_on   NUMBER;     BEGIN       OPEN v_curs FOR 'SELECT order_number ' ||                        ' FROM orders';       FETCH v_curs INTO v_on;       LOOP         EXIT when v_curs%NOTFOUND;         IF v_curs%ROWCOUNT = 1 THEN           NULL;         END IF;         FETCH v_curs INTO v_on;       END LOOP;       CLOSE v_curs;     END;

2.4.2.3. Dynamic data access

REF cursors are very handy in situations in which query text is not known beforehand, but logical processing is. For example, the following procedure will be passed the text of a query, and it will open a REF cursor for it. It will then send the REF cursor off to another procedure to fetch from (and eventually close).

     CREATE OR REPLACE PROCEDURE order_cancel ( p_sql  VARCHAR2 ) IS       v_curs SYS_REFCURSOR;     BEGIN       IF v_curs%ISOPEN THEN         CLOSE v_curs;       END IF;       BEGIN         OPEN v_curs FOR p_sql;       EXCEPTION         WHEN OTHERS THEN           RAISE_APPLICATION_ERROR(-20000,'Unable to open cursor  ');       END;       order_cancel_details(v_curs);       CLOSE v_curs;     END;

The order_cancel function could then be executed like this:

     BEGIN       order_cancel('SELECT order_number FROM orders                        WHERE due_date <= TRUNC(SYSDATE)');     END;

2.4.3. Cursor Parameters

As the examples in the previous section suggest, it is possible to pass cursors as parameters using straight SQL. This can also be done in a SELECT statement using the CURSOR keyword .

     SELECT count_valid(CURSOR(SELECT order_number                                 FROM orders                                WHERE processed IS NULL))       FROM dual;

The count_valid function might look something like this:

     CREATE OR REPLACE FUNCTION count_valid( p_curs SYS_REFCURSOR )                       RETURN NUMBER IS       v_on NUMBER;       v_ret_val NUMBER := 0;     BEGIN       FETCH p_curs INTO v_on;       LOOP         EXIT WHEN p_curs%NOTFOUND;         IF extensive_validation(v_on) THEN           v_ret_val := v_ret_val + 1;         END IF;         FETCH p_curs INTO v_on;       END LOOP;       RETURN(v_ret_val);     END;

The SELECT statement is passed right into the function that then loops through the records it returns, validating them and then returning a count of those deemed valid. This results in two cursors in the shared pool and the soft-closed list for the user.

     SQL_TEXT     ----------------------------------------     SELECT "A2"."ORDER_NUMBER" "ORDER_NUMBER     " FROM "ORDERS" "A2" WHERE "A2"."PROCESS     ED" IS NULL     SELECT count_valid(CURSOR(SELECT order_n     umber                             FROM o     rders                            WHERE p     rocessed IS NULL))   FROM dual

2.4.4. Cursor Expressions

Cursor expressions are essentially nested cursors . When I refer to a "cursor expression," I am not talking about nested subqueries that determine a result set; instead, I am talking about nested queries that return nested result sets. Let me explain with an example.

     SELECT order_number,            CURSOR ( SELECT order_line_amt                       FROM order_lines ol                      WHERE ol.order_number = orders.order_number )       FROM orders;

This query returns a list of orders plus a cursor to find the lines of that order later. Here's how it might be used in a PL/SQL procedure:

     /* File on web: nested_cursor.sql */     CREATE OR REPLACE PROCEDURE nested AS       -- cursor to get orders plus a nested cursor       -- to its line amounts       CURSOR curs_orders IS       SELECT order_number,              CURSOR ( SELECT order_line_amt                         FROM order_lines ol                        WHERE ol.order_number = orders.order_number )         FROM orders;       lines_curs SYS_REFCURSOR;  -- for order lines       v_order_id NUMBER;       -- local variables for bulk fetch of lines       TYPE v_number_t IS TABLE OF NUMBER;       v_line_amt  v_number_t;     BEGIN       OPEN curs_orders;       FETCH curs_orders INTO v_order_id, lines_curs;       -- for every order...       LOOP         EXIT WHEN curs_orders%NOTFOUND;         -- only process even numbered orders         IF MOD(v_order_id,2) = 0 THEN           -- get all lines for the order at once           FETCH lines_curs BULK COLLECT INTO v_line_amt;           -- loop through the order lines           IF NVL(v_line_amt.COUNT,0) > 0 THEN             FOR counter IN v_line_amt.FIRST..v_line_amt.LAST LOOP               process_lines;             END LOOP;           END IF;         END IF;  -- only even numbered orders         FETCH curs_orders INTO v_order_id, lines_curs;       END LOOP;  -- every order       CLOSE curs_orders;     END;

Cursor expressions have slightly esoteric syntax, but they offer some advantages. The main advantage is they provide a direct link between logical and physical processing for both the Oracle optimizer and the code itself. The optimizer benefits from being explicitly informed of the physical link between the two tables (ORDERS and ORDER_LINES), so it can make better decisions when it is eventually asked to get order lines. The code itself limits physical work by logically deciding whether or not to even get certain order lines. This avoids querying records only to ignore them later.

What's even more interesting is what is loaded into the SGA after executing the nested procedure against 1,000 orders.

     SQL_TEXT                       PARSE_CALLS EXECUTIONS     ------------------------------ ----------- ----------     SELECT ORDER_NUMBER, CURSOR (            1          1     SELECT ORDER_LINE_AMT FROM ORD     ER_LINES WHERE ORDER_NUMBER =     ORDERS.ORDER_NUMBER ) FROM ORD     ERS     SELECT "A2"."ORDER_LINE_AMT" "         500        500     ORDER_LINE_AMT" FROM "ORDER_LI     NES" "A2" WHERE "A2"."ORDER_NU     MBER"=:CV1$

Notice that the right side of the nested query's WHERE clause was changed to a cursor bind variable. That's how it is linked back to the main cursor. Also notice that the parse and execution counts are at 500 for the second cursorthat's because it executed only the absolutely required 500 times. More importantly, the underlying data was accessed only 500 times.

After the procedure has been run, the only cursor left open for the session is the main one. However, there are actually many more open during execution. You can expose this fact by adding a sleep of 10 seconds to the code and checking V$OPEN_CURSORS while the sleep occurs.

     SQL_TEXT     ------------------------------     SELECT ORDER_NUMBER, CURSOR (     SELECT ORDER_LINE_AMT FROM ORD     ER_LINES WHERE ORDER_NUMBER =     ORDERS.ORDER_NUMBER ) FROM ORD     ERS     SELECT "A2"."ORDER_LINE_AMT" "     ORDER_LINE_AMT" FROM "ORDER_LI     NES" "A2" WHERE "A2"."ORDER_NU     MBER"=:CV1$

It turns out that 500 of the second cursor will actually be listed as being open before the procedure finishes and closes them (because they have gone out of scope). All 500 of the nested cursors will make use of the already compiled version in the SGA, as you can see by the cursors' ever-increasing parse and execution counts after six runs of the nested procedure.

     SQL_TEXT                       PARSE_CALLS EXECUTIONS     ------------------------------ ----------- ----------     SELECT "A2"."ORDER_LINE_AMT" "        3000       3000     ORDER_LINE_AMT" FROM "ORDER_LI     NES" "A2" WHERE :CV1$=:CV1$

As written, however, all 500 will not take advantage of soft-closed cursors. As a matter of fact, they bring me unnecessarily close to my session's maximum OPEN_CURSORS limit. Thus, it's best in such cases to explicitly close the nested cursor when you are finished with it. (This may not be obvious because the nested cursor does not have an explicit open to associate with.) Here is the changed section of code:

     -- process only even numbered orders     IF MOD(v_order_id,2) = 0 THEN       -- implcitly opened       FETCH lines_curs BULK COLLECT INTO v_line_amt;         IF NVL(v_line_amt.COUNT,0) > 0 THEN           FOR counter IN v_line_amt.FIRST..v_line_amt.LAST LOOP             Process_lines;         END LOOP;     END IF;  -- only even numbered orders     -- close the nested cursor     CLOSE lines_curs;     END IF;

At this point, I'm sure you are wondering if the example I'm using would not be better written as a single cursorperhaps something like this:

     SELECT o.order_number,            order_line_amt       FROM orders      o,            order_lines ol      WHERE ol.order_number = o.order_number;

Then I could check to see whether the order number is evenly divisible by two in the PL/SQL code. The difference between these two approaches is the number of rows processed during the query. The nested cursor approach shows these values:

     SQL_TEXT                       ROWS_PROCESSED     ------------------------------ --------------     SELECT ORDER_NUMBER, CURSOR (            1000     SELECT ORDER_LINE_AMT FROM ORD     ER_LINES OL WHERE OL.ORDER_NUM     BER = ORDERS.ORDER_NUMBER ) FR     OM ORDERS     SELECT "A2"."ORDER_LINE_AMT" "           5000     ORDER_LINE_AMT" FROM "ORDER_LI     NES" "A2" WHERE "A2"."ORDER_NU     MBER"=:CV1$

On the other hand, the single cursor approach shows these numbers:

     SQL_TEXT                       ROWS_PROCESSED     ------------------------------ --------------     SELECT O.ORDER_NUMBER, ORDER_L          10000     INE_AMT FROM ORDERS O, ORDER_L     INES OL WHERE OL.ORDER_NUMBER     = O.ORDER_NUMBER

Four thousand fewer rows had to be processed by Oracle to build the results set(s). That may seem like a small number, but in a busy system, that is 4,000 fewer records for which Oracle has to keep a read-consistent copy for the duration of the query.

Another option would be to add "MOD(order_number,2) = 0" directly to the query, and that is perfectly viable syntactically. However, the Oracle optimizer may choose a query plan geared to returning all of the order lines and then weed out the odd ones in memory. Of course, you could use a function-based index to work around that, but there is overhead there, as well.

Changing it to a single query negates the further benefit of bulk fetching the order lines, as well.

Oracle does not maintain read-committed result sets across nested cursors. Result sets are maintained only between the implicit open and the subsequent close of the nested cursor. The main cursor, however, still experiences full read-committed consistency.

Another equally viable alternative would be two cursors, one to get the orders and one to get the lines. However the optimizer is then forced to treat them as two separate cursors because it has not been told of the link.