October 2005
Intermediate to advanced
454 pages
14h 44m
English
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);
BEGIN
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_text2FROM v$open_cursor oc,3v$sql sq4WHERE user_name = 'DRH'5AND oc.sql_id = sq.sql_id6AND command_type = 3;SQL_TEXT --------------------------------- 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; CURSOR curs_66 IS SELECT 'B' FROM orders; ...