Oracle SQL*Loader WHEN Clause Raising Error 2

Question

I am trying to exclude the last line of a data file using SQL*Loader, using the WHEN clause, but when it gets to that line it populates both the bad and discard file, and raises an Error 2.

The line to ignore is the last line and starts with "NOL".

After some reading, Error 2 is a warning about the synatx of the CTL file, but cannot find out where it is wrong. Note, if l remove the last line and then run the SAME CTL file, no ERROR is raised, so the issue cannot be the synatx of the CTL file.

To resolve the issue, l am removing the last line BEFORE loading the data, but would like to find out what the issue is for any future use of the WHEN clause.

I have tried:

• file_dt != 'NOL'
• (1:1) != 'N'
• . . .

But l get the same Error 2

.

Has anybody else come across this issue? Or have something that l can try?

---

Oracle Docs

SQL*Loader Command-Line Reference

For UNIX, the exit codes are as follows:

EX_SUCC 0
EX_FAIL 1
EX_WARN 2
EX_FTL  3

Data:

File-Date,Number
2021-05-04,24
2021-05-04,24
2021-05-04,24
2021-05-04,24
NOL:  4

CTL File:

OPTIONS (READSIZE=51200001, BINDSIZE=51200000, ROWS=5000, ERRORS=0, SKIP=1)
load data
append
into table SOME_SCHEMA.SOME_TABLE
WHEN  (01)  'NOL'
fields terminated by ';'
OPTIONALLY ENCLOSED BY '"' AND '"'
trailing nullcols
           (
    file_dt                      DATE "YYYY-MM-DD",
    a_number
           )

Result:

Path used:      Conventional
Commit point reached - logical record count 5
Table SOME_SCHEMA.SOME_TABLE:
4 Rows successfully loaded.
Check the log file:
loading-file.log
for more information about the load.
2021-05-06 09:42:12: Finished Loading Data into Table
2021-05-06 09:42:12: Status: 2

Answer 1

I'm not on Unix. Nonetheless, loading should work the same.

Table:

SQL> desc test
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- --------------------------
 FILE_DT                                                        DATE
 A_NUMBER                                                       NUMBER

SQL>

Control file (I included sample data into it, for simplicity):

OPTIONS (READSIZE=51200001, BINDSIZE=51200000, ROWS=5000, ERRORS=0, SKIP=1)
load data
infile *
replace
into table test
WHEN  (01) <>  'NOL'
fields terminated by ','
trailing nullcols
(
    file_dt    DATE "YYYY-MM-DD",
    a_number
)
           
begindata
File-Date,Number
2021-05-04,24
2021-05-04,24
2021-05-04,24
2021-05-04,24
NOL:  4        

Loading session and result:

SQL> $sqlldr scott/tiger@orcl control=test38.ctl log=test38.log

SQL*Loader: Release 11.2.0.1.0 - Production on ╚et Svi 6 11:38:00 2021

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

Commit point reached - logical record count 4

SQL> select * from test;

FILE_DT               A_NUMBER
------------------- ----------
04.05.2021 00:00:00         24
04.05.2021 00:00:00         24
04.05.2021 00:00:00         24
04.05.2021 00:00:00         24

Seems to be OK.

---

So, what did I do differently?

• modified WHEN clause
• fields are terminated by comma, not semi-colon
• removed superfluous information