oracle sql precision,scale ,insert calculate and drop

Question

table = mytable
temp col = tempcol
col = mycol

currently contains 5000 rows various values from 99999.99999 to 0.00001

I need to keep the data create a script to create a temp column,round the values to 7,3 update mycol to a null value, modify my column from 10,5 to 7,3 return the data to mycol, drop the temp column. Job done.

so far

SELECT mycol
INTO tempcol
FROM mytable

update mytable set mycol = null

alter table mytable modify mycol number (7,3)

SELECT tempcol
INTO mycol
FROM mytable

drop tempcol

can you please fill in the missing gaps are direct me to a solution.

Answer 1

Well first of all a NUMBER(10,5) can store results from -99999 to 99999 while NUMBER(7,3) interval is only [-9999,9999] so you will potentially encounter conversion errors. You probably want to change the column into a NUMBER(8,3).

Now your plan seems sound: you can not reduce the precision or the scale of a column while there is data in that column, so you will store data into a temporary column. I would do it like this:

SQL> CREATE TABLE mytable (mycol NUMBER(10,5));     
Table created

SQL> /* populate table */
  2  INSERT INTO mytable
  3     (SELECT dbms_random.value(0, 1e10)/1e5
  4        FROM dual CONNECT BY LEVEL <= 1e3);     
1000 rows inserted

SQL> /* new temp column */
  2  ALTER TABLE mytable ADD (tempcol NUMBER(8,3));     
Table altered

SQL> /* copy data to temp */
  2  UPDATE mytable
  3     SET tempcol = mycol,
  4         mycol = NULL;     
1000 rows updated

SQL> ALTER TABLE mytable MODIFY (mycol NUMBER(8,3));     
Table altered

SQL> UPDATE mytable
  2     SET mycol = tempcol;     
1000 rows updated

SQL> /* cleaning */
  2  ALTER TABLE mytable DROP COLUMN tempcol;
Table altered