Re: help: append query results to cursor

dorancat_at_gmail.com wrote:
> 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

Look at this for a suggestion:

http://asktom.oracle.com/pls/ask/f?p=4950:8:2782851770239135314::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:246014735810 Received on Wed Aug 30 2006 - 08:20:56 CDT