RE: open/close cursor question

From: Guang Mei <GMei_at_ph.com>
Date: Thu, 17 Mar 2005 09:11:26 -0500
Message-ID: <7E62D965D357694C993D4F0E13B3C39B04D37F85@phexchange.ph.com>

Hi Chris,

Thanks for your response. It was my mistake not to call "select count(*) ..." after close cursor. Now I added that and I am still puzzled by the result.

Why did I see the open_cursor jump from 1 to 3 when I just opened one cursor, then to 5 when the second cursor opened?
Why did I get the same count before and after close the cursor?

Thanks.

Guang

new test result:

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

COUNT(*)

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      select count(*) into cnt_cursor from v$open_cursor where sid=9;
 20      dbms_output.put_line('1st time, after close cursor total number of

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

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

open cursors =' || cnt_
cursor );

 32      CLOSE outmsg ;
 33      select count(*) into cnt_cursor from v$open_cursor where sid=9;
 34      dbms_output.put_line('2nd time, after close cursor total number of

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

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 =5
2nd time, after close cursor total number of open cursors =5
2nd time, total number of customers =157

PL/SQL procedure successfully completed.

-----Original Message-----
From: Christian Antognini [mailto:Christian.Antognini_at_trivadis.com] Sent: Thursday, March 17, 2005 1:52 AM
To: GMei_at_ph.com
Cc: Oracle-L (E-mail)
Subject: RE: open/close cursor question

Hi Guang

> 16 select count(*) into cnt_cursor from v$open_cursor where =
sid=3D9;
> 17 dbms_output.put_line('1st time, before close cursor total =
number
> of open cursors =3D' || cnt_cursor );
> 18 CLOSE outmsg ;
> 19 dbms_output.put_line('1st time, after close cursor total =
number=20
> of open cursors =3D' || cnt_cursor );

The variable cnt_cursor cannot change between line 17 and 19. So, why do = you expect different values?

HTH
Chris

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

************************************************************************* 
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 Thu Mar 17 2005 - 09:15:25 CST