BPEL DBAdapter : Using Oracle Native Sequence

Do you know , how to use DBAdapter to insert a record in the database ? Seem's very straight forward . isn't it ?

Yes, in fact it is straight forward. Just configure the database, select the operation (insert,update, merge and delete ) and then map the input's. run the sample and there you go, there is a record in the database. Bang!!!!!!!.

But , the real problem begin, when you have the table which has a primary key. Now either you need to generate a unique ID in the BPEL layer and map it to the DBAdapter message type variables or use a sequence created in the database. Best thing is to use the database sequence . But how to do it ? Is there a documentation or if it exists is it good enough ? For the benefit of this blog readers , here is how I made it to work after spending long hours on the internet and trial and error. I am sure , it will save you a lot of time and if so, just leave your comments.

OK, now the steps to make it work.

1. Create a table with a primary key

2. Create a sequence with start value as 1 and increment by 1

3. Configure the DBAdapter for an insert option.

4. After successful configuration of the DBAdapter, you will find the "offline objects" and toplink mapping created for you.

so far normal. nothing much.. here are the undocumented (not with proper documentation) steps

5. Configure the DBAdapter's oc4j-ra.xml with the following parameters

<connector-factory location="eis/DB/XE" connector-name="Database Adapter">
        <config-property name="xADataSourceName" value="jdbc/XEDataSource"/>
        <config-property name="dataSourceName" value=""/>
        <config-property name="platformClassName" value="oracle.toplink.platform.database.Oracle9Platform"/>
        <config-property name="usesNativeSequencing" value="true"/>
        <config-property name="sequencePreallocationSize" value="1"/>
        <config-property name="defaultNChar" value="false"/>
        <config-property name="usesBatchWriting" value="false"/>
        <connection-pooling use="none">
        </connection-pooling>
        <security-config use="none">
        </security-config>
    </connector-factory>

usesNativeSequencing : Set this to "true" to allow native sequencing

sequencePreallocationSize: This should be the counter that you use for your sequence. (eg : If we created a sequence with increment by 1, so the value of this should be 1). Please note , the default value is 50.

xADataSourceName : datasource name for connecting to the database in the oc4j.

The important steps to come..stay tuned.