how to convert csv to table in oracle

Question

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

one
two
three

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

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

Answer 1

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.

Answer 2

See this blog post on the topic:

http://ora-00001.blogspot.com/2010/04/select-from-spreadsheet-or-how-to-parse.html

Answer 3

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;
/

Answer 4

I don't have 11g installed to play with, but there is a PIVOT and UNPIVOT operation for converting columns to rows / rows to columns, that may be a good starting point.

http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/11g-pivot.html

(Having actually done some further investigation, this doesn't look suitable for this case - it works with actual rows / columns, but not sets of data in a column).

There is also DBMS_UTILITY.comma_to_table and table_to_comma for converting CSV lists into pl/sql tables. There are some limitations (handling linefeeds, etc) but may be a good starting point.

My inclination would be to use the TYPE approach, with a simple function that does comma_to_table, then PIPE ROW for each entry in the result of comma_to_table (unfortunately, DBMS_UTILITY.comma_to_table is a procedure so cannot call from SQL).