Changing datatype for the existing column [message #215080] |
Fri, 19 January 2007 03:59  |
shahnazurs
Messages: 240 Registered: June 2005 Location: India
|
Senior Member |
|
|
Hi,
Could you please let me know if any possibbiliy of changing datatype of existing column to another datatype
From
emp
---
empno number(6)
ename varchar2(20)
sal number(10)
To
emp
---
empno varchar2(7)
ename varchar2(20)
sal number(10)
Thanks
|
|
|
Re: Changing datatype for the existing column [message #215085 is a reply to message #215080] |
Fri, 19 January 2007 04:24   |
dmitry.nikiforov
Messages: 723 Registered: March 2005
|
Senior Member |
|
|
Yes, you can, but the column has to be empty if you change datatype:
SQL> create table t (id number(6));
Table created.
SQL> insert into t select rownum from dict where rownum <=3;
3 rows created.
SQL> desc t
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(6)
SQL> alter table t modify (id varchar2(7));
alter table t modify (id varchar2(7))
*
ERROR at line 1:
ORA-01439: column to be modified must be empty to change datatype
SQL> truncate table t;
Table truncated.
SQL> alter table t modify (id varchar2(7));
Table altered.
And by the way this is not good idea to change NUMBER to CHAR.
Rgds.
|
|
|
Re: Changing datatype for the existing column [message #215108 is a reply to message #215080] |
Fri, 19 January 2007 06:05   |
shahnazurs
Messages: 240 Registered: June 2005 Location: India
|
Senior Member |
|
|
Thanks for your suggestion...
let us say if i want to change mgr from number to varchar2
update emp set mgr=null;
alter table emp
modify mgr varchar2(7);
this will also work!
but i need to preserve data before changing the field mgr to null...
Thanks.
|
|
|
Re: Changing datatype for the existing column [message #215122 is a reply to message #215108] |
Fri, 19 January 2007 07:02  |
dmitry.nikiforov
Messages: 723 Registered: March 2005
|
Senior Member |
|
|
For example
SQL> desc dept1
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
PARENT_DEP NUMBER
SQL> select deptno from dept1;
DEPTNO
----------
10
20
30
SQL> create table t1 as select * from dept1;
Table created.
SQL> truncate table dept1;
Table truncated.
SQL> alter table dept1 modify(deptno varchar2(2));
Table altered.
SQL> insert into dept1 select * from t1;
3 rows created.
SQL> select * from dept1;
DE DNAME LOC PARENT_DEP
-- -------------- ------------- ----------
10 ACCOUNTING NEW YORK 20
20 RESEARCH DALLAS 20
30 SALES CHICAGO 20
SQL> desc dept1
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO VARCHAR2(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
PARENT_DEP NUMBER
Rgds
|
|
|