Bi-directional cursors

Question:
I want to move back and forth within a cursor result set, comparing the contents of distinct rows. I also want to read from the end of the result set to the beginning. How can I do that inside a PL/SQL program?
Answer:
I assume that your question refers to server-side-only code that needs to traverse a cursor's result set. An entirely different set of considerations come into play if you are talking about transferring data from the server to a user interface, such as a Web page, and allowing that front-end environment to flexibly move through result sets.
Sadly, Oracle does not yet support bi-directional access to cursor result sets (a.k.a, "scrollable cursors") through a PL/SQL interface. You might well find, however, that you can achieve the desired effect with a combination of the following:
• Multiple queries (each with different ORDER BY clauses that correspond to the different ways you need to traverse the result set)
• Analytic functions: As the SQL Reference states, "Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a window and is defined by the analytic_clause. For each row, a sliding window of rows is defined. The window determines the range of rows used to perform the calculations for the current row..."
For tables with relatively small numbers of rows, the use of multiple queries might yield a satisfactory implementation. If, however, your result set is very large, you might run into some performance issues. In addition, you may still not be able to reference arbitrary rows within the result set as desired.
Fortunately, you can achieve the desired effect of a bi-directional cursor rather easily by caching the result in a PL/SQL collection. Once the data has been moved into the cache, you can move back and forth through the result set, compare rows, etc., with complete freedom and a high degree of efficiency. Here I will demonstrate how you can build and move through such a cache.
Recall that PL/SQL program data consumes Process Global Area memory (PGA), distinct from the System Global Area (SGA), and that there is a separate PGA for each session connected to an Oracle instance. With large result sets, you are going to be manipulating lots of data, the PGA will require lots of memory for the collection.
This technique will make the most sense under these circumstances:
• You are running this program for a small number of simultaneous sessions or it is a single batch process. You must have sufficient memory to hold the cache(s) you will create to emulate bi-directional cursors.
• The data in the result set is static (or you want to ignore any changes that occur once your program starts). Once you have copied your result set to your collection-based cache, any changes to the tables that contributed to your result set will not be reflected in the cache—even if those changes are committed in some other session. This is a "one off", static copy of the table (or whatever result set you have defined with your query).
Listing 1 offers an example of bi-directional cursor processing built around a collection of records with the same structure (and data) as the jokes table defined below:
CREATE TABLE jokes (
joke_id INTEGER,
title VARCHAR2(100),
text VARCHAR2(4000)
)
/
Listing 1
1 CREATE OR REPLACE PROCEDURE bidir_example
2 IS
3 TYPE joke_tt IS TABLE OF jokes%ROWTYPE
4 INDEX BY PLS_INTEGER;
5
6 joke_cache joke_tt;
7 l_row PLS_INTEGER;
8 BEGIN
9 SELECT *
10 BULK COLLECT INTO joke_cache
11 FROM jokes;
12
13 DBMS_OUTPUT.put_line ('From first to last...');
14 l_row := joke_cache.FIRST;
15
16 WHILE (l_row IS NOT NULL)
17 LOOP
18 DBMS_OUTPUT.put_line (' ' || joke_cache (l_row).title);
19 l_row := joke_cache.NEXT (l_row);
20 END LOOP;
21
22 DBMS_OUTPUT.put_line ('From last to first...');
23 l_row := joke_cache.LAST;
24
25 WHILE (l_row IS NOT NULL)
26 LOOP
27 DBMS_OUTPUT.put_line (' ' || joke_cache (l_row).title);
28 l_row := joke_cache.PRIOR (l_row);
29 END LOOP;
30
31 DBMS_OUTPUT.put_line ('Compare fifth row to twelfth row...');
32
33 IF LENGTH (joke_cache (5).title) > LENGTH (joke_cache (12).title)
34 THEN
35 DBMS_OUTPUT.put_line (' Fifth row title longer than twelfth.');
36 ELSE
37 DBMS_OUTPUT.put_line (' Fifth row title is not longer than twelfth.');
38 END IF;
39
40 joke_cache.DELETE;
41* END bidir_example;
In lines 3-6, I declare a collection type that mimics the structure of the jokes relational table, and then instantiate a variable based on that type (joke_cache).
In lines 9 – 11, I copy the contents of the jokes table into the cache, using the BULK COLLECT query syntax, the most efficient way to retrieve multiple rows of data into a PL/SQL program. Notice that I am retrieving all the rows in the joke table. I do not use the LIMIT clause to fetch a subset of the result set's rows, because I have already established that I have sufficient memory to create the larger cache. As you explore places to apply this caching technique in your applications, you should strongly consider using the FETCH...BULK COLLECT...LIMIT syntax so that you can control the size of your PGA memory.
Lines 13-20 demonstrate the use of the FIRST and NEXT built-in collection methods to iterate through the collection (result set) from the first row to the last. Lines 22-29 show how to go in reverse, from the last row to the first using the LAST and PRIOR methods.
Finally, lines 31-38 show how you can compare values in different rows, simply by referencing the index value of the rows in which you are interested.