Home » Developer & Programmer » JDeveloper, Java & XML » XA Driver for stored proc querying multiple schemas
XA Driver for stored proc querying multiple schemas [message #156490] Wed, 25 January 2006 07:17 Go to next message
theres16
Messages: 1
Registered: January 2006
Junior Member
Hi,

I need to configure a connection pool in Weblogic 8.1 using an Oracle 9i driver to execute a stored proc that queries two (or more) different schemas in the same database.

I've tried Oracle's Driver(Thin XA), Weblogic's Oracle Driver (Type 2 and 4 XA), as well adding the jdbc driver from Oracle client to the weblogic classpath instead of using one of the proprietary drivers in weblogic.

The problem :

If I call a stored procedure from Weblogic's ejb container (using a java.sql.Datasource linked to connection pool), I cannot retrieve any information as a result of the following error ->
java.sql.SQLException: ORA-24777: use of non-migratable database link not allowed (if I use out parameters).

When I use a direct connection (not datasource) / call stored proc querying only one schema (using datasource), it works.

Please advice
Re: XA Driver for stored proc querying multiple schemas [message #176239 is a reply to message #156490] Wed, 07 June 2006 08:35 Go to previous messageGo to next message
ajnd05
Messages: 1
Registered: June 2006
Junior Member
Does someone know how to resolve this ? I am using XA driver using Oracle 9i/WebSphere MQ Version 5.2 in IBM WebSphere version 5.1.1.4. This problem shows up when I make a query to a different database instance connected via dblink.

java.sql.SQLException: ORA-24777: use of non-migratable database link not allowed
ORA-06512: at "XYZUSER.PKG_XYZ_QUERIES", line 528
ORA-06512: at line 1

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:589)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1957)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1114)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2181)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2063)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2922)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:644)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:722)
at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.execute(WSJdbcPreparedStatement.java:400)
...
...
...
at com.ibm.CORBA.iiop.ServerDelegate.dispatchInvokeHandler(ServerDelegate.java:608)
at com.ibm.CORBA.iiop.ServerDelegate.dispatch(ServerDelegate.java:461)
at com.ibm.rmi.iiop.ORB.process(ORB.java:432)
at com.ibm.CORBA.iiop.ORB.process(ORB.java:1728)
at com.ibm.rmi.iiop.Connection.doWork(Connection.java:2227)
at com.ibm.rmi.iiop.WorkUnitImpl.doWork(WorkUnitImpl.java:65)
at com.ibm.ejs.oa.pool.PooledThread.run(ThreadPool.java:95)
Re: XA Driver for stored proc querying multiple schemas [message #242735 is a reply to message #176239] Mon, 04 June 2007 23:10 Go to previous messageGo to next message
eriknet
Messages: 1
Registered: June 2007
Junior Member
ORA-24777: use of non-migratable database link not allowed

Cause: The transaction, which needs to be migratable between sessions, tried to access a remote database from a non-multi threaded server process.

Action: Perform the work in the local database or open a connection to the remote database from the client. If multi threaded server option is installed, connect to the Oracle instance through the dispatcher.

You may try to use a shared database link to solve the problem:
sample:
---------------------------------------
-- Create database link
create shared database link DB_LINK
connect to DB_APP identified by password1
authenticated by DB_APP identified by password1
using 'sid12345';
Re: XA Driver for stored proc querying multiple schemas [message #242764 is a reply to message #242735] Tue, 05 June 2007 00:59 Go to previous message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Let's leave this thread like it was, ok? (asleep for a year) Wink
Previous Topic: Session
Next Topic: XML output change
Goto Forum:
  


Current Time: Fri Mar 29 09:11:03 CDT 2024