I have a table in an Oracle DB which contains a list of items in a clob column. The items in the clob are separated by chr(10). What I need to do is to convert the clob into a unique list with separate rows for each item in the clob column.

I start with something like this:

record# clobdata
1 abcd
2 xyz

I need to end with

record# List Item
1 a
1 b
1 c
1 d
2 x
2 y
2 z

The end result must have each unique item in the clob returned as an individual row in the resulting query which I can then insert into a materialized view or table. Oh and just for fun I have no idea how many unique items can be found in the clob data. Could be 1, 5 or 500.

Any ideas?

Thanks