0 Replies Latest reply: Apr 19, 2013 1:44 AM by cmalhotra RSS

    Facing issues with JCA DB Adapter

    cmalhotra
      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