Solution 1: Use oracle Regex

select t1.field_01,t1.field_02

from t_XXX t1

where Exists

(

select 1 from

(

SELECT TRIM(REGEXP_SUBSTR (:v_id_list, '[^,]+', 1,rownum)) as ID

FROM DUAL

CONNECT BY ROWNUM <= LENGTH (:v_id_list) - LENGTH (REPLACE (:v_id_list, ',','')) + 1

)  tmp

where t1.ID= tmp.ID

);

------------------------------------------------------------------------------------------------------------

Solution 2: Use record type

Step1: create record type:

create or replace typemyTableType as table of varchar2 (32767);

----------------------------------------------

Step2:Create function that it convert string to datatable.

Create and replace function var_list(p_string in varchar2 ) return myTableType

as

l_string       long default p_string || ',';

l_data         myTableType := myTableType();

n              number;

begin

loop

exit whenl_string is null;

n := instr(l_string, ',' );

l_data.extend;

l_data(l_data.count) :=

ltrim( rtrim( substr( l_string, 1, n-1 ) ) );

l_string :=substr( l_string, n+1 );

end loop;

return l_data;

end;

----------------------------------------------

Step3: test example

select *

from THE

(

select cast( var_list('abc, xyz, 012') as

mytableType )from dual

) a

------------------------------------------------------

Step4: I Use in code, sql format

Note: :v_id_list’svalue is like ：value_1,value_2,..,value_n. and n<=1000

select field_xxx01, t1.field_xxx02

from t_xxxx t1

where Exists

(

select 1 from THE

(

select cast( var_list(:v_id_list) as  --select cast( in_list('abc, xyz, 012') as

mytableType ) from dual

)  t2

where t1.field_ID = t2.COLUMN_VALUE

);