• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 308
  • Last Modified:

convert clob to varchar2 datatype

Hi ,

I have a table called comments , in which I have converted the column data type from varchar2 to clob , as provided in the solution  mentioned below.

http://www.experts-exchange.com/Database/Oracle/Q_28524807.html

I need to create an rollback script which will modify the comments  column from CLOB to Varchar2 and the data to be stored in  USER_TAB tablespace . from USERS_LOB tablespace.

Any help is really appreciated.
0
sam_2012
Asked:
sam_2012
  • 3
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
Just use SUBSTR on the CLOB column. You should get a varcahr2 result.

create table rollback_table as (select substr(comments,1,4000) from clob_table);
0
 
sam_2012Author Commented:
Is it possible to change the column data type from CLOB to VARCHAR2 using alter table command for global temporary table.
0
 
slightwv (䄆 Netminder) Commented:
Set up a dev/test database and try it?

Doesn't work for me...
create global temporary table gt_junk
(
	col1 clob
) on commit preserve rows
/

alter table gt_junk modify col1 varchar2(4000);

Open in new window


That said:
It is a temporary table. Any reason you cannot just drop and recreate it?
0
 
sam_2012Author Commented:
I have dropped and re-created it , since it is an global temporary table.
0
 
sam_2012Author Commented:
Thanks.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now