This document describes the solution for errors TBR-10021 and JDBC-10021 that occur when executing Merge statements.

Issue

During batch job execution, the process either completes successfully in an irregular manner or the following error occurs:

JDBC-10021:Inconsistent set of rows in source tables.

$>tberr 10021
/*
* err: -10021
* name: ERROR_EXEC_INCONSISTENT_ROW_SET
* desc: Inconsistent set of rows in source tables.
* cause: Inconsistent DML result due to a non-deterministic WHERE condition.
* action: Modify the non-deterministic WHERE condition.;
*/

Error Reproduction

Create Tables

CREATE TABLE test_tbl1 (
code varchar(10),
name varchar(40)
);
CREATE TABLE test_tbl2 (
code varchar(10)
);

Insert Sample Data

insert into test_tbl1 values('TEST', 'tbl1_New');
insert into test_tbl2 values('TEST');
commit;

Execute Merge Query

MERGE INTO test_tbl1 a USING (select code from test_tbl2) b
ON (a.code=b.code)
WHEN MATCHED THEN
UPDATE SET a.name = 'tbl1_updated'
WHEN NOT MATCHED THEN
INSERT (a.code, a.name) VALUES ('TEST2','tbl1_inserted');

1 row merged.

Confirm Successful Merge Execution

Cause Error

Insert duplicate code values to trigger ERROR_EXEC_INCONSISTENT_ROW_SET error

insert into test_tbl2 values('TEST');
commit;

MERGE INTO test_tbl1 a USING (select code from test_tbl2) b
ON (a.code=b.code)
WHEN MATCHED THEN
UPDATE SET a.name = 'tbl1_updated'
WHEN NOT MATCHED THEN
INSERT (a.code, a.name) VALUES ('TEST2','tbl1_inserted');


TBR-10021: Inconsistent set of rows in source tables. 

Cause

The issue occurs when two rows in b.code map to a single a.code. (e.g., two 'TEST' entries)

The data in the ON clause must be uniquely selected for proper operation.

Solutions

• Modify data so that duplicate codes are not selected from test_tbl2 b in the ON clause

-- Normal operation after data modification
DELETE FROM  test_tbl2 WHERE ROWNUM =1;

SQL> MERGE INTO test_tbl1 a USING (select code from test_tbl2) b
ON (a.code=b.code)
WHEN MATCHED THEN
UPDATE SET a.name = 'tbl1_updated_2nd'
WHEN NOT MATCHED THEN
INSERT (a.code, a.name) VALUES ('TEST2','tbl1_inserted'); 

1 row merged.

Confirmed successful update

• Prevent duplicates by setting constraints on the code column in the table used in the ON clause

-- Recreate table
drop table test_tbl2;

-- Set constraint
CREATE TABLE test_tbl2 (
code varchar(10) UNIQUE
);

-- Insert sample data
insert into test_tbl2 values('TEST');
commit;

Attempt to insert duplicate data to trigger error now fails.

insert into test_tbl2 values('TEST');
TBR-10007: UNIQUE constraint violation ('SYS'.'_SYS_CON54900481').