?
Solved

convert clob to varchar2 datatype

Posted on 2014-10-08
5
Medium Priority
?
305 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 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

719 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