Re: Changing Datatype from Varchar2 to Number

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 1 Jul 2003 07:23:11 GMT
Message-ID: <bdrcsv$10a5mm$1@ID-82536.news.dfncis.de>

> 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.
 

Are you using oracle 9i, then try this:

create table tableA (
  col1 Varchar2(3),
  col2 Number primary key,
  col3 Number(6)
);

insert into tableA values ('1',1,1);
insert into tableA values ('2',2,2);
insert into tableA values ('3',3,3);
insert into tableA values ('4',4,4);

commit;

create table tableA_temp (
  col1 Number(20),
  col2 Number primary key,
  col3 Number(3)
);

begin
  dbms_redefinition.start_redef_table(
    user,

    'tableA',
    'tableA_temp',
    'to_number(col1) col1, col2 col2, col3 col3');

  dbms_redefinition.sync_interim_table(
    user,
    'tableA',
    'tableA_temp');

  dbms_redefinition.finish_redef_table(
    user,
    'tableA',
    'tableA_temp');
end;
/

drop table tableA_temp;

desc tableA;

select * from tableA;

hth
Rene Nyffenegger

-- 
  Projektleitung und Entwicklung in Oracle/C++/C# Projekten
  http://www.adp-gmbh.ch/cv.html

Received on Tue Jul 01 2003 - 02:23:11 CDT