Re: modify datatype with data?

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Thu, 21 Mar 2002 20:50:43 +0100
Message-ID: <r7ek9ush29a943d3lg0ns0jl5idjmqqk1h@4ax.com>

On Thu, 21 Mar 2002 19:40:02 GMT, Andrew Metcalfe <ametcalf_1999_at_yahoo.com> wrote:

>
>Is there a mechanism in Oracle that'll allow me to modify the datatype
>of a column without disrupting the data in it?
>
>Alter table modify column is telling me the field must be empty.
>
>_Am

So the usual way to resolve this is
- create an extra table consisting of the primary key of the source table and the affected column(s)

- insert into select ... from source table
- update source table set affected column = NULL
- alter table source table modify...
- update source table

  set affected column =
  (select affected column
   from extra table
  where extra table.primary key = source table.primary key)

commit

And that is basically all!

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Thu Mar 21 2002 - 13:50:43 CST