how to convert csv to table in oracle

The following worksinvoke it asselect * from table(splitter('a,b,c,d'))

create or replace function splitter(p_str in varchar2) return  sys.odcivarchar2listisv_tab sys.odcivarchar2list:=new sys.odcivarchar2list();beginwith cte as (select level  ind from dualconnect by level <=regexp_count(p_str,',') +1)select regexp_substr(p_str,'[^,]+',1,ind)bulk collect into v_tabfrom cte;return v_tab;end;/

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_vc2collis    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----------------------------------------------------------------onetwothreeSQL>

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) abcFROM dual CONNECT BY regexp_substr('one,two,three', '[^,]+', 1, level) IS NOT NULL