Too Many Cursors?
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 nameReceived on Tue Oct 04 2005 - 01:30:00 CEST