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?
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).
The following works invoke it as select * from table(splitter('a,b,c,d'))
For optimal performance, it is best to avoid using hierarchical (CONNECT BY) queries in the splitter function.
The following splitter function performs a good deal better when applied to greater data volumes
This performance difference can be observed below (I used the function splitter as was given earlier in this discussion).
Here is another solution using a regular expression matcher entirely in sql.
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:
Here it is in action:
Acknowledgement: this code is a variant of some code I found on Tanel Poder's blog.