Verse Of The Day

Monday, November 01, 2010

Oracle Service Bus - Getting XML Out of Table Column

This took me a while to figure out, and ended up being pretty simple once I knew which XQuery functions were available.

Let's say you have a table you need to query from the OSB. The result set you get back from the JCA DB adapters gets turned into XML for you. If one of the columns is a LOB containing XML, then it gets escaped using CDATA. Anything in CDATA is basically ignored by XML, XSL, XPath, etc.

It is a 2 step process to extract that value and make it usable XML:

  1. Use the fn:string() function to turn the value into a string. In this case, it strips off the CDATA wrapper and gives you a string that looks like XML.
  2. Use the fn-bea:inlinedXML() function to parse that string into XML.

You can use an ASSIGNMENT task to jam that XML into a variable, and then run any XSL, XPath, etc on it. You can also use INSERT or REPLACE tasks to embed it back into your original XML.

Enabling Oracle Trace For WLS Connection Pools

We recently had the need to run an Oracle trace to catch some database diagnostics for an OSB call flow. To enable the trace only for the sessions coming from WebLogic, I added the following to the "initSql" field in the connection pool setup screens (in the WLS console). Just remove it and save the pool settings when you're done collecting your *.trc files. Works like a charm.

SQL BEGIN DBMS_MONITOR.session_trace_enable(waits=>TRUE, binds=>TRUE); END;