This discussion is archived
9 Replies Latest reply: Nov 30, 2012 12:17 AM by user522961 RSS

modify data type of column

user522961 Newbie
Currently Being Moderated
Hi,
on 11g R2, is it possible to modify data type of column ?
How ?
Thank you.
  • 1. Re: modify data type of column
    Ashu_Neo Pro
    Currently Being Moderated
    It depends upon data availability on table. You can modify column datatype.
    But it's not a feature of 11g, I guess!
  • 2. Re: modify data type of column
    Hoek Guru
    Currently Being Moderated
    Yes, ofcourse it is possible:
    SQL> create table t (col number);
    
    Table created.
    
    SQL> alter table t modify col varchar2(10);
    
    Table altered.
    But ofcourse there's more to it and it is dependent on things like:
    - is there already data present in the column?
    - any constraints?
    Etc...
    See:
    {message:id=9360002}
  • 3. Re: modify data type of column
    Solomon Yakobson Guru
    Currently Being Moderated
    Only if table column is empty:
    SQL> create table tbl(c1 number,c2 number);
    
    Table created.
    
    SQL> insert into tbl values(1,1);
    
    1 row created.
    
    SQL> alter table tbl
      2  modify c2 date
      3  /
    modify c2 date
           *
    ERROR at line 2:
    ORA-01439: column to be modified must be empty to change datatype
    
    
    SQL> update tbl set c2=null
      2  /
    
    1 row updated.
    
    SQL> alter table tbl
      2  modify c2 date
      3  /
    
    Table altered.
    
    SQL> desc tbl
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------
     C1                                                 NUMBER
     C2                                                 DATE
    
    SQL> 
    SY.
  • 4. Re: modify data type of column
    LPS Journeyer
    Currently Being Moderated
    You can modify when the table is empty.....
  • 5. Re: modify data type of column
    Solomon Yakobson Guru
    Currently Being Moderated
    Solomon Yakobson wrote:
    Only if table column is empty:
    Hoek is right,

    only if it is empty and not constrained (except NOT NULL constraint). And we might run into issues if there is column default:
    SQL> create table tbl(c1 number,c2 number default 1);
    
    Table created.
    
    SQL> alter table tbl
      2  modify c2 date
      3  /
    modify c2 date
           *
    ERROR at line 2:
    ORA-02262: ORA-932 occurs while type-checking column default value expression
    
    
    SQL> alter table tbl
      2  modify c2 varchar2(10)
      3  /
    
    Table altered.
    
    SQL> 
    SY.
  • 6. Re: modify data type of column
    ranit B Expert
    Currently Being Moderated
    on 11g R2, is it possible to modify data type of column ?
    Hoek and SY has given nice explanations.
    I would like to add something to it...

    Changing Precision and Scale of a NUMBER type
    NUMBER(p,s)
    where p = Precision, s = Scale 
    1] To do the change in P and S, column should be empty.

    If it is NOT Empty, these rules apply -
    2] Scale can never be decreased.
    3] Scale can only be increased and that too only when Precision is increased by the same amount.

    Please rectify me if I'm wrong.

    Hope this Helps,
    Ranit B.

    Edited by: ranit B on Nov 29, 2012 10:21 PM
  • 7. Re: modify data type of column
    Solomon Yakobson Guru
    Currently Being Moderated
    ranit B wrote:
    Please rectify me if I'm wrong.
    Column should be empty if you are decreasing+* precision - scale or scale:
    SQL> create table tbl(n number(5,2),s varchar2(5));
    
    Table created.
    
    SQL> insert
      2    into tbl
      3    values(1,'123');
    
    1 row created.
    
    SQL> alter table tbl
      2    modify n number(4,2);
      modify n number(4,2)
             *
    ERROR at line 2:
    ORA-01440: column to be modified must be empty to decrease precision or scale
    
    
    SQL> alter table tbl
      2    modify n number(6,4);
      modify n number(6,4)
             *
    ERROR at line 2:
    ORA-01440: column to be modified must be empty to decrease precision or scale
    
    
    SQL> alter table tbl
      2    modify n number(6,3);
    
    Table altered.
    
    SQL> 
    Notice error message wording is wrong. Column n was number(5,2) and we increased both precision from 5 to 6 and scale from 2 to 4. Error is raised since we tried to decrease number of whole part digits from 5 - 2 =3 to 6 - 4 = 2.
    Now about VARCHAR2:
    SQL> alter table tbl
      2    modify s varchar2(4)
      3  /
    
    Table altered.
    
    SQL> alter table tbl
      2    modify s varchar2(2)
      3  /
      modify s varchar2(2)
             *
    ERROR at line 2:
    ORA-01441: cannot decrease column length because some value is too big
    
    
    SQL> 
    Oracle allows to decrease VARCHAR2 length as long as there are no column values longer than decreased length.

    SY.
  • 8. Re: modify data type of column
    ranit B Expert
    Currently Being Moderated
    Column should be empty if you are decreasing+* precision - scale or scale:
    Yes SY... I just saw your post after posting mine.

    And exactly the same research was done by me & i came to this conclusion.
    BTW, there's a lot hidden facts present apart from the theories mentioned in any manual/articles.

    And may be I'm wrong but in my 2yrs of Oracle experience, I realized that the more we experiment the more we learn. And it's not just the theories which will do all the work.

    Am I correct... SY?
    Please suggest... I've to learn a lot and go a long way.

    Ranit B.
  • 9. Re: modify data type of column
    user522961 Newbie
    Currently Being Moderated
    Thanks to all.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points