Passing Dynamic value to IN clause

I came across a wonderful solution for passing values dynamically to IN clause in SQL query and would like to share with all.

Problem:
To pass the list of values in IN clause in SQL Query dynamically. We need to just keep in mind that giving ? after IN clause will not work.

Solution:
1) Use DB Adapter, select Execute Custom SQL as Operation Type and click on Next
2)You need to write a SQL query like the one given below if you want to use IN clause in SQL query and pass the value list dynamically:

SELECT ID,FNAME,MNAME,LNAME,LOC,DEPT
FROM EMP
WHERE LOC
IN (WITH VALUE_LIST AS
(SELECT ? val FROM dual)
SELECT SUBSTR(val, (decode(LEVEL, 1, 0, instr(val, ':', 1, LEVEL -1)) + 1), (decode(instr(val, ':', 1, LEVEL) -1, -1, LENGTH(val), instr(val, ':', 1, LEVEL) -1)) -(decode(LEVEL, 1, 0, instr(val, ':', 1, LEVEL -1)) + 1) + 1) a
FROM VALUE_LIST CONNECT BY LEVEL <=
(SELECT(LENGTH(val) -LENGTH(REPLACE(val, ':', NULL)))
FROM VALUE_LIST) + 1)


NOTE1:
a) Here you need to change the portion showed in Bold as per your query requirement.
b) In this SQL, value list for IN clause should be delimited with a : symbol, If you want to use any other symbol as a delimiter you need to replace : with the symbol you want to use.


3) You need to create IN value list dynamically. Use XSL transformation to generate the dynamic value list and pass it to the DB adapter.

4) To query all the employees from US,INDIA,UK locations you need to create an IN clause value list as US:INDIA:UK

NOTE2:
While generating value list you don’t need to wrap character data with apostrophe.


Limitations:
1) If you create a very big value list for IN query (greater than 4000 characters) than you need to break value list and invoke this query multiple times, otherwise you will get ORA-01704: string literal too long error.

2) If you use SELECT * rather than giving the field list with SELECT, DB adapter won't be able to generate a correct XSD for request.

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'

Error: XPATH returns zero node

Problem:
Sometimes you get following runtime error message in your BPEL Console for the transform activity;

XPath query string returns zero node.
According to BPEL4WS spec 1.1 section 14.3, The assign activity part query should not return zero node.
Please check the BPEL source at line number "211" and verify the part xpath query.
Possible reasons behind this problems are: some xml elements/attributes are optional or the xml data is invalid according to XML Schema.
To verify whether XML data received by a process is valid, user can turn on validateXML switch at the domain
administration page.

Solution:
The probable reason for getting this error is due to assigning value to an element using Assign activity just after the Transform activity, for which the same element is not mapped in transformation. Assign activity attempts to update an element not being transformed in Transform (the element without any value).

Following steps should be done to overcome this problem:

1) Goto the transfomation mapper file
2) Right-click on the element that the Assign activity in going to use for assigning value in the next step
3) Select Set Text
4) Set any temporary value there. This will be overwritten by the Assign activity in the next step anyways.