Re: Too Many Cursors?

Helge Moulding schrieb:
> 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
>
Helge,

As I understand, you are looking for the greatest/last row - concernig val4 - of table b_table.
[Quoted] What about using a group function (ORACLE Optimzier does a good job usually).

• assuming, that val4 makes rows unique whithin a_key select b.val2, b.val3 from b_table b where (b.a_key, b.val4) in ( select b2.a_key, max( b2.val4) from b_table b2 where b2.a_key = in_a_key group by b2.a_key)

Increasing parameter open_cursor should not be necassary in most cases, often it points to a programming error, e.g. cursors are not closed after using, or a statement is not recognized as identical by the optimizer (this seems to be the problem in your example - I use toad to inspect open cursors for a session, of course there are Dictionary tables too).

Hope that helps,

Bernhard Received on Tue Oct 04 2005 - 18:37:00 CEST