Categories

JAVA DATEBASE
Technology Network Community
Oracle Database
Fusion Middleware
Development Tools
Java
Desktop
Server & Storage Systems
Enterprise Management
Berkeley DB Family
Cloud Computing
Big Data
Business Intelligence
Architecture
Migration and Modernization
E-Business Suite
Siebel
PeopleSoft Enterprise
JD Edwards World
Industries
JD Edwards EnterpriseOne
User Productivity Kit Pro (UPK) and Tutor
Governance, Risk & Compliance (GRC)
Master Data Management (MDM)
Oracle CRM On Demand
On Demand: SaaS and Managed Applications
AutoVue Enterprise Visualization
Primavera
ATG
Agile PLM
Endeca Experience Management
Fusion Applications
Archived Forums

 



Tags

General Questions


How to change column type from LONG to VARCHAR2(4000)


Hi All  I need to change type for one column "AdditionalInfo" from LONG to VARCHAR2(4000) in my table ProjectVersionTab. I note that texts in this columns don't exceed length of 4000 characters and will not be truncated.  When I tried to execute following command  alter table ProjectVersionTab modify (AdditionalInfo varchar2(4000));  I've got such error: 01439. 00000 -  "column to be modified must be empty to change datatype"  The I tried something with additional column: alter table ProjectVersionTab add (AdditionalInfo2 varchar2(4000)); update ProjectVersionTab set AdditionalInfo2 = sys.dbms_metadata_util.long2varchar(4000,'cmgsfa.ProjectVersionTab','additionalinfo',rowid); alter table ProjectVersionTab drop column AdditionalInfo; alter table ProjectVersionTab rename column AdditionalInfo2 to AdditionalInfo;  My questions are: 1.Does any way exist how to do it without additional columns?  2.Can some coversion problems occurs when I copy texts from LONG to VARCHAR2 by way mentioned above?  thanks

Hi,  perhaps CLOB is a better choice for the new column type. You can use the TO_LOB function to do the conversion.   Best regards,   Nikolay

Hi,  1. I don't think you can do an in-place conversion so the additional column is not a bad idea. Have in count that, if once converted you want to drop the source column you'll have to empty it first.   2. A conversion from LONG to VARCHAR2 shouldn't convert chars but, as I have not tested it, you better perform a simulation first and see if you get what you want   regards  http://aboutorcl.blogspot.com/

How to Convert a Long to Varchar2? [ID 228532.1]

Sorry forsaken, but I don't understand what do you mean by following words: Have in count that, if once converted you want to drop the source column you'll have to empty it first.  Can you please explain me better?  my english is wrong.  thanks

Hi;  please check  How to Convert a Long to Varchar2? [ID 228532.1]  Regard Helios

A column can have no values if you want to drop it. You must put a NULL value in this column for all the records in the table  If you have acces to My Oracle Support (https://support.oracle.com) you can check doc 228532.1 which is about conversion from long to varchar2  regards

A column can have no values if you want to drop it. You must put a NULL value in this column for all the records in the table  That means that if i want to drop my LONG column 'AdditionalInfo' from table 'VersionTab' I will have to execute update query first as shown bellow?  UPDATE VersionTab SET AdditionalInfo = NULL;  Why is this necessary? I though it's automatically done by drop command.  I have not access to Oracle support. From where can  I download it. Can somebody send me any link or text document?  thanks


Related Links

Deadlock - LIBRARY OBJECT LOCK
Switch shared session DB connection from DB connection
Windows bactch job to trim the Listener log
create directory
Simple insert, delete and update not working after oracle 10g installation
Create Incremental Full Backup
online dba training
Oracle Database administration
link database technology
still cannot reclaim free space after shrinking tables?
Find out queries running in Database at somepoint of time
Utilization Reports
Serious problems after APEX installation
ORA-00600: internal error code, arguments: [17099], [] ... URGENT!
Question regarding Archived Logs Sequence ---
Need help with dbms_scheduler