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

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

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

    B.

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

    so back to square 1...

  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

  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.

  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.

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