Re: Changing Datatype from Varchar2 to Number

From: Craig Burtenshaw <crb_at_amsa.gov.au>
Date: Wed, 9 Jul 2003 11:54:14 +1000
Message-ID: <3f0b75bf$0$59954$c30e37c6@lon-reader.news.telstra.net>

"Mike" <kn0xster_at_hotmail.com> wrote in message news:bd1f5e90.0306302204.47cf4b96_at_posting.google.com...
> Hi,
>
> I thought that I would be able to do this without too many problems
> but it seems I was wrong. What I have is the following
>
> tableA
> =======
> col1 Varchar(3)
> col2 Number
> col3 Number(6)
>
> What I wanted to do is to change the datatyoe of 'col1' to NUMBER(20)
> and then to change the precision of 'col2' to become NUMBER(3).
>
> The main sticking point here is that there is already data inside the
> table. What I thought I might be able to do is to create a new table
> called 'tempA' using the following:
>
> CREATE TABLE AS SELECT * FROM tableA;
>
> Then I could drop all the data in 'tableA' and make the changes which
> I require to the columns. After that i thought i might be able to
> reimport the data from 'tempA' back into 'tableA'. The problem there
> is that the datatypes aren't the same anymore.
>
> Does anyone have any ideas? I thought about trying to use the
> TO_NUMBER function on 'col1' but I can only do this to one row at a
> time. Is the best way to do this to write a function in PL/SQL or is
> there something that I have overlooked?
>
> Many thanks in advance,
>
> Mike Knox
>
> kn0xster_at_hotmail.com

It's much simpler than the examples you were given.

create table temp

(col1 number(20),
 col2 number(3),
 col3 number(6));

insert into temp
select to_number(col1),

         to_number(col2),
         col3

from tableA;

rename tableA to oldTableA;

rename temp to tableA;

Cheers
Craig. Received on Tue Jul 08 2003 - 20:54:14 CDT