how to define a set of strings in declare section of pl/sql script

Question

In pl/sql i can use in keyword with a set of strings:

select * from languages where language_tag in ('en','fr','es')

how can i define the set of ('en','fr','es') in DECLARE section of script and use it over again?

--edit: A very nasty approach (which is my current approach!) is to define items as csv strings in declare section and use execute_immediate:

DECLARE
  v_csv_tags VARCHAR2(123) :='''en'',''es''';

BEGIN
execute immediate 'delete from config_supports_language where language_code not in ('||v_csv_tags||')';
execute immediate 'delete from languages where language_code not in ('||v_csv_tags||')';

END;
/
EXIT;

Answer 1

You can create a nested table or varray SQL type(as schema object) and then use it in a PL/SQL stored procedure or an anonymous PL/SQL block as follows:

1. SQL type

   create type T_List as table of varchar2(123);
   /
   Type created
   

2. PL/SQ block:

   declare
      l_list T_List3 := T_List3('en','fr','es'); -- the l_list also can be initialized
   begin                                         -- in the BEGIN..END section
      select <<columns list>> 
        into <<variables>>
        from languages 
       where language_tag in (select column_values  -- you can query table(l_list)
                                from table(l_list)) -- as many times as you like
   exception
     when no_data_found
     then dbms_output.put_line('No data is found');
   end;