Home » SQL & PL/SQL » SQL & PL/SQL » Change CLOB column to VARCHAR2 data type (Oracle 9i)
Change CLOB column to VARCHAR2 data type [message #294733] Fri, 18 January 2008 13:33 Go to next message
kollerrk
Messages: 1
Registered: January 2008
Junior Member
My knowledge of writing SQL code for Oracle doesn't go far beyond creating basic tables and relationships. I have a situation where I need to convert a column in a table with data in it from CLOB to VARCHAR2. While I know this statement does not work, I would essentially like to accomplish this:

ALTER TABLE table1
MODIFY clob_column VARCHAR2(1000);

This needs to be done with SQL code in SQL*Plus. The Oracle version is 9i. I am guessing I would need to create the VARCHAR2 column and then somehow move the data over and then drop the CLOB column. An example of how you would accomplish this in SQL*Plus would be greatly appreciated. Thanks!
Re: Change CLOB column to VARCHAR2 data type [message #294734 is a reply to message #294733] Fri, 18 January 2008 13:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I am guessing I would need to create the VARCHAR2 column and then somehow move the data over and then drop the CLOB column.

Yes you are right.

Quote:
An example of how you would accomplish this in SQL*Plus would be greatly appreciated.

What part don't you know?

You can also use dbms_redefinition package.

Regards
Michel
Re: Change CLOB column to VARCHAR2 data type [message #294735 is a reply to message #294733] Fri, 18 January 2008 13:51 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
you have not many choices here. I suggest one of the following:

given this table:

create table temp1 (a clob)
/

do this:

alter table temp1 add b varchar2(4000)
/

update temp1 set a = null, b = a
/

alter table temp1 drop column a
/

or this:

create table t2
as
select cast(a as varchar2(4000)) a
from temp1
/
--
-- rebuild indexes, constraints, etc
--

drop table temp1;
rename t2 to temp1;


good luck, Kevin
Previous Topic: reading a file into the database
Next Topic: Full Outer Joins with more than 2 tables
Goto Forum:
  


Current Time: Wed Dec 07 18:32:04 CST 2016

Total time taken to generate the page: 0.07443 seconds