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