BTW, in case you were wondering, I'm formatting the number in the ctl
file because the sign precedes the number, and it's been my experience
that Oracle by default wants the sign after the number.
Here's a couple of sample records from the .dat file (sorry for the word
wrap!):
A040598471A,2004,01,20040617,0,00000,00000000,-000022.21,00000000,-00002
2.58,06172004,CF,,,,,
A040598471A,2004,03,20050215,1,07725,00000000,000057.73,00000000,000055.
00,02172005,,,,O,002754,
Thanks again for your input,
Don Kolenda
> -----Original Message-----
> From: Kolenda Don [mailto:oracledba-ezmlmshield-
> x31844761.[Email address protected]
> Sent: Tuesday, March 06, 2007 3:41 PM
> To: LazyDBA Discussion
> Subject: Problem using SQL*LOADER
>
> 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
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> To post a dba job: http://jobs.lazydba.com
> To Subscribe : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
Oracle LazyDBA home page