Oracle Certification, Database Administration, SQL, Application, Programming Reference Books
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)


Create Table Demo_Time_In_Sec As Select Rowid Row_Id, Time_In_Sec From Demo;
Update Demo Set Time_In_Sec = Null;
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);
Drop Table Demo_Time_In_Sec;

Quick Links:
Do you have an Oracle Question?

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

All the site contents are Copyright © and the content authors. All rights reserved.
All product names are trademarks of their respective companies.
The site is not affiliated with or endorsed by any company listed at this site.
Every effort is made to ensure the content integrity.  Information used on this site is at your own risk.
 The content on this site may not be reproduced or redistributed without the express written permission of or the content authors.