Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Bala.

Asked: December 15, 2004 - 12:43 pm UTC

Last updated: June 06, 2012 - 11:31 am UTC

Version: 8.x or above

Viewed 50K+ times! This question is

You Asked

Hi Tom,
We have a requirement to change the datatype of a column from Numeric to Varchar2. A few questions come up:
1. Is this do-able at all i.e. converting the datatype using a DDL command? I believe Oracle manuals mention that ALTER TABLE MODIFY COLUMN can be used to do this.
2. What if the column under question is a key field or has an index? Any other considerations before we do this conversion?
3. Should the data in the column be empty?
4. Any risks in doing this?

Appreciate your response and thanks in advance.

and Tom said...

alter table emp modify empno varchar2(30)
*
ERROR at line 1:
ORA-01439: column to be modified must be empty to change datatype



You'll want to use CREATE TABLE as SELECT to_char(empno)....

then index/grant/constrain/trigger/whatever this table....

then drop the old table, rename the new table to the old name

adding any foreign keys back at that time (after you do the same thing to each of the child tables!)


it requires the entire table be rewritten. In place it would tend to migrate many/most of the rows (as the varchar2 is going to take approximiately 2x the space now)

Rating

  (10 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

How about this?

Dan Kefford, December 15, 2004 - 4:08 pm UTC

Tom.... couldn't you use this strategy?

SQL> create table blah_dk
  2        (num_col   number)
  3  /

Table created.

SQL> insert into blah_dk
  2  select rownum from all_objects
  3  where rownum < 11
  4  /

10 rows created.

SQL> commit
  2  /

Commit complete.

SQL> alter table blah_dk add 
  2        (varchar_col   varchar2(3))
  3  /

Table altered.

SQL> update blah_dk
  2  set varchar_col = num_col
  3  /

10 rows updated.

SQL> alter table blah_dk drop column num_col
  2  /

Table altered.

Of course, I didn't take into consideration whether num_col had any indexes, constraints, etc. Wouldn't this be any easier or less risky than dropping and recreating the whole table?

 

Tom Kyte
December 15, 2004 - 6:28 pm UTC

row migration
foreign keys
constraints
indexes

there would be even more row migration going on (have the number and the varchar2 at the same time)

same fkey issues (they mentioned key field) and so on.


Exp/Imp would be "unsafe" (when you take the data out of the database - you are at risk)


we are not dropping and recreating -- we are recreating and then dropping. You copy the data over -- even if you drop the old data, you still have it.


In 9i, they could use an online redefine -- but they are in 8.x....

Changing datatype without changing name

Bob B, December 15, 2004 - 5:27 pm UTC

Dan, I believe you forgot one piece of the whole puzzle: changing the datatype leaves the name the same.

If all access to the table was done through a view, say,

CREATE OR REPLACE VIEW blah_dk_view as
SELECT num_col
FROM blah_dk

then

CREATE OR REPLACE VIEW blah_dk_view as
SELECT varchar_col num_col
FROM blah_dk

Can probably handle many of the naming issues. If that is not the case, then the column migration has to occur twice once to move the column data to a new name and data type and a second time to move it back to the original name. If you decide you want to be able to recover from this change, then you'll probably end up using CTAS to backup the data anyway, so you're probably better off sticking to simple and safe.

Additional

Bob B, December 15, 2004 - 5:35 pm UTC

I meant to put this in my previous post, apologies. The CTAS method can be made "safer" by renaming the old table and keeping it as a backup for however long you think your paranoia lasts: i.e. (Note: I added the 'c' in front of the n field to show that n was now a varchar2)

CREATE TABLE t (
n NUMBER
)
Table created

INSERT INTO t
SELECT ROWNUM
FROM ALL_USERS
WHERE ROWNUM <= 10
10 rows inserted


SELECT *
FROM t
N
--
1
2
3
4
5
6
7
8
9
10
10 rows selected


CREATE TABLE t_new AS
SELECT 'c' || TO_CHAR( n ) n
FROM t
Table created

RENAME t TO t_old
Table renamed

RENAME t_new TO t
Table renamed

SELECT *
FROM t
N
-----------------------------------------
c1
c2
c3
c4
c5
c6
c7
c8
c9
c10
10 rows selected

Response to Bob B on December 15, 2004

Dan Kefford, December 15, 2004 - 5:40 pm UTC

DOH! Knew I would have missed at least _one_ detail. ;P

Well... at this point, I'm probably barely defending my argument, but one could still do this:

SQL> alter table blah_dk rename column varchar_col to num_col
  2  /

Table altered.

 

Renaming column in place

Bob B, December 15, 2004 - 9:07 pm UTC

If we were going to use the add a column method, I guess the following might work:

Add new number column (yes, number)
update new column with old columns value
disable constraints/remove indexes (if needed)
change the old column from number to varchar2
update the old column with the new_column values
rebuild indexes/enable constraints
drop new column

still seems like an awful amount of work and a lot unsafer than the CTAS, rename, rename method

Need some help

Terry, June 13, 2005 - 8:10 am UTC

Hi Tom,

I tried to change a column's datatype of a table with 13G of data, and faced a lot of problems.Like rollback
segment ran out of space, lot of archiving, temp segment full, so on....i tried all append, parallel hints
to accomplish....

Can you suggest me some other ways to do this?


This is kind of critical.

Thanks

Tom Kyte
June 13, 2005 - 11:41 am UTC

how did you try to change it, perhaps that would help me understand what you are doing.

You cannot change the datatype of a column that has a NON NULL value so you must not have been using "alter" and your concept of "change the datatype", needs to be defined.

Lets say we wanted to change the datatype of empno from number to varchar2...


ops$tkyte@ORA9IR2> create table emp as select empno, ename, job from scott.emp;
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter table emp modify empno varchar2(10);
alter table emp modify empno varchar2(10)
                       *
ERROR at line 1:
ORA-01439: column to be modified must be empty to change datatype
 
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table new_emp as
  2  select cast( to_char(empno) as varchar2(20) ) empno, ename, job from emp;
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> drop table emp;
 
Table dropped.
 
ops$tkyte@ORA9IR2> rename new_emp to emp;
 
Table renamed.
 
Now, of course all of that can be done without redo, without undo.  And 13gig is pretty small, won't take long to copy (but the indexes and constraints and such could take a while) 

Which one is most efficient approach ???.

A reader, September 27, 2006 - 2:19 am UTC

Hi Tom,
We are using oracle 8i for our datawarehousing application.

We have 40 tables in our DW which contain one common column of numeric datatype. All these tables contain more than 20 million records.

Now, we were asked to alter the datatype of this column to varchar2 for all the tables.

I could see find some approaches discussed in this page.
Could you please suggest which can be most appropriate in our case?
What could be the risks involved with each of them?

Thanks in advance.

Tom Kyte
September 27, 2006 - 5:02 am UTC

as the move from number to varchar2 will necessarily double the storage required for this field - many rows would likely migrate.

So, I would be tempted to use a create table as select, drop the old table, rename new table and then index it.

That would be to avoid row migration.

It can be done nologging, parallel of course.

Change multiple columns at a shot

Hitesh, June 06, 2012 - 9:29 am UTC

Hello Tom,

I have to convert multiple columns from VARCHAR2 to CLOB. There are around 50 columns and tables are very huge.

To reduce downtime i am looking for any script by which together i can convert them to CLOB. Please suggest.

Thanks
Tom Kyte
June 06, 2012 - 11:31 am UTC

I would use a create table as select or dbms_redefinition to do this, not multiple alters.



A reader, January 25, 2013 - 5:33 am UTC


Why Oracle is so strict about altering columns with data presnt in the table.

Prashanti, May 07, 2015 - 7:19 am UTC

Hi Tom,

I have a column with current type as VARCHAR2 with all numeric values and I want to alter the type to NUMBER. As mentioned , if I know for sure that all values are numeric and altering will not result in any inconsistency at all , still I have to do all the workaround of creating another table/ column and copying the value and then altering.

What might be the reason that Oracle is so stringent about it, or is there any way where we can specify to force alter it.

Please let me know.

Thanks,
Prashanti

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library