Changing Datatype from Varchar2 to Number

From: Mike <kn0xster_at_hotmail.com>
Date: 30 Jun 2003 23:04:45 -0700
Message-ID: <bd1f5e90.0306302204.47cf4b96@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 Received on Tue Jul 01 2003 - 01:04:45 CDT