My comments in line ... ----------------- Ron Reidy Lead DBA Array BioPharma, Inc.
<snip> On Wed, 20 Apr 2005 12:47:01 -0600, "Reidy, Ron" <[EMAIL PROTECTED]> said: > Michael et. al, > > That is not how ref cursors work. When the Perl variable goes out of > scope, the cursor is not closed. See > http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:553222846752 So now I'm really confused. In this thread (http://www.mail-archive.com/dbi-users@perl.org/msg24109.html) Charles Jardine suggests that when the variable goes out of scope the cursor is closed, but you're saying they stay in existence. [rr] Wjile I can agree that hist assertion may be correct, it is indeed, not correct. You can see this by querying v$open_cursor and v$sesstat while the program is running. In my testing, I created a statement handle to return a ref cursor to 'select object_name from user_objects'. I then fetched one row from this ref cursor and then: a) undefed the vref cursor variable b) called the finish() method on the variable In both cases, the cursor remained open (as seen in v$open_cursor). When I called disconnect() on my database handle, the cursors were closed. I have searched metalink, google, and otn for some reference on this issue and found nothing that indicates there is a way to close a ref cursor object prior to disconnect. This is not just a Perl issue, in case anyone might think in this manner. This is the behavior as manifested in the OCI and will be the behavior in all APIs (Java, .Net, ADO, ODBC, etc.). > In my testing, I can confirm the cursors stay in existence until the > connect handle is disconnected/destroyed. If this is the case, and I do believe you, what do I need to do to close the cursor? If I'm calling a stored proc that returns a cursor every time I run through a loop, and I run through the loop 1500+ times, I run out of cursors, so I need to close the cursor. But none of the methods I've tried for doing that seem to work. [rr] I would look at how you are creating ref cursor objects. Maybe you can reuse these objects, although, I am not sure if this will solve the problem. Do you have a method that works effectively for ensuring that ref cursors get closed? [rr] Sorry, none. Thanks! Mike This electronic message transmission is a PRIVATE communication which contains information which may be confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. Please notify the sender of the delivery error by replying to this message, or notify us by telephone (877-633-2436, ext. 0), and then delete it from your system.
