Convert Clob to unique Rows
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:
I need to end with
record# List Item
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.