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....
- 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.
- Create a "shared" database link. The syntax is a little different than a "normal" link. This is what we did, and it worked fine.
- Third option, though not right for everyone, would be to use the non-XA driver.
CREATE SHARED DATABASE LINK