I'm reading data in from an external table, but the negative numbers are rounding. I don't want them to.

So, I set up a simple table and started inserting my values to see if I could understand how and why the numbers are not inserting the way I want them to.

Here simply is my 2 column table - a way to display what was entered and what was stored.

NOTE: the values I want to insert are those as they come in as strings in my file: -9994999.99 and +7777777.99.

I have reviewed and reviewed the documentation and I can't find anything specific for how negative numbers are handled. And, it seems like once I get to a negative number with 9 significant digits, it rounds. I have also tried number(10,2), number(11,2), and number(12,2) with the same results.

Can someone help me understand what datatype to define for this column to read in the data and store it (without rounding)?

Any help is greatly appreciated.

drop table t;

Table dropped.

create table t ( msg varchar2(11), num_col number(9,2) );

Table created.

insert into t (msg,num_col) values ( '-9994999.99', to_number('-9994999.99') );

1 row created.

insert into t (msg,num_col) values ( '+7777777.99', to_number('+7777777.99') );

1 row created.

commit;

Commit complete.

select * from t;

MSG NUM_COL
----------- ----------
-9994999.99 -9995000
+7777777.99 7777777.99

Oracle isn't truncating your numbers. Rather, it is having difficulty displaying your output because the minus sign takes an extra character in the output field. So, if I use the default sql*plus format, I get the same results as you:


But if I change the default format to provide an extra space for the minus sign, I get the values you actually inserted into your table: