Everyone - I am about to pull out my hair (at least) or stop by the
liquor store on the way home!

Scenario: there are two columns in the .dat file I am using with sqlldr,
both defined in my table as NUMBER; "Volume" is 8,0; "Charged" is 9,2.
The data in the source file can be positive or negative. I have over
50K rows in my source file.

If the numbers are negative, the sign precedes the number (i.e.,
-99999999).

My .ctl file is as follows:
------------------------------
LOAD DATA
INFILE 'c:\gws28f.dat'
INTO TABLE GWS1.GWS28F
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
ACCOUNT_NUMBER,
BILL_YEAR,
BILL_CYCLE,
TRANSACTION_DATE DATE "YYYYMMDD",
TRANSACTION_TYPE,
POSTING_SEQUENCE_NO,
WATER_VOLUME "MI99999999",
WATER_CHARGE "MI999,999.99",
SEWER_VOLUME "MI99999999",
SEWER_CHARGE "MI999,999.99",
PROCESS_DATE DATE "MMDDYYYY",
JOURNAL_CODE,
TYPE_CODE,
GOVT_CODE,
TELLER_CODE,
OCR_SEQUENCE_CODE,
LOGON_ID
)
------------------------------
I looked up the number format in the Oracle doc. "MI" is supposed to
explain where the minus sign is (at least the way I interpreted it).

Right now, none of the rows get inserted. The error message in the .log
file is, "Record 1: Rejected - Error on table GWS1.GWS28F. ORA-00913:
too many values." Of course, the Oracle doc has no good explanation for
their cryptic message.

I have tried using "to_number(water_volume,'S99999999')". I get an
error message about sqlldr expecting " or (. I have spent most of the
day trying a myriad of other combinations. Can anyone point me in the
right direction, please?

TIA,

Don Kolenda


Oracle LazyDBA home page