How can I create and compare a custom list of items in the where clause of multiple queries?

Question

I want to create a custom list of varchar2 items within a pl/sql procedure that I would like to be able to compare in the where clause of multiple queries within that procedure without having to type out each of the items for every query that uses them.

Currently I am trying to use a nested table to define the list of items:

declare
  type t_aa is table of varchar2(12);
  v_aa t_aa := t_aa('bat', 'cat', 'dog', 'turtle', 'monkey');
begin
  for r_loop in (select animal_name
                   from animals
                  where animal_name in v_aa)
  loop   
    dbms_output.put_line(r_loop.animal_name);
  end loop;

  for r_loop in (select pet_name
                   from pets
                  where pet_type in v_aa)
  loop   
    dbms_output.put_line(r_loop.pet_name);
  end loop;
end;

Unfortunately it seems that you can't use nested tables in this way in queries. Can anyone suggest a way of achieving what I'm after?

Answer 1

Collections defined in the PL/SQL scope can be used in PL/SQL statements but not in SQL statements. If you try then you get the errors:

ORA-06550: line 7, column 40:
PLS-00642: local collection types not allowed in SQL statements
ORA-06550: line 14, column 37:
PLS-00642: local collection types not allowed in SQL statements

However, if you create a collection in the SQL scope:

CREATE TYPE string_list IS TABLE OF VARCHAR2(12);

Then you can use it in SQL statements:

declare
  v_aa string_list := string_list('bat', 'cat', 'dog', 'turtle', 'monkey');
begin
  for r_loop in (
    select animal_name
    from   animals
    where  animal_name MEMBER OF v_aa
  )
  loop   
    dbms_output.put_line(r_loop.animal_name);
  end loop;

  for r_loop in (
    select pet_name
    from   pets
    where  pet_type IN (SELECT COLUMN_VALUE FROM TABLE(v_aa))
  )
  loop   
    dbms_output.put_line(r_loop.pet_name);
  end loop;
end;
/

Which, for the sample data:

CREATE TABLE animals (animal_name) AS
SELECT 'aardvark' FROM DUAL UNION ALL
SELECT 'bat' FROM DUAL UNION ALL
SELECT 'cat' FROM DUAL UNION ALL
SELECT 'dog' FROM DUAL UNION ALL
SELECT 'turtle' FROM DUAL UNION ALL
SELECT 'monkey' FROM DUAL;

CREATE TABLE pets (pet_name, pet_type) AS
SELECT 'alice', 'aardvark' FROM DUAL UNION ALL
SELECT 'betty', 'bat' FROM DUAL UNION ALL
SELECT 'carol', 'monkey' FROM DUAL;

bat
cat
dog
turtle
monkey
betty
carol

fiddle