Sql loader hanging

[anuragmin]

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

[anuragmin]

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]