Changing data type of a column containing data in Oracle db

Question

I am using Oracle 19c. I have a table,which I need to change the data-type of one of its columns:

from number to number (24,8)

The column contains data, nearly 300.000 records and I am required to keep the data.

If I do this without truncating/deleting data:

Does it harm data integrity?
Does it effect the datatype of existing data?

The reason for this operation is that the column should have had 7 or 8 decimals but it has been limited to 4 decimal somehow, even though data type is number. Either my etl tool (informatica ) or oracle db has limited, I do not know.

thanks in advance

number is a less strict datatype than number(24, 8), you cannot perform such modification with alter table. If you have something that truncates a number it may be easier to adjust the datatype mapping on the reader/writer side. — astentx
– astentx, Commented Feb 7, 2023 at 9:11

Pugzly · Accepted Answer · 2023-02-07 22:50:24Z

1

Your problem doesn't appear to be with Oracle.


CREATE TABLE T1 (
 num NUMBER 
);

INSERT INTO T1 (num) VALUES (123.12345678);


SELECT * FROM T1;
NUM
123.12345678

answered Feb 7, 2023 at 22:50

Pugzly

9441 gold badge10 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

serkan Over a year ago

probably, Im missing sth in either in my query, or in informatica.

Collectives™ on Stack Overflow

Changing data type of a column containing data in Oracle db

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related