Forum Stats

  • 3,875,512 Users
  • 2,266,931 Discussions
  • 7,912,240 Comments

Discussions

how to change the column datatype to number from varchar2 in oracle10g

612739
612739 Member Posts: 4
edited Mar 19, 2012 3:23AM in General Database Discussions
hi i created one table in oracle 10g in that table i took one column data type as varchar2
ex:desc user;
Name Null? Type
----------------------------------------- -------- ----------------------------
USERID NOT NULL VARCHAR2(5)
FIRSTNAME NOT NULL VARCHAR2(15)
LASTNAME VARCHAR2(15)
EMPID VARCHAR2(5)
USERNAME VARCHAR2(20)
PASSWORD NOT NULL VARCHAR2(20)
MOBILE NUMBER(12)
EMAILID VARCHAR2(20)

but i don't want to take userid as varchar i want to change it as number
for this i tried with alter statement like:
alter table user modify(UserId number(5));
but i'm getting this error:
column type incompatible with referenced column type
how to resolve this one, here i'm using oracle 10g rl2

Comments

  • 12cdb
    12cdb Member Posts: 2,971
    Try this command;
    SQL> delete from user;
    SQL>alter table user modify(UserId number(5) not null);
  • Laurent Schneider
    Laurent Schneider Member Posts: 5,220 Bronze Trophy
    SQL> create table "USER" (UserId varchar2(5))
    Table created.
    SQL> insert into "USER" values ('12345')
    1 row created.
    SQL> alter table "USER" add (UserId_tmp number(5))
    Table altered.
    SQL> update "USER" set userid_tmp=userid
    1 row updated.
    SQL> alter table "USER" set unused column userid
    Table altered.
    SQL> alter table "USER" rename column userid_tmp to userid
    Table altered.
    SQL> alter table "USER" drop unused columns
    Table altered.
    SQL> desc "USER"
    TABLE USER
     Name                                      Null?    Type                        
     ----------------------------------------- -------- ----------------------------
     USERID                                             NUMBER(5)                   
    but what a doubtful practice to use a reserved word like "USER" to name your table !
  • Laurent Schneider
    Laurent Schneider Member Posts: 5,220 Bronze Trophy
    Try this command;
    SQL> delete from user;
    SQL>alter table user modify(UserId number(5) not
    null);
    Well, do not try it on your productive database LOL
  • 611837
    611837 Member Posts: 156
    If you don't want to loose the data in the table, then first do a:

    create table userid_backup as (select * from userid);

    You can then re-populate the userid table (after you have dropped and recreated) from the userid_backup table, using TO_NUMBER(userid) to convert the userid from a VARCHAR2 to a NUMBER.

    cheers,

    R
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    Oh that greate solution.

    A little shortCut is here.
    create table TestTable(ColA varchar2(5));
    insert into TestTable values('12345');
    alter table TestTable add (ColATemp number(5));
    update TestTable set ColATemp = ColA;
    alter table TestTable drop column ColA;
    alter table TestTable rename column ColATemp to ColA;
  • RN
    RN Member Posts: 452 Bronze Badge
    If the table has half million records then


    update TestTable set ColATemp = ColA;

    will it be a good solution? Any performance issue? There's no index on this column.
  • 729338
    729338 Member Posts: 1,475
    This is a two year old thread. Open a new thread for your question?

    Regards
  • RN
    RN Member Posts: 452 Bronze Badge
    Oh. Ok.

    Thanks.
  • 917921
    917921 Member Posts: 1
    If the table has no data means table is empty.

    Alter table <Table Name>
    Modify <Column name> datatype

    If table has huge volume of data, then simply take a back up of the table then delete all records of the table and modify the column type and then restore the data from back up table with proper convert method.
  • 924756
    924756 Member Posts: 1
    This was very helpful Thanks for the information
This discussion has been closed.