Write Oracle Procedure To Accept List of Items used in Select

Question

There have been a couple of hints that seem to have gotten me close here, but with some unique issues, I'm hoping this question is distinguishing enough to merit its own posting.

For starters here's what I have. I have an Oracle procedure that returns a standard REF CURSOR, and this REF CURSOR is passed back to my application. The REF CURSOR is a list of lookup IDs.

I then want to take this list and bring it to another data store and use it in a select statement. It will absolutely be possible to accomplish this by looping through the REF CURSOR, but I'm hoping to avoid that. I would much rather be able to write a SELECT...WHERE lookup_id IN result_ref_cursor OR SELECT...WHERE EXISTS...

First is this possible or should I just try a less than elegant solution? If it is possible, any hints as to where I should get started looking?

I'm relatively new to Oracle, but fairly experienced in RDBMs in general, so feel free to just through some links at me and I can study up. Much appreciated

Answer 1

Why kurosch didn't put his response as an "answer" I'll have no idea.

So, what you do is define a SQL type which describes one row of the output of the ref cursor, and also a SQL type which is a table of the previous. Then, you'll create a pipelined function which returns the rows returned by the ref cursor. This function can then be used in a standard SQL. I'm borrowing from Ask Tom on this one.

create or replace type myLookupId as object ( id int)
/

create or replace type myLookupIdTable as table of myLookupId
/

create or replace function f return myLookupIdTable PIPELINED is
  l_data myLookupId;
  l_id number;
  p_cursor SYS_REFCURSOR;
begin
  p_cursor := function_returning_ref_cursor();
  loop
    fetch p_cursor into l_id;
    exit when p_cursor%notfound;
    l_data := myLookupId( l_id );
    pipe row (l_data);
  end loop;
 return;
end;
/

And now a sample query...

SELECT  * 
FROM    SOME_TABLE
WHERE   lookup_id in (SELECT ID FROM table(f));

Sorry if the code isn't exactly right, I don't have the DB to test right now.