| Select and load CLOB column data into another table [message #681713] |
Wed, 19 August 2020 02:30  |
 |
pmreddy.mahi@gmail.com
Messages: 16
Registered: December 2011
|
Junior Member |
|
|
Hi All,
please suggest me how to extract and load below type data into another table if the column data type is CLOB. some of the rows of the length is 12000 bytes or characters.
with
t (clob_column) as (
select 'CUST_ID|value1|CUST_NAME|value2|CUST_DEPT|value3|ITEM_ID|value4|' from dual union all
select 'CUST_ID|value1|CUST_DEPT|value3|ITEM_ID|value4|' from dual union all
select 'CUST_NAME|value2|CUST_DEPT|value3|' from dual union all
select 'CUST_ID|value1|ITEM_ID|value4|' from dual union all
select 'CUST_NAME|value2|CUST_DEPT|value3|ITEM_ID|value4|' from dual union all
select 'ITEM_ID|value4|' from dual union all
select 'CUST_DEPT|value3|' from dual union all
select 'CUST_ID|value1|CUST_DEPT|value3|' from dual union all
select 'CUST_NAME|value2|' from dual union all
select 'CUST_DEPT|value3|CUST_ID0|value10|' from dual
)
select * from t;
below is my required output format. I want to retrieve data like below and load this data into another table with below four columns.
CUST_ID CUST_NAME CUST_ DEPT ITEM_ID
-------- -------- -------- --------
value1 value2 value3 value4
value1 value3 value4
value2 value3
value1 value4
value2 value3 value4
value4
value3
value1 value3
Thanks,
Maheswar
[Updated on: Wed, 19 August 2020 03:47] Report message to a moderator |
|
|
|
| Re: Select and load CLOB column data into another table [message #681715 is a reply to message #681713] |
Wed, 19 August 2020 03:53  |
 |
Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.
SQL> create table t1 (id integer, val clob);
Table created.
SQL> create table t2 (id integer, val clob);
Table created.
SQL> insert into t1 values (1, rpad('A',100000,'A'));
1 row created.
SQL> insert into t2 select * from t1;
1 row created.
[Updated on: Wed, 19 August 2020 03:53] Report message to a moderator |
|
|
|
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
