On Thu, 21 Apr 2005 18:12:08 +0100, "Charles Jardine" <[EMAIL PROTECTED]>
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.
So it looks like the problem is indeed that a reference is being held
open somewhere.
> Yes. I have taken a level 2 trace of the above program (after
> modifying it to do two iterations rather than 1500). This trace is
> included below with my annotations - lines starting with ' [cj]'.
Thank you for that -- your annotations are very helpful in identifying
where the cursors are being created and destroyed. I wasn't sure how to
identify those points in the trace output.
> It shows that the first cursor handle is not DESTROYed when the first
> instance of $cursor goes out of scope at the end of the loop. The
> destructor is actually called from within bind_param_input as the
> binding of the second $cursor causes the reference count of the first
> $cursor to drop to zero. Similarly the second cursor handle is not
> DESTROYed until the pl/sql handle is destroyed.
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;
2) the cursor handle doesn't get destroyed at the end of the calling
loop because there is still an open reference to it;
3) and the cursor handle also doesn't get destroyed because there is
never another operation called on the statement handle that was
used to create it, since a new statement handle is created each
time through the loop. In your code the first cursor handle only
gets destroyed when the second cursor handle is created, using the
same statement handle that created the first cursor.
Which would suggest that one solution would be to deal with the cursor
handle, i.e. retrieve the data from it, within the _do_proc method, and
allow both handles to go out of scope at the end of the method without
being passed back to the loop.
Does this sound like a reasonable explanation of how the reference might
be held open? Although this sounds plausible, I don't know enough about
the DBI and DBD::Oracle internals to know whether it is actually the
case. (1) sounds slightly suspect to me, which calls into question (2)
as well. But it seems that (3) could plausibly be true without either
(1) or (2) being true.
If this, or something like this, is correct, then my question becomes
one of memory management -- does creating a cursor handle retrieve all
the data from the database for that cursor and just provide a convenient
interface to it, or do subsequent calls to (eg) fetchrow_hashref() on
the cursor handle actually retrieve the next row from the database? If
the former, I've already retrieved the data so I might as well just get
it all out of the cursor; if the latter, obviously, I'll have to deal
with holding all the retrieved data in memory and passing it around.
Thanks again for all the assistance I've received from the list!
Mike
