Oracle PL/SQL for DBAs

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);
    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_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;

    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;  ...