Oracle 11g DB Adapter Unique Constraint/Sequence mismatch

In my last Project, I was using a DB adapter in my BPEL code. It was using an Oracle  seq 'DEMO_INSERT_S' as below -

DB Adapter with Sequence

Problem -


Even though the sequence was an Oracle generated sequence, I faced a unique issue in PROD. The sequence was incrementing of its own accord with no relation whatsoever with the actual seq.NEXTVAL. So while the DB adapter was generating a seq value say 101, it so happened that 101 already existed in the database resulting in a UNIQUE CONSTRAINT error! It took us some time to figure out the issue to be as follows -

Oracle BPEL DB Adapter follows or rather maintains a sequence of its own and does not exactly use the oracle generated seq.NEXTVAL.

Workaround -


So instead of providing the SEQUENCE in the DB adapter and let it have a mind of its own, we removed it from there and provided the Oracle sequence generated NEXTVAL in the xsl transformation.
So here's the mapping for the primary key of the DEMO_INSERT_TABLE. We are no longer dependent on the DB adapter for the sequence, instead we use the bpel extension function oraext:sequence-next-val to derive it from the actual Oracle Sequence i.e. DEMO_INSERT_S and pass it to the DB adapter mapping xsl.


<ns1:DemoInsertTable>

     <xsl:for-each select="/ns0:ChangeOrderCanonical/ns0:Parts">

        <ns1:GglPlmItemUpload>

          <!--ns1:id>

            <xsl:value-of select="oraext:sequence-next-val('APPS.DEMO_INSERT_S','jdbc/MyDataSource')"/>

          </ns1:id-->

 <!-- other column mappings -->

</ns1:DemoInsertTable>

This resolved my issue and hope it helps you too!