help: append query results to cursor

From: <dorancat_at_gmail.com>
Date: 29 Aug 2006 12:57:54 -0700
Message-ID: <1156881474.114427.95530@74g2000cwt.googlegroups.com>

Hello,

I am very new to Oracle/PL/SQL programming and am looking for some help on a problem I am trying to solve...

I would like to loop through a bunch of values I supply to a procedure,

run a select for each loop through and append these results to a cursor that is an out parm (to be sent back to a .NET app if it matters).

Anyways, here's what I got...
(some names have been changed to protect the innocent)

• Do some type declarations TYPE varchar2_array_t is table of VARCHAR(20); TYPE ref_cursor_type IS REF CURSOR;

PROCEDURE GetAssignments(

    p_Codes        IN    varchar2_array_t,
    p_Numbers      IN    varchar2_array_t,
    p_OutRefCursor OUT   ref_cursor_type)
IS
    refCursor             ref_cursor_type;
    idx                    NUMBER := 0;

BEGIN
    FOR idx IN 1 .. p_Codes.COUNT
    LOOP

        OPEN refCursor FOR
            select
                id,
                code,
                nbr
            from
                assignments
            where
                code = p_Codes(idx)
            and
                nbr = p_Numbers(idx)

    END LOOP;     p_OutRefCursor := refCursor;

END GetAssignments;

My hope was that I could loop through the codes/numbers given, each select should return one id based on code/number and then I would append it to my cursor. At the end, I return my cursor and badda-bing! I got me a result set of all my ids/codes/numbers.

I know that I could probably do some dynamic sql to build a single query to return all rows, but I thought I would see if this route produced any results (almost just for the sake of curiosity).

Any suggestions or helpful nudges in the right direction would be appreciated.

Thanks,

Doran Received on Tue Aug 29 2006 - 14:57:54 CDT