I have an Oracle PLSQL program in database A that selects data from another database B through DBLink. I am invoking a PLSQL program from a Java EE application. Everything is working fine so far. Base B does not go out of my area, it belongs to an external provider.
Now I need to update the Java EE application, use another Java EE application (call EJB), so I need an XA data source to manage the distributed transaction. He then gives the error message "ORA-24777: Use of non-cross reference to database is not allowed." I was looking for one solution - connect to B via the MTS (shared) dblink connection. I cannot change DBLink for a general connection (due to client restrictions).
Is there a way to tell Oracle in database A that DBLink is for selection (read-only data), so there is no need to distribute a distributed transaction to database B? I tried to define AUTONOMOUS_TRANSACTION and set READ ONLY TRANSACTION, but it does not work. Also tried defining READ ONLY views for remote tables, not working
There are other possible solutions that I cannot apply, for example, to have a materialized representation in A, reading data from B (an external provider does not allow this).
Both databases: ORacle 11g Release 11.2.0.2.0
source
share