Re: Character to number conversion

Norazman Sahir wrote
>In my table, the column datatype is character.
>However, the data is always number

    alter table my_table modify

        ( my_column number(10, 2) );

If the column was a not null column, then it will still be a not null column. That is: in the modify part you should only mention the properties that need to be changed.

I am not sure if Oracle will let you do this alter table as your column is not empty. If you get an error, then you simply need to create a backup and delete all data from your table...:

    create table my_table_bak as

        (select * from my_table); -- create backup

    truncate table my_table; -- delete all data

    alter table my_table modify

        ( my_column number(10, 2) ); -- change it

    insert into my_table

        (select * from my_table_bak); -- copy all data

    drop table my_table_bak;

Of course, constraints might prohibit the truncate table command, so if you get any errors then be careful. As this is all DDL (Data Definition Language) you cannot undo the commands. Even worse: any command that was not yet committed will be committed automatically when you issue the create table command!

Just to be sure: note that the 2 in (10, 2) represents the precision of the size 10. So, you can store 8 digits before the decimal dot, and 2 after it. For example: 10.50 will *not* fit into a number(2, 2). Instead, you'd need a number(4, 2)

Arjan. Received on Mon Mar 08 1999 - 10:52:35 CST