How Oracle modifies field types (2)

At the time of development, I encountered the need to change the field type of the data table from number to varchar, but the field has a value.

Use ALTER TABLE t-name modify CNAME newType;

Words, at that time on the internet did not find the right solution, very distressed!

Today in the blog Park to see the solution to this problem, very nice, share!

You cannot change the field type when the field you want to modify has a value :

two ways to solve the problem :

1, > Added a column, the column type is the same as the new type to be modified;

> assigns the value of the old column to the new column (forcing type conversions);

> Update tname Set Cname_new=cast (cname_old as new type)

> Delete old columns.

This method changes the position of the column in the table.

2, > added a column, the column type is the same as the old column;

> assigns the value of the old column to the new column, and the value of the old column becomes null;

Update tname set cname_new =cname_old;

> Change the field type of the old column (null at this time, can be modified)

ALTER TABLE tname modify Cname_old newType;

> assigns the value of the new column to the old column;

Update Tname set cname_old=cname_new;

> Delete new columns.

Related SQL statements:

View Oracle Version : SELECT * from V$version;

Update Field name : ALTER TABLE t-name rename column cname_old to cname_new;

new field : ALTER TABLE t-name add CNAME type;

add field and assign value : ALTER TABLE t-name add CNAME type default value;

Delete field : ALTER TABLE t-name drop column cname;

Modify field value : Update tname set Cname=value where ...

Modify field type : ALTER TABLE t-name modify CNAME type;

Reference: http://www.cnblogs.com/david-zhang-index/archive/2012/04/10/2441015.html