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;

How to convert a comma string to an insert string

Leave a Reply

Your e-mail address will not be published. Required fields are marked *

25 − = nineteen