how to convert csv to table in oracle

Alas, in 11g we still have to handroll our own PL/SQL tokenizers, using SQL types. In 11gR2 Oracle gave us a aggregating function to concatenate results into a CSV string, so perhaps in 12i they will provide the reverse capability.

If you don't want to create a SQL type especially you can use the built-in SYS.DBMS_DEBUG_VC2COLL, like this:

create or replace function string_tokenizer
    (p_string in varchar2
        , p_separator in varchar2 := ',')
    return sys.dbms_debug_vc2coll
is
    return_value SYS.DBMS_DEBUG_VC2COLL;
    pattern varchar2(250);
begin

    pattern := '[^('''||p_separator||''')]+' ;

    select trim(regexp_substr (p_string, pattern, 1, level)) token
    bulk collect into return_value
    from dual
    where regexp_substr (p_string, pattern, 1, level) is not null
    connect by regexp_instr (p_string, pattern, 1, level) > 0;

    return return_value;

end string_tokenizer;
/

Here it is in action:

SQL> select * from table (string_tokenizer('one, two, three'))
  2  /

COLUMN_VALUE
----------------------------------------------------------------
one
two
three

SQL>

Acknowledgement: this code is a variant of some code I found on Tanel Poder's blog.

Here is another solution using a regular expression matcher entirely in sql.

SELECT regexp_substr('one,two,three','[^,]+', 1, level) abc
FROM dual 
CONNECT BY regexp_substr('one,two,three', '[^,]+', 1, level) IS NOT NULL

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
is
v_tab sys.odcivarchar2list:=new sys.odcivarchar2list();
begin
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;
end;
/