Re: Converting a numeric column into an alphanumeric column

>Hi,
>
>I've got the next problem. I want to convert a table column from NUMBER to
>VARCHAR2. Oracle does not permit it as it only lets you to change the
lenght
>of a column, and only to a higher lenght. I know that the solution is to
>drop the table and to create with the new column type, but the problem is
>that I need to preserve the data inside the table. Any ideas??
>
>Thank you in advance.
>
>Bye.
>

For changing the datatype column must be empty (all values must be NULL). For saving data in column which type you must change:

1. If your table has a primary key (f.e. pk_column):

--your table SOURCE_TABLE(pk_column number, char_columnn varchar2)

create table tmp_tbl as select pk_column, char_column from RCE_TABLE; -- storing data in temporary table update table SOURCE_TABLE set char_column=null; commit;
alter table SOURCE_TABLE modify char_column number; update table SOURCE_TABLE s set char_column=(select to_number(char_column) from tmp_tbl t where s.pk_column=t.pk_column); drop table tmp_tbl;

I recommend you to do this step by step, not like a script to prevent tmp_tbl to be dropped if any errors will occured while converting char_column data to number.

2. If your table has not a primary key, you must find other way like described.

Alexander Chuchko Received on Thu Dec 16 1999 - 03:15:24 CST