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


in pl/sql can use in keyword set of strings:

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

how can define set of ('en','fr','es') in declare section of script , use on again?

--edit: nasty approach (which current approach!) define items csv strings in declare section , use execute_immediate:

declare   v_csv_tags varchar2(123) :='''en'',''es''';  begin execute immediate 'delete config_supports_language language_code not in ('||v_csv_tags||')'; execute immediate 'delete languages language_code not in ('||v_csv_tags||')';  end; / exit; 

you can create nested table or varray sql type(as schema object) , use in pl/sql stored procedure or anonymous pl/sql block follows:

  1. sql type

    create type t_list table of varchar2(123); / type created 
  2. pl/sq block:

    declare    l_list t_list3 := t_list3('en','fr','es'); -- l_list can initialized begin                                         -- in begin..end section    select <<columns list>>       <<variables>>      languages      language_tag in (select column_values  -- can query table(l_list)                              table(l_list)) -- many times exception   when no_data_found   dbms_output.put_line('no data found'); end; 

Comments

Popular posts from this blog

ios - UICollectionView Self Sizing Cells with Auto Layout -

node.js - ldapjs - write after end error -

DOM Manipulation in Wordpress (and elsewhere) using php -