Hello,
Please see the log file attached. Have also attached the create table script, data file and the bad and discard files after running.
Sqlldr client in Windows version -
SQL*Loader: Release 11.2.0.1.0 - Production
The CTL file has two INTO TABLE clauses because of the nature of the data. The data shown is a subset of the data in the real-world file. We are only interested in the rows having the word "Index" as part of the first column.
The problem we are facing is, depending on which INTO TABLE clause appears first in the CTL file, the rows corresponding to its WHEN CLAUSE would be inserted and the rest are getting discarded.
1. Create table statement : create table dummy_load (name varchar2(30), rate number, effdate date);
2. Data file for simulating this issue contains the below 10 rows. Save this as name.dat. The intention is to load all the rows through one CTL file. The real-world file would have additional rows before and after these rows which can be discarded.
H15T1Y Index|2|01/19/2016|
H15T2Y Index|2|01/19/2016|
H15T3Y Index|2|01/19/2016|
H15T5Y Index|2|01/19/2016|
H15T7Y Index|2|01/19/2016|
H15T10Y Index|2|01/19/2016|
CPDR9AAC Index|2|01/15/2016|
MOODCAVG Index|2|01/15/2016|
H15TXXX Index|2|01/15/2016|
H15TXXX Index|2|01/15/2016|
3. The CTL file - name.ctl
LOAD DATA
APPEND
INTO TABLE dummy_load
WHEN (9:13) = "Index"
TRAILING NULLCOLS
(
name TERMINATED BY "|",
rate TERMINATED BY "|",
effdate TERMINATED BY "|" "TO_DATE(:effdate, 'MM/DD/YYYY')"
)
INTO TABLE dummy_load
WHEN (8:12) = "Index"
TRAILING NULLCOLS
(
name TERMINATED BY "|",
rate TERMINATED BY "|",
effdate TERMINATED BY "|" "TO_DATE(:effdate, 'MM/DD/YYYY')"
)
The sql loader invoke in a bat file ->
C:\Oracle\product\11.2.0\client\bin\sqlldr USERID=myid/mypass@mydb.world CONTROL=C:\temp\t\name.ctl BAD=C:\temp\t\name_bad.dat LOG=C:\temp\t\name_log.dat DISCARD=C:\temp\t\name_disc.dat DATA=C:\temp\t\name.dat
Once this is run, the following is seen in the log file (excerpt):
Table DUMMY_LOAD, loaded when 9:13 = 0X496e646578(character 'Index')
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
NAME FIRST * | CHARACTER
RATE NEXT * | CHARACTER
EFFDATE NEXT * | CHARACTER
SQL string for column : "TO_DATE(:effdate, 'MM/DD/YYYY')"
Table DUMMY_LOAD, loaded when 8:12 = 0X496e646578(character 'Index')
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
NAME NEXT * | CHARACTER
RATE NEXT * | CHARACTER
EFFDATE NEXT * | CHARACTER
SQL string for column : "TO_DATE(:effdate, 'MM/DD/YYYY')"
Record 1: Discarded - all columns null.
Record 2: Discarded - all columns null.
Record 3: Discarded - all columns null.
Record 4: Discarded - all columns null.
Record 5: Discarded - all columns null.
Record 7: Discarded - failed all WHEN clauses.
Record 8: Discarded - failed all WHEN clauses.
Record 9: Discarded - failed all WHEN clauses.
Record 10: Discarded - failed all WHEN clauses.
Table DUMMY_LOAD:
1 Row successfully loaded.
0 Rows not loaded due to data errors.
9 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Table DUMMY_LOAD:
0 Rows successfully loaded.
0 Rows not loaded due to data errors.
5 Rows not loaded because all WHEN clauses were failed.
5 Rows not loaded because all fields were null.
The bad file is empty. The discard file has the following
H15T1Y Index|2|01/19/2016|
H15T2Y Index|2|01/19/2016|
H15T3Y Index|2|01/19/2016|
H15T5Y Index|2|01/19/2016|
H15T7Y Index|2|01/19/2016|
CPDR9AAC Index|2|01/15/2016|
MOODCAVG Index|2|01/15/2016|
H15TXXX Index|2|01/15/2016|
H15TXXX Index|2|01/15/2016|
Based on the understanding of the statements in the CTL file, ideally the first 6 rows should have been inserted into the table. instead the table just has the 6'th row.
| NAME | RATE | EFFDATE |
| H15T10Y Index | 2 | 19-Jan-2016 |
If the INTO TABLE clauses were switched in the CTL file, then the first 5 rows are inserted and the rest are in the discard file. The 6'th row would have a "Discarded - all columns null." in the log file.
Could someone please take a look and advise ? My apologies that the files could not be attached.