On Variables, Moving, and Meaning

Apply best practices to cursor variables, bidirectional cursor access, and error messages.

March/April 2006

I have a stored procedure that uses a REF CURSOR parameter to return a result set as a cursor variable. How can I call that procedure and then insert the rows identified by the cursor variable into a table, using FORALL?

A cursor variable is a PL/SQL variable that points to a result set. You can fetch the rows of a cursor variable's result set just as you would fetch the rows identified by an explicitly declared cursor. You can in particular use BULK COLLECT against a cursor variable to deposit into a collection all the rows identified by the result set. You can then reference that collection in a FORALL INSERT statement.

Let's take a look at the kind of code you would write to achieve your goal. I will use the jokes and joke_archive tables as my example data structures:

CREATE TABLE jokes (
   joke_id INTEGER,
   title VARCHAR2(100),
   text VARCHAR2(4000)
)
/
CREATE TABLE joke_archive (
   archived_on DATE,
   old_stuff VARCHAR2(4000)
)
/

I will place two jokes in the jokes table, as shown in Listing 1.

Code Listing 1: Inserting jokes into the jokes table

BEGIN
   INSERT INTO jokes
        VALUES (100, 'Why does an elephant take a shower?'
               ,'Why does an elephant take a shower? ' ||
                'Because it can''t fit into the bathtub!');
   INSERT INTO jokes
        VALUES (101
           ,'How can you prevent diseases caused by biting insects?'
           ,'How can you prevent diseases caused by biting insects?' || 'Don't bite any!');
   COMMIT;
END;

I now need to write a procedure that will identify joke text or titles that need to be moved to the joke_archive table. Here is the header of my procedure:

CREATE OR REPLACE
PROCEDURE get_title_or_text (
   title_like_in IN VARCHAR2
  ,return_title_in IN BOOLEAN
  ,joke_count_out OUT PLS_INTEGER
  ,jokes_out OUT SYS_REFCURSOR
)

I pass in a string (title_like_in) that acts as a filter to identify the rows in the jokes table to be moved to the archive. I specify whether I want to retrieve titles (return_title_in => TRUE) or text (return_title_in => FALSE). I then return the total number of rows identified by the result set (joke_count_out), as well as the result set itself (joke_out). I use the system-defined weak REF CURSOR type, SYS_REFCURSOR (available in Oracle9i Database Release 2 and later).

Listing 2 contains the implementation of the get_title_or_text procedure.

Code Listing 2: get_title_or_text procedure

1  CREATE OR REPLACE PROCEDURE get_title_or_text (
 2     title_like_in IN VARCHAR2
 3    ,return_title_in IN BOOLEAN
 4    ,joke_count_out OUT PLS_INTEGER
 5    ,jokes_out OUT SYS_REFCURSOR
 6  )
 7  IS
 8     c_from_where      VARCHAR2 (100) := ' FROM jokes WHERE title LIKE :your_title';
 9     l_colname           all_tab_columns.column_name%TYPE   := 'TEXT';
10    l_query              VARCHAR2 (32767);
11  BEGIN
12     IF return_title_in
13     THEN
14        l_colname := 'TITLE';
15     END IF;
16
17     l_query := 'SELECT ' || l_colname || c_from_where;
18
19     OPEN jokes_out FOR l_query USING title_like_in;
20
21     EXECUTE IMMEDIATE 'SELECT COUNT(*)' || c_from_where
22                  INTO joke_count_out
23                 USING title_like_in;
24  END get_title_or_text;

Here is an explanation of the interesting parts of the get_title_or_text procedure in Listing 2.

Line 8. Because the get_title_or_text procedure is executing two dynamic queries, differing only in the SELECT list, store the rest of those queries (the FROM and WHERE clauses) in a reusable string.

Lines 12-17. Construct the dynamic query to retrieve all titles or text with a matching title.

Line 19. Associate the result set with the cursor variable, for the specified title filter.

Lines 21-23. Compute the number of rows identified by the query.

I can now call this procedure, return a result set, move that data into a collection, and then use the collection in a FORALL statement, as shown in Listing 3.

Code Listing 3: From results to collection to FORALL

1  DECLARE
 2     l_count         PLS_INTEGER;
 3     l_jokes          sys_refcursor;
 4
 5     TYPE jokes_tt IS TABLE OF jokes.text%TYPE;
 6
 7     l_joke_array   jokes_tt      := jokes_tt ();
 8  BEGIN
 9     get_title_or_text (title_like_in         => '%insect%'
10                           ,return_title_in      => FALSE
11                           ,joke_count_out     => l_count
12                           ,jokes_out            => l_jokes
13                           );
14     DBMS_OUTPUT.put_line ('Number of jokes found = ' || l_count);
15
16     FETCH l_jokes
17     BULK COLLECT INTO l_joke_array;
18
19     CLOSE l_jokes;
20
21     FORALL indx IN l_joke_array.FIRST .. l_joke_array.LAST
22        INSERT INTO joke_archive
23             VALUES (SYSDATE, l_joke_array (indx));
24  END;

Here is an explanation of the interesting parts of the anonymous block in Listing 3.

Lines 2 and 3. Declare variables to retrieve the values returned by the get_title_or_text procedure.

Lines 5-7. Declare a nested table to hold the data identified by the cursor variable.

Lines 8-13. Call the stored procedure to return the cursor variable and the count of rows in the result set.

Lines 16-19. Use BULK COLLECT to fetch all the rows in the result set into the nested table. Then close the cursor variable. Note that I use BULK COLLECT with an implicit SELECT INTO to retrieve all the rows identified by the cursor. If you are querying a large volume of data, this approach can consume an unacceptable amount of memory. Under such circumstances, you may want to switch to using BULK COLLECT with a LIMIT clause.

Lines 21-23. Use a FORALL statement to push the data into the joke archive.

Listing 4 contains a modified version of the previous anonymous block in Listing 3, showing how to use the LIMIT clause to restrict the number of rows fetched with a BULK COLLECT query, thus reducing the amount of memory needed to populate the collection.

Code Listing 4: From results to collection to FORALL plus LIMIT

DECLARE
   l_count        PLS_INTEGER;
   l_jokes        sys_refcursor;
   TYPE jokes_tt IS TABLE OF jokes.text%TYPE;
   l_joke_array   jokes_tt      := jokes_tt ();
BEGIN
   get_title_or_text (title_like_in             => '%insect%'
                          ,return_title_in          => FALSE
                          ,joke_count_out          => l_count
                          ,jokes_out                 => l_jokes
                          );
   DBMS_OUTPUT.put_line ('Number of jokes found = ' || l_count);
   LOOP
      -- Fetch the next 100 rows.
      FETCH l_jokes
      BULK COLLECT INTO l_joke_array LIMIT 100;
        EXIT WHEN l_joke_array.COUNT = 0;
      -- Push them into the archive.
      FORALL indx IN l_joke_array.FIRST .. l_joke_array.LAST
         INSERT INTO joke_archive
              VALUES (SYSDATE, l_joke_array (indx));
   END LOOP;
   CLOSE l_jokes;
END;

This answer demonstrates how you can take advantage of the flexibility of cursor variables to construct your queries at runtime without losing the performance benefits of the BULK COLLECT and FORALL features.

I need to be able to move back and forth within a cursor result set, comparing the contents of distinct rows. I also would like to be able to read from the end of the result set to the beginning. How can I do that inside a PL/SQL program?

I will assume in my answer that your question refers to server-side-only code that needs to traverse a cursor's result set. An entirely different set of considerations comes 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.

Oracle does not currently support bidirectional access to cursor result sets (aka 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 Oracle Database 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 a relatively small number of rows, the use of multiple queries may yield a satisfactory implementation. If, on the other hand, your result set is very large, you may 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 bidirectional 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, and so on, with complete freedom and a high degree of efficiency. I will demonstrate how you can build and move through such a cache.

Recall that PL/SQL program data consumes program global area (PGA) memory, distinct from the system global area (SGA), and 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 and the PGA will require lots of memory for the collection.

This technique of building and moving through a PL/SQL collection cache will make the most sense under the following 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 bidirectional 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 5 offers an example of bidirectional 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)
)
/

Code Listing 5: Example of bidirectional cursor processing with collections

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;

Lines 3-6. Declare a collection type that mimics the structure of the jokes relational table, and then instantiate a variable based on that type (joke_cache).

Lines 9-11. 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). Note that I am retrieving all the rows in the jokes 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. Use 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. Use the LAST and PRIOR methods to go in reverse, from the last row to the first.

Lines 31-38. Compare values in different rows by referencing the index value of the rows in which you are interested.

What are your thoughts about how much information should be displayed to application users when an exception occurs? Say a record can't be created because of a primary key constraint—should users see the "ORA-00001...," so they can give a meaningful message to Support to help track the problem down, or should all that stuff be hidden, so the user gets a more "cuddly" message, maybe with an error log number pointing to a record in an error log table?

My feeling is that when an error occurs, you should provide a message to the user that doesn't violate your company's security protocols, is meaningful, and does not intimidate, in that order of priority.

First, let's address the issue of security. When an error occurs in your application or underlying database that you can anticipate and for which you can write explicit handler logic, you can both control the message and tailor it to user needs and the application's business model.

If, on the other hand, that error is unexpected, it is quite difficult to know a priori (that is, when you are writing generic error-handling and -reporting code) how much information should be shown. What if the error is caused by a malicious user who is attempting to hack into your Web-based application that makes heavy use of dynamic SQL? You don't really want to give any information about the state of the database, such as

ORA-1653 unable to extend table
secure_app.personal_info in tablespace sensitive_data....

And you don't want to display "bad" SQL statements—statements made bad by SQL injection attempts. You could easily reveal enormous amounts of sensitive information.

So for exceptions that are not handled in the PL/SQL code—errors that cannot be anticipated as you write your programs—it is best to keep feedback to an absolute minimum, probably something along these lines:

An unexpected error has occurred.
Please contact Support and mention the problem identifier 12345.

Legitimate users will follow up; malicious hackers will look for softer targets.

Let's now address a situation in which your code has handled an error that was anticipated and likely caused by user input. Simply displaying the Oracle error message or, worse, allowing the exception to go unhandled, resulting in a display of the error stack, will not do. So, yes, you should "translate" the generic ORA-0001 or ORA-1403 information and the like into more-cuddly communications that achieve the following objectives:

Notify the user that an error has occurred. You surely do not want to "swallow" errors.

• The error message should explain the problem in terms the user can understand. From a security standpoint, you should not display the Oracle error code or message. Instead, display a message that explains in application-specific terms what has happened. This usually entails providing context-specific values, such as the name of the column that was being set to NULL or was involved in the duplicate index error.

• Where appropriate, tell the user what to do to address the problem (if a "retry" makes sense). You might say, "Change the name of the account to a value that is not currently being used in the database" or "Adjust the birthday of the employee so that the employee is at least 18 years old."

• Give the user all the information needed to help the application-support team investigate and resolve the problem. The Oracle exception has only two attributes: error code and error message. Usually, when an error occurs in an application, there are lots of different pieces of data that both contributed to the error's occurrence and can explain the current execution context. If that information is not extracted and saved at the time of the exception, it will be much harder to fix the problem.

For example, distinguish between an error such as ORA-1403 (no data found) and the particular instance of an error ("No company located with ID = 1504"). Just as each error has its own unique identifier, each instance should also be uniquely identifiable.

When an error is encountered, gather as much information as possible about the application and system context (by calling, for example, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE) and store it with the unique identifier. Provide this instance identifier to the user, and that person can communicate it back to Support. Support then has access to a rich array of information, which it can use to fix the problem.

I have implemented many of the elements of this approach in Qnxo, a product that helps PL/SQL developers generate, reuse, and test their code more effectively. If you download and install Qnxo, you can take a look at the implementation.