Thanks for the question, Mahmoud.
Asked: May 16, 2017 - 6:12 pm UTC
Last updated: May 26, 2017 - 10:03 am UTC
Version: 10.2.0.4
Viewed 10K+ times! This question is 
You Asked
Hi Tom,
I have a stored procedure that returns ref cursor, the procedure runs in DB.1 (10g), reading from a remote view on DB.2 (11g), and that view is selecting union of 2 tables from DB.2 and the other table from remote DB.3 (11g).
The procedure is being called from a java program, with connection pooling. Initially I was getting error "ORA-02046 distributed transaction already begun" from time to time, then I add the following lines before cursor opening:
ROLLBACK;
set transaction read only;
execute immediate 'alter session close dblink LINK_TO_DB_2';
open cur1 for
select .....
Now I get another error from time to time, ora-01453 set transaction must be first statement of transaction followed by ORA-02063: preceding line from LINK_TO_DB_2.
This error never happen while calling the procedure using sql plus or TOAD, only when call from the Java program. Any advise please?
Thanks.
and Chris said...
It's tough to diagnose these kinds of issues over the internet. But it sounds like you still have an open transaction on the remote database. When you run your code in SQL*Plus, TOAD, etc. it's likely you have a fresh session. But your connection pool could have "anything" run prior to your code which is leading to the error.
To get to the bottom of it, add more logging to your code. Specifically capture what's returned by:
select * from v$transaction
select * from v$dblink
On the local and remote databases before and after you run the "set transaction..." statement.
If you're still struggling, please post details of the exact code you're running and the debug information you're getting from these queries.