Here is my table creation script:
create table mapzet00a(col1 varchar2(5),col2 varchar2(5),col3 varchar2(9),col4 varchar2(5),col5 varchar2(8));
Now I have the clob data having the table name also part of it.(first 9 characters are table name and it is fixed)
Now I have to split the clob data which is having data for all the columns . clob data having no delimiter character. So we need to split the clob based on the user_tab_cols dictionary (although It is bad design, there no option as of now )
I need a sql , which will split the clob based on the column order and column size from the user_tab_column.
WITH t AS
(SELECT to_clob('mapzet00aData1Data2Data3 ZData4rData5 u')AS data
FROM dual
)
SELECT *
FROM t,
user_tab_cols c
WHERE c.TABLE_NAME = upper(TO_CHAR(SUBSTR(t.data,1,9)))
ORDER BY COLUMN_ID ;