Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

convert clob to varchar2 datatype

Posted on 2014-10-08
5
Medium Priority
?
306 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
  • 3
  • 2
5 Comments
 
LVL 78

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 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

773 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