Cursors1 a cursor is a vehicle for extracting data from an Oracle database. Cursors determine the columns and objects

3.1. Explicit and Implicit Open Cursors

The list of open cursors is another area where explicit and implicit cursors are treated differently, as shown in the following example with OPEN_CURSORS set to 20. First, I'll execute several implicit cursors:

DECLARE

v_dummy varchar2(10);

SELECT 'A' INTO v_dummy FROM orders;

SELECT 'B' INTO v_dummy FROM orders;

...and so on through lower and upper case alphabets...

SELECT 'x' INTO v_dummy FROM orders;

SELECT 'y' INTO v_dummy FROM orders;

SELECT 'z' INTO v_dummy FROM orders;

END;
The session's list of associated cursors now looks like this:

SQL> SELECT oc.sql_text

2 FROM v$open_cursor oc,

3 v$sql sq

4 WHERE user_name = 'DRH'

5 AND oc.sql_id = sq.sql_id

6 AND command_type = 3;

---------------------------------

SELECT 'n' FROM ORDERS

SELECT 'z' FROM ORDERS

SELECT 'o' FROM ORDERS

SELECT 'q' FROM ORDERS

SELECT 'x' FROM ORDERS

SELECT 'l' FROM ORDERS

SELECT 'v' FROM ORDERS

SELECT 's' FROM ORDERS

SELECT 'p' FROM ORDERS

SELECT 'w' FROM ORDERS

SELECT 'm' FROM ORDERS

SELECT 'u' FROM ORDERS

SELECT 'k' FROM ORDERS

SELECT 'j' FROM ORDERS

SELECT 'i' FROM ORDERS

SELECT 'y' FROM ORDERS

SELECT 'r' FROM ORDERS

SELECT 't' FROM ORDERS

SELECT 'h' FROM ORDERS
19 rows selected.
Only the last cursors remained behind as soft-closed. The others were flushed out to make room for newer ones.

Now, I'll execute several explicit cursors by opening and closing each one.

DECLARE

CURSOR curs_65 IS SELECT 'A' FROM orders;

...and so on through the lowercase and uppercase alphabets

...ASCII 65 through 122

CURSOR curs_122 IS SELECT 'z' FROM orders;

BEGIN

OPEN curs_65;

...and so on...

OPEN curs_122;

CLOSE curs_122;

END;
I'll wind up with a list of associated cursors that is very similar to the list I had for the implicit run. But imagine what would happen if I were lazy and decided not to close the explicit cursors, as in this code.

DECLARE

CURSOR curs_66 IS SELECT 'B' FROM orders;

...and so on through the lowercase and uppercase case alphabets

...ASCII 65 through 122

CURSOR curs_122 IS SELECT 'z' FROM orders;

BEGIN

OPEN curs_66;

...and so on...

OPEN curs_122;

END;
At or near the 20^th cursor, I get this error because the session tried to go beyond its limit of 20 open cursors.

ERROR at line 1:

ORA-01000: maximum open cursors exceeded

ORA-06512: at line 21

ORA-06512: at line 80
This is one of the reasons why it's important to always close explicit cursors.

So, what's a good setting for the OPEN_CURSORS parameter? That's easy: set it to whatever you need plus one. That may not seem to be a very helpful answer, but there is no tried-and-true method for setting this parameter. If the value is too low, the code won't run because of the ORA-1000 error. If the value is too high, then cursors (explicit and implicit) may remain soft-closed forever. The good news is that the space is not pre-allocated, so setting this value high does not have to factor into your overall memory sizing.

This query provides a good value to use by determining the maximum number of cursors (open or soft-closed) associated with a current session. I run this at regular intervals early in the application's life cycle.

SYS> SELECT *

2 FROM ( SELECT sid,

3 COUNT(*)

4 FROM v$open_cursor

5 GROUP BY sid

6 ORDER BY COUNT(*) DESC)

7 WHERE ROWNUM = 1;

---------- ----------

46 20
I then set OPEN_CURSORS to the highest maximum plus a safety buffer of 10 or 20.

3.2. Native Dynamic SQL

Native Dynamic SQL (NDS) is also generally able to take advantage of soft-closed cursors and cursor reuse, but it does best when bind variables are used. Consider the following two procedures; they do the same thing except that one uses bind variables while the other uses concatenation.

CREATE OR REPLACE PROCEDURE bind ( p_on NUMBER ) AS

v_od DATE;

BEGIN

EXECUTE IMMEDIATE 'SELECT order_date ' ||

' WHERE order_number = :v_on'

INTO v_od

USING p_on;

END;
CREATE OR REPLACE PROCEDURE concatenate ( p_on NUMBER ) AS

v_od DATE;

BEGIN

EXECUTE IMMEDIATE 'SELECT order_date ' ||

' WHERE order_number = ' || p_on

INTO v_od;

END;
First, I'll execute the bind version three times:

SQL> BEGIN

2 FOR counter IN 1..3 LOOP

3 bind(counter);

4 END LOOP;

5 END;

6 /
PL/SQL procedure successfully completed.

SELECT order_date FROM orders

WHERE order_number = :v_on
The parse and execution counts are, as expected, 1 and 3:

SQL_TEXT PARSE_CALLS EXECUTIONS

------------------------------ ----------- ----------

SELECT order_date FROM orders 1 3

WHERE order_number = :v_on
Now I'll run the concatenation version three times.

SQL> BEGIN

2 FOR counter IN 1..3 LOOP

3 concatenate(counter);

4 END LOOP;

5 END;

6 /
PL/SQL procedure successfully completed.
The open cursor list looks like this because only the very last one is kept around, hoping for re-execution.

SQL_TEXT

SELECT order_date FROM orders

WHERE order_number = 3
The parse calls and execution counts are interesting. Three separate cursors all parsed once and executed once. That's just plain wasteful.

SQL_TEXT PARSE_CALLS EXECUTIONS

------------------------------ ----------- ----------

SELECT order_date FROM orders 1 1

WHERE order_number = 3

SELECT order_date FROM orders 1 1

WHERE order_number = 1
SELECT order_date FROM orders 1 1

WHERE order_number = 2