Skip to Main Content
Oracle PL/SQL for DBAs
book

Oracle PL/SQL for DBAs

by Arup Nanda, Steven Feuerstein
October 2005
Intermediate to advanced content levelIntermediate to advanced
454 pages
14h 44m
English
O'Reilly Media, Inc.
Content preview from 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;  ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle PL/SQL Best Practices

Oracle PL/SQL Best Practices

Steven Feuerstein
Expert Oracle PL/SQL

Expert Oracle PL/SQL

Ron Hardman, Michael McLaughlin
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 0596005873Supplemental ContentErrata Page