Imagine that you want to use the Oracle Database Adapter of the Oracle SOA Suite in a BPMN 2.0 (Oracle BPM Suite) or BPEL (Oracle SOA Suite) scenario to retrieve master-detail data. The adapter normally expects the foreign key in the detail table to equal the technical primary key of the master table. This post provides a workaround if you have a business foreign key for the master-detail relationship which differs from the technical primary key of the database master table.

The example is based on the HR schema. The default master-detail in HR schema is build out of REGIONS  (master, primary key REGIONS_ID) and COUNTRIES  (detail, with the primary key COUNTRIES_ID). Both are associated by the foreign key REGION_ID  in the COUNTRIES  table which is indeed the primary key of the REGIONS  table.

ER diagram of default REGIONS and COUNTRIES table from HR schema

Fig. 1: ER diagram of default REGIONS and COUNTRIES table from HR schema

The configuration of the database adapter is straight forward according to the documentation. The important step in the Adapter Configuration Wizard is the definition of the relationship.

Default Master-Detail Relationship definition in DB-Adapter Wizard of Oracle SOA/BPM Suite

Fig. 2: Default Master-Detail Relationship definition in DB-Adapter Wizard of Oracle SOA/BPM Suite

Now imagine that not the primary key of the master table is used to associate the detail. Instead, the master has an additional unique identifier defined. To underline this, the example below even defines this identifier to be a String value (VARCHAR2 as database data type) instead of the usually used NUMBER database type.

ER diagram of customized REGIONS3 and COUNTRIES3 table

Fig. 3: ER diagram of customized REGIONS3 and COUNTRIES3 table

First of all, I have to admit that this is a poor database model design. But let’s face it: Especially in historical grown structures, those things can happen and redesigning the database model is often out of discussion.

The problem is now that the database adapter is not supporting such a master-detail relation. Even if you can select any field as foreign key of the detail, you cannot change the primary key of the master.

Failing Master-Detail Relationship definition in DB-Adapter Wizard of Oracle SOA/BPM Suite

Fig. 4: Failing Master-Detail Relationship definition in DB-Adapter Wizard of Oracle SOA/BPM Suite

So what options do we have to overcome this limitation?

  1. Redesign of the database model to make the unique business identifier of the REGIONS  to become the primary key of the REGIONS3  table. First of all, VARCHAR2-typed columns are no usual primary keys of database tables. But let’s assume the unique business identifier is a numeric value as well. Even the (as described above), redesigning the database model is often out of question.
  2. Write your own SQL statement. It’s the most accurate way. But the disadvantage is that you are leaving the “zero-code” approach. Regarding maintenance and reuse, it’s better to stick to “drag & drop and wizard-click-through-configuration”.
  3. Write your own JCA database adapter. Regarding the power of the Oracle Database Adapter and reusability, this is obviously the worst choice. But for the sake of completeness, let’s name it anyway.
  4. Use database synonyms. That’s actually the workaround I would suggest. I admit that it is a little bit “dirty” but it has the least impact to the “zero-code” approach.

The relationship configuration in the database adapter wizard is based upon the definition of primary keys. If you are working with database tables, the wizard retrieves the technical primary keys of the database tables to be the primary keys for any relationship definition. But if you are using synonyms, you are free to define your primary keys on your own. You can now select your unique business identifier REGION_NAME  instead of the technical table id REGION_ID .

Custom Primary Key definition for synonymed tables in DB-Adapter Wizard of Oracle SOA/BPM Suite

Fig. 5: Custom Primary Key definition for synonymed tables in DB-Adapter Wizard of Oracle SOA/BPM Suite

Consequently, you are able to define your master-detail relation based on this primary key.

Master-Detail Relationship definition in DB-Adapter Wizard of Oracle SOA/BPM Suite based on custom PK

Fig. 6: Master-Detail Relationship definition in DB-Adapter Wizard of Oracle SOA/BPM Suite based on custom PK

Once configured the master-detail relationship according to your needs, you can now proceed to finalize your database adapter configuration to retrieve the master-detail selected by your unique business identifier.

Entire Master-Detail configuration based on custom PK in DB-Adapter Wizard of Oracle SOA/BPM Suite

Fig. 7: Entire Master-Detail configuration based on custom PK in DB-Adapter Wizard of Oracle SOA/BPM Suite

Reminder: Make sure your synonyms are defined as direct representation of the according database tables. You will not be able to perform all CRUD operations if your synonym associates database views. A well-known best practice to keep track of your different types of synonyms is to add prefixes: “V_” for synonyms representing database views, “T_” for synonyms representing database tables (as used in the above example) and so on.

Wir bleiben für Sie da!

In unserem neusten Blog-Beitrag sprechen wir mit zwei neuen Mitarbeitern, die Mitten in der Corona-Krise bei virtual7 angefangen haben.

Welche Eigenheiten und Herausforderungen einen Jobwechsel in dieser Zeit bedeuten und wie ihr erster Arbeitstag im Home-Office ausgesehen hat, erfahren Sie in diesem Beitrag.