Too Many Cursors?

From: Helge Moulding <hmoulding_at_gmail.com>
Date: 3 Oct 2005 16:32:02 -0700
Message-ID: <1128382322.770371.138660@g44g2000cwa.googlegroups.com>

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

Received on Mon Oct 03 2005 - 18:32:02 CDT