This function will split an input string separated by commas into a string enclosed with single quotes. This is helful in applying conditions in dynamic SQL. 

dyn_string1 Function: 


     create or replace function dyn_string1(ab varchar2)return varchar2 as

     t varchar2(2):=',';

     t1 varchar2(2):='''';

     t2 varchar2(32767);

     t_check number(10);

     a varchar2(32767);

     begin

     a:=ab;

     -- dbms_output.put_line('The string passed is '||a);

     t_check:=instr(a,',');

     -- dbms_output.put_line('The instr value is '||t_check);

     if t_check!=0 then

     t2:=t1||t||t1;

     a:=replace(a,',',t2);

     a:='('||''''||a||''''||')';

     -- dbms_output.put_line(a);

     else

     a:=a;

     a:='('||''''||a||''''||')';

     end if;

     return a;

     END ;


Example: 



     select dyn_string1('2334,3! 45,5656,6767,7878,78989,8989,8989,9090,9090,90,90,9090,90,090,,9090,909) from dual


Output: 



     DYN_STRING1('2334,345,5656,6767,7878,78989,8989,8989,9090,9090,90,90,9090,90,090,9090,909)

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

     ('2334','345','5656','6767','7878','78989','8989','8989','9090','9090','90','90','9090','90','090','','9090','909')


To use this dyn_string1 function along with dynamic SQL: 


Create or replace PROCEDURE test(actcode IN VARCHAR2,fr_date IN DATE,to_date IN DATE,)

     AS

     B date;

     C date;



     actcode VARCHAR2(32767);



     sql_stmt varchar2(32767);



     Begin



     a:=dyn_string1(actcode);

     b:=fr_date;

     c:=to_date;


     sql_stmt:='INSERT INTO Table2 select A1.NYCWAY_CASE_NUMBER 

     FROM table1 a 

     WHERE A.column1 in '||a||' and

     A.column2 between '||''''||b||''''||' AND '||''''||c||''''||' ;


     execute immediate sql_stmt ;

     end;