3 Replies Latest reply: Sep 19, 2013 12:00 PM by Frank Kulash RSS

    Datatype chnage from number to varchar


      hi ,


      I have to change the datatype for a column ( From number to varchar), it is an existing table so  data is there.


      i am runnning alter command to do so but it is asking me to make all the values null before processing.


      Is there any way to do this without making values null.



      As per me one solution is


      1) to get data copied to another table , make the column NULL and alter ... load the data back to the table



      is this the only option ?

        • 1. Re: Datatype chnage from number to varchar

          Could do this to save yourself from creating a whole new table:

          copy the data into a new column in the same table

          set the original column to null

          change the data type

          copy data back to original column

          drop new column

          • 2. Re: Datatype chnage from number to varchar

            You can add a new column of the appropriate type.  Copy from old to new column.  Then drop the old column (or make it invisible).

            • 3. Re: Datatype chnage from number to varchar
              Frank Kulash



              First of all, make sure you really want to lose the NUMBER column.  Why are you changing the column?  For example, if the column is temperature, and you're changing it so that you can enter values like '68 F' or '20 C', then maybe you should leave the temperature column as a NUMBER, and add a new column, temperature_scale VARCHAR2 (1).  (Better yet, convert all the temperatures to one system on input, perhaps using a trigger or a virtual column, and save the original measurment, like '68 F' in a separate column, if you really need to know how it was entered.)


              Given that you really do need to change the column to a VARCHAR2, then one way is

              1) add a new VARCHAR2 column

              2) populate this column from the existing NUMBER column

              3) add a NOT NULL constraint to the new column (if needed)

              4) drop the original NUMBER column

              5) change the name of the new column to what the original column was (if wanted)


              If you drop the whole table, then you lose all its indexes, triggers and grants.  If you just add and drop columns, you don't lose those things.