help: append query results to cursor

Giganews Newsgroups
Subject: help: append query results to cursor
Posted by:  doranc…@gmail.com
Date: 29 Aug 2006

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

Replies