Storing CLOB data in DB - Oracle BPEL

While working at the clients' place an year ago, being newly exposed to Oracle SOA & BPEL, we had a requirement to store large objects which were purely XML content in database tables. Hence, I created a column in the oracle database of CLOB datatype which can fit data upto 4GB and a DB Adapter in my BPEL process which will execute a query to insert data into the database column with CLOB datatype. So far so good.

This seemed to work fine for quite some time until the data was not more than 32766 bytes, which triggered my mind that there ought to be some issue with the Oracle SOA settings, as I was able to insert very large payloads (Obviously more than 32766 bytes) using the same sql query executed via SQL developer. The Oracle BPEL PM also threw the following exception when the BPEL process tried to invoke the DB adapter for large payloads;

java.sql.SQLException: setString can only process strings of less than 32766 chararacters

Internal Exception: java.sql.SQLException: setString can only process strings of less than 32766 chararacters

Error Code: 17157 when trying to insert record in clob type of size more then 32766 characters

After googling around for solutions, finally found the following solution (couldn't recall the thankful resource) which needs to be configured in the Oracle SOA suite for the CLOB data insertion.

With Oracle SOA Suite 10.1.3.3 advanced installation, I tried the following settings which resolved the issue;
Step 1:
Add/Append the following properties under the following file: $Oracle_Home\j2ee\oc4j_soa\connectors\DbAdapter\META-INF\ra.xml

<config-property>
<config-property-name>usesStreamsForBinding</config-property-name>
<config-property-type>java.lang.Boolean</config-property-type>
<config-property-value>true</config-property-value>
</config-property>
<config-property>
<config-property-name>usesStringBinding</config-property-name>
<config-property-type>java.lang.Boolean</config-property-type>
<config-property-value>true</config-property-value>
</config-property>

Step 2:
Configure the above properties in the connector-factory of the DB Adapter and ensure that "usesStreamsForBinding" and "usesStringBinding" are set to true;
$Oracle_Home\j2ee\oc4j_soa\application-deployments\default\DbAdapter\oc4j-ra.xml


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


Step 3:
Restart Oracle SOA Suite for all configurations to take effect and the BPEL process should work fine even with very large payloads.

Meet you in my next post with a possible solution for a new SOA challenge.....