|Subject:||help: append query results to cursor|
|Date:||29 Aug 2006|
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
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;
p_Codes IN varchar2_array_t,
p_Numbers IN varchar2_array_t,
p_OutRefCursor OUT ref_cursor_type)
idx NUMBER := 0;
FOR idx IN 1 .. p_Codes.COUNT
OPEN refCursor FOR
code = p_Codes(idx)
nbr = p_Numbers(idx)
p_OutRefCursor := refCursor;
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
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.