The constraint validation has failed. Adding the exceptions into clause doesn't make the statement "work". It just captures all the rows that violate the constraint.
You still get an exception. So WHENEVER SQLERROR EXIT triggers, closing the connection.
But you should still have the offending rowids in the exceptions table:
create table exceptions (
row_id rowid,
owner varchar2(30),
table_name varchar2(30),
constraint varchar2(30)
);
whenever sqlerror exit
create table par ( c1 primary key ) as
select level c1 from dual
connect by level <= 10;
create table chd (
c1 constraint fk
references par ( c1 )
disable
);
insert into chd values ( -1 );
insert into chd values ( 0 );
insert into chd values ( 1 );
commit;
alter table chd
modify constraint fk
enable
exceptions into exceptions;
ERROR at line 2:
ORA-02298: cannot validate (CHRIS.FK) - parent keys not found
Disconnected from Oracle Database 18c EE High Perf Release 18.0.0.0.0 - Production
Version 18.6.0.0.0
C:\Users\csaxon>sqlplus chris@db18c
SQL*Plus: Release 18.0.0.0.0 - Production on Wed Jun 12 09:09:12 2019
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Last Successful login time: Wed Jun 12 2019 09:08:29 +01:00
Connected to:
Oracle Database 18c EE High Perf Release 18.0.0.0.0 - Production
Version 18.6.0.0.0
select rowid from exceptions;
ROWID
------------------
AAASQ6AAMAAAACeAAA
AAASQ6AAMAAAACeAAB
select * from exceptions;
ROW_ID OWNER TABLE_NAME CONSTRAINT
AAASQtAAMAAAACTAAA CHRIS CHD FK
AAASQtAAMAAAACTAAB CHRIS CHD FK
select status
from user_constraints
where constraint_name = 'FK';
STATUS
--------
DISABLED Until you've resolved the junk data, you'll still get an exception when validating the foreign key. And your script will terminate.