CODEWITHSUNDEEP
oracle-databaseplsql
AllOutOfSalt
AllOutOfSalt

Reputation: 1492

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

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;

Upvotes: 0

Views: 236

Answers (1)

Nick Krasnov
Nick Krasnov

Reputation: 27251

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;

Upvotes: 1

Related Questions