Tuesday, May 19, 2009

BPEL: Insert huge data in DB

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.

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:


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>

7) Restart Oracle SOA Suite and you are done.


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.