Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Negative numbers and rounding on insert

Negative numbers and rounding on insert

Negative numbers and rounding on insert

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 complete.

select * from t;

----------- ----------
-9994999.99 -9995000
+7777777.99 7777777.99

RE: Negative numbers and rounding on insert

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:


SQL> select * from t; MSG NUM_COL ----------- ---------- -9994999.99 -9995000 +7777777.99 7777777.99

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:


SQL> set numformat 99999999.99 SQL> select * from t; MSG NUM_COL ----------- ------------ -9994999.99 -9994999.99 +7777777.99 7777777.99

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close