9 Replies Latest reply: Nov 30, 2012 2:17 AM by user522961 RSS

    modify data type of column

    user522961
      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
          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
            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
              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
                You can modify when the table is empty.....
                • 5. Re: modify data type of column
                  Solomon Yakobson
                  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
                    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
                      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
                        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
                          Thanks to all.