SQL Loader
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: SQL Loader

Hybrid View

  1. #1
    Join Date
    Aug 2001
    Posts
    12

    SQL Loader

    Hello All,

    I am having a dilenma with sql loader. Upon attempting to load data All of my data is being sent to my discard file.I am receiving the following error message in my log.

    rejected - Error on table FABBKTP, column FABBKTP_TRANS_DATE.
    ORA-00911: invalid character

    MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.

    Table FABBKTP:
    0 Rows successfully loaded.
    51 Rows not loaded due to data errors.
    0 Rows not loaded because all WHEN clauses were failed.
    0 Rows not loaded because all fields were null.



    This is a copy of my control file.

    LOAD DATA
    INFILE 'BankRecon2.prn'
    BADFILE 'bankrecon.bad'
    DISCARDFILE 'bankrecon.dsc'

    INTO TABLE FABBKTP APPEND
    WHEN FABKTP_CHECK_DEPOSIT_CODE != BLANKS AND
    FABBKTP_TRANS_DATE != BLANKS AND
    FABBKTP_AMT != BLANKS
    (
    FABBKTP_BANK_CODE CONSTANT 'HC',
    FABKTP_CHECK_DEPOSIT_CODE POSITION(0001:0008) CHAR "DECODE(TRIM(:FABKTP_CHECK_DEPOSIT_CODE), NULL, NULL, '', NULL, TRIM(:FABKTP_CHECK_DEPOSIT_CODE))",
    FABBKTP_CHECK_DEPOSIT_IND CONSTANT 'C' ,
    FABBKTP_TRANS_DATE POSITION(0011:0020) CHAR "TO_DATE(TRIM(:FABBKTP_TRANS_DATE),DD-MON-YY)",
    FABBKTP_TAPE_DATE SYSDATE,
    FABBKTP_AMT POSITION(0021:0031) CHAR "TO_NUMBER(TRIM(:FABBKTP_AMT))",
    FABBKTP_DESC CONSTANT 'Check Reconciliation From Bank Tape Load- July 2003')

    Please Help!
    Mind like water.

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    WHEN FABKTP_CHECK_DEPOSIT_CODE != BLANKS

    What is BLANKS supposed to be - number or varchar type of data? If number, what number is BLANKS, and if varchar, wouldn't you rather use 'BLANKS'?

  3. #3
    Join Date
    Aug 2001
    Posts
    12
    This is a copy of some of my Data.

    A0000001 11-JUL-03 196.00
    A0000002 07-JUL-03 337.00
    A0000003 14-JUL-03 1077.15
    A0000004 28-JUL-03 695.15
    A0000005 14-JUL-03 132.10
    A0000006 03-JUL-03 610.00
    A0000007 07-JUL-03 539.09
    A0000008 10-JUL-03 69.00
    A0000009 21-JUL-03 28.21
    A0000010 15-JUL-03 2.31
    A0000011 17-JUL-03 39.68
    A0000012 21-JUL-03 164.48
    A0000013 18-JUL-03 300.00
    A0000014 21-JUL-03 97.50
    A0000015 16-JUL-03 1027.42
    A0000016 15-JUL-03 217.94
    A0000017 16-JUL-03 666.39
    A0000018 15-JUL-03 575.00
    A0000019 16-JUL-03 416.26
    A0000020 15-JUL-03 72.79
    A0000021 16-JUL-03 1000.00
    A0000022 16-JUL-03 635.00
    A0000023 17-JUL-03 6.08
    A0000024 21-JUL-03 400.00
    A0000025 21-JUL-03 97.50
    A0000026 21-JUL-03 415.16
    A0000027 17-JUL-03 822.50
    A0000028 15-JUL-03 19556.47
    A0000029 15-JUL-03 61.67
    A0000030 16-JUL-03 2228.75
    A0000031 16-JUL-03 90.00
    A0000032 16-JUL-03 1749.27
    A0000033 16-JUL-03 356.00
    A0000034 14-JUL-03 213.59
    A0000036 22-JUL-03 1515.00
    A0000037 15-JUL-03 97.50
    A0000038 16-JUL-03 704.00
    A0000039 15-JUL-03 165.14

    Hope this helps!
    Last edited by micnash; 10-07-2003 at 09:04 PM.
    Mind like water.

  4. #4
    Join Date
    Oct 2002
    Location
    Ljubljana,Slovenia
    Posts
    28
    I think that you should check your controlfile definition. There is a mistake about positions.

    Data :
    1 2
    123456789012345678901234567890
    A0000001 11-JUL-03 196.00
    A0000002 07-JUL-03 337.00

    Position of the date data ( FABKTP_CHECK_DEPOSIT_CODE ) is ( 11:20) but it has to be 10:18.

    I hope it will help.
    Ale Orehek

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439

    Re: SQL Loader

    Originally posted by micnash
    This is a copy of my control file.
    .....
    FABBKTP_TRANS_DATE POSITION(0011:0020) CHAR "TO_DATE(TRIM(:FABBKTP_TRANS_DATE),DD-MON-YY)",
    .....
    From your data that you send in another message it is obvious that the field :FABBKTP_TRANS_DATE in the text file is on positions 11 through 19. With this in mind and according to the date format you specified in your control file it is strange why you specified positions 11 through 20 for that field in jour ctl, isn't it. Can you change the above line into:

    FABBKTP_TRANS_DATE POSITION(0011:0019) CHAR "TO_DATE(:FABBKTP_TRANS_DATE,DD-MON-YY)",

    and try again? IS is still complaining about the column FABBKTP_TRANS_DATE (because your specification for field FABBKTP_AMT looks suspicious to me too)?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Aug 2001
    Posts
    12
    Gentlemen,

    Thank you for pointing out my positioning error, however after making the corrections to my control file, I am still receiving the same error message regarding the FABBKTP_TRANS_DATE column.

    Thanks in Advance,
    Mind like water.

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