My sqlldr only loads 1 record
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: My sqlldr only loads 1 record

  1. #1
    Join Date
    Mar 2014
    Posts
    3

    Unhappy My sqlldr only loads 1 record

    Hi,

    I am using a set ctl file that has been used many times before. Now, it only loads 1 tran out of 203. The input file is a .dat file. I have triple checked the positioning of the fields, all are on the mark. This process has run in the past with no issues.

    It could only be the input file in some way, I think.

    Any input to resolve would be very helpful. THX


    the ctl:

    LOAD DATA
    Infile "fapmscd_03262014.dat"
    badfile "fapmscd_03262014.bad"
    discardfile "fapmscd_03262014.dis"
    INTO TABLE FATCARD APPEND
    WHEN (22) = 'C'
    (FATCARD_REC_TYPE CONSTANT '2',
    FATCARD_USER_ID CONSTANT 'FIMSMGR',
    FATCARD_ACTIVITY_DATE sysdate,
    FATCARD_CARD_TYPE_CODE constant 'MC',
    FATCARD_SYSTEM_ID CONSTANT 'FAPCARD',
    FATCARD_SYSTEM_TIME_STAMP constant 'HOLD',
    FATCARD_DUP_OVERRIDE_IND CONSTANT 'N',
    FATCARD_CAMPUS_CODE CONSTANT '00',
    FATCARD_CARD_CODE POSITION(01:016) CHAR,
    FATCARD_DR_CR_IND CONSTANT '-',
    FATCARD_TRANS_TYPE_CODE POSITION(025:025) CHAR,
    FATCARD_TRANS_AMT POSITION(028:047) ":FATCARD_TRANS_AMT/100",
    FATCARD_TRANS_REF_NUM POSITION(048:070) CHAR,
    FATCARD_VEND_NAME POSITION(073:098) CHAR,
    FATCARD_VEND_CITY POSITION(105:115) CHAR,
    FATCARD_VEND_STATE POSITION(127:129) CHAR,
    FATCARD_VEND_NATN_CODE POSITION(132:134) CHAR,
    FATCARD_SIC_CODE POSITION(137:140) CHAR,
    FATCARD_BANk_POST_DATE POSITION(143:150) date "MMDDYYYY",
    FATCARD_VEND_TRANS_DATE POSITION(151:158) DATE "MMDDYYYY")

    INTO TABLE FATCARD APPEND
    WHEN (22) = 'D'
    (FATCARD_REC_TYPE CONSTANT '2',
    FATCARD_USER_ID CONSTANT 'FIMSMGR',
    FATCARD_ACTIVITY_DATE sysdate,
    FATCARD_CARD_TYPE_CODE constant 'MC',
    FATCARD_SYSTEM_ID CONSTANT 'FAPCARD',
    FATCARD_SYSTEM_TIME_STAMP constant 'HOLD',
    FATCARD_DUP_OVERRIDE_IND CONSTANT 'N',
    FATCARD_CAMPUS_CODE CONSTANT '00',
    FATCARD_CARD_CODE POSITION(01:016) CHAR,
    FATCARD_DR_CR_IND CONSTANT '+',
    FATCARD_TRANS_TYPE_CODE POSITION(025:025) CHAR,
    FATCARD_TRANS_AMT POSITION(028:047) ":FATCARD_TRANS_AMT/100",
    FATCARD_TRANS_REF_NUM POSITION(048:070) CHAR,
    FATCARD_VEND_NAME POSITION(073:098) CHAR,
    FATCARD_VEND_CITY POSITION(105:115) CHAR,
    FATCARD_VEND_STATE POSITION(127:129) CHAR,
    FATCARD_VEND_NATN_CODE POSITION(132:134) CHAR,
    FATCARD_SIC_CODE POSITION(137:140) CHAR,
    FATCARD_BANk_POST_DATE POSITION(143:150) date "MMDDYYYY",
    FATCARD_VEND_TRANS_DATE POSITION(151:158) DATE "MMDDYYYY")

    The log:


    SQL*Loader: Release 11.2.0.2.0 - Production on Fri Mar 28 16:22:10 2014

    Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

    Control File: ././fapmscd_1.ctl
    Data File: ././fapmscd_03262014.dat
    Bad File: fapmscd_03262014.bad
    Discard File: fapmscd_03262014.dis
    (Allow all discards)

    Number to load: ALL
    Number to skip: 0
    Errors allowed: 50
    Bind array: 64 rows, maximum of 256000 bytes
    Continuation: none specified
    Path used: Conventional

    Table "FIMSMGR"."FATCARD", loaded when 22:22 = 0X43(character 'C')
    Insert option in effect for this table: APPEND

    Column Name Position Len Term Encl Datatype
    ------------------------------ ---------- ----- ---- ---- ---------------------
    FATCARD_REC_TYPE CONSTANT
    Value is '2'
    FATCARD_USER_ID CONSTANT
    Value is 'FIMSMGR'
    FATCARD_ACTIVITY_DATE SYSDATE
    FATCARD_CARD_TYPE_CODE CONSTANT
    Value is 'MC'
    FATCARD_SYSTEM_ID CONSTANT
    Value is 'FAPCARD'
    FATCARD_SYSTEM_TIME_STAMP CONSTANT
    Value is 'HOLD'
    FATCARD_DUP_OVERRIDE_IND CONSTANT
    Value is 'N'
    FATCARD_CAMPUS_CODE CONSTANT
    Value is '00'
    FATCARD_CARD_CODE 1:16 16 CHARACTER
    FATCARD_DR_CR_IND CONSTANT
    Value is '-'
    FATCARD_TRANS_TYPE_CODE 25:25 1 CHARACTER
    FATCARD_TRANS_AMT 28:47 20 CHARACTER
    SQL string for column : ":FATCARD_TRANS_AMT/100"
    FATCARD_TRANS_REF_NUM 48:70 23 CHARACTER
    FATCARD_VEND_NAME 73:98 26 CHARACTER
    FATCARD_VEND_CITY 105:115 11 CHARACTER
    FATCARD_VEND_STATE 127:129 3 CHARACTER
    FATCARD_VEND_NATN_CODE 132:134 3 CHARACTER
    FATCARD_SIC_CODE 137:140 4 CHARACTER
    FATCARD_BANK_POST_DATE 143:150 8 DATE MMDDYYYY
    FATCARD_VEND_TRANS_DATE 151:158 8 DATE MMDDYYYY

    Table "FIMSMGR"."FATCARD", loaded when 22:22 = 0X44(character 'D')
    Insert option in effect for this table: APPEND

    Column Name Position Len Term Encl Datatype
    ------------------------------ ---------- ----- ---- ---- ---------------------
    FATCARD_REC_TYPE CONSTANT
    Value is '2'
    FATCARD_USER_ID CONSTANT
    Value is 'FIMSMGR'
    FATCARD_ACTIVITY_DATE SYSDATE
    FATCARD_CARD_TYPE_CODE CONSTANT
    Value is 'MC'
    FATCARD_SYSTEM_ID CONSTANT
    Value is 'FAPCARD'
    FATCARD_SYSTEM_TIME_STAMP CONSTANT
    Value is 'HOLD'
    FATCARD_DUP_OVERRIDE_IND CONSTANT
    Value is 'N'
    FATCARD_CAMPUS_CODE CONSTANT
    Value is '00'
    FATCARD_CARD_CODE 1:16 16 CHARACTER
    FATCARD_DR_CR_IND CONSTANT
    Value is '+'
    FATCARD_TRANS_TYPE_CODE 25:25 1 CHARACTER
    FATCARD_TRANS_AMT 28:47 20 CHARACTER
    SQL string for column : ":FATCARD_TRANS_AMT/100"
    FATCARD_TRANS_REF_NUM 48:70 23 CHARACTER
    FATCARD_VEND_NAME 73:98 26 CHARACTER
    FATCARD_VEND_CITY 105:115 11 CHARACTER
    FATCARD_VEND_STATE 127:129 3 CHARACTER
    FATCARD_VEND_NATN_CODE 132:134 3 CHARACTER
    FATCARD_SIC_CODE 137:140 4 CHARACTER
    FATCARD_BANK_POST_DATE 143:150 8 DATE MMDDYYYY
    FATCARD_VEND_TRANS_DATE 151:158 8 DATE MMDDYYYY


    Table "FIMSMGR"."FATCARD":
    0 Rows successfully loaded.
    0 Rows not loaded due to data errors.
    1 Row not loaded because all WHEN clauses were failed.
    0 Rows not loaded because all fields were null.


    Table "FIMSMGR"."FATCARD":
    1 Row successfully loaded.
    0 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.


    Space allocated for bind array: 23424 bytes(64 rows)
    Read buffer bytes: 1048576

    Total logical records skipped: 0
    Total logical records read: 1
    Total logical records rejected: 0
    Total logical records discarded: 0

    Run began on Fri Mar 28 16:22:10 2014
    Run ended on Fri Mar 28 16:22:16 2014

    Elapsed time was: 00:00:05.38
    CPU time was: 00:00:00.02

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,478

    Cool

    Post the first 5 lines of your data file.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Mar 2014
    Posts
    3
    1111111102718478 d m 0000000000000000127955432864081000527350204 stamps.com, inc. 855-608-2677 ca usa 7399 0324201403222014
    1111111102718478 d m 0000000000000002052855417344082120828937409 gold standard enterprises 847-5813100 il usa 5921 0324201403222014
    1111111102718478 d m 0000000000000000849805410194082105207836018 staples inc 800-3333330 ca usa 5111 0324201403222014
    1111111123234234 d m 0000000000000002419805227024082500141137113 mmcp llc columbia il usa 7011 0324201403222014
    1111111123054194 d m 0000000000000004853505410194082105191506676 staples inc 800-3333330 ca usa 5111 0324201403222014
    1111111102718478 c m 0000000000000002009645180134080051600075568 waramaug ib phoenix llc phoenix az usa 3504 0324201403212014
    1111111123234234 d m 0000000000000001058325247804082000789010267 jjs inc lansing il usa 3562 0324201403212014
    1111111123234234 d m 0000000000000000780055310204081206831800075 semper fi printing llc 08476020335 il usa 7333 0324201403212014
    1111111123234234 d m 0000000000000000253805416014080141002721549 wal-mart stores wheeling il usa 5411 0324201403212014
    1111111123234234 d m 0000000000000001437205416014080141016777057 wal-mart stores wheeling il usa 5300 0324201403212014
    1111111123234234 d m 0000000000000004989555541754080286202000030 optima, inc. 05083932727 ma usa 5964 0324201403212014
    1111111123054202 d m 0000000000000078600085247714080980002779177 fathom seo llc valley view oh usa 8999 0324201403212014
    1111111123054202 d m 0000000000000058563385247714080980002779185 fathom seo llc valley view oh usa 8999 0324201403212014
    1111111123054202 d m 0000000000000041680085247714080980002779193 fathom seo llc valley view oh usa 8999 0324201403212014

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,015
    You have "Number to skip: 0" you may want to let it know that its ok
    to skip rows, since the bad rows will be sent to a separate file.
    this space intentionally left blank

  5. #5
    Join Date
    Mar 2014
    Posts
    3
    Finally got this thing to work. Appeared the original input file downloaded was corrupt in some way. The renaming from a .txt to .dat carried over the corrupted part of the file. I created a standalone .dat file and then cut/paste the data. The sqlldr worked and all 203 records were read successfully. This one was a weird one.

    Thanks to all who have responded to this. :-)

  6. #6
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,478

    Cool

    Quote Originally Posted by gandolf989 View Post
    You have "Number to skip: 0" you may want to let it know that its ok
    to skip rows, since the bad rows will be sent to a separate file.
    Not ok...
    "Number to skip: 0" refers to the number of records to skip BEFORE to begin loading the data.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  7. #7
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,015
    Quote Originally Posted by LKBrwn_DBA View Post
    Not ok...
    "Number to skip: 0" refers to the number of records to skip BEFORE to begin loading the data.
    Well if you have to be technical about it...
    this space intentionally left blank

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