open/close cursor question
- From: Guang Mei <GMei@xxxxxx>
- To: "Oracle-L (E-mail)" <Oracle-L@xxxxxxxxxxxxx>
- Date: Wed, 16 Mar 2005 11:20:54 -0500
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(*)
----------
1
-- 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.
*************************************************************************
--
//www.freelists.org/webpage/oracle-l
Other related posts:
