create or replace type mytabletype as table of varchar2; /create or replace function strtab(p_str in varchar2) return mytabletype as lstr varchar2(1000) default p_str||','; ln number; ldata mytabletype:=mytabletype(); begin loop ln:=instr(lstr,','); exit when (nvl(ln,0)=0); ldata.extend; ldata(ldata.count):=ltrim(rtrim(substr(lstr,1,ln-1))); lstr:=substr(lstr,ln+1); end loop; return ldata; end; /SQL> select * from table(cast(strtab('11,12,13') as mytabletype));COLUMN_VALUE ------------ 11 12 13create procedure pro(p_str in varchar2) as cursor t_sor is select DISTINCT COLUMN_VALUE from table(cast(strtab(p_str) as mytabletype)); str varchar2(50); num:=0; begin for v_sor in t_sor loop if num=0 then str:=v_sor.COLUMN_VALUE; num:=num+1; else str:=str||','||v_sor.COLUMN_VALUE; end if; end loop; dbms_output.put_line(str); end; /
/create or replace function strtab(p_str in varchar2)
return mytabletype
as
lstr varchar2(1000) default p_str||',';
ln number;
ldata mytabletype:=mytabletype();
begin
loop
ln:=instr(lstr,',');
exit when (nvl(ln,0)=0);
ldata.extend;
ldata(ldata.count):=ltrim(rtrim(substr(lstr,1,ln-1)));
lstr:=substr(lstr,ln+1);
end loop;
return ldata;
end;
/SQL> select * from table(cast(strtab('11,12,13') as mytabletype));COLUMN_VALUE
------------
11
12
13create procedure pro(p_str in varchar2)
as
cursor t_sor is
select DISTINCT COLUMN_VALUE from table(cast(strtab(p_str) as mytabletype));
str varchar2(50);
num:=0;
begin
for v_sor in t_sor loop
if num=0 then
str:=v_sor.COLUMN_VALUE;
num:=num+1;
else
str:=str||','||v_sor.COLUMN_VALUE;
end if;
end loop;
dbms_output.put_line(str);
end;
/