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 ;