TECHNOLOGY: PL/SQL Practices
On Variables, Moving, and Meaning
By Steven Feuerstein
Apply best practices to cursor variables, bidirectional cursor access, and error messages.
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.
Moving Back and Forth
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:
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:
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.
Meaningful or Cuddly?
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.
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.
Steven Feuerstein (firstname.lastname@example.org) is considered one of the world's leading experts on the Oracle PL/SQL language, having written ten books on the subject, including Oracle PL/SQL Programming and Oracle PL/SQL Best Practices (O'Reilly Media). Feuerstein serves as a senior technology advisor for Quest Software, and his projects include Qnxo, the world's first active mentoring product (www.qnxo.com).