Mehur Mehur - 2 years ago 181
SQL Question

how to convert csv to table in oracle

How can I make a package that returns results in table format when passed in csv values.

select * from table(schema.mypackage.myfunction('one, two, three'))

should return


I tried something from ask tom but that only works with sql types.

I am using oracle 11g. Is there something built-in?

Answer Source

The following works invoke it as select * from table(splitter('a,b,c,d'))

create or replace function splitter(p_str in varchar2) return  sys.odcivarchar2list
v_tab sys.odcivarchar2list:=new sys.odcivarchar2list();
with cte as (select level  ind from dual
connect by 
level <=regexp_count(p_str,',') +1
select regexp_substr(p_str,'[^,]+',1,ind)
bulk collect into v_tab
from cte;
return v_tab;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download