How do I check for a IN condition against a dynamic list in Oracle?

Question

EDIT: changed the title to fit the code below.

I'm trying to retrieve a list of acceptable values from an Oracle table, then performing a SELECT against another while comparing some fields against said list.

I was trying to do this with cursors (like below), but this fails.

DECLARE
    TYPE gcur IS REF CURSOR;
    TYPE list_record IS TABLE OF my_table.my_field%TYPE;
    c_GENERIC gcur;
    c_LIST list_record;
BEGIN
    OPEN c_GENERIC FOR
    SELECT my_field FROM my_table
    WHERE some_field = some_value;

    FETCH c_GENERIC BULK COLLECT INTO c_LIST;

    -- try to check against list
    SELECT * FROM some_other_table
    WHERE some_critical_field IN c_LIST;

END

Basically, what I'm trying to do is to cache the acceptable values list into a variable, because I will be checking against it repeatedly later.

How do you perform this in Oracle?

Answer 1

Why pull the list instead of using a semi-join?

SELECT * 
  FROM some_other_table 
 WHERE some_critical_field IN (SELECT my_field 
                                FROM my_table
                               WHERE some_field = some_value); 

Answer 2

We can use collections to store values to suit your purposes, but they need to be declared as SQL types:

create type list_record is table of varchar2(128)
/

This is because we cannot use PL/SQL types in SQL statements. Alas this means we cannot use %TYPE or %ROWTYPE, because they are PL/SQL keywords.

Your procedure would then look like this:

DECLARE
    c_LIST list_record;
BEGIN

    SELECT my_field 
    BULK COLLECT INTO c_LIST 
    FROM my_table
    WHERE some_field = some_value;

    -- try to check against list
    SELECT * FROM some_other_table
    WHERE some_critical_field IN ( select * from table (c_LIST);

END;    

---

"I see that you still had to perform a SELECT statement to populate the list for the IN clause."

If the values are in a table there is no other way to get them into a variable :)

"I'm thinking that there's a significant performance gain using this over a direct semi-join"

Not necessarily. If you're only using the values once then the sub-query is certainly the better approach. But as you want to use the same values in a number of discrete queries then populating a collection is the more efficient approach.

In 11g Enterprise Edition we have the option to use result set caching. This is a much better solution, but one which is not suited for all tables.