| INSERT INTO CLOB Column [message #348578] |
Wed, 17 September 2008 05:34  |
ramesh_samane
Messages: 63
Registered: May 2006
|
Member |
|
|
1. I have a table1 having one CLOB column.
2. I have created another table table2 with the same structure.
3. INSERT INTO table2 SELECT col1, TO_LOB(clob_col) FROM table1;
This works and I get table2 poupulated.
4. When I see table data from TOAD, I can see data from table1 including CLOB column, but TOAD gives me error while I try to check data from table2 ('Data type not supported').
Any ideas??? How will I verify that I have the same data in both table's CLOB column? Thanks.
|
|
|
|
|
|
|
|
| Re: INSERT INTO CLOB Column [message #348588 is a reply to message #348587] |
Wed, 17 September 2008 06:06  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I strongly suspect that you've made a mistake somewhere.
When I try to use TO_LOB on a CLOB column, I get exactly the exception I would expect.
TO_LOB exists for the sole purpose of migrating LONG/LONG RAW columns to CLOB/BLOB.
How about you show us the table structures and an example of the code working in SQL*Plus
SQL> create table test_0092 (col_1 number, col_2 clob);
Table created.
SQL> create table test_0093 (col_1 number, col_2 clob);
Table created.
SQL> insert into test_0092 values (1,'some text');
1 row created.
SQL> insert into test_0092 values (2,'some more text');
1 row created.
SQL> insert into test_0093 select col_1,to_lob(col_2) from test_0092;
insert into test_0093 select col_1,to_lob(col_2) from test_0092
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected LONG BINARY got CLOB
|
|
|
|
| Re: INSERT INTO CLOB Column [message #348592 is a reply to message #348578] |
Wed, 17 September 2008 06:33 |
ramesh_samane
Messages: 63
Registered: May 2006
|
Member |
|
|
You are right. when I checked the table structure, in original table the column is of LONG type and in copy table it is CLOB. That's why TO_LOB is working. I checked the data from SQL Plus and I am getting the correct data in that column.
Now my question is can I migrate data from CLOB column to LONG column?
Just to simplify question...
SQL> DESC org_tab
col1 NUMBER
col2 LONG
SQL> DESC bkup_tab
col1 NUMBER
col2 CLOB
SQL> insert into bkup_tab select col1, TO_LOB(col2) from org_tab;
10 rows inserted
Now if I delete data from org_tab, can I be able to transfer data from bkup_tab to org_tab?
Thanks much for quick response...
|
|
|
|
|
|
|
|
| Re: INSERT INTO CLOB Column [message #348616 is a reply to message #348592] |
Wed, 17 September 2008 07:35 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
You can just use ALTER TABLE...
SQL> create table test_0094 (col_1 number, col_2 long);
Table created.
SQL> insert into test_0094 values (1,'AAAA');
1 row created.
SQL> alter table test_0094 modify (col_2 clob);
Table altered.
SQL> desc test_0094;
Name Null? Type
----------------------------------------------------- -------- --------------
COL_1 NUMBER
COL_2 CLOB
SQL> select * from test_0094;
COL_1 COL_2
---------- -------------------------------------------------------------------
1 AAAA
Here's a link to how to migrate from Long to Lob
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
