Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle Functions with multiple selects writing to a cursor
In article <20a78044.0312041104.2668c373_at_posting.google.com>,
mgm_at_dca.net says...
> I'm using a package with mutiple functions that use cursors. Each
> function has multiple selects that i expect to create a recordet
> (item1, item2...).
> Sql as follows.
>
> CREATE OR REPLACE PACKAGE BODY x_pkg AS
> FUNCTION GET_1 (dSTARTDATE IN DATE,dENDDATE IN DATE) RETURN REF_CURSOR
> IS
> my_cur ref_cursor;
> BEGIN
> OPEN my_cur FOR
> SELECT COUNT(*) FROM ..........;
>
> OPEN v_cur FOR
> SELECT COUNT(*) FROM .......;
>
> RETURN v_cur;
> END GET_NSYSUM_GFM;
>
> my_cur contains only the results of the last select. Is it possible to
> return this recordset( 1 row with multiple columns) from Oracle
> function or do I need something else?
>
> Thanks a lot for any help!
> mgm_at_dca.net
>
Just use one cursor and go right for the answer:
open v_cur for
select
(select count(*) from Table1) c1, (select count(*) from Table2) c2