OSB Table Poller Issue Setting Status

After building quite a few Oracle Service Bus table pollers, my last project should have been something I could do with my eyes closed. Poll a table looking for certain 'actions', and call a few web services to make that 'action' happen in another enterprise system.

A very simple service flow:

1. JCA table poller adapter, configured for logical delete (change an indicator from one value to another), set up for distributed polling so it will work in a clustered environment. Basically look for records with status of N, switch it to P.

2. Wrap that in a proxy that simply routes to one of several other proxies based on the 'action'. (local transport)

3. Those other proxies call web services based on the values in the original message. On success, the response pipeline will set the status to C. If any of those services fails or returns an error, an error is raised to the proxy's error handler, which sets to the status to E.

That's it, dead simple. If there are no errors, everything works great, record goes to C, we're all good. 

However, if an error is raised on the request pipeline, the status doesn't get changed to E. Using the same service callout that works for C, does not work for E.

I watch my WebLogic logs and my TOAD query at the same time and notice some things:

1. For a success, I see in TOAD the record stays in N status until all the web services have been called. Once the final service returns, it immediately goes to P then C. All good.

2. If an error is raised along the way, or even if I manually try to set the status anywhere on the request pipeline, that call hangs until the transaction timeout occurs, then that update to E fails, as well as the update to P. End result, the record stays in N and gets pulled again, and again, and....

(As an extra red herring to keep me busy, I get a log full of XA transaction type errors, so I spend way too much time working with the DBA trying to set up XA permissions.)

So it seems that all along the request pipeline, the OSB has that row locked for update, but hasn't done the update to P yet. If I try to set it in my flow, the two updates deadlock until the XA timeout occurs. However, when setting it on the response pipeline, the update to P has already happened, so it works there.

I could have rewritten all my proxies to only change the status on the response pipeline, but that would have been a painful rewrite of the flows. An easier solution was to have the poller proxy immediately drop the message on a JMS queue, and then have a queue listener proxy pick those up and route them. Doing this, I see the record go to P immediately, no matter what, and then success or failure, my status changes without any timeouts or XA errors.

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.

Oracle Service Bus and JCA Adapters

If you've spent any time using Oracle's SOA suite, the first thing you will notice is their toolset kind of blows donkey. It's a half-baked patchwork of crap - some of it written by them, some of it bought from other companies and bastardized. And chock full of interesting bugs and random crashes.

So the first thing I noticed when working with the OSB for the first time (Oracle Service Bus, a.k.a. BEA Aqualogic service bus, or ALSB) is that the "Workshop" tooling lumps everything as either a "business service" or a "proxy service". That's all fine and good, but when I first started reading about integration patterns, SOA, and service buses years ago, a lot of what I learned about was JCA adapters. Where are the JCA adapters!?

If "Workshop for Weblogic 10g" is your only Oracle SOA IDE, then you might think JCA doesn't exist anymore. Oh, but it does. And chances are you need it. So here comes that patchwork - that menagerie of mismatched pieces. You need to use Oracle JDeveloper as well as Workshop for Weblogic - 2 IDE's to accomplish your 1 task.

Ugh, seriously?

This tutorial and sample, while well written, confirms it in all it's ridiculous glory. You need to launch JDeveloper to create the JCA adapter. Then you launch Workshop for Weblogic and create a business service or proxy service in your OSB project. Within that service, you have to import the WSDL that you generated from JDeveloper (as well as any XSD's and TopLink mappings).

Maybe next time Oracle will put all that into one tool? Though since they bought Sun, they have aquired even more application servers and tools, so next time you'll probably have to launch JDeveloper, Workshop, and NetBeans to get a simple service working.

ORA-24777 When Using XA Driver

Here's something we came across this past week, and after some searching, it appears to be a fairly common issue.

We are calling some PL/SQL stored procedures through JCA adapters on the Oracle Service Bus (OSB). Our connection pool on WebLogic is setup using the XA JDBC driver.

Everything was great until we called a stored procedure that queries across a database link. Then we got the dreaded ORA-24777 - use of non-migratable database link not allowed.

Turns out there are at least 3 ways to rectify this issue....

1. Set up Oracle to use multi-threaded server, a.k.a. shared server. There are ups and downs, and depending on who you talk to, mostly downs, especially concerning performance. We haven't tried this, but it does come up often as a fix.
2. Create a "shared" database link. The syntax is a little different than a "normal" link. This is what we did, and it worked fine.
3. Third option, though not right for everyone, would be to use the non-XA driver.

Creating a shared link....

CREATE SHARED DATABASE LINK 
CONNECT TO  IDENTIFIED BY 
AUTHENTICATED BY  IDENTIFIED BY 
USING ;