-
Hi,
I am trying to load data using sql loader but it just hangs without inserting any records. It sends all the records to the bad file.
Below is the output i get on my command prompt when trying to execute sql loader.
Also please see the control file at the bottom.
C:\ofus>sqlldr80 userid=ofus/ofus@orcl control=loaddata.ctl log=loaddata.log
SQL*Loader: Release 8.0.5.0.0 - Production on Tue May 29 10:37:16 2001
(c) Copyright 1998 Oracle Corporation. All rights reserved.
SQL*Loader-307: Warning: conflicting lengths 20 and 4 specified for column TEST.
ACTUAL.
SQL*Loader-307: Warning: conflicting lengths 20 and 4 specified for column TEST.
ACTUALUS.
Commit point reached - logical record count 101
(This is where it hangs)
Control File
-------------
load data
infile 'test.txt'
badfile 'test.bad'
discardfile 'test.dcs'
into table test
(company position(01:10) char,
time position(11:20) char,
region position(21:30) char,
lineitem position(31:45) char,
actual position(46:65) decimal,
actualus position(66:85) decimal)
Sample Data
--------------
MBOC YR_1991 REGROLL BSPSIEB 1.000000 1.000000
ALLEP YR_1991 REGROLL COFYE 24.000000 24.000000
ALLEP YR_1991 REGROLL COLOP 48.000000 48.000000
ALLEP YR_1991 REGROLL COEPAM 8.000000 8.000000
ALLEP YR_1991 REGROLL COCAI 5.000000 5.000000
Please help.
Thanks
Anurag
-
Hi ,
I was able to figure out the problem. In the control file I had to specify external for the decimal.
Example
----------
Control File
-------------
load data
infile 'test.txt'
badfile 'test.bad'
discardfile 'test.dcs'
into table test
(company position(01:10) char,
time position(11:20) char,
region position(21:30) char,
lineitem position(31:45) char,
actual position(46:65) decimal external,
actualus position(66:85) decimal external)
What does the external parameter signify??
Now I have another problem. sql loader is rejecting the records which have a comma in them.
Example of the records are
ALLEP YR_1991 REGROLL ISGR 2,215.964133 2,090.143202
ALLEP YR_1991 REGROLL ISOEXP 1,237.364420 1,176.367821
Is there any way I can import the data without commas by making some changes in the control file rather than removing the commas from the datafile itself
The other question which I have is why does the sql loader not return you back to the command prompt after Commit point reached - logical record count 101
Please suggest.
Thanks
Anurag
[Edited by anuragmin on 05-29-2001 at 11:12 AM]
