Solved

convert clob to varchar2 datatype

Posted on 2014-10-08
5
298 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle Distributed Transaction Lock Error ORA-01591 8 93
Fill Null values 5 56
error doing substr 3 52
Oracle SQL_Error . 7 24
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

752 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