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! 

Java Client to Query BPEL processes in Oracle SOA 10.1.3.x Suite

Already existing console views for BPEL and ESB is good but there might be situations where you need to create your own customized console view based on business/functional requirements or you might need to generate a report on deployed services etc for customer. In such scenarios this blog might come in handy because it talks about java code to access BPEL processes deployed on Oracle 10g server. There is a separate blog post that talks about accessing ESB services.
 
1. The jars that need to be placed in the build path are as below -

• orabpel.jar - bpel/lib
• orabpel-common.jar - bpel/lib
• oc4j-internal.jar - home/lib
• optic.jar -opmn/lib

 2. To connect to the BPEL server the code is as below since we use rmi connection -

 where securityCredentials is the password for the 10g server.

3. Now connect to the server and fetch all the domains available in there....

4. Now using the API from orabpel.jar, query the domain for processes and corresponding properties.

 

 5. A typical output of the entire code called from a Java client is shown below. Use similar code from your web project to create a totally different console view etc