OCI Data Integration and ORA-30926 or ORA-00001

Getting a unique key or unstable row set error during your task runs? More than likely you have more than one input row for the key in the target table. Even if you use merge mode on the target operator you may get this — on Oracle, the SQL Merge statement is used for the operation; merge is going to decide between inserts and updates, it’s not going to deduplicate for you. The merge on the target operator isn’t consolidating your data, only you know what logic should be done to consolidate many rows for a specific merge key into one.

These are the types of errors you may see;

ORA-30926: unable to get a stable set of rows in the source tables

ORA-00001: unique constraint (SCHEMA.CONSTRAINT) violated

This can be recreated with the simplest of examples in SQL, take this one here where there are 2 rows with the same merge key identifier;

create table bricks_for_sale (
  id       number,
  colour   varchar2(10),
  shape    varchar2(10),
  last_updated    date,
  price    number(10, 2)
);

create table bricks (
  id number,
  colour   varchar2(10),
  shape    varchar2(10),
  last_updated    date,
  price    number(10, 2),
  primary key ( id )
);

insert into bricks_for_sale values (1, 'red', 'cube', SYSDATE-1, 4.95 );
insert into bricks_for_sale values (1, 'red', 'cube', SYSDATE, 10.95 );
insert into bricks_for_sale values (2, 'blue', 'cube', SYSDATE, 7.75 );
insert into bricks_for_sale values (3, 'blue', 'pyramid', SYSDATE, 9.99 );
commit;

merge into bricks pb
using ( 
  select id, colour, shape, last_updated, price 
  from   bricks_for_sale 
) bfs
on    ( pb.id = bfs.id )
when not matched then
  insert ( pb.id, pb.colour, pb.shape, pb.last_updated, pb.price )
  values ( bfs.id, bfs.colour, bfs.shape, bfs.last_updated, bfs.price )
when matched then
  update set pb.price = bfs.price;

When this is executed we see the error below;

ORA-00001: unique constraint (ADMIN.SYS_C0046081) violated
Error at Line: 7 Column: 0

To resolve this in SQL, the query can be changed to use analytic SQL to get the most recent row based on the LAST_UPDATED DATE;

select * from (
    select id, colour, shape, last_updated, price, ROW_NUMBER()  OVER (PARTITION BY ID ORDER BY LAST_UPDATED DESC) RN 
    from   bricks_for_sale )
where RN = 1

To do this in OCI Data Integration we can define define this in an expression and filter. Here is the expression definition;

Then we can filter to get the latest row based on LAST_UPDATED column or whatever determines your most recent change (maybe its just a version number);

We can see in the source data below, there are two records for ID with value 1, there is two different LAST_UPDATED values;

When we use the analytic function to compute RN, we get values 1 and 2, with 1 being most recent since we partitioned by ID and ordered by LAST_UPDATED descending order;

Then to get the latest row we can filter where RN has value 1;

That’s one technique for doing this.

Common Expressions — User Defined Functions

Let’s look at how we can use a user-defined function to capture this — if this is going to be a common enough pattern, the function can have the key attribute and the order attribute parameterized.

Can then define the expression below using the user defined function and use in any other dataflows.

Here we have seen what you can do to deduplicate your data when you get a unique key or unstable row set error. This is one approach, there are other approaches too, interested in hearing your feedback. We’ve also seen how you can use user defined functions to define common expressions that are useful for building out your data integration solutions.

Related resources;

Data Integration

Data Integration Tutorials

https://apexapps.oracle.com/pls/apex/r/dbpm/livelabs/view-workshop?wid=859&clear=180&session=14070618636675