Home » SQL & PL/SQL » SQL & PL/SQL » Select and load CLOB column data into another table (Oracle 11g)
Select and load CLOB column data into another table [message #681713] Wed, 19 August 2020 02:30 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67850
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.
Where is the problem?

[Updated on: Wed, 19 August 2020 03:53]

Report message to a moderator

Re: Select and load CLOB column data into another table [message #681720 is a reply to message #681715] Wed, 19 August 2020 05:14 Go to previous messageGo to next message
_jum
Messages: 573
Registered: February 2008
Senior Member
Cross post to:
https://community.oracle.com/thread/4343994
https://community.oracle.com/thread/4342439

[Updated on: Wed, 19 August 2020 05:15]

Report message to a moderator

Re: Select and load CLOB column data into another table [message #681721 is a reply to message #681720] Wed, 19 August 2020 08:10 Go to previous message
Michel Cadot
Messages: 67850
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Oh! so it appears the posted question is not the real question for the actual OP's issue!

[Updated on: Wed, 19 August 2020 08:10]

Report message to a moderator

Previous Topic: SQL CASE or IF Else Condition
Next Topic: ORA-29279: SMTP permanent error (split from hijacked topic)
Goto Forum:
  


Current Time: Wed May 12 16:16:33 CDT 2021