Here you go
Decimal External or Integer External, I always use Decimal
I created a table called tony for the test.
SQL> desc tony
Name Null? Type
----------------------------------------- --------
----------------------------
ACCOUNT_NUMBER VARCHAR2(1)
TRANSACTION_DATE DATE
WATER_VOLUME NUMBER(8)
WATER_CHARGE NUMBER(8,2)
SEWER_VOLUME NUMBER(8)
SEWER_CHARGE NUMBER(8,2)
PROCESS_DATE DATE
And used this ctl file.
LOAD DATA
INFILE *
INTO TABLE tony
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
ACCOUNT_NUMBER,
TRANSACTION_DATE DATE "YYYYMMDD",
WATER_VOLUME DECIMAL EXTERNAL,
WATER_CHARGE DECIMAL EXTERNAL,
SEWER_VOLUME DECIMAL EXTERNAL,
SEWER_CHARGE DECIMAL EXTERNAL,
PROCESS_DATE DATE "MMDDYYYY"
)
BEGINDATA
1,20070101,-123,-123.45,-123,-123.45,01012007
2,20070101,123,123.45,-123,-123.45,01012007
And I got these Results.
SQL> select * from tony;
A TRANSACTI WATER_VOLUME WATER_CHARGE SEWER_VOLUME SEWER_CHARGE
PROCESS_D
- --------- ------------ ------------ ------------ ------------
---------
1 01-JAN-07 -123 -123.45 -123 -123.45
01-JAN-07
2 01-JAN-07 123 123.45 -123 -123.45
01-JAN-07
-----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