The file names will be the same.. Bad file has ONLY the 1000 record. The good file has many. I can load the 1st record for both the good file and the bad file but the good file aborts because it skips all the other record types. I was using the WHEN clause to load JUST the 1000 record.
This is an example of a bad file
1000,payment file failed,002 - Duplicate File.
=====================================
This is an example of a good file
1000,1.0,TEMPSUA,10142020071021,10162020172131
4000,1.0,814605760,Failure,TSFMS100000248581445,101 - Payable: Unique Payable Identifier field is required.,101 - Payable: Total Payable Amount field is required.,,,,,,,,,
4000,1.0,814605770,Failure,TSFMS100000248581445,101 - Payable: Unique Payable Identifier field is required.,101 - Payable: Total Payable Amount field is required.,,,,,,,,,
4000,1.0,814605780,Failure,TSFMS100000248581445,101 - Payable: Unique Payable Identifier field is required.,101 - Payable: Total Payable Amount field is required.,,,,,,,,,
4000,1.0,814605790,Failure,TSFMS100000248581445,101 - Payable: Unique Payable Identifier field is required.,101 - Payable: Total Payable Amount field is required.,,,,,,,,,
4000,1.0,814605810,Failure,TSFMS100000248581445,101 - Payable: Unique Payable Identifier field is required.,101 - Payable: Total Payable Amount field is required.,,,,,,,,,
4000,1.0,814605820,Failure,TSFMS100000248581445,101 - Payable: Unique Payable Identifier field is required.,101 - Payable: Total Payable Amount field is required.,,,,,,,,,
4000,1.0,814605830,Failure,TSFMS100000248581445,101 - Payable: Unique Payable Identifier field is required.,101 - Payable: Total Payable Amount field is required.,,,,,,,,,
4000,1.0,814605840,Failure,TSFMS100000248581445,101 - Payable: Unique Payable Identifier field is required.,101 - Payable: Total Payable Amount field is required.,,,,,,,,,
4000,1.0,814605850,Failure,TSFMS100000248581445,101 - Payable: Unique Payable Identifier field is required.,101 - Payable: Total Payable Amount field is required.,,,,,,,,,
5000,9,0,9
I have a table with 1 header record and multiple detail records. A good file and a bad file will have the same file name. Only the header record will alert us to a failed file. I have a 'PRETEST' table that I only want to load the header record into. Then I have a 'PRETEST' script to test the header record. I use a WHEN clause and only load 1 record but the step aborts because it creates a discard file with all the other good records. How can I prevent this from aborting?
Thank you
Sherry Borden
SQL*Loader: Release 19.0.0.0.0 - Production on Wed Nov 18 10:44:55 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Control File: C:\AppWorx\sql\F_JPM_TF_RESPONSE_PRETEST_LOAD.ctl
Data File: 3140121.tmp
Bad File: 3140121.bad
Discard File: 3140121.dis
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 250 rows, maximum of 1048576 bytes
Continuation: none specified
Path used: Conventional
Table TEMPLE_FINANCE.JPM_SUA_RESPONSE_PRETEST, loaded when JPM_REC_TYPE = 0X31303030(character '1000')
Insert option in effect for this table: REPLACE
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
JPM_REC_TYPE FIRST * , O(") CHARACTER
JPM_S_OR_F NEXT * , O(") CHARACTER
JPM_ERRCODE_DESCRIP NEXT * , O(") CHARACTER
Record 2: Discarded - failed all WHEN clauses.
Record 3: Discarded - failed all WHEN clauses.
Record 4: Discarded - failed all WHEN clauses.
Record 5: Discarded - failed all WHEN clauses.
Table TEMPLE_FINANCE.JPM_SUA_RESPONSE_PRETEST:
1 Row successfully loaded.
0 Rows not loaded due to data errors.
4 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind array: 193500 bytes(250 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 5
Total logical records rejected: 0
Total logical records discarded: 4
Run began on Wed Nov 18 10:44:55 2020
Run ended on Wed Nov 18 10:44:57 2020
Elapsed time was: 00:00:02.64
CPU time was: 00:00:00.25
I'm unsure exactly how you want to load these records into the tables - are they all going into the same table? Different tables?
Either way, you need many INTO clauses because the records have a different structure. The WHEN clauses for the second, third, etc table continue processing from the end of the first record.
To reset back to the start of the record, set position(1) for the first field.
For example this loads the data into two different tables:
load data
infile *
into table headers
append
when rec_type = '1000'
fields terminated by ","
( rec_type filler char, c1 char, c2 char )
into table details
append
when rec_type = '4000'
fields terminated by ","
( rec_type filler position(1) char, c1 char, c2 char
)
BEGINDATA
1000,1.0,TEMPSUA,10142020071021,10162020172131
4000,1.0,814605760,Failure,TSFMS100000248581445,101 - Payable: Unique Payable Identifier field is required.,101 - Payable: Total Payable Amount field is required.
4000,1.0,814605770,Failure,TSFMS100000248581445,101 - Payable: Unique Payable Identifier field is required.,101 - Payable: Total Payable Amount field is required.
4000,1.0,814605780,Failure,TSFMS100000248581445,101 - Payable: Unique Payable Identifier field is required.,101 - Payable: Total Payable Amount field is required.
4000,1.0,814605790,Failure,TSFMS100000248581445,101 - Payable: Unique Payable Identifier field is required.,101 - Payable: Total Payable Amount field is required.
4000,1.0,814605810,Failure,TSFMS100000248581445,101 - Payable: Unique Payable Identifier field is required.,101 - Payable: Total Payable Amount field is required.
4000,1.0,814605820,Failure,TSFMS100000248581445,101 - Payable: Unique Payable Identifier field is required.,101 - Payable: Total Payable Amount field is required.
4000,1.0,814605830,Failure,TSFMS100000248581445,101 - Payable: Unique Payable Identifier field is required.,101 - Payable: Total Payable Amount field is required.
4000,1.0,814605840,Failure,TSFMS100000248581445,101 - Payable: Unique Payable Identifier field is required.,101 - Payable: Total Payable Amount field is required.
4000,1.0,814605850,Failure,TSFMS100000248581445,101 - Payable: Unique Payable Identifier field is required.,101 - Payable: Total Payable Amount field is required.
5000,9,0,9
You can load into two tables like so:
create table headers ( c1 varchar2(20), c2 varchar2(20) );
create table details ( c1 number, c2 integer );
ho sqlldr control=sqlldr.ctl
select * from details;
C1 C2
---------- ----------
1 814605760
1 814605770
1 814605780
1 814605790
1 814605810
1 814605820
1 814605830
1 814605840
1 814605850
select * from headers;
C1 C2
-------------------- --------------------
1.0 TEMPSUA
You could also load all the data into the same table - just change the into <tablename> clause and field definitions as needed.