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:
sql type
create type t_list table of varchar2(123); / type created
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
Post a Comment