Just a quick note as to something that worked for me. Taken from the Oracle forums @ https://forums.oracle.com/forums/thread.jspa?messageID=6861756

Situation
I have one column of values that I've suffixed a comma to but I want to display this as a rows in a table. This is for Oracle PL/SQL.

My List:
1,
2,
3,
4,
5,
6
Yields: 
1,2,3,4,5,6

Using this snippet
SELECT EXTRACT (VALUE (d), '//row/text()').getstringval () AS AppNo
  FROM (SELECT XMLTYPE (   ''
                        || REPLACE (
'1,
2,
3,
4,
5,
6'                    
                        , ',', '')
                        || ''
                       ) AS xmlval
          FROM DUAL) x,
       TABLE (XMLSEQUENCE (EXTRACT (x.xmlval, '/rows/row'))) d
Yields: 
1
2
3
4
5
6

Note that I actually used text in the example above and this worked. Obviously don't get the commas mixed up.