C H A P T E R  10

Choosing the Right Cursor

by Melanie Caffrey

Anyone who has ever written a PL/SQL function or procedure that performs any looping logic knows the pain of choosing just the right type of cursor—or the pain of choosing the wrong type of cursor. Choosing the right type of cursor for the right programmatic situation is what this chapter strives to teach you. Choosing the wrong type of cursor may result in your users, your peers, or your managers (or all of them) losing faith in your ability to serve the technical needs of the business requirements. Choosing the wrong type of cursor may also lead to a great amount of time debugging system slowdowns in production and, as what you may deem a worst-case scenario, a diminished paycheck. Given the potential pitfalls and consequences, every PL/SQL programmer should strive to choose a type of cursor that works best for each individual technical problem she must solve.

This chapter highlights four types of cursors—not because they are the only four types of cursors available to you, but because they are the most common types of cursors most PL/SQL programmers typically implement. Implementing them correctly, given a particular set of business requirements, is key to having PL/SQL code that is performant and scalable. The four types of cursors discussed in this chapter are

  • Explicit
  • Implicit
  • Static REF Cursors
  • Dynamic REF Cursors

Your goal in writing any PL/SQL program that obtains sets of records for processing programmatically (either individually or in bulk) is to choose a type of cursor that allows you and the Oracle database to obtain the correct answer with the least amount of work. It is really just that simple. Of course, there are many types of cursors you can use in many situations. But should you? This is the question you need to ask yourself each time you write a PL/SQL program. Choose knowledgeably. Know first what business question you are trying to answer, and then choose the best programmatic tool to answer it quickly and correctly given each individual situation.

Explicit Cursors

The most common type of cursor used in any PL/SQL program, hands down, is the explicit cursor. Everyone learns the explicit cursor upon first learning the PL/SQL language, and most PL/SQL programmers feel instantly drawn to it because they are under the impression that it gives them more programmatic control over their processing. Programmers (at least the newly minted ones) are all about control; their impression is often that if they do not have control over every aspect of their program, then it will not execute correctly.

Explicit cursors are often referred to as open, fetch, close cursors due to their required keywords: OPEN, FETCH, and CLOSE. If you write an explicit cursor, you are required to explicitly open the cursor, fetch from the cursor, and close the cursor. So far, that doesn’t sound too bad and may even give an all-about-control programmer peace of mind. However, it is important to find out not only in which cases such types of cursors may reasonably be used, but also in which cases they may actually be doing more harm than good. Consider the following explicit cursor example in Listing 10-1:

Listing 10-1. An Explicit Cursor Used to Fetch Just One Value

CREATE FUNCTION f_get_name (ip_emp_id in number ) RETURN VARCHAR2
AS
CURSOR c IS SELECT ename FROM emp WHERE emp_id = f_get_name.ip_emp_id;
lv_ename emp.ename%TYPE;
BEGIN
OPEN c;
FETCH c INTO lv_ename;
CLOSE c;
RETURN lv_ename;
END;

images Note The naming conventions for parameter names and variable names in this chapter are as follows: ip_ for input parameters, op_ for output parameters, lv_ for local variables, and gv_ for global variables. When input and output parameters are referenced in a function or procedure, they are prepended with the function or procedure name in order to avoid confusion and to illustrate scope. In the previous example, in Listing 10-1, ip_emp_id is referenced as f_get_name.ip_emp_id.

At first glance, this function probably looks like any typical example of a get function (a function whose sole purpose is to obtain, or get, one row or even one value). The business requirement is obviously to grab at least one (and at most one) employee name, ename, from the emp table given an entered employee ID, emp_id. The cursor is opened; the single value is fetched from the open cursor and placed into the variable, lv_ename; the cursor is closed; and finally the function returns the value stored in the lv_ename variable to the calling program. So, why might this type of cursor be an inappropriate choice for this type of business requirement? Because the function, f_get_name, is a bug waiting to happen.

What might end up in lv_ename after a fetch that fetches nothing? You wouldn’t know whether you received a value or not. Furthermore, what if the data is bad? What if there ends up being more than one row returned for your entered ip_emp_id value? Listing 10-2 illustrates a more correct version of this explicit cursor:

Listing 10-2. A More Correct Explicit Cursor Used to Fetch Just One Value (11.2.0.1)

CREATE FUNCTION f_get_name (ip_emp_id IN NUMBER) RETURN VARCHAR2
AS
CURSOR c IS SELECT ename FROM emp WHERE emp_id = f_get_name.ip_emp_id;
lv_ename emp.ename%TYPE;
BEGIN
   OPEN c;
   FETCH c INTO lv_ename;
      IF (SQL%NOTFOUND) THEN
         RAISE NO_DATA_FOUND;
      ENDIF;
   FETCH c INTO lv_ename;
      IF (SQL%FOUND) THEN
         RAISE TOO_MANY_ROWS;
      ENDIF;
   CLOSE c;
   RETURN lv_ename;
END;

As you can see, however, this is becoming really complex. This is why an explicit cursor is a bad choice when coding a simple get function. It becomes quickly apparent that your simple function is suddenly not so simple. The problem is two-fold:

  • Many people do the open/fetch/close and that’s it. That is a bug waiting to happen.
  • If, on the other hand, you code correctly, you are writing much more code than you need to. MORE CODE = MORE BUGS. Just because you can get the right answer to the previous business question using an explicit cursor doesn’t mean you should. In the section “Implicit Cursors,” you will see an example of this same business problem answered with the use of an implicit cursor.

The Anatomy of an Explicit Cursor

Knowing how an explicit cursor goes about its work can help you to decide whether it is appropriate for your business function. It was noted earlier that explicit cursors have the syntax: open, fetch, close. So, what does this mean? What is actually happening during each step? The following outlines the process or programmatic function each step performs:

  1. OPEN: This step initializes the cursor and identifies the result set. Note that it does not actually have to assemble the result set. It just sets the point in time the result set will be “as of.”
  2. FETCH: This step executes repeatedly until all rows have been retrieved (unless you are using BULK COLLECT (described later), which fetches all of the records at once).
  3. CLOSE: This step releases the cursor once the last row has been processed.

Additionally, as Listing 10-1 illustrates, an explicit cursor must be declared before it is fetched from. An explicit cursor must always be declared in the declaration section of any procedure or function (or package, as it can be declared globally in a package specification or body) before it is actually invoked. However, what makes it explicit is that you have to explicitly open/fetch/close from it. Note that you can declare a cursor and then use it implicitly. Declaring a cursor is not what makes it explicit; it’s how you code your subsequent interaction with it. In the “Implicit Cursors” section, you’ll see that when using implicit cursors, this declaration step is not necessary. Implicit cursors may be declared, but, unlike their explicit counterparts, they don’t have to be.

images Note REF cursors (discussed shortly) are not explicitly declared with the actual SELECT statement. The SELECT statement does not come into play until they are explicitly opened.

The details involved in working with a cursor are a lot to keep track of, especially if you happen to have multiple cursors open and in the process of fetching records. Given the amount of work an explicit cursor goes through (as outlined previously in the three-step list), it is important to only use an explicit cursor when absolutely necessary. So in which cases might an explicit cursor be needed? The most obvious reason to use an explicit cursor is when you have a requirement to process records in bulk.

Explicit Cursors and Bulk Processing

In instances where it is necessary to process thousands (or millions) of records at a time, you cannot process all records at once. Sure, you may try. But the performance slowdown would be so tremendous that you wouldn’t want to. In instances where you must process a very large dataset in smaller chunks, bulk processing is one of the best ways to handle the requirement. Consider the following bulk processing example in Listing 10-3.

Listing 10-3. An Explicit Cursor Used to Perform Bulk Processing

CREATE OR REPLACE PROCEDURE refresh_store_feed AS
   TYPE prod_array     IS TABLE OF store_products%ROWTYPE INDEX BY BINARY_INTEGER;
   l_prod              prod_array;
   CURSOR c IS
      SELECT  product
        FROM  listed_products@some_remote_site;
   BEGIN
   OPEN C;
   LOOP
   FETCH C BULK COLLECT INTO l_prod LIMIT 100;
   FOR i IN 1 .. l_csi.COUNT
   LOOP
      /*    ... do some procedural code here that cannot be done in SQL to l_csi(i) ... */
   END LOOP;
      FORALL i IN 1 .. l_csi.COUNT
         INSERT INTO store_products (product) VALUES (l_prod(i));
   EXIT WHEN c%NOTFOUND;
   END LOOP;
   CLOSE C;
   END;
   /

Since bulk processing that involves piece-wise processing like that illustrated in Listing 10-3 cannot be accomplished using an implicit cursor, you must use an explicit cursor to complete the task. In the previous example, you may ask “If the goal is simply to select from one table and insert into another, why not simply use INSERT AS SELECT methodology?” It’s a reasonable question, since using SQL before resorting to PL/SQL is usually the fastest way to accomplish a task. But not in the case outlined in Listing 10-3. If you must perform some type of intermediate processing on the data just after you’ve selected it—but before you insert it—and you’d like to perform the task in bulk, then you’ll need an explicit cursor. Furthermore, though you can use an implicit cursor to array fetch (as of Oracle10g, an implicit cursor will automatically perform an array fetch of 100 rows), and you can process them (read them, then perform DML), you cannot perform DML in bulk. Only when using FORALL functionality can you perform DML in bulk. And only when using an explicit cursor can you use FORALL functionality.

Another example of when you would need to use an explicit cursor is somewhat dynamic in nature. It may sound counterintuitive, but when you do not know until runtime what the query will be, then you need a special type of explicit cursor to help you determine what result set will actually be processed. In this case, the type of explicit cursor you would use is called a REF (for reference) cursor. If you need to return a resultset to a client, this is one of the easiest ways to do it. A REF cursor offers you flexibility that regular PL/SQL cursors cannot.

REF Cursors in Brief

Though there are two other sections in this chapter that cover REF cursors more fully (“Static REF Cursors” and “Dynamic REF Cursors”), a brief overview regarding these types of cursors warrants mention in this section due to their structure: they must be explicitly opened, fetched from, and closed. If you are returning a result set to a client, a great way to avoid lots of repetitive code is to use a REF cursor. Essentially, the cursor you open usually depends on what input you receive from the requesting client. Listing 10-4 provides a brief example of a REF cursor scenario in PL/SQL:

Listing 10-4. REF Cursors Work Dynamically, But Are Declared

DECLARE
   prod_cursor sys_refcursor;
BEGIN
IF ( input_param = 'C' )
THEN
      OPEN prod_cursor FOR
         SELECT * FROM prod_concepts
          WHERE concept_type = 'COLLATERAL'
            AND concept_dt   < TO_DATE( '01-JAN-2003', 'DD-MON-YYYY'),
ELSE
      OPEN prod_cursor FOR
         SELECT * FROM prod_concepts
          WHERE concept_category = 'ADVERTISING';
END IF;
LOOP
       FETCH prod_cursor BULK COLLECT INTO .... LIMIT 500;
       ...procedural code to process results here...
       EXIT WHEN prod_cursor%NOTFOUND;
END LOOP;
   CLOSE prod_cursor;
END;

Listing 10-4 illustrates how REF cursors are opened dynamically dependent on the input received from the client. In the example displayed in Listing 10-4, if the input received is ‘C,’ then one cursor is opened. However, if the input is not ‘C,’ an entirely different cursor is opened. Only after the correct cursor is opened is it then fetched from and closed. This works because the name of the cursor variable does not change—only its contents do.

It is important to note that this REF cursor is an example of a static REF cursor. It is called a static REF cursor because it is associated with a query known at compile time, as opposed to a dynamic REF cursor whose query is not known until runtime. The query associated with a static REF cursor is static (never changing). When using REF cursors, if it is possible to use a static REF cursor, you should do so. Dynamic code of any kind breaks dependencies since you are waiting until runtime to discover either

  • the number or types of columns you will be working with, or
  • the number or types of bind variables you will be working with.

If you do know both of these things before creating your REF cursor, then you should defer to creating a static REF cursor. Static code is almost always better than dynamic code since it is known to the RDBMS at compile time and, therefore, the compiler can catch many errors for you. Dynamic code is not known to the RDBMS until runtime and can therefore be unpredictable and prone to error.

REF cursors will be elaborated upon in subsequent sections. For now, it is enough to know that a REF cursor is yet another reason to use explicit cursors. There are only a handful of situations in which explicit cursors would actually be a more useful programmatic choice than implicit cursors. When performing bulk processing that cannot be performed using SQL or when returning a dataset to a client, you need to use explicit cursors. In almost all other cases, you should try to use SQL or implicit PL/SQL cursors before resorting to explicit cursors.

Implicit Cursors

Implicit cursors, unlike their explicit counterparts, do not need to be declared before they are invoked, though they can be, if you prefer (you’ll see an example of declaring an implicit cursor shortly). Additionally, implicit cursors do not need to be opened from, fetched from, and closed from explicitly. Instead of the open, fetch, close syntax, they use either the for … in syntax or a simple select ... into syntax. An example of a typical implicit cursor is shown in Listing 10-5.

Listing 10-5. An Implicit Cursor Used to Fetch Just One Value

CREATE FUNCTION f_get_name (ip_emp_id IN NUMBER) RETURN VARCHAR2
AS
lv_ename emp.ename%TYPE;
BEGIN
   SELECT ename INTO lv_ename FROM emp WHERE emp_id = f_get_name.ip_emp_id;
   RETURN lv_ename;
END;

You may recognize the business question being asked here. It’s the same business question asked in Listing 10-2. The main difference between the two is that, obviously, the solution in Listing 10-5 employs the use of an implicit cursor. When your goal is merely to fetch just one value, you should always defer to using an implicit cursor first. Compare the simplicity of this code with the verbosity and error-proneness of the explicit cursor example in Listing 10-2. In this implicit cursor example, there are no checks for SQL%FOUND or SQL%NOTFOUND. The code is very clear. There is one simple select ... into cursor. If just one name is selected, then just one name is returned. An exception-handling section could be written to handle the two cases where either no name exists for an entered employee ID or more than one name exists due to bad data.

images Note Notice that the examples thus far omit an exception-handling section. This is intentional. If there is a bug in your code and you don’t have an exception-handling section, Oracle will display the exact line item within your PL/SQL code where the error occurred. With an exception-handling section, the error line displayed is where your exception-handling section begins. This may be useful if you are providing instructions delivered up the stack to the user for the user to do something differently when interacting with your program. However, if the error message is for you and other programmers, then noting the exact line item on which your error occurred is essential for you to be able to debug quickly and effectively.

When there is at most one answer to the question you are asking, a for … in cursor is not necessary at all. In that case, use a simple SQL SELECT statement to retrieve an answer that could potentially have either zero records or one record to process. Listing 10-5 assumes that the business question asked will never return more than one record. If that is the case, the simple SQL select … into statement is the best programmatic choice for implementing the solution. If more than one record exists, your program would return an error. That is a good thing. You want to know if the data is bad. Similarly, your program would return an error if no record exists. Your program is expecting one and only one answer—any other result is an error and your program would fail immediately. Using the explicit cursor would typically not have the second fetch (described in Listing 10-2) to test whether there are more rows and that would be a bug. The select … into version is “bug free.”

The Anatomy of an Implicit Cursor

As mentioned previously, an implicit cursor can take one of two forms. It can be implemented using either a FOR LOOP or, more specifically, using the form for ... in, or it can be implemented using simple SQL select … into syntax. An implicit cursor does not require that you open it explicitly. When using the for … in form to implement an implicit cursor, keep the following in mind:

  • Once your implicit cursor enters its for loop structure, the cursor is initialized and the result set is identified.
  • The IN step is very similar to the FETCH step outlined in the “The Anatomy of an Explicit Cursor” section, with one key difference. When using an explicit cursor, if you want to ensure that there are no more rows to process, you need to add syntax similar to the following (an example of this is illustrated in Listings 10-3 and 10-4):
    EXIT WHEN cursor%NOTFOUND;
    Such extra syntax is not a requirement of implicit cursors. They do not need to check whether the cursor has more data to process to know when to exit.
  • Similarly, the CLOSE syntax is not required of an implicit cursor as it will exit automatically once the FOR loop has terminated.

The select … into cursor behaves differently from the way the for … in cursor behaves. Listing 10-6 illustrates the difference between the two implementations.

Compare each of the BEGIN … END anonymous blocks with the DECLARE … BEGIN … END anonymous blocks that immediately follow.

Listing 10-6. A Select … Into Cursor vs. a For … In Cursor

BEGIN
   FOR x IN (SELECT * FROM dual) LOOP ... END LOOP;
END;



DECLARE
   CURSOR c IS SELECT * FROM dual;
BEGIN
   OPEN c;
   LOOP
      FETCH c INTO …
      EXIT WHEN c%NOTFOUND;
       …
   END LOOP;
   CLOSE c;
END;


BEGIN
   SELECT *INTO ...FROM dual;
END;



DECLARE
   CURSOR c IS SELECT * FROM dual;
   l_rec dual%ROWTYPE;
BEGIN
   OPEN c;
   FETCH c INTO l_rec;
      IF (SQL%NOTFOUND)
      THEN
         RAISE NO_DATA_FOUND;
      END IF;
   FETCH c INTO l_rec;
      IF (SQL%FOUND)
      THEN
         RAISE TOO_MANY_ROWS;
      END IF;
   CLOSE c;
END;

Conceptually, select … into is like the two procedural blocks shown in the listing. However, an implicit cursor’s for … in processing performs an array fetch (as of Oracle10g), and is really, therefore, more like a bulk fetch and collect, making it much more efficient than its explicit cursor counterpart.

The select … into cursor uses different keywords and should be implemented when you expect zero records to, at most, one record. The for … in cursor should be implemented when you expect to be processing more than one record. Once you SELECT … INTO some PL/SQL variable, the cursor is initialized with the SELECT keyword, it fetches INTO the PL/SQL variable implicitly, then closes automatically because it has no more data to retrieve.

The Implicit Cursor and the Extra Fetch Theory

An oft batted-around theory regarding why implicit cursors should be avoided (and one that has been debunked for many years, though I still see it outlined in the odd company policy database programming guideline) is that implicit cursors engage in wasted work by performing an extra row fetch to test for row existence. This theory has been proposed in several (to remain anonymous) independent educational texts regarding the Oracle RDBMS. The problem with this theory is that it is rarely supported with actual evidence. It is easy enough to test this theory on any version of Oracle from version 7.1 onwards. In fact, it should be your moral imperative to challenge any edict that makes its way into a company database programming policy. If it is stated, then it should be provable. If it is not provable, then you should question whether it is an edict that should remain in force.

It is simple enough to test this theory using any implicit cursor of your choosing. For illustrative purposes, you may want to try a more universally-available case like a simple SELECT from an Oracle data dictionary table to test this theory. Listing 10-7 outlines a simple test case:

Listing 10-7. A Simple Implicit Cursor Fetch Test Case

SQL> DECLARE
  2      v_test NUMBER := 0;
  3  BEGIN
  4      SELECT user_id INTO v_test FROM all_users WHERE username = 'SYS';
  5  END;
  6   /
PL/SQL procedure successfully completed.

This is an easy test to conduct because it includes no looping constructs. At least and at most one value is expected. If the SELECT statement needs to perform an extra fetch, then the extra fetch should be visible in the TKPROF output of a simple SQL traced session. Listing 10-8 displays the TKPROF output for the test case illustrated in Listing 10-7.

Listing 10-8. TKPROF Output for Implicit Cursor Extra Fetch Test (11.2.0.1)

********************************************************************************

SQL ID: bs5v1gp6hakdp
Plan Hash: 3123615307
SELECT USER_ID
FROM
 ALL_USERS WHERE USERNAME = 'SYS'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          6          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          6          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  NESTED LOOPS  (cr=6 pr=0 pw=0 time=0 us cost=3 size=32 card=1)
      1   NESTED LOOPS  (cr=4 pr=0 pw=0 time=0 us cost=2 size=29 card=1)
      1    TABLE ACCESS BY INDEX ROWID USER$ (cr=2 pr=0 pw=0 time=0 us cost=1 size=26 card=1)
      1     INDEX UNIQUE SCAN I_USER1 (cr=1 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object images
 id 46)
      1    TABLE ACCESS CLUSTER TS$ (cr=2 pr=0 pw=0 time=0 us cost=1 size=3 card=1)
      1     INDEX UNIQUE SCAN I_TS# (cr=1 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object images
 id 7)
      1   TABLE ACCESS CLUSTER TS$ (cr=2 pr=0 pw=0 time=0 us cost=1 size=3 card=1)
      1    INDEX UNIQUE SCAN I_TS# (cr=1 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object images
 id 7)

********************************************************************************

As you can see from the TKPROF output, no extra fetch is performed. Therefore, I can think of only a handful of reasons why you would not want to do everything you can to ensure that you are using implicit cursors over explicit cursors. As mentioned in the “Explicit Cursors and Bulk Processing” subsection, when needing to perform FORALL bulk processing on data fetched from the database, you will need to use explicit cursors. In other words, if you are merely selecting data and using htp.p to output the data in APEX, or you are using utl_file.put_line to send the selected data to a file, for example, there is no need to bulk process using an explicit cursor. Behind the scenes, the Oracle RDBMS already is bulk processing in those types of instances.

It is only when you fetch data out, process it procedurally, and then put it back into the database using FORALL updates, inserts, and/or deletes that you would have a need for explicit cursor processing. In the same section, REF cursors were briefly illustrated as yet another reason to use explicit cursors if your requirement is to pass data to a client (residing outside of, but connecting to, the database) application. The next two sections introduce the two types of REF cursors available to you.

Static REF Cursors

As mentioned in the section “REF Cursors in Brief,” if you are returning a result set to a client, REF cursors (also referred to as reference cursors or cursor variables) are the best way to do so. A REF cursor often depends on the input of the client to decide exactly which cursor to open, and therefore, which result set to return. Given the quantity of unknowns that can be present when returning data to a client, the ability to select (no pun intended) the query to be run at runtime provides your program with a great amount of flexibility. This ability also helps you to avoid writing repetitive code. You see an example of a static REF cursor in section “REF Cursors in Brief.” Listing 10-9 illustrates an expansion on this example of a static REF cursor with package parameter specifications:

Listing 10-9. A Static REF Cursor Declared in a PL/SQL Package (11.2.0.1)

CREATE OR REPLACE PACKAGE product_pkg
AS
-- In Package Specification, declare the cursor variable type
TYPE my_cv IS REF CURSOR;

PROCEDURE get_concepts ( ip_input    IN  NUMBER,
                         op_rc      OUT  my_cv);

END product_pkg;

CREATE OR REPLACE PACKAGE BODY product_pkg
AS
-- In Package Body procedure, declare the cursor variable using the type declared in images
 the package specification

PROCEDURE get_concepts (ip_input    IN  NUMBER,
                        op_rc      OUT  my_cv)
IS
    BEGIN
       IF ( ip_input = 1 )
       THEN
          OPEN op_rc FOR
             SELECT * FROM prod_concepts
              WHERE concept_type  = 'COLLATERAL'
                AND concept_dt    < TO_DATE( '01-JAN-2003', 'DD-MON-YYYY'),
       ELSE
          OPEN op_rc FOR
             SELECT * FROM prod_concepts
              WHERE concept_category = 'ADVERTISING';
       END IF;
    END get_concepts;

END product_pkg;

The package and package body in Listing 10-9 illustrate code that makes use of a cursor variable. The package specification declares a REF cursor type, as shown by this line of code:

TYPE my_cv IS REF CURSOR;

Note that this type of cursor variable is weak-typed. The weak-typed cursor variable provides you with more flexibility as to the result set returned. However, because it is not created as a specific type of record like the following

TYPE my_cv IS REF CURSOR RETURN prod_concepts%ROWTYPE;

or

TYPE ProdConTyp IS RECORD (
     prod_id                  NUMBER,
     con_id                   NUMBER,
     req_id                   NUMBER,
     concept_type       VARCHAR2(30),
     concept_category   VARCHAR2(30),
     concept_dt                DATE);
     TYPE my_cv IS REF CURSOR RETURN ProdConTyp;

it is more prone to error. If you find yourself having to use cursor variables, it is better to create them strong-typed (as just shown), than weak-typed whenever possible. The type declared in the package specification is then referenced in the package body to declare the actual cursor variable, as illustrated with the following line of code:

op_rc      OUT  my_cv)

From this point forward, this cursor variable may be used and re-used for any result set you would like, as evidenced by the following code that opens the REF cursor and returns it as an out parameter in the procedure get_concepts:

IF ( ip_input = 1 )
THEN
   OPEN op_rc FOR
             SELECT * FROM prod_concepts
              WHERE concept_type = 'COLLATERAL'
                AND concept_dt   < TO_DATE( '01-JAN-2003', 'DD-MON-YYYY'),
 ELSE
          OPEN op_rc FOR
             SELECT * FROM prod_concepts
              WHERE concept_category = 'ADVERTISING';
END IF;

Note that in this code, all columns from the PROD_CONCEPTS table are returned dependent upon the WHERE clause that ultimately is executed. In this case, it would be better to strong-type the cursor variable used to return either of these result sets since they both contain the same column list. However, for purposes of illustration, if a third outcome were possible, expanding the IF statement as follows

ELSIF ( ip_input = 2 )
THEN
   OPEN op_rc FOR
             SELECT prod_name FROM products p, prod_concepts pc
              WHERE p.prod_id    = pc.prod_id
                AND concept_type = 'COLLATERAL'
                AND concept_dt   < TO_DATE( '01-JAN-2003', 'DD-MON-YYYY'),

this result set would be completely different from either of the others. In this particular instance, all things being equal, a weak-typed cursor variable such as the example shows would be the only type of cursor variable that would work for this scenario. In other words, because the example’s chosen type of cursor variable can accept virtually any type of result set, it will likely succeed.

However, because its structure is not known to the compiler at compile time, if a failure were to occur regarding this REF cursor, it would occur at runtime. You always take a risk when you sidestep possible compiler errors and allow them to occur (if they are going to occur) at runtime because such errors affect your users. Remember, just because you can use a particular type of cursor, doesn’t mean it is your best choice. Your goal is to write PL/SQL as efficiently as possible. If you are using a REF cursor, it is assumed that either

  • You are passing a result set to a client, and/or
  • You don’t know until runtime what the query will be, and/or
  • There is no method available to you in achieving your goal using implicit cursors.

There are several items to keep in mind when using REF cursors. REF cursors are owned by whomever (whether a client program or a PL/SQL program) is processing/fetching their records at a current point in time. Because a REF cursor is a pointer to a cursor and can, in fact, be pointed to many different cursors, it is not cached in PL/SQL. Statement caching is extremely important in reducing overall parse calls and, therefore, increasing application scalability. Repeated parsing of the same SQL lowers your scalability and increases the amount of work you do. By using non-REF cursors in PL/SQL, you decrease your parsing greatly. More regarding the PL/SQL cursor cache is discussed in the section “A Few Words About Parsing.”

There is a caveat to this in that you can limit the amount of soft parsing work (or rather, the amount of work soft parsing performs, since you cannot limit soft parsing) that will inevitably take place by setting your initialization file’s value for the session_cached_cursors parameter appropriately. With REF cursors, PL/SQL doesn’t know what the cursor will be until a request is made for it and the associated code is run. Therefore, since the overhead is slightly higher in using REF cursors, it’s a good idea to see if you can accomplish what you’d like to do by using regular cursors first.

Why are all of these points important? Because there are a slew of restrictions on using cursor variables. If you think you’ve chosen the easy way to accomplish your goal by using REF cursors instead of using non-REF cursors, you’ve actually chosen a method that will be much harder to manage long-term.

Cursor Variable Restrictions Laundry List

Consider these very important points regarding cursor variables:

  • You can’t declare cursor variables in a package (specification or body, that is). They may only be declared in a procedure or function.
  • You can’t pass cursor variables back and forth over a database link. If you pass a host cursor variable to PL/SQL, you can’t fetch from it on the server side unless you also open it within the same server call.
  • Cursors and cursor variables are not interoperable. You can’t use one where the other is expected.
  • You can’t use comparison operators to test cursor variables for equality, inequality, or nullity.
  • You can’t use a REF CURSOR type to specify the element type of a collection. Therefore, collection elements can’t store the values of cursor variables. This may be something to consider, especially if you are planning on using REF cursors to aid in bulk processing, for example.
  • By the same token, you can’t use REF CURSOR types to specify column types in a CREATE TABLE or CREATE VIEW statement. In other words, database columns can’t store the values of cursor variables.
  • You can’t assign null values to a cursor variable.

This is a lot to think about. Given all of these restrictions on using cursor variables, your choice to use them, therefore, must be well considered. However, using cursor variables is definitely preferable to putting all of your database access logic directly into the application. So, if you choose to use cursor variables, it’s important to not only have an idea about how you’d structure your PL/SQL code portion, but additionally, how you’d structure your Java code portion.

Your Client and REF Cursors

Remember that the client owns the REF cursor once it is returned to them. Your PL/SQL program opens the REF cursor, sometimes based on input it receives from the client, and then returns the result set it opens to the client. Listing 10-10 provides a brief example of a Java program used to accept the result set received from the PL/SQL program outlined in Listing 10-9.

Listing 10-10. A Java Program Used for REF Cursor Processing (11.2.0.1)

import java.sql.*;
import java.io.*;
import oracle.jdbc.driver.*;
class ProdConRefCursor
{
public static void main (String args [ ])
throws SQLException, ClassNotFoundException
{
   String query  = "BEGIN " + "product_pkg.get_concepts( ?, ?); " + "end;";
   DriverManager.registerDriver
        (new oracle.jdbc.driver.OracleDriver());
   Connection conn =
       DriverManager.getConnection
       ("jdbc:oracle:thin:@proximo-dev:1521:mozartora112dev", "mktg", "mktg");
   Statement trace = conn.createStatement();
   CallableStatement  cstmt = conn.prepareCall(query);
   cstmt.setInt(1, 37);
   cstmt.registerOutParameter(2 ,OracleTypes.CURSOR);
   cstmt.execute();
   ResultSet rset = (ResultSet)cstmt.getObject(2);
   for(int i = 0;  rset.next(); i++ )
        System.out.println( "rset " + rset.getString(1) );
   rset.close();
}
}

This is a rather simplistic, yet straightforward example of a client accepting the result set returned by a REF cursor. The salient parts of this are the following code snippets:

cstmt.registerOutParameter(2 ,OracleTypes.CURSOR);
cstmt.execute();
ResultSet rset = (ResultSet)cstmt.getObject(2);

Your return type is a cursor of OracleTypes. Once you receive the cursor, you iterate over each record until there are no more records to process on the client side, like so:

for(int i = 0;  rset.next(); i++ )
        System.out.println( "rset " + rset.getString(1) );

Finally, it is the job of the client to close the REF cursor. It cannot pass the REF cursor back to your PL/SQL program. Therefore, once it is done processing the records returned by your REF cursor, it simply closes the cursor with the following statement:

rset.close();

A Few Words about Parsing

Though this may sound like a bit of an aside, parsing is something to be kept high on your list of priorities when choosing which cursor will suit you best for each type of programmatic situation. As mentioned earlier, REF cursors, even the static type, generally perform a soft parse each time they are opened (see the caveat regarding setting a value in session_cached_cursors mentioned earlier). Though a soft parse is infinitely better than a hard parse, it is much worse than no parse at all. Your goal when writing any kind of SQL or PL/SQL is to reduce parsing whenever and wherever possible. So what can you do to reduce parsing?

If you are using PL/SQL implicit cursors or explicit (non-REF) cursors and you are not using literal values for your predicates, you are using bind variables. This means that, by default, you have opened up the opportunity to reduce parsing since you have reduced the number of unique SQL statements. Your cursor queries will parse the first time they are accessed and remain in the shared pool to be reused by multiple accesses until they are aged out. This is a huge savings for the runtime and scalability of your application. Your cursor queries will not be seen as individual, new cursors to be parsed, executed, closed, and reopened (wash, rinse, repeat) over and over again.

In addition to being cached in the shared pool, PL/SQL itself can “cache” these queries. It will cache them in an open state. This means they can be executed later again without having to even open (parse) them. So, not only will you have excellent shared SQL in the shared pool, but you’ll also eliminate all but one parse call per SQL statement in your session. That is one of the best reasons to use non-REF cursors. PL/SQL does not close them, it keeps them open and avoids the parse altogether.

REF cursors, on the other hand, always perform a soft parse. On the one hand, they provide you with great coding flexibility, particularly if you are required to pass a result set back to a client, which many database applications are required to do. On the other hand, that flexibility comes with the tradeoff of possible reduced scalability and excessive soft parses. And with excessive soft parsing comes possible latch contention. So if you can’t write static implicit cursors and you must write REF cursors, what can you do to work towards reducing the effects of inevitable soft parsing?

Consider setting an appropriate value for your session_cached_cursors initialization file parameter. Your goal is to reduce the effects (latching) of excessive soft parses. Caching cursors system-wide is generally a very good thing because it will reduce the effects of too much latching and help your application to avoid latch contention. Using session_cached_cursors helps you to make a soft parse softer. It makes soft parsing easier, so it is, therefore, a good setting to have in place when your applications use REF cursors. When you close a cursor in PL/SQL, implicitly or explicitly, PL/SQL does not close the cursor. It caches it open. And the size of the PL/SQL cache is controlled by the value supplied for the session_cached_cursors parameter.

Static SQL using non-REF cursors in PL/SQL is what helps to reduce parsing, due to the PL/SQL cursor cache. More than one parse will take place when the application parses the query more than once. The parses might be hard or soft. For example, say you have a script similar to the following:

ALTER SESSION SET SQL_TRACE = true;

BEGIN
    FOR i IN 1 .. 2000 LOOP
        FOR x IN ( SELECT /*+ implicit cursor query goes here */ * FROM dual )
        LOOP
            NULL;
        END LOOP;
    END LOOP;
END;
/

DECLARE
    TYPE rc_type IS REF CURSOR;
    rc rc_type;
BEGIN
    FOR i IN 1 .. 2000 LOOP
        OPEN rc FOR SELECT /*+ REF cursor query goes here */ * FROM dual;
        CLOSE rc;
    END LOOP;
END;
/

The resultant TKPROF output reveals the following:

********************************************************

SQL ID: dybuvv2j10gxt
Plan Hash: 272002086
SELECT /*+ implicit cursor query goes here */ *
FROM
 DUAL


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute   2000      0.01       0.02          0          0          0           0
Fetch     2000      0.03       0.04          2       6000          0        2000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     4001      0.05       0.07          2       6000          0        2000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84     (recursive depth: 1)


Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS FULL DUAL (cr=3 pr=2 pw=0 time=0 us cost=2 size=2 card=1)

********************************************************

The TKPROF output illustrates how the query was parsed once and executed 2,000 times. PL/SQL cached the cursor. The “misses in library cache during parse” line in the output also demonstrates this was a hard parse. It wasn’t found in the shared pool. Here’s another example:

********************************************************************************

SQL ID: gc72r5wh91y0k
Plan Hash: 272002086
SELECT /*+ REF cursor query goes here */ *
FROM
 DUAL


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse     2000      0.02       0.02          0          0          0           0
Execute   2000      0.02       0.02          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     4000      0.04       0.05          0          0          0           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  TABLE ACCESS FULL DUAL (cr=0 pr=0 pw=0 time=0 us cost=2 size=2 card=1)

********************************************************************************

This time the TKPROF output demonstrates the query was parsed 2000 times. It was opened with a REF cursor, and PL/SQL can’t cache such cursors. The output also demonstrates one hard parse and (therefore) 1,999 soft parses. The second parse was done by finding the already existing plan in the shared pool’s library cache. Optimally, you should see parse =1 but it is not unusual to see parse = execute = N.

Dynamic REF Cursors

Dynamic REF cursors are, for all practical purposes, very similar to static REF cursors, except for a few notable differences. The query for a dynamic REF cursor is not known until runtime and is also built dynamically. You may ask how this is different from static REF cursors, since a static REF cursor’s query is also not known until runtime. Strongly-typed REF cursors cannot be dynamically opened. Weakly-typed REF cursors can be dynamically opened.

Example and Best Use

Consider the example in Listing 10-11 that outlines a procedure that uses a dynamic REF cursor:

Listing 10-11. A Procedure That Uses a Dynamic REF Cursor (11.2.0.1)

PROCEDURE get_concepts (ip_input      IN  NUMBER,
                        ip_con_type   IN  VARCHAR2,
                        ip_con_dt     IN  DATE,
                        ip_con_cat    IN  VARCHAR2,
                        op_rc        OUT  my_cv)
IS
    lv_query VARCHAR2 (512) DEFAULT 'SELECT * FROM prod_concepts';
    BEGIN
         IF ( ip_con_type IS NOT NULL )
            lv_query := lv_query ||
            '  WHERE concept_type LIKE ''%''||:ip_con_type||''%'' ';
         ELSE
            lv_query := lv_query ||
            ' WHERE (1 = 1 OR :ip_con_type IS NULL) ';
         END IF;
         IF ( ip_con_dt IS NOT NULL )
            lv_query := lv_query ||
            '  AND concept_dt < :ip_con_dt ';
         ELSE
            lv_query := lv_query ||
            ' AND (1 = 1 OR :ip_con_dt IS NULL) ';
         END IF;
         IF ( ip_con_cat IS NOT NULL )
            lv_query := lv_query ||
            '  AND concept_category LIKE ''%''||:ip_con_cat||''%'' ';
         ELSE
            lv_query := lv_query ||
            ' AND (1 = 1 OR :ip_con_cat IS NULL) ';
         END IF;
            OPEN op_rc FOR
                lv_query USING ip_con_type, ip_con_dt, ip_con_cat;
       
    END get_concepts;

So, how is this version of the get_concepts procedure different from the one illustrated in Listing 10-9? To begin with, unlike the static OPEN-FOR version, the dynamic version has an optional USING clause—optional because it would also be possible to code this procedure using literals, as opposed to bind variables, similar to the following:

'SELECT * FROM prod_concepts
  WHERE concept_type  = ''||COLLATERAL||''
    AND concept_dt    < ''||TO_CHAR(''''01-JAN-2003''', '''DD-MON-YYYY''')||''';

Obviously, this appears to be a non-ideal and cumbersome way to build your query. It would also lead to the dreaded hard parse since all queries would be unique, which would kill performance. If it is necessary to build a dynamic query of any kind, ask the reasons for doing so. You need dynamic SQL in the following situations:

  • You want to execute a SQL data definition statement (such as CREATE), a data control statement (such as GRANT), or a session control statement (such as ALTER SESSION). In PL/SQL, such statements cannot be executed via static SQL.
  • You want more flexibility. For example, you might want to wait until runtime to choose which schema objects you use. Or, you might want your program to build different search conditions for the WHERE clause of a SELECT statement as is being done in Listing 10-11. Dynamically building a WHERE clause is a very common reason to use dynamic SQL. A more complex program might choose from various SQL operations, clauses, etc.

If you don’t need to perform any of the above, but you do need a REF cursor, then you should definitely use a static REF cursor. If you do need to use any of the above, ensure that you are using bind variables, as with the first example in Listing 10-11.

Now, because the structure you are using is a REF cursor, it will still be performing soft parses, but at least, if you are using bind variables, it won’t be performing hard parses. Because the cursor could be different, the Oracle RDBMS assumes that it might be, since it is dynamic, and therefore some type of parse always takes place. Of all the cursor types you have to choose from, this one should definitely be your last resort. Dynamic SQL is extremely fragile, hard to code, hard to tune, and simply hard to maintain. The chain of code dependencies no longer exists and you are therefore hoping that data structure changes do not adversely affect your code.

But you learned in the previous section that REF cursors perform, at a minimum, soft parsing. Other than the cumbersome coding that dynamic REF cursors present, what might be other reasons to avoid them? The biggest reason I can think of to avoid dynamic REF cursors is the possibility of SQL injection. SQL injection is a very real threat to your applications and, more importantly, your data. If your application code allows malicious users to attach their own SQL statements to your dynamically built queries, such attacks can pose a dangerous threat to the security of your data.

The Threat of SQL Injection

Dynamic SQL that allows a user to concatenate strings into statements without bind variables is not only a parsing, scalability, and maintainability nightmare; it also presents a very tangible security threat. Each time you allow someone to provide inputs from outside your control, your data is no longer safe. And to top it all off, the malicious user, because they are using your code, is executing their statements as you. You’ve opened the door and left your data vulnerable. Consider the example in Listing 10-12 that outlines an innocent-looking procedure subject to SQL injection:.

Listing 10-12. A Procedure Subject to SQL Injection (11.2.0.1)

PROCEDURE get_concepts (ip_input      IN  NUMBER,
                        ip_con_type   IN  VARCHAR2,
                        ip_con_dt     IN  DATE,
                        ip_con_cat    IN  VARCHAR2,
                        op_rc        OUT  my_cv)
IS
    BEGIN
       IF ( ip_input = 1 )
       THEN
       OPEN op_rc FOR
             'SELECT * FROM prod_concepts
               WHERE concept_type  = ''||ip_con_type||''
                 AND concept_dt    < ''||ip_con_dt||''' ;
       ELSE
          OPEN op_rc FOR
             'SELECT * FROM prod_concepts
               WHERE  concept_category = ''||ip_con_cat||''' ;
       END IF;
    END get_concepts;

If someone wishes to do something they shouldn’t, such as gain access or something destructive, then these harmless looking parameters (obviously intended for input regarding product concept data) can easily be manipulated to where the resultant dynamic SQL queries end up looking similar to the following:

CREATE OR REPLACE
    PROCEDURE get_concepts (ip_input      IN  NUMBER,
                            ip_con_type   IN  VARCHAR2,
                            ip_con_dt     IN  DATE,
                            ip_con_cat    IN  VARCHAR2,
                            op_rc        OUT  sys_refcursor )
    IS
        BEGIN
           IF ( ip_input = 1 )
          THEN
                   DBMS_OUTPUT.PUT_LINE(
                'SELECT * FROM prod_concepts
                  WHERE concept_type  = ''' ||ip_con_type|| '''
                    AND concept_dt    < ''' || to_char(ip_con_dt, 'dd-mon-yyyy' ) images
 ||'''' );
          ELSE
                   DBMS_OUTPUT.PUT_LINE(
                'SELECT * FROM prod_concepts
                  WHERE  concept_category = ''' || ip_con_cat||'''' );
          END IF;
       END get_concepts;
   /
scott%ORA11GR2> variable x refcursor
scott%ORA11GR2> exec get_concepts( ip_input => 2, ip_con_type => null, ip_con_dt => null, images
 ip_con_cat => '''||admin_pkg.change_app_passwd( ''INJECTED'' ) --', op_rc => :x );
SELECT * FROM prod_concepts
 WHERE concept_category =
''||admin_pkg.change_app_passwd( 'INJECTED' ) --'

PL/SQL procedure successfully completed.

What has taken place here? In the call to this procedure, the input the user provides does not care about matching either the concept_type or the concept_dt and thus provides the actual SQL they intend to execute: admin_pkg.change_app_passwd('INJECTED'), . The addition of the notation, --, just at the end of that input marks the start of an SQL comment. This is a clever way to consume the final quote provided by the application and not worry about having to match it.

As you can see, dynamic SQL leaves the door wide open to malicious intruders. Any time you are not using bind variables, you are not only impeding application performance, you are putting your data at severe risk. Therefore, since dynamic REF cursors (and dynamic SQL of any kind) are fragile and prone to both error and risk, you need to ask yourself whether the added flexibility of coding applications that use REF cursors is worth the security risks and maintainability headaches. And if you have no other choice but to use REF cursors, keep the following in mind:

  • If you must use REF cursors, use strongly-typed static REF cursors.
  • If you cannot use a static REF cursor, then ensure that your dynamic REF cursor uses bind variables.

Describing REF Cursor Columns

In Oracle versions gone by, it was never the case that you could describe the columns of a REF cursor. You had to use a client external to the database if you wanted this functionality. This situation has changed as of Oracle version 11.1 and later. As of Oracle 11.1, DBMS_SQL, a supplied package that lets you procedurally process a result set much like JDBC, works in tandem with REF cursor functionality to allow you to be able to describe your columns programmatically after opening your REF cursor. This functionality is possible because Oracle 11g allows a REF cursor to be converted to DBMS_SQL and vice versa. Consider the example in Listing 10-13.

Listing 10-13. A REF Cursor That Uses DBMS_SQL to Describe Columns (11.2.0.1)

DECLARE
            lv_ref_cursor   SYS_REFCURSOR;
            lv_col_cnt      NUMBER;
            lv_desc_tab     DBMS_SQL.DESC_TAB;
BEGIN
            OPEN lv_rcursor FOR SELECT * FROM prod_concepts;
  
            DBMS_SQL.DESCRIBE_COLUMNS
            ( cur_id      => DBMS_SQL.TO_CURSOR_NUMBER(lv_ref_cursor),
              col_cnt     => lv_col_cnt,
              desc_tab    => lv_desc_tab );
 
           FOR i IN 1 .. lv_col_cnt
           LOOP
                   DBMS_OUTPUT.PUT_LINE( lv_desc_tab(i).col_name );
           END LOOP;
END;
/

The salient part of this example is the following code snippet:

cur_id      => DBMS_SQL.TO_CURSOR_NUMBER(lv_ref_cursor)

The function TO_CURSOR_NUMBER can convert any REF cursor (either strongly or weakly typed) to a SQL cursor number, which can then be passed to any DBMS_SQL subprograms. You can harness the capability of DBMS_SQL within REF cursor processing and provide your programs with further flexibility. Be aware, however, that once you convert a REF cursor to a DBMS_SQL cursor number, you can no longer use native dynamic SQL operations to access it. You can use the DBMS_SQL.TO_REFCURSOR function to switch the cursor number back to a REF cursor, but be aware that this latest version of the REF cursor will be weakly typed, even if it was strongly typed originally. So, as with any other PL/SQL cursor functionality, use this functionality with full knowledge of its side effects, as well as its benefits.

Summary

Choosing the right types of cursors for your application needs can make or break your application. If you have the opportunity to use static SQL, do so and don’t look back. When choosing between implicit and explicit cursors, defer to implicit cursors whenever possible. Your performance will be increased, your scalability will be measurable, and your users will thank you. However, if you are performing bulk processing where you need to also process the data procedurally, or you are passing a result set to a client application, you will have little choice but to use an explicit cursor.

When choosing a REF cursor (that your application needs have deemed inevitable), see if you can meet your goal by use of a strongly-typed REF cursor. Creating compiler-aware code should be one of your paramount goals. Remember that dynamic code is fragile and error-prone by its very nature. But if you must use dynamic REF cursors, ensure that you are using bind variables—not just for the runtime gains, but for the security peace of mind as well. SQL injection is just waiting for any application caught unaware.

Your job, as a developer, is to use the right tools for the right job. All of these cursor types are right for a particular purpose. Ensure you have a good understanding of the risks and pitfalls, in addition to the benefits. Your cursor choice should be made in a way that maximizes your performance, decreases your concerns, and increases your popularity amongst your application user community. So, try out things different ways. Weigh one method versus another. Keep the order of precedence in mind, and when you must use something other than an implicit cursor, do so judiciously and wisely.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset