Using ref cursor in where clause
403082Sep 5 2003 — edited Sep 10 2003I have a stored procedure (package procedure) that returns a ref cursor as a parameter. This procedure performs a query and returns the cursor. The procedure calls another stored procedure within the package. This second procedure also returns a ref cursor. I want to do my query on the results of the cursor returned by the second procedure. Hopefully the example below is okay.. "I had to change names to protect the blah blah...."
For example:
TYPE out_cursor IS REF CURSOR;
TYPE IDTable IS TABLE OF table1.ID%TYPE;
PROCEDURE A
(
v_ID IN NUMBER,
v_Param2 IN NUMBER,
v_FromClause IN VARCHAR2, -- ignore for now
v_WhereClause IN VARCHAR2, -- ignore for now
v_retval OUT NUMBER,
the_cursor OUT out_cursor
)
IS
v_SQLString VARCHAR2(2048);
v_cursor1 out_cursor;
v_tabIDs IDTable;
BEGIN
v_SQLString := '';
packageA.procedureA(v_ID, v_retval, v_cursor1);
-- this does not work
-- I get ORA-00942: table or view does not exist
v_SQLString := 'select distinct * from v_cursor1';
-- I found this on the message board but
-- it did not compile.
-- local collection types not allowed in SQL statements
FETCH v_cTSFiles BULK COLLECT INTO v_tabIDs;
v_SQLString := 'select distinct * from table1 WHERE table1.ID in (select * from table(v_tabIDs))';
OPEN the_cursor FOR v_SQLString;
END;