14

I have a table (that contains data) in Oracle 11g and I need to use Oracle SQLPlus to do the following:

Target: change the type of column TEST1 in table UDA1 from number to varchar2.

Proposed method:

  1. backup table
  2. set column to null
  3. change data type
  4. restore values

The following didn't work.

create table temp_uda1 AS (select * from UDA1); 

update UDA1 set TEST1 = null;
commit;

alter table UDA1 modify TEST1 varchar2(3);

insert into UDA1(TEST1)
  select cast(TEST1 as varchar2(3)) from temp_uda1;
commit;

There is something to do with indexes (to preserve the order), right?

Sebastian Zartner
  • 18,808
  • 10
  • 90
  • 132
Din
  • 223
  • 3
  • 4
  • 8
  • Your last `insert` statement needs to be an `update`. – Colin 't Hart Sep 24 '13 at 09:54
  • also, someone told me "the table has a key" i need to export with key... and then restore back with keys matching. is this right? – Din Sep 24 '13 at 09:56
  • 1
    What do you mean by "someone told me the table has a key"... Do you know the table? – Srini V Sep 24 '13 at 09:58
  • yes, its a big table, so i should ONLY backup column TEST1 in the first step. if thats the case, how to do the last step? – Din Sep 24 '13 at 09:59
  • 1
    Refer the answer by @a_horse_with_no_name that is the correct procedure. Remember to create the indexes. There will not be the same order since number and varchar2 are different – Srini V Sep 24 '13 at 10:01
  • Please refer to the manual to understand how to create an index: http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_5012.htm#i2062403 –  Sep 24 '13 at 10:06
  • Can you try using package DBMS_REDEFINE ? final solution ? – Kiquenet Nov 04 '14 at 15:19

4 Answers4

37
create table temp_uda1 (test1 integer);
insert into temp_uda1 values (1);

alter table temp_uda1 add (test1_new varchar2(3));

update temp_uda1 
   set test1_new = to_char(test1);

alter table temp_uda1 drop column test1 cascade constraints;
alter table temp_uda1 rename column test1_new to test1;

If there was an index on the column you need to re-create it.

Note that the update will fail if you have numbers in the old column that are greater than 999. If you do, you need to adjust the maximum value for the varchar column

  • what do you do here? alter table temp_uda1 add (test1_new varchar2(3)); – Din Sep 24 '13 at 10:03
  • ^ shouldnt it be: alter table UDA1 modify TEST1 VARCHAR2(3); I want to change the previous column, not to add a new one.. – Din Sep 24 '13 at 10:04
  • 2
    @Din: Why don't you run one statement after the other and inspect that table after each step. Then you'll understand what each statement is doing. –  Sep 24 '13 at 10:07
17

Add new column as varchar2, copy data to this column, delete old column, rename new column as actual column name:

ALTER TABLE UDA1
ADD (TEST1_temp  VARCHAR2(16));

update UDA1 set TEST1_temp = TEST1;

ALTER TABLE UDA1 DROP COLUMN TEST1;

ALTER TABLE UDA1 
RENAME COLUMN TEST1_temp TO TEST1;
mkb
  • 1,106
  • 1
  • 18
  • 21
6

Look at Oracle's package DBMS_REDEFINE. With some luck you can do it online without downtime - if needed. Otherwise you can:

  • Add new VARCHAR2 column
  • Use update to copy NUMBER into VARCHAR2
  • Drop NUMBER column
  • Rename VARCHAR2 column
ibre5041
  • 4,903
  • 1
  • 20
  • 35
1

Here you go, this solution did not impact the existing NOT NULL or Primary key constraints. Here i am going to change the type of Primary key from Number to VARCHAR2(3), Here are the Steps on example table employee.

  1. Take backup of table and Index, Constraints created table employee_bkp create table employee_bkp as select * from employee commit;
  2. Truncate the table to empty it truncate table employee
  3. Alter the table to change the type ALTER TABLE employee MODIFY employee_id varchar2(30);
  4. Copy the data back from backup table insert into employee (select * from employee_bkp) commit;
  5. Verify
Kul Bhushan Prasad
  • 859
  • 3
  • 12
  • 19