Re: More on closing Oracle ref cursors

Michael Styer wrote: 
On Thu, 21 Apr 2005 18:12:08 +0100, "Charles Jardine" <cj10@xxxxxxxxx>
said:

I am sure that Oracle can close the cursors automatically in Oracle
9.2..6. I would be very grateful if you could find out for me if the
   same is true in your Oracle 8i database.

Could you please run the following simplified test program. It doesn't
run out of cursors in my setup. Does it in yours?


No, it runs all the way through without a problem. 

Thank you very much indeed. That is a great help.

So it looks like the problem is indeed that a reference is being held
open somewhere.


Yup.

[snip]

In my code, I create a new statement handle ($sth) each time through the
loop in the _do_proc method. This variable goes out of scope at the end
of the method, so presumably is destroyed. However, in the _do_proc
method I also create a cursor handle ($out) using bind_param_inout that
is returned from the method. This handle is passed into a scalar
variable in the calling loop, and this variable then goes out of scope
at the end of the loop.

It appears that something is holding open a reference to the
cursor handle, but I can't see what would be doing that. One guess
would be that:
  * IF bind_param_inout creates a reference from the pl/sql statement
    handle ($sth in my code) to the cursor handle ($out),
  * THEN because I've passed the cursor handle reference back to the
    calling loop at the end of the code:
  1) the pl/sql statement handle doesn't get destroyed at the end of the
     method because it has a reference open to the cursor handle;


No. That's backwards. It is being refrered to that stops an object
being destroyed. Referring does not. With code as in your original posting,
the $sth handle will be destroyed as _do_proc returns, leaving the returned
value as the only remaining reference to the cursir handle.

  2) the cursor handle doesn't get destroyed at the end of the calling
     loop because there is still an open reference to it;


You original posting does not include the loop, so I can't comment on
this.

[snip]

I am going to have to drop out of this discussion now - the
day job looms.

P.S. The error messages included in your original posting show that
your DBD::Oracle was compiled to use the obsolete Oracle 7 version
of  OCI. If you havn't already done so, I suggest you recompile to
use the Oracle 8 OCI.


--
Charles Jardine - Computing Service, University of Cambridge
cj10@xxxxxxxxx    Tel: +44 1223 334506, Fax: +44 1223 334679

.

Relevant Pages

  • Re: whats a "reasonable" length of time to keep a cursor open?
    ... an image processing work dispatcher that runs continuously. ... loop forever: ... for each row in the cursor: ... Oracle would return the previously mentioned ...
    (comp.databases.oracle.misc)
  • Re: More on closing Oracle ref cursors
    ... So it looks like the problem is indeed that a reference is being held ... > instance of $cursor goes out of scope at the end of the loop. ... one of memory management -- does creating a cursor handle retrieve all ...
    (perl.dbi.users)
  • Re: Learning Cursors
    ... A cursor may be necessary when you need to process each row in a result ... be complex and not easily solved within a single query. ... Oracle 10g ... Many times all I do with a cursor is to loop the ...
    (comp.databases.oracle.server)
  • Re: cursor for loop
    ... karthika Jegatheesan via SQLMonster.com wrote: ... > for emp_rec IN c loop ... > Similar to cursor for loop in oracle is there any corresponding for ... Look up Declare Cursor in the docs. ...
    (microsoft.public.sqlserver.programming)
  • Using BULK BIND.....
    ... Have a program which is currently processing a cursor and doing various ... bits and bobs with the data. ... each iteration of the loop. ... Using Oracle 9.2 and will be several thousand rows. ...
    (comp.databases.oracle.server)