SQL-LDR controlfile error, fixed width files
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: SQL-LDR controlfile error, fixed width files

  1. #1
    I can't seem to see my error:
    this is from the log file...

    Table SQRL_SALES_DETAIL, loaded when 37:40 != 0X39393939(character '9999')
    Insert option in effect for this table: APPEND
    TRAILING NULLCOLS option in effect

    Column Name Position Len Term Encl Datatype
    ------------------------------ ---------- ----- ---- ---- ---------------------
    CHECK_NUM 1:4 4 CHARACTER
    DATA_DATE 5:10 6 DATE TO_DATE(:DATA_DATE, 'MM/DD/YY')
    STORENUM 11:14 4 CHARACTER
    EMPNUM 15:23 9 CHARACTER
    EMPNAME 24:33 10 WHT CHARACTER
    SEATNUM 34:36 3 CHARACTER
    ITEMNUM 37:40 4 CHARACTER
    ITEMDESC 41:56 16 WHT CHARACTER
    FACTOR 57:62 6 CHARACTER
    OPRICE 63:72 10 CHARACTER
    SPRICE 73:82 10 CHARACTER
    Record 1: Rejected - Error on table SQRL_SALES_DETAIL, column DATA_DATE.
    ORA-00907: missing right parenthesis

    This is my control file:
    LOAD DATA
    INFILE 'all_close_data.dat'
    BADFILE 'all_close_data.bad'
    DISCARDFILE 'all_close_data.discard'
    APPEND
    INTO TABLE SQRL_SALES_DETAIL
    WHEN (37-40) != '9999'
    TRAILING NULLCOLS
    (CHECK_NUM POSITION(01:04) INTEGER EXTERNAL,
    DATA_DATE POSITION(05:10) DATE "TO_DATE(:DATA_DATE, 'MM/DD/YY')",
    STORENUM POSITION(11:14) INTEGER EXTERNAL,
    EMPNUM POSITION(15:23) INTEGER EXTERNAL,
    EMPNAME POSITION(24:33) CHAR TERMINATED BY WHITESPACE,
    SEATNUM POSITION(34:36) INTEGER EXTERNAL,
    ITEMNUM POSITION(37:40) INTEGER EXTERNAL,
    ITEMDESC POSITION(41:56) CHAR TERMINATED BY WHITESPACE,
    FACTOR POSITION(57:62) DECIMAL EXTERNAL,
    OPRICE POSITION(63:72) DECIMAL EXTERNAL,
    SPRICE POSITION(73:82) DECIMAL EXTERNAL
    )

    ==========
    what is wrong with my DATE formatter?
    Note, the data for that col looks like "10/31/00"

    thanks
    Share on Google+

  2. #2
    Join Date
    Jun 2000
    Posts
    417
    I haven't used sql loader much so this may be an incorrect observation, but I'll mention it anyway.

    Your control file is checking positions 5-10 for the date, that's 6 characters.

    Your date format is "mm/dd/yy" which is 8 characters.

    Unless sql loader ignores the '/' or something else I'm unaware of, it's only seeing "mm/dd/" when it try's to make the date. I'm not sure why that would generate a missing right paren error though, maybe I'm missing something :)
    Share on Google+

  3. #3
    thanks. I knew it was something simple... I was just staring at it too long.

    B.
    Share on Google+

  4. #4
    i fixed that... made it 'MMDDYY' but I still get the Missing Right Parenthesis" error.

    so back to square 1...
    Share on Google+

  5. #5
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Try this out,

    DATA_DATE POSITION(05:10) DATE "MM/DD/YY",


    I hope this would work for you.

    Sam
    Share on Google+

  6. #6
    Join Date
    Nov 2000
    Location
    Charlotte
    Posts
    88
    I would have agree with "sambavan". If you set up your control card with his fix you should be o.k.

    Let us know.
    Share on Google+

  7. #7
    YES... thanks a lot. I guess I was trying to change the data where for dates you only need to show the format.

    There isn't an example for this in the Oracle docs.

    thanks all.
    Share on Google+

Posting Permissions

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


Click Here to Expand Forum to Full Width