Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2005
    Location
    Kuala Lumpur, MY
    Posts
    108

    Unanswered: .ctl file and .sh

    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 ?

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by shatishr
    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)",

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Aug 2005
    Location
    Kuala Lumpur, MY
    Posts
    108
    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

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by shatishr
    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...

    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.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •