What is this Site!!

All about Real time requirements in Orale Stay Tune!!

Sunday, January 6, 2008

Modifying A Column Of A Table


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;

2 comments:

12345 said...

Good blog thanks for sharing this informative article.
Oracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
Oracle Fusion Financials Online Training
Big Data and Hadoop Training In Hyderabad

Anonymous said...

Such a nice blog, I really like what you write in this blog, I also have some relevant Information about Best HR Training In Hyderabad | Hr training institute in Hyderabad! if you want more information.
Oracle Fusion HCM Online Training
Oracle Fusion Financials Online Training