open/close cursor question

From: Guang Mei <GMei_at_ph.com>
Date: Wed, 16 Mar 2005 11:20:54 -0500
Message-ID: <7E62D965D357694C993D4F0E13B3C39B04D37F7D@phexchange.ph.com>

I have a question about the number of open cursors. I did a quick test and I am the only one that connected to the instance. The output message from my pl/sql program confuses me. Please see my test result and question below:

SQL> select version from v$instance;

VERSION

9.2.0.1.0

I am the only user:

SQL> select substr(sid,1,3) sid,substr(SERIAL#,1,6) SERIAL#, 2 substr(username,1,12)username, substr(osuser,1,10) osuser, 3 substr(program,1,20),status from v$session;

SID SERIAL USERNAME OSUSER SUBSTR(PROGRAM,1,20) STATUS

--- ------ ------------ ---------- -------------------- --------
1   1                   SYSTEM     ORACLE.EXE           ACTIVE
2   1                   SYSTEM     ORACLE.EXE           ACTIVE
3   1                   SYSTEM     ORACLE.EXE           ACTIVE
4   1                   SYSTEM     ORACLE.EXE           ACTIVE
5   1                   SYSTEM     ORACLE.EXE           ACTIVE
6   1                   SYSTEM     ORACLE.EXE           ACTIVE
7   1                   SYSTEM     ORACLE.EXE           ACTIVE
8   1                   SYSTEM     ORACLE.EXE           ACTIVE
9   126    DEV72UPD     KANSAS\gua sqlplusw.exe         ACTIVE

9 rows selected.

SQL> select count(*) from customers;

COUNT(*)

157

1 open cursor before the pl/sql program test:

SQL> select count(*) from v$open_cursor where sid=9;

COUNT(*)

run the same code twice, with open and close cursor statements:

SQL> declare

  2      sqlstmt              VARCHAR2(32000);
  3      TYPE refCur IS REF CURSOR;
  4      outmsg refCur;
  5      CNT                  NUMBER :=0;
  6      r_rid                  number;
  7      cnt_cursor        NUMBER :=0;
  8  begin
  9      sqlstmt := ' SELECT RID from customers';
 10      OPEN outmsg FOR sqlstmt;
 11      LOOP
 12          FETCH outmsg INTO r_rid;
 13          EXIT WHEN outmsg%NOTFOUND;
 14          cnt := cnt +1;
 15      END LOOP;    
 16      select count(*) into cnt_cursor from v$open_cursor where sid=9;
 17      dbms_output.put_line('1st time, before close cursor total number of

open cursors =' || cnt_
cursor );

 18      CLOSE outmsg ;
 19      dbms_output.put_line('1st time, after close cursor total number of

open cursors =' || cnt_c
ursor );
20 dbms_output.put_line('1st time, total number of customers =' || cnt );

 21      --- same code run again:
 22     cnt :=0;
 23     OPEN outmsg FOR sqlstmt;
 24      LOOP
 25          FETCH outmsg INTO r_rid;
 26          EXIT WHEN outmsg%NOTFOUND;
 27          cnt := cnt +1;
 28      END LOOP;
 29      select count(*) into cnt_cursor from v$open_cursor where sid=9;
 30      dbms_output.put_line('2nd time, before close cursor total number of

open cursors =' || cnt_
cursor );

 31      CLOSE outmsg ;
 32      dbms_output.put_line('2nd time, after close cursor total number of

open cursors =' || cnt_c
ursor );
33 dbms_output.put_line('2nd time, total number of customers =' || cnt );
34 end;
35 /

1st time, before close cursor total number of open cursors =3
1st time, after close cursor total number of open cursors =3
1st time, total number of customers =157
2nd time, before close cursor total number of open cursors =4
2nd time, after close cursor total number of open cursors =4
2nd time, total number of customers =157

PL/SQL procedure successfully completed.

I thought I should have got the output like this:

1st time, before close cursor total number of open cursors =2
1st time, after close cursor total number of open cursors =1
1st time, total number of customers =157
2nd time, before close cursor total number of open cursors =2
2nd time, after close cursor total number of open cursors =1
2nd time, total number of customers =157

I guess somehow my understanding is wrong?

Guang

PRIVILEGED AND CONFIDENTIAL:
This communication, including attachments, is for the exclusive use of addressee and may contain proprietary, confidential and/or privileged information. If you are not the intended recipient, any use, copying, disclosure, dissemination or distribution is strictly prohibited. If you are not the intended recipient, please notify the sender immediately by return e-mail, delete this communication and destroy all copies.

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Mar 16 2005 - 11:24:38 CST