Home » SQL & PL/SQL » SQL & PL/SQL » Split clob data and store in table (Oracle 11g)
Split clob data and store in table [message #661861] Tue, 04 April 2017 10:17 Go to next message
Roots_ct5
Messages: 4
Registered: April 2011
Location: Coimbatore
Junior Member
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 ;
Re: Split clob data and store in table [message #661862 is a reply to message #661861] Tue, 04 April 2017 10:22 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
So this clob can contain other table names, so insert into other tables with different structures?
Re: Split clob data and store in table [message #661863 is a reply to message #661862] Tue, 04 April 2017 10:30 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
https://community.oracle.com/thread/4033189
Re: Split clob data and store in table [message #661864 is a reply to message #661862] Tue, 04 April 2017 10:30 Go to previous message
Roots_ct5
Messages: 4
Registered: April 2011
Location: Coimbatore
Junior Member
Yes. Clob might have n number of tables with different structure
Previous Topic: Need a help with SQL
Next Topic: package
Goto Forum:
  


Current Time: Thu Mar 28 11:54:19 CDT 2024