Re: Oracle Functions with multiple selects writing to a cursor

Mike wrote:

> I'm using a package with multiple functions that use cursors. Each
> function has multiple selects that i expect to create a recordset
> (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_1;
>
> 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

If you are doing what you appear to be doing it looks like the cursors are serving no useful purpose other than to consume resources.

A count is a single integer. Return the integer and be done with it.

DECLARE i PLS_INTEGER;

BEGIN
    SELECT COUNT(*)
    INTO i
    FROM ....;     RETURN i;
END;
/

And if I misunderstand what you are doing please explain why you think you need a REF CURSOR to return a single value.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)