BibleGateway.com Verse Of The Day

Wednesday, August 18, 2010

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 ;

2 comments:

Unknown said...

Had a lot of fun reading this page LOL!
And the most of all - a clear and straight answer was given to a ORA-24777 what I was looking for.
Mike

Blogger said...

Bluehost is ultimately the best hosting provider for any hosting plans you require.