Solved

convert clob to varchar2 datatype

Posted on 2014-10-08
5
303 Views
Last Modified: 2014-10-09
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
Comment
Question by:sam_2012
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40369091
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
 

Author Comment

by:sam_2012
ID: 40371241
Is it possible to change the column data type from CLOB to VARCHAR2 using alter table command for global temporary table.
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40371357
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
 

Author Comment

by:sam_2012
ID: 40371517
I have dropped and re-created it , since it is an global temporary table.
0
 

Author Closing Comment

by:sam_2012
ID: 40371518
Thanks.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to recover a database from a user managed backup
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question