Re: NUMBER to VARCHAR2 column conversion

"sarah" <sarahm_at_vetassess.com.au> wrote in message news:6a26bbc4.0107012144.34bf6e38_at_posting.google.com...
> 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

The best 'tool' is a small hand-written sql script:

Create an adhoc table with the following columns all columns of the primary key of the table to be adressed (going to be the primary key to the adhoc table)
the column to be addressed.
Now : insert into adhoc table select <your columns> from <affected table> update <affected table>
set <affected column> = NULL
commit

alter table <affected table> modify <affected column> (varchar2(x))

update <affected table> x
set <affected column>
= (select <affected column> from adhoc table y

     where y.<primary key> = x.<primary key> )

And that's all there is to it.

Regards,

Sybrand Bakker, Senior Oracle DBA Received on Sat Jul 21 2001 - 16:22:35 CDT