Document Type | Troubleshooting
Category | Monitoring/Inspection
Applicable Product Versions | 7FS02PS, 6FS07_CS_2005
Document Number | TMOTS033
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').