sql - Using "WHERE" with a variable that has multiple values -

i want know how select columns table column has several possible values. so, example, code return column x table 1 , column b table 2 if column g has value given input.

variable input varchar2(60); exec :input := 'c';  select table1.x, table2.b   table1,         table2  table1.g = :input 

what if instead, wanted return column x table1 , columnb table2 if columng had value "k" or "c" or "d" without doing

where table1.g = "k" or table1.g = "c" or table1.g = "d" 

how define input such effect?

i use: oracle 10g, pl/sql

as understood comment @superman answer

yes, there way define variable placeholder ('c', 'd', 'k')?

you want parametrize in clause.

obviously cannot pass :input variable in in clause defined exec :input := 'k,c,d'. mean, technically can, query wont produce desired output because search entire 'k,c,d' string.

what can is, can split 'k,c,d'(or other) string comma , use result in in clause.

here example:

-- variable of type contain letters produced  -- splitting 'k,c,d' string create or replace type t_vars table of varchar2(5)  -- table function returns collection of individual letters create or replace function getletters(p_input in varchar2) return t_vars   l_res t_vars; begin   select cast(               collect(                        cast(res varchar2(5))                       ) t_vars              )     l_res     ( select regexp_substr(p_input, '[^,]+', 1, level) res              dual           connect level <= regexp_count(p_input, '[^,]+' )           );    return l_res; end;   

here how works:

sql> variable x varchar2(11);  sql> exec :x := 'k,c,d';  pl/sql procedure completed  sql> select col   2    table1   3   col in (select column_value table(getletters(:x)))   4  ; 

result:

 col  ---  k  c  d