Integration

Hi,

I have to create a DB Adapter in BPEL on Reporting Database of OSB.
I have a requirement to extract DATA_VALUE from WLI_QS_REPORT_DATA Table stored in BLOB datatype by passing parameters in WLI_QS_REPORT_ATTRIBUTE with a join condition of a common unique ID (MSG_GUID) in both tables.

This DB Adapter files will be imported in OSB to generate Biz Service, which will perform this function.
I have followed all steps as mentioned in this blog :

http://guidoschmutz.wordpress.com/2010/08/08/oracle-service-bus-11g-and-db-adapter-a-different-more-integrated-approach/

My query looks like this :

SELECT t0.MSG_GUID, XMLTYPE(t0.DATA_VALUE,nls_charset_id('AL32UTF8')) FROM osb_infra.WLI_QS_REPORT_DATA t0
LEFT OUTER JOIN osb_infra.WLI_QS_REPORT_ATTRIBUTE t1
ON (t0.MSG_GUID = t1.MSG_GUID)
WHERE t1.DB_TIMESTAMP >= ADD_MONTHS(SYSDATE,-1)
AND t1.DB_TIMESTAMP < SYSDATE AND
t1.INBOUND_SERVICE_NAME =#serviceName
AND (SUBSTR(t1.MSG_LABELS,22,10) =#keyValue OR SUBSTR(t1.MSG_LABELS,18,10)=#keyValue)
AND t1.STATE='REQUEST'

This query runs fine when run independently on DB. but gives no response when used in DB adapter generated Biz Service, while testing in OSB Test Console.
I have done trials for this tool by modifying query as following.

1. Query on 1 table : Report Attribute – Success
2. Query on 1 table : Report data – Success but no XML data
3. Query on both tables with ServiceName Hardcoded – Failure
4. Query on both tables with keyValue hardcoded – Failure
5. Query on both tables with both params hardcoded – Failure
6. Query on both tables without XML data, selecting MSG_GUID only - Failure

All the above queries give desired outputs with XML message converted as well when run independently on SQL woksheet.

1. Query on multiple tables
2. Fetching of data in XML format as response into OSB pipeline.

I am still trying hard to somehow get data from DATA Table with providing MSG_GUID as parameter, but no progress.
The query I used here is :

SELECT XMLTYPE(DATA_VALUE,nls_charset_id('AL32UTF8')).getClobVal FROM osb_infra.WLI_QS_REPORT_DATA
WHERE MSG_GUID = 'uuid:b2bf9a6e66ba73f5:-7d5e4cfc:13deecde95d:5434'

This query runs fine in SQL Worksheet of JDeveloper with no issue and also extracts XML out of BLOB data.
But no success in getting it inside OSB pipelines.
Output in OSB for dataValue is : <dataValue xsi:nil = "true"/>


Please guide on this.

Thanks
Cheena Malhotra