Re: NUMBER to VARCHAR2 column conversion

On 1 Jul 2001 22:44:11 -0700, sarahm_at_vetassess.com.au (sarah) wrote:

>Hi,
>
>This may seem like a really stupid question but I need to change the
>datatype in a column in an existing table from NUMBER to VARCHAR2
>datatype. Because the rows in the table aren't null i need another
>method other than alter table modify.... to preserve the existing info
>in the column. What is the best tool to use and why? (and most
>importantly how?) SQLLoader? Direct-Load Insert? Or is there some
>other way around this...
>
>Could you please email replies to me at sarahm_at_vetassess.com.au.
>
>Thanks in advance,
>
>sarah

Hi Sarah,

If you are running Oracle 8i or higher, you can also use the following, simpler method :

Say you have the following table :

mytab ( col1 number(10))

and want to change col1 to varchar2(10).

Do the following :

alter table mytab add temp varchar2(10); -- create temporary column update mytab set temp = col1; -- save the old value update mytab set col1= null;
alter table mytab modify col1 varchar2(10); update mytab set col1 = temp; -- restore the old values alter table mytab drop column temp; -- needs Oracle 8i or higher

And you're done.

Regards,
Kenneth Koenraadt
Systems Consultant
Oracle DBA
plovmand@<no-spam>hotmail.com Received on Sat Jul 21 2001 - 16:23:03 CDT