1 Reply Latest reply: Mar 3, 2010 11:09 AM by 702767 RSS

    modify datatype of existing column

    702767
      Dear all,

      someone asked me if is possible to change datatype of an existing column in a table, so I created this simple case
      of test:



      ------------------------------------------------
      drop table change_type_table;

      ORA-00942: table or view does not exist

      create table change_type_table
      as
      select 2001 as anno
      from dual
      union
      select 2002 as anno
      from dual;

      Table created.

      desc change_type_table;

      TABLE change_type_table

      Name Null? Type
      ANNO NUMBER



      select * from change_type_table;

      ANNO
      2001
      2002

      2 rows selected.

      alter table change_type_table modify anno varchar2(4);

      ORA-01439: column to be modified must be empty to change datatype

      desc change_type_table;

      TABLE change_type_table

      Name Null? Type
      ANNO NUMBER



      select * from change_type_table;

      ANNO
      2001
      2002

      2 rows selected.

      ---------------------------------------------------------------------



      This explain that if the column you would change datatype is not empty it is not possible to change. You need to copy the content into another table, empty the column or table, change the datatype and insert data again.

      I hope this could be usefull to all.

      regards,

      Ivan Luminaria