Re: Too Many Cursors?

Helge Moulding wrote:
[snip]
> 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
>
For every a.val2, you call your cursor.
And no - you'll have to keep the cursor open until the results are displayed. Nothing else to do than up your open_cursors parameter (or recode...)

In general, you can query (using %ISOPEN) whether you already opened this cursor. If so, this session has already used this cursor - generally speaking the results have been displayed, and you can now safely close the cursor, and open for a new query.

-- 
Regards,
Frank van Bortel

Top-posting is one way to shut me up...