Converting a delimited string (or column) to rows in Oracle using a pre-defined system function

Question

A few years ago in Oracle 10 or 9 I used a function which was something like "DBMS_COL_2_VAL" (This is definitely not the right function).

The purpose of this built in function is to convert the provided string to rows based on a specified delimiter. I know that there are multiple ways of converting a delimited string to rows, but what I looking for is this specific function.

If you can help that would be great.

Please do not provide any solutions with CONNECT, CASE, or REGEX.

Thanks

Answer 1

The function I was trying to find was SYS.DBMS_DEBUG_VC2COLL.

Technically speaking it does not convert a delimited string to column, but it converts a list of comma separated values to rows. I realized that after I found an old post.

Sample code and results:

with test as  (
    select column_value AS c1  
      from table( SYS.DBMS_DEBUG_VC2COLL( 'a','b','c' ) )  
   )  
 select * from test;

Result:

c1   
__  
a    
b    
c