OraFAQ Forum: Server Utilities » SQL Loader rounds up to the nearest whole number

Home » RDBMS Server » Server Utilities » SQL Loader rounds up to the nearest whole number (Oracle 9i on Solaris 2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL Loader rounds up to the nearest whole number [message #316362]

Thu, 24 April 2008 15:45

aliceg
Messages: 29
Registered: July 2007
Location: Los Angeles

Junior Member

I am loading an external file using SQL Loader.
The format of the columns in my table are all varchar(x).
One of the columns represents a cost value of a number with 2 decimals. However when the value is for example 15.98 the value loaded in the temporary table comes out to 16. Same holds true for any decimal value that is very close to the whole number.
I would appreciate any suggestions on how to go around this issue.
Thanks, --alice

Report message to a moderator

Re: SQL Loader rounds up to the nearest whole number [message #316367 is a reply to message #316362]

Thu, 24 April 2008 16:27

aliceg
Messages: 29
Registered: July 2007
Location: Los Angeles

Junior Member

I added the precision of 2 decimals to the temp table column that stores the cost. This fixed the issue.

Report message to a moderator

Re: SQL Loader rounds up to the nearest whole number [message #316404 is a reply to message #316362]

Fri, 25 April 2008 00:37

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

The format of the columns in my table are all varchar(x).
One of the columns represents a cost value of a number with 2 decimals.

Wow! you WILL have many problems: performances, inconsistencies, invalid numbers... for sure.

Regards
Michel

Report message to a moderator

Re: SQL Loader rounds up to the nearest whole number [message #316520 is a reply to message #316362]

Fri, 25 April 2008 08:32

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

aliceg wrote on Thu, 24 April 2008 16:45

The format of the columns in my table are all varchar(x).

One of the columns represents a cost value of a number with 2 decimals. However when the value is for example 15.98 the value loaded in the temporary table comes out to 16.

This of course is impossible. A VARCHAR column does not round values being put into it. How would it round strings that do not look like numbers?

Then...

Quote:

I added the precision of 2 decimals to the temp table column that stores the cost

How can you add a precision of 2 to a VARCHAR column?

Report message to a moderator

Re: SQL Loader rounds up to the nearest whole number [message #316551 is a reply to message #316404]

Fri, 25 April 2008 12:11

aliceg
Messages: 29
Registered: July 2007
Location: Los Angeles

Junior Member

Michel. I apologize for the post. I wanted to remove once
I realized which was the problem. I then posted that hasty reply and that's where the confusion.
So here we go:
1. The table had the cost column (and some other columns as number format). When I was reading the file I considered however that everything was as Character.
The issue was related to the fact that the number format of the cost column had no specified precision (took it from the mapping indicated by the file generator). This caused the number to be rounded to the next whole.
I then adjusted the precision to 2 (Number(17,2)) and this solved the problem.
Thanks, --Alice

Report message to a moderator

Previous Topic:	About Export/Import Utility
Next Topic:	EXP-00008: ORACLE error 904 encountered, Error while expoerting

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 24 23:03:26 CST 2024