Re: Change from Long to Varchar2(2000)

Cyrus AU YEUNG wrote:

> Dear all experts,
>
> I want to alter the datatype of a column from 'LONG' to
> 'VARCHAR2(2000)'. But when I issue the following sql,
>
> alter table mytable modify (mycolumn varchar2(2000));
>
> The following error occurs,
> ORA-01439: column to be modified must be empty to change datatype
>
> In my case, I cannot update all the non-null column to null and then
> alter the table since it is very large. Can anyone give me a helping hand?

You can use the export/import to do this work . First export the existing table.
Drop the table and recreate with the column definition changed from long to varchar2(2000). then use import to import the table contents. Be sure to give ignore errors for object existence as yes.

This will work if your long column contents are not very large. I am assuming that they are not more than 2000 characters as you are changing it to varchar2(2000) now.

Vishal Received on Fri Mar 13 1998 - 00:00:00 CST