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.

Jdeveloper log window disappears

Problem:
Log window from Jdeveloper disappears. If we goto view->log, it does not reappear.

Solution:
For JDeveloper 10.1.3.4:-
Search for the windowinglayout.xml file.
Its present in the directory: <Jdev_Home>/jdev/system/oracle.ide.10.1.3.42.70

Rename or delete windowinglayout.xml file. After renaming/deleting it, we get our log window back on Jdeveloper again.

For JDeveloper 10.1.3.3:-
Search for the windowinglayout.xml file.
Its present in the directory: <Jdev_Home>/jdev/system/oracle.ide.10.1.3.41.57

Rename or delete windowinglayout.xml file. After renaming/deleting it, we get our log window back on Jdeveloper again.

Note:
JDeveloper saves the file after closure. So first close JDeveloper and then rename or delete the file.

Playing with ESB Schema-2

Problem:
Need to have all ESB Services and their respective parent System

Solution:
I have already mentioned in my previous blog with subject - Playing with ESB Schema-1, about what all details are required and the details on how to connect to ESB schema.

Query:
SELECT systems.name SYSTEMNAME, event.name ESBSERVICENAME, event.guid GUID,
DECODE(event.type, 'GROUP', 'Service', 'SERVICEGROUP', 'Service Group') TYPE, event.status STATUS
FROM wf_events event, wf_systems systems
WHERE event.type != 'EVENT'
AND event.system_guid = systems.guid
AND event.type IN ('GROUP')
AND systems.name != 'BPELSystem'
ORDER BY systems.name

Description:
1) event.type != 'EVENT' :- Condition to exclude esb activities
2) systems.name != 'BPELSystem' :- Condition to exclude BPEL processes
3) 'GROUP' :- Here GROUP means ESB service
4) 'SERVICEGROUP' :- Here SERVICEGROUP is the actual Service Group

Playing with ESB Schema-1

Problem:
Need to get the list of Systems and its respective GUID's.

Solution:
The ESB schema name to connect is ORAESB. You need to have the following information to connect to this schema:
1) Password
2) Hostname
3) Port

You can get the Hostname and Port details from the datasources.xml file.

Query:
SELECT name,guid,status FROM wf_systems WHERE name != 'BPELSystem'