Skip to Main Content
  • Questions

  • ora-01453 set transaction must be first statement of transaction when using 2 dblinks between 3 databases

Breadcrumb

Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

Question and Answer

Chris Saxon

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.

Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library