Hey Don,
I know you are going to get answer form the group! But, I couldn't
help it! Mashed potatoes, brown gravy! Help me! Red Stripe beer!
Hehe.





Please take a few minutes to provide feedback on the quality of service you received from our staff. The Department of Education values your feedback as a customer. Commissioner of Education Jeanine Blomberg is committed to continuously assessing and improving the level and quality of services provided to you.Simply use the link below. Thank you in advance for completing the survey.


http://data.fldoe.org/cs/default.cfm?staff=Ed.[Email address protected]




-----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