Hi,
We have a requirement to insert CLOB data into table.
CLOB data is stored like this:
col1,col2,col3,col4,col5 LineFeed
val1,val2,val3,val4,val5 LineFeed
val1,val2,val3,val4,val5
I am able to split it into string based on the LineFeed but how can i insert that string in the table (as a row).
I have tried below logic to convert comma separated string into rows but i don't need rows. I need them as column values.
with c_rec as
(
select
to_char(trim(
regexp_substr(l_string, '[^,]+', 1, levels.column_value)
)) as col_val
from dual,
table(cast(multiset(
select level from dual
connect by level <= length (regexp_replace(l_string, '[^,]+')) + 1
) as sys.OdciNumberList)) levels
)
select col_val
from c_rec
Your help will be really appreciated.
Thanks