Oracle Converting delimited value in a row to collection of values

Using a Table Function  
A nice approach for converting a delimited list of a collection is via a custom  PL/SQL Table Function which you can then SELECT from via the TABLE operator,  i.e. 


/* Create the output TYPE, here using a VARCHAR2(100) nested table type */   
SQL> CREATE TYPE test_type AS TABLE OF VARCHAR2(100);    
2  /   
Type created.   


/* Now, create the function.*/   
SQL> CREATE OR REPLACE FUNCTION f_convert(p_list IN VARCHAR2)    
2    RETURN test_type    
3  AS    
4    l_string       VARCHAR2(32767) := p_list || ',';    
5    l_comma_index  PLS_INTEGER;   
6    l_index        PLS_INTEGER := 1;    
7    l_tab          test_type := test_type();    
8  BEGIN    
9    LOOP   
10      l_comma_index := INSTR(l_string, ',', l_index);   
11      EXIT WHEN l_comma_index = 0;   
12      l_tab.EXTEND;   
13      l_tab(l_tab.COUNT) := SUBSTR(l_string, l_index, l_comma_index - l_index);   
14      l_index := l_comma_index + 1;   
15    END LOOP;   
16    RETURN l_tab;   
17  END f_convert;   
18  /  
Function created.    


/* Prove it works */    
SQL> SELECT * FROM TABLE(f_convert('AAA,BBB,CCC,D'));   
COLUMN_VALUE  
--------------------------------------------------------------------------------  
AAA  
BBB  
CCC  
D    
4 rows selected.