msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

Cursors Again

Reading through other programmers’ code I often discover interesting things that sometimes I find hard to explain. For example, consider the following simple PL/SQL block:

DECLARE
   CURSOR emp_cur
   IS
      SELECT first_name
        FROM employees
       WHERE employee_id = 100;

   l_emp_name employees.first_name%TYPE;
BEGIN
   IF emp_cur%ISOPEN
   THEN
      CLOSE emp_cur;
   END IF;

   OPEN emp_cur;

   FETCH emp_cur
    INTO l_emp_name;

   CLOSE emp_cur;

END;

The PL/SQL package I was reviewing had the same exact pattern for every query. Basically, every query was declared as an explicit cursor and code similar to the above was used to open, fetch and close every cursor in the program.

I do not understand two things here: the need for an explicit cursor, and the need for this “IF cur%ISOPEN THEN CLOSE cur; END IF;” before “OPEN cur …. CLOSE cur“. Do you?

I would have coded the above like this:

DECLARE
   l_emp_name   employees.first_name%TYPE;
BEGIN
   SELECT first_name
     INTO l_emp_name
     FROM employees
    WHERE employee_id = 100;

EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.put_line ('Not found');
END;

In this example, since the employee_id is the primary key of the employees table, there is no need to code for the TOO_MANY_ROWS exception.

Andrew Clarke has a good post about implicit and explicit cursors in response to Misbah Jalil’s post about the same subject. And here is a virtual book about cursors in Oracle DB 10gR2.


Filed in Oracle on 21 Jun 07 | Tags:


Reader's Comments

  1. |

    I suppose that good practice changes over time and depending on when the code was first written or when the developer learned his trade, explicit cursors were favoured over implicit cursors and used to have a slight performance advantage, etc.

    Times change, software changes and best practice evolves but a lot of time people don’t keep on top of this evolution and stick to the what they used to know.

    And he probably had a friend who overheard a conversation in the pub about a rumour that you should always check that your cursor is closed before you open it.

    Anyway, it does look sort of old fashioned these days and your implicit version is both neater and also allegedly slightly better performing in any recent version.

    But we’re probably all guilty of using bits of code automatically and without thinking. For example, I think I’ve just about got out of the habit of creating table types indexed by binary_integer rather than pls_integer. And frequently look at some of my code to find that I’m iterating through a sparse collection using FOR i IN x.FIRST … x.LAST, etc.

    That’s why code reviews can be such a good thing in that they can stop and make you think again about what you’ve done.

  2. |

    I would add the raise in the exception code.

    EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.put_line (‘Not found’); RAISE;

  3. |

    Dominic, I could not agree more. Old habits are hard to break. I believe that knowing what’s new is very essential to staying up to date and improving your skills.

    Steeve, I believe adding a RAISE in the when NO_DATA_FOUND exception depends on the application logic. However, whenever you have WHEN OTHERS it is almost always the case that you would want to add RAISE.

  4. |

    Perhaps an olde mythe from pre-7.3 days: http://www.jlcomp.demon.co.uk/faq/exp_cursor.html

  5. |

    How about using a Table API, like the QDA (Quest Development Architecture) provided by Quest CodeGen Utility. That way, developers don’t need to write any SQL anymore. Whether you decide to use explicit cursors or implicit cursors is up to the developer(s) maintaining the Table API. The other developers don’t need to worry about how the SQL is done, they just call a procedure to get the information they need. If the underlying table structure changes, you just need to change the implementation of the TAPI, which helps in Single Point Of Definition. By the way, in our ‘old’ code, we tend to use explicit cursors, because we tend to forget to write all the exception handlers for selects in the code.