Re: Too Many Cursors?

Helge Moulding пишет:
> I originally wrote this query to just run off the tables directly:
>
> select a.val1
> ,b.val2
> ,b.val3
> from a_table a
> join b_table b on a.key = b.a_key
> where a.val2 = parameter
> and 0 = (
> select count(*)
> from b_table b2
> where b.a_key = b2.a_key
> and b2.val4 > b.val4 )
>
> The trouble was that b_table is much much larger than a_table (and
> the join is actually more complex and involves another table). It
> ran much too slowly, so I thought to rewrite the entire mess as
> follows:
>
> create or replace function get_b_vals( in_a_key varchar2)
> return refcur
> is
> rc refcur;
> begin
> open rc for
> select b.val2
> ,b.val3
> from b_table b
> where b.a_key = in_a_key
> and 0 = (
> select count(*)
> from b_key b2
> where b2.a_key = in_a_key
> and b2.val4 > b.val4 );
> return rc;
> end;
> /
> select a.val1
> ,get_b_vals(a.key)
> from a_table a
> where a.val2 = parameter;
> /
>
> It seemed to compile fine, but when I ran it I got a "too many cursors
> open" error. Why is that? Can I safely close the cursor before
> returning,
> even though I'm returning the cursor?
> --
> Helge Moulding
> hmoulding at gmail dot com Just another guy
> http://hmoulding.cjb.net/ with a weird name
>
May be you can increase OPEN_CURSORS parameter in your instance?

OPEN_CURSORS specifies the maximum number of open cursors (handles to private

SQL areas) a session can have at once. You can use this parameter to prevent
a

session from opening an excessive number of cursors. This parameter also

constrains the size of the PL/SQL cursor cache which PL/SQL uses to avoid having

to reparse as statements are reexecuted by a user.

It is important to set the value of OPEN_CURSORS high enough to prevent your

application from running out of open cursors. The number will vary from one

application to another. Assuming that a session does not open the number of

cursors specified by OPEN_CURSORS, there is no added overhead to setting this

value higher than actually needed.

Parameter type Integer Received on Tue Oct 04 2005 - 16:26:14 CEST