Re: Changing Datatype from Varchar2 to Number

kn0xster_at_hotmail.com (Mike) 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

Geeezz. Use ordinary sql.

create table as select <pk> column, <affected columns> from affected table then NULLIFY the orginal data, affected columns only. Then
update <affected table>
set affected column =
(
select to_number(column_from_temp)
from <temptable>
where <temptable_pk> = <affected table pk> )
where affected_table_pk in
(select temp_table_pk from temp_table)

and THAT IS ALL.
No NEED to use pl/sql

Sybrand Bakker
Senior Oracle DBA Received on Tue Jul 01 2003 - 04:22:13 CDT