Too Many Cursors?

From: Helge Moulding <hmoulding_at_gmail.com>
Date: 3 Oct 2005 16:30:00 -0700
Message-ID: <1128382200.440481.132820_at_f14g2000cwb.googlegroups.com>



[Quoted] [Quoted] 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:

[Quoted] 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
[Quoted]   hmoulding at gmail dot com                 Just another guy
  http://hmoulding.cjb.net/                  with a weird name
Received on Tue Oct 04 2005 - 01:30:00 CEST

Original text of this message