I have a table lets say demo, and one of the columns of this table is time_in_sec is of type number(38).
This table has around 20,000 rows of data.  Now I want to modify the time_in_sec column of demo table to number (7,3) so that I can store values like 234.987

All the existing datas in this column are numbers up to maximum of 3 digit whenever I am trying to modify the column I get errors like ORA-01440: column to be modified must be empty to decrease precision or scale one solution to this was drop this column and add new column but I do not want to loose existing data.

How to achieve the changes I want ie changing the column type from number (38) to number (7,3)
 

CODE :

Create Table Demo_Time_In_Sec As Select Rowid Row_Id, Time_In_Sec From Demo;
Update Demo Set Time_In_Sec = Null;
Commit;
Alter Table Demo Modify (Time_In_Sec Number(7,3));
Update Demo U Set Time_In_Sec = (
Select Time_In_Sec From Demo_Time_In_Sec T
Where U.Rowid = T.Row_Id);
Commit;
Drop Table Demo_Time_In_Sec;
 

Quick Links:
Do you have an Oracle Question?

Best regards,
Oracle Database, SQL, Application, Programming Tips