Home » SQL & PL/SQL » SQL & PL/SQL » Changing datatype for the existing column
Changing datatype for the existing column [message #215080] Fri, 19 January 2007 03:59 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: How to produce trace file?
Next Topic: Optimizer Choosing a Path with higher cost
Goto Forum:
  


Current Time: Thu Dec 08 16:03:47 CST 2016

Total time taken to generate the page: 0.06402 seconds