sql loader rounding of decimals while loading--Urgent.Help!!!!

Hi,
Here is the sample data which I am trying to load using sql loader.

ALLEP ,1996,REGROLL ,ISTUSPI , 43522.886393, 34635.613098,YR
ALLEP ,1996,REGROLL ,ISOCS , 11489.679547, 10619.922145,YR
ALLEP ,1996,REGROLL ,ISNGS , 8335.460687, 8144.415170,YR
ALLEP ,1996,REGROLL ,ISNGLS , 462.127086, 424.456126,YR


Here is the control file I am using to load this data

load data
infile 'testa'
badfile 'ofasweb.bad'
discardfile 'ofasweb.dcs'
truncate
into table testme
fields terminated by ","
(company,
time,
region,
lineitem,
actual
nullif (actual = BLANKS),
actualus
nullif (actualus = BLANKS),
year_quarter)

and here is what it loads
COMPANY TIME REGION LINEITEM ACTUAL ACTUALUS YEAR_
---------- ---------- ---------- --------------- ---------- ---------- -----
ALLEP 1996 REGROLL ISTUSPI 43522.8864 34635.6131 YR
ALLEP 1996 REGROLL ISOCS 11489.6795 10619.9221 YR
ALLEP 1996 REGROLL ISNGS 8335.46069 8144.41517 YR
ALLEP 1996 REGROLL ISNGLS 462.127086 424.456126 YR

Notice that the fields in bold have been rounded off.

I dont want to do this since this is a financial database.

Please suggest how.

Thanks
Anurag

How is your table defined?
Are you sure you are displaying all digits after the decimal?

Hi,
Here is the table definition.
As you can see that the actual and actualus columns have a decimal size of 6. But after loading it has only 4 decimals



SQL> desc testme
Name Null? Type
----------------------------------------- -------- ----------------------------
COMPANY VARCHAR2(10)
TIME VARCHAR2(10)
REGION VARCHAR2(10)
LINEITEM VARCHAR2(15)
ACTUAL NUMBER(20,6)
ACTUALUS NUMBER(20,6)
YEAR_QUARTER VARCHAR2(5)

Also if you see my last sample record in the previous post it is getting converted properly. Its only a few records.


Thanks
Anurag

Hi

i'v used sqlloader many times and never ren into this one, but the diff between you and me is that i never ren into a culomn defind as NUMBER(20,6).
try
ACTUAL NUMBER
ACTUALUS NUMBER

and see what happens

Quote:

---

Originally posted by shawish_sababa
Hi

i'v used sqlloader many times and never ren into this one, but the diff between you and me is that i never ren into a culomn defind as NUMBER(20,6).
try
ACTUAL NUMBER
ACTUALUS NUMBER

and see what happens

---

Hi,
I tried that also and still get the same result. It is very very important for me to preserve the decimals.


SQL> desc testme
Name Null? Type
----------------------------------------- -------- -------------------------
COMPANY VARCHAR2(10)
TIME VARCHAR2(10)
REGION VARCHAR2(10)
LINEITEM VARCHAR2(15)
ACTUAL NUMBER
ACTUALUS NUMBER
YEAR_QUARTER VARCHAR2(5)

SQL> select * from testme;

COMPANY TIME REGION LINEITEM ACTUAL ACTUALUS YEAR_
---------- ---------- ---------- --------------- ---------- ---------- -----
ALLEP 1996 REGROLL ISTUSPI 43522.8864 34635.6131 YR
ALLEP 1996 REGROLL ISOCS 11489.6795 10619.9221 YR
ALLEP 1996 REGROLL ISNGS 8335.46069 8144.41517 YR
ALLEP 1996 REGROLL ISNGLS 462.127086 424.456126 YR

Please suggest.

Thanks
Anurag

Hi All,
I was able to fix the problem from help with Oracle support. This is what they said.

"the number itself is not being truncated, but the default column width for Number is set to 10. From SQL*Plus, use the command SET NUM nn where nn is the desired column width. Once doing so, you should be able to see all the numbers in that field"

My question to them now is
"why does the number width gets set to 10. Shouldnt it be based on
the table description. If it is set to Number(20) then it should be 20. I dont
want to set the number width for a sql session every time and also I dont want
to set it in the login.sql file and set it for all the sessions.
Shouldnt it be dynamic.
Also how do I find out the current settings of the number width"

Now I am waiting for their response and will post it when they get back to me.

Thanks
Anurag

Quote:

---

Originally posted by marist89
Are you sure you are displaying all digits after the decimal?

---

hmmm, imagine that...

Quote:

---

Originally posted by marist89

Quote:

---

Originally posted by marist89
Are you sure you are displaying all digits after the decimal?

---

hmmm, imagine that...

---

Hi Jeff,
Thanks


I could not understand what you meant by Are you sure you are displaying all digits after the decimal? ta that time.


Anyways any Ideas why shouldnt it be dynamic.

Thanks
Anurag

10 is the default width for this type. You can change the defaults either in your login.sql or glogin.sql initialization script for sqlplus.