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
