DBMS_SQL.TO_CURSOR_NUMBER and “cursor leaking”

Some time ago it was a hot discussion in http://www.sql.ru forum over “does PL/SQL machine close cursors when I leave PL/SQL block or not”. I have found that many people are still being missdriven by Oracle mess of terminology for “open cursor”. In many cases people miss “open SQL area”, “parsing”, “PL/SQL cashed cursor”,”session cached cursor” ets. No wonder – Oracle documentation treats “open cursor” in too many ways and many Oracle gurus wrote of this many times.
But this misunderstanding and a wrong use of V$OPEN_CURSOR may lead to confusions in Oracle development and production support. Let’s look at the case when we use the brand new and useful DBMS_SQL functionality – the function TO_CURSOR_NUMBER.

First of all I have to remember that V$OPEN_CURSOR dictionary view reflects the information about specific library cache locks (but not about just open SQL area handlers). Because of this it reflects the information about parsed SQL statements. But this is not equivalent to open cursor ! To get it clear let’s execute the following code:

SQL> select value
  2  from v$mystat a, v$statname b
  3  where a.statistic# = b.statistic#
  4  and b.name = 'opened cursors current'
  5  /

     VALUE                                                                      
----------                                                                      
         2                                                                      

SQL> 
SQL> select cursor_type, count(*)
  2  from v$open_cursor
  3  where sid = sys_context('USERENV','SID')
  4  group by rollup(cursor_type)
  5  order by 1
  6  /

CURSOR_TYPE                                                        COUNT(*)     
---------------------------------------------------------------- ----------     
BUNDLE DICTIONARY LOOKUP CACHED                                           7     
DICTIONARY LOOKUP CURSOR CACHED                                          20     
OPEN                                                                      1     
OPEN-RECURSIVE                                                            2     
PL/SQL CURSOR CACHED                                                      5     
SESSION CURSOR CACHED                                                     3     
                                                                         38     

7 rows selected.

SQL> 
SQL> 
SQL> DECLARE
  2      rc_number INTEGER;
  3  BEGIN
  4      rc_number := DBMS_SQL.OPEN_CURSOR;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL> 
SQL> select value
  2  from v$mystat a, v$statname b
  3  where a.statistic# = b.statistic#
  4  and b.name = 'opened cursors current'
  5  /

     VALUE                                                                      
----------                                                                      
         3                                                                      

SQL> 
SQL> select cursor_type, count(*)
  2  from v$open_cursor
  3  where sid = sys_context('USERENV','SID')
  4  group by rollup(cursor_type)
  5  order by 1
  6  /

CURSOR_TYPE                                                        COUNT(*)     
---------------------------------------------------------------- ----------     
BUNDLE DICTIONARY LOOKUP CACHED                                           7     
DICTIONARY LOOKUP CURSOR CACHED                                          19     
OPEN                                                                      1     
OPEN-RECURSIVE                                                            2     
PL/SQL CURSOR CACHED                                                      5     
SESSION CURSOR CACHED                                                     4     
                                                                         38     

7 rows selected.

SQL> 
SQL> DECLARE
  2      rc_number INTEGER;
  3  BEGIN
  4      for i in 1..100 loop
  5          rc_number := DBMS_SQL.OPEN_CURSOR;
  6      end loop;
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL> 
SQL> select value
  2  from v$mystat a, v$statname b
  3  where a.statistic# = b.statistic#
  4  and b.name = 'opened cursors current'
  5  /

     VALUE                                                                      
----------                                                                      
       103                                                                      

SQL> 
SQL> select cursor_type, count(*)
  2  from v$open_cursor
  3  where sid = sys_context('USERENV','SID')
  4  group by rollup(cursor_type)
  5  order by 1
  6  /

CURSOR_TYPE                                                        COUNT(*)     
---------------------------------------------------------------- ----------     
BUNDLE DICTIONARY LOOKUP CACHED                                           7     
DICTIONARY LOOKUP CURSOR CACHED                                          19     
OPEN                                                                      1     
OPEN-RECURSIVE                                                            2     
PL/SQL CURSOR CACHED                                                      5     
SESSION CURSOR CACHED                                                     4     
                                                                         38     

7 rows selected.

I did not close cursor handlers explicitly in purpose to demostrate clearly what dispite I have 103 open cursor handlers (what is the subject of ORA-01000 exception), but V$OPEN_CURSOR says to me about 38 “open cursors” – i.g. about 38 handled library cache locks.
Next round of cursor opening leads to ORA-01000:

SQL> DECLARE
  2      rc_number INTEGER;
  3  BEGIN
  4      for i in 1..200 loop
  5          rc_number := DBMS_SQL.OPEN_CURSOR;
  6      end loop;
  7  END;
  8  /
DECLARE
*
ERROR at line 1:
ORA-01000: maximum open cursors exceeded 
ORA-06512: at "SYS.DBMS_SQL", line 1163 
ORA-06512: at line 5 


SQL> show parameter open_cursors

NAME                                 TYPE        VALUE                          
------------------------------------ ----------- ------------------------------ 
open_cursors                         integer     300                            

So if you want to see the real statistics of “cursor leaking” – please use v$mystat or v$sesstat, not v$open_cursor.
OK, let’s come back to DBMS_SQL functionality. If we use cursor variable opened in PL/SQL you may want to use DBMS_SQL.TO_CURSOR_NUMBER functon to get the acess to other DBMS_SQL functions (like DESCRIBE). But in this case you should handle you cursors explicitly. Let’s look at this.

SQL> select value
  2  from v$mystat a, v$statname b
  3  where a.statistic# = b.statistic#
  4  and b.name = 'opened cursors current'
  5  /

     VALUE                                                                      
----------                                                                      
         2                                                                      

SQL> DECLARE
  2  --    rc_number INTEGER;
  3      rc SYS_REFCURSOR;
  4  BEGIN
  5      OPEN rc FOR SELECT * FROM DUAL;
  6  --    rc_number := DBMS_SQL.TO_CURSOR_NUMBER(rc);
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL> select value
  2  from v$mystat a, v$statname b
  3  where a.statistic# = b.statistic#
  4  and b.name = 'opened cursors current'
  5  /

     VALUE                                                                      
----------                                                                      
         2                                                                      

SQL> DECLARE
  2      rc_number INTEGER;
  3      rc SYS_REFCURSOR;
  4  BEGIN
  5      OPEN rc FOR SELECT * FROM DUAL;
  6      rc_number := DBMS_SQL.TO_CURSOR_NUMBER(rc);
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL> select value
  2  from v$mystat a, v$statname b
  3  where a.statistic# = b.statistic#
  4  and b.name = 'opened cursors current'
  5  /

     VALUE                                                                      
----------                                                                      
         3                                                                      

SQL> DECLARE
  2      rc_number INTEGER;
  3      rc SYS_REFCURSOR;
  4  BEGIN
  5      OPEN rc FOR SELECT * FROM DUAL;
  6      rc_number := DBMS_SQL.TO_CURSOR_NUMBER(rc);
  7      DBMS_SQL.CLOSE_CURSOR(rc_number);
  8  END;
  9  /

PL/SQL procedure successfully completed.

SQL> 
SQL> select value
  2  from v$mystat a, v$statname b
  3  where a.statistic# = b.statistic#
  4  and b.name = 'opened cursors current'
  5  /

     VALUE                                                                      
----------                                                                      
         3    

When I just declare the cursor variable and open the cursor using it Oracle closes cursor handler implicitly when I leave PL/SQL block. In contrast when I transform REF CURSOR into DBMS_SQL cursor handler Oracle does not care of cursor closure during PL/SQL block leave.
I did not find the explicit mention of this in
Oracle documentation (except with the point what original REF CURSOR variable becomes unusable).
Interesting to note what the reverse operation TO_REFCURSOR seems moves control back to PL/SQL machine:

SQL> select value
  2  from v$mystat a, v$statname b
  3  where a.statistic# = b.statistic#
  4  and b.name = 'opened cursors current'
  5  /

     VALUE                                                                      
----------                                                                      
         2                                                                      

SQL> DECLARE
  2      rc_number INTEGER;
  3      rc1 SYS_REFCURSOR;
  4      rc2 SYS_REFCURSOR;
  5  BEGIN
  6      OPEN rc1 FOR SELECT * FROM DUAL;
  7      rc_number := DBMS_SQL.TO_CURSOR_NUMBER(rc1);
  8      rc2 := DBMS_SQL.TO_REFCURSOR(rc_number);
  9  END;
 10  /

PL/SQL procedure successfully completed.

SQL> select value
  2  from v$mystat a, v$statname b
  3  where a.statistic# = b.statistic#
  4  and b.name = 'opened cursors current'
  5  /

     VALUE                                                                      
----------                                                                      
         2     

P.S. ID 235291.1

About dnikiforov

I'm writing here about everything what is in my interest - Java, Oracle, MySQL and other things. These are just remarks about things I'm working with, and if this helps you, guys, in your work - this makes me happy. ;)
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s