RE: open/close cursor question
- From: Guang Mei <GMei@xxxxxx>
- To: "'Christian.Antognini@xxxxxxxxxxxx'" <Christian.Antognini@xxxxxxxxxxxx>
- Date: Thu, 17 Mar 2005 09:11:26 -0500
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.
1. 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?
2. 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(*)
----------
1
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@xxxxxxxxxxxx]
Sent: Thursday, March 17, 2005 1:52 AM
To: GMei@xxxxxx
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
--
//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.
*************************************************************************
--
//www.freelists.org/webpage/oracle-l
Other related posts:
