create type tab_str as table of varchar2(30);
/
create or replace function to_table(p_str varchar2) return tab_str
as
ltab tab_str := tab_str();
pos integer := 0;
ls varchar2(255) := p_str;
begin
pos := instr(ls,',');
while pos > 0 loop
ltab.extend;
ltab(ltab.count) := substr(ls,1,pos - 1);
ls := substr(ls,pos + 1);
pos := instr(ls,',');
end loop;
ltab.extend;
ltab(ltab.count) := ls;
return ltab;
end;
/
select * from table(cast(to_table('ab,cd,ef') as tab_str));