Hie all,
I have a question here,
Im trying to load a file into my oracle via sqlloader and cron it but im facing some problem


The files are delivered in to my folder with the name "TRAP_YYYYMMDD"
and the files are in .dat format without std deliminator.

Data:
------
10/10/2005 00:00:02 modbalrel - ok (msisdn = 60163050499, amount=1000, val_period = 7, seq_nb = 199370643 , balance = 1055, va
ldate = 08/11/2005) (48 ms)

10/10/2005 00:00:03 modbalrel - ok (msisdn = 60162433164, amount=1000, val_period = 7, seq_nb = 199370644 , balance = 987, val
date = 03/11/2005) (133 ms)
------

this is the ctrl file i written :
Load DATA
INFILE '/app/ITRAKPI/TRAP/TRAP_LOAD/TRAP_20051005.txt'
APPEND INTO TABLE trap_ra_k2_ain_balance
FIELDS TERMINATED BY ","
(
DATE_LOADED "(SELECT sysdate FROM dual)",
WEEK_NO "(sELECT to_char(sysdate,'WW') FROM dual)",
RELOAD_DATE position(01:10),
MSISDN position(46:56),
NEW_BALANCE position(118:122), <-- differs when the amount is 2 or 3 digit
RELOAD_AMOUNT position(66:69) <-- differs when the amount is 2 or 3 digit
)

---------

what i dun understand is, the positions differs especially the "NEW_BALANCE" AND "RELOAD_AMOUNT" when the digits are 3 and two, it mistakenly takes the = and , sign and uploads it to the column.
The second this is,
i need to write the ctl and also .sh file to change the date according to one day back and automatically load it,
anyone could help ?

1) SQL*Loader does NOT "mistakenly" take the wrong data. You specifed fixed positions, yet you have a variable length record. You must choose to create the source data in either fixed or variable length format (not both).


2) Try: DATE_LOADED "TRUNC(sysdate-1)",

LKBrwn_DBA ,
I do understand but then since u said that I can use variable length.. can u explain
i think im using fixed if im not mistaken..
as the data's position is not consistent, any idea ? if you count, the first row and second row position for certain data is not the same...

Thanks

Again,
In your controlfile you specify fixed positions, but the data file itself has variable length fields.

You must re-design your input file to either consistent fixed length fields or variable length fields with correct delimiters.