Skip to Main Content
  • Questions
  • How to prevent sqlldr from aborting with WHEN clause

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Sherry.

Asked: November 18, 2020 - 4:30 pm UTC

Last updated: November 20, 2020 - 11:11 am UTC

Version: SQL*Loader:Version 19.3.0.0.0

Viewed 1000+ times

You Asked

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

and Chris said...

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.

Is this answer out of date? If it is, please let us know via a Comment