Tuesday, May 19, 2009

BPEL: Insert huge data in DB

Problem:
To store large objects in database tables. If data is more than 32766 bytes, the DB Adapter doesnot insert the data. The Oracle BPEL PM throws following exception:
"
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
"
Note:- To store large data, the column in the oracle database should be of CLOB datatype which can store data.

Solution:
1) Goto location <SOA_Home>\j2ee\<ContainerName>\connectors\DbAdapter\META-INF
2) Open file ra.xml file
3) Copy and paste the below mentioned content:

<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>

4) Now Goto <SOA_Home>\j2ee\<ContainerName>\application-deployments\default\DbAdapter\oc4j-ra.xml
5) Open oc4j-ra.xml file
6) Copy and paste the above mentioned properties in the connector-factory of the DB Adapter.

<connector-factory location="eis/DB/TestDB" name="TestDatabase 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>

7) Restart Oracle SOA Suite and you are done.

5 comments:

Soumyajit said...

Soumyajit: is the workaround applicable for Oracle SOA suite 11g also?

Abhishek Saurabh said...

I have not tested this solution on SOA 11g. But I have a feeling that it should work on 11g too.

Just give a shot.

Amit said...
This comment has been removed by the author.
Amit said...

Hi Abhisek,

What is the maximum no.of records that a DB Adapter can insert?
Is it possible to insert large records through DB Adapter. By large here i mean ~ 50,000-300,000

Abhishek Saurabh said...

Hi Amit,

If you want to insert such a huge data then go for ODI. It will be best suited for your requirement.