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;