How to stop execution of SQLcl load when it hits an error (multiple tables loading)

Question

I have multiple CSV files that need to be loaded into multiple Oracle tables. I am using SQLcl (version 22.3) load functionality; I have a sql script (loadCSVs.sql) of the following format:

WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK 
whenever oserror EXIT SQL.SQLCODE ROLLBACK 

set load errors 0
set load truncate on

load tableA input_tableA.csv 
load tableB input_tableB.csv 
load tableC input_tableC.csv 

Expected: if load into tableB fails for whatever reason (PK, FK, UK violations, not nullability etc etc) I need the execution to stop (so that I fix the CSVs and re-run).

Actual: execution continues, load into tableC goes on (so you end up with success for table A and C while load in B fails - having actually hundreds of tables/loading - it is easy to miss these errors).

I tried to capture the output of the execution (of the load statement and/or SQL file) - so the exit code - and depending on this exit or not but no success so far. I considered also spool to a (log) file and read it for #ERROR - but don't like this approach.

How can I stop the execution of this (when tableB fails) ? Any idea is welcome, thanks in advance!

Output:

SQL> @loadCSVs.sql

Load data into table tableA

csv
column_names on
delimiter ,
enclosures ""
encoding UTF8
row_limit off
row_terminator default
skip_rows 0
skip_after_names
batch_rows 50
batches_per_commit 10
clean_names transform
column_size rounded
commit on
date_format
errors 0
map_column_names off
method insert
timestamp_format yyyy-mm-dd HH24:MI:SS.FF9
timestamptz_format
locale English United States
scan_rows 100
truncate on
unknown_columns_fail on

#INFO Number of rows processed: 2
#INFO Number of rows in error: 0
#INFO Last row processed in final committed batch: 2
SUCCESS: Processed without errors

Load data into table tableB

csv
column_names on
delimiter ,
enclosures ""
encoding UTF8
row_limit off
row_terminator default
skip_rows 0
skip_after_names
batch_rows 50
batches_per_commit 10
clean_names transform
column_size rounded
commit on
date_format
errors 0
map_column_names off
method insert
timestamp_format yyyy-mm-dd HH24:MI:SS.FF9
timestamptz_format
locale English United States
scan_rows 100
truncate on
unknown_columns_fail on

#ERROR Insert failed in batch rows  1  through  2
#ERROR ORA-01722: invalid number
#ERROR Row 1 data follows:
< removed atual data for anonimization reasons>

#INFO Number of rows processed: 2
#INFO Number of rows in error: 2
#INFO No rows committed
ERROR: The Errors Allowed or the Error Limit parameter was exceeded

Load data into tableC

csv
column_names on
delimiter ,
enclosures ""
encoding UTF8
row_limit off
row_terminator default
skip_rows 0
skip_after_names
batch_rows 50
batches_per_commit 10
clean_names transform
column_size rounded
commit on
date_format
errors 0
map_column_names off
method insert
timestamp_format yyyy-mm-dd HH24:MI:SS.FF9
timestamptz_format
locale English United States
scan_rows 100
truncate on
unknown_columns_fail on

#INFO Number of rows processed: 28
#INFO Number of rows in error: 0
#INFO Last row processed in final committed batch: 28
SUCCESS: Processed without errors
SQL> version
Oracle SQLDeveloper Command-Line (SQLcl) version: 22.3.0.0 build: 22.3.0.257.1628
SQL>

Answer 1

It's a bug. We'll have it fixed for an upcoming release of Oracle SQLcl.