Home » SQL & PL/SQL » SQL & PL/SQL » INSERT INTO CLOB Column
INSERT INTO CLOB Column [message #348578] Wed, 17 September 2008 05:34 Go to next message
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 #348580 is a reply to message #348578] Wed, 17 September 2008 05:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
TO_LOB on a LOB column is meaningless. First fix that.

Then if you want to see if there are the same data, then select and compare them.

Regards
Michel
Re: INSERT INTO CLOB Column [message #348587 is a reply to message #348580] Wed, 17 September 2008 05:58 Go to previous messageGo to next message
ramesh_samane
Messages: 63
Registered: May 2006
Member
Oracle gives error if I don't use TO_LOB. Also would like to know why TOAD is not displaying the data from copied table. It displays data from original table including CLOB column. Thanks.
Re: INSERT INTO CLOB Column [message #348588 is a reply to message #348587] Wed, 17 September 2008 06:06 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #348594 is a reply to message #348592] Wed, 17 September 2008 06:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Now my question is can I migrate data from CLOB column to LONG column?

No.

Quote:
Now if I delete data from org_tab, can I be able to transfer data from bkup_tab to org_tab?

Convert the column to a CLOB.

Regards
Michel
Re: INSERT INTO CLOB Column [message #348606 is a reply to message #348578] Wed, 17 September 2008 07:12 Go to previous messageGo to next message
ramesh_samane
Messages: 63
Registered: May 2006
Member
Is it possible to alter a table and change data type from LONG to CLOB? Are there any restrictions? Thanks all for the quick help.
Re: INSERT INTO CLOB Column [message #348616 is a reply to message #348592] Wed, 17 September 2008 07:35 Go to previous messageGo to next message
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
Re: INSERT INTO CLOB Column [message #348875 is a reply to message #348578] Thu, 18 September 2008 03:36 Go to previous message
ramesh_samane
Messages: 63
Registered: May 2006
Member
Thank you !
Previous Topic: data encryption in oracle 10g following hipaa guidelines
Next Topic: need help on multidimentional array access
Goto Forum:
  


Current Time: Sat Dec 03 22:39:51 CST 2016

Total time taken to generate the page: 0.03808 seconds