Using collection list in subquery to restrict rows
531473May 20 2008 — edited May 21 2008How can I use a collection list in a subquery?
Something like this:
TYPE listOfIDs_t IS TABLE OF NUMBER;
v_listOfIDs_arr listOfIDs_t;
CURSOR c1 IS
SELECT *
FROM tbl
WHERE id IN (SELECT column_value
FROM TABLE ( CAST(v_listOfIDs_arr AS listOfIDs_t ))
);
When I try something like this, I get "invalid datatype" on the collection type, listOfIDs_t. Any suggestions? Basically, I want to use the list of values in my collection to restrict my cursor/query without having to loop through my collection and execute the cursor/query each time.