Why does DB-adapter return duplicate rows?

June 25, 2015

Reading time ~1 minute

Here are some DB adapter tips regarding duplicate rows.

The DB Adapter has some nice features, but sometimes you can get fooled. One of my colleagues experiences this one day. The query returned the same row - just repeated many times.

The reason for this is because the primary key was not defined correctly.

The good thing is that the documentation describes this - and more: Oracle documentation

For tables where primary key is defined - this should not be a problem, but in cases where it is not defined or you are querying a view - you would need to specify a primary key yourself. A couple of relevant notes here:

  • If you do not provide a valid primary key, then the unique constraint is not guaranteed, and this could result in possible loss of messages at runtime. That is, rows with duplicate primary key values are likely to be lost.
  • You should ensure that you primary key is less than 100 bytes.
  • Oracle recommends that you use varchar instead of char for primary key columns

If you do not have a valid primary key - you can use ROWID option for Oracle databases. There are some limitations - for example ROWID can only be used in conjunction with a single table, as relationships between tables require rows to have explicit primary keys. This could happen for instance where a view references multiple tables.

Picking a non-unique column here can lead to operations updating multiple unintended rows, or Selects returning the some rows twice and not others.

Therefore - verify if the primary key you select is a valid one - or you may get fooled.

Jon Petter is Department Manager for Middleware at SYSCO. Building an organization of skilled Oracle Middleware experts, with a focus on Oracle SOA Suite, Weblogic, Oracle Service Bus, BPM Suite, BAM, Oracle Event Processing. He has worked with Oracle products since 1997, and has worked with some of the larger SOA customers in Norway. Board member of the Oracle User Group Norway (OUGN), and Oracle ACE Associate.

Customizing service calls with multiple levels of security in OSB 12c

This will be a part two of my previous post which you can read [here](http://blog.sysco.no/osb/jdeveloper/Service-call-with-multiple-leve...… Continue reading