Still returns a result that includes 'cached cursors' that has been closed. SP2-0625: Error printing variable "myref" Now, let us close the cursor explicitly :
Refer the code below, where we would get the error, if we close the cursor explicitly) So, when you print the value of cursor variable, you should'nt get anything (infact you should get an error, because the handle of cursor that was set in the cursor variable, has been closed. What does this prove to you ? Was the cursor closed after the RETURN happened ? :)) Even if the cursor variable was available at the SQL*Plus prompt, the cursor should have been closed (as per your theory). Open_cursors integer select value-(select max(value) from v$sesstat where STATISTIC#='3')Ģ from v$parameter where alter system set open_cursors = 5 Ģ from v$parameter where set serveroutput on size 1000000Ģ TYPE emp_cur_t is REF CURSOR return SCOTT.EMP%ROWTYPE ħ open v_cur for select * from scott.emp where rownum print myrefħ499 ALLEN SALESMAN 7698 20-FEB-81 1600 300ħ521 WARD SALESMAN 7698 22-FEB-81 1250 500ħ654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400ħ844 TURNER SALESMAN 7698 08-SEP-81 1500 0 Opened cursors current exec open :z for select * from dual Īny chance someone did an alter select STATISTIC#, name from v$sysstat where NAME='opened cursors current' ģ opened cursors select max(VALUE) from v$sesstat where show parameter open_cursors Opened cursors current exec open :y for select * from dual Opened cursors current variable x variable y variable z exec open :x for select * from dual If you do this on a "single user" system - you'll see much more predicable responses. Both answers are *right* at their respective points in time. V$sesstat is queried at a different point in time then v$open_cursor. Now, since V$ views are *not* read consistent - they are 100% point in time pictures - at the point in time you look at them - you'll see this.Įven if you try a single query like select sum(a.value), b.nameģ where a.statistic# = b.statistic# and b.name = 'opened cursors current'Ħ select count(*), 'v$open_cursor' from v$open_cursor All you need is one session to exit between calls and bamm - there go hundreds of cursors potentially. thanks for checking back.īut anyway, on a busy system - one would expect there to be differences - cursors come and cursors go. You are looking across sessions.Ībove) thats what I get for firing off an answer too fast. Never mind, restart.(geez, i even said myself:Ģ) select sid, count(*) from v$open_cursor group by sid