Re: Convert NUMBER to VARCHAR2

A copy of this was sent to tndoan_at_abondigas.engr.ucdavis.edu (Tin Ngoc Doan) (if that email address didn't require changing) On 24 Nov 1999 11:35:19 GMT, you wrote:

>Sorry, maybe I was not clear on the issue.
>
>I would like to change the column datatype in the table, not converting
>types during a select.
>

something like:

tkyte_at_8.0> create table t ( x number );

Table created.

tkyte_at_8.0>
tkyte_at_8.0> insert into t values ( 1 );

1 row created.

tkyte_at_8.0> insert into t values ( 2 );

1 row created.

tkyte_at_8.0> insert into t values ( 3 );

1 row created.

tkyte_at_8.0> insert into t values ( 4 );

1 row created.

tkyte_at_8.0>
tkyte_at_8.0> create table tmp as select rowid r, x from t;

Table created.

tkyte_at_8.0>
tkyte_at_8.0> desc t

 Name                            Null?    Type
 ------------------------------- -------- ----
 X                                        NUMBER

tkyte_at_8.0>
tkyte_at_8.0> update t set x = null;

4 rows updated.

tkyte_at_8.0> alter table t modify x varchar2(3);

Table altered.

tkyte_at_8.0> update t set x = ( select x from tmp where r = t.rowid );

4 rows updated.

tkyte_at_8.0>
tkyte_at_8.0> desc t

 Name                            Null?    Type
 ------------------------------- -------- ----
 X                                        VARCHAR2(3)

tkyte_at_8.0>
tkyte_at_8.0> select * from t;

X
---
1
2
3
4

will work

>Thanks,
>Tin
>
>Screamer (mflopes_at_bigfoot.com) wrote:
>: to_char(number_field)
>
>: "Tin Ngoc Doan" <tndoan_at_abondigas.engr.ucdavis.edu> wrote in message
>: news:81g9j9$s4f$1_at_mark.ucdavis.edu...
>: > Hi all,
>: >
>: > Here is my situation. I've a multi-column table with data, and one of
>: > the columns is a NUMBER type. I need to convert it to VARCHAR2() type.
>: >
>: > What is the best way to do this?
>: >
>: > Unlike MySQL, Oracle does not handle all the copying in the background
>: > using the ALTER TABLE command.
>: >
>: > Appreciate your help,
>: > Tin
>

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Nov 24 1999 - 07:10:03 CST