Skip to Main Content
  • Questions

  • ORA-02089: COMMIT is not allowed in a subordinate session

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Kostya.

Asked: February 14, 2017 - 2:00 pm UTC

Last updated: February 15, 2017 - 5:15 am UTC

Version: Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

Viewed 10K+ times! This question is

You Asked

Hello Tom,

I got an issue above. I have some stored procedure which enables/disables some Oracle queue, procedure is being called from java, java method is wrapped by '@Transactional' annotation. It opens the main transaction, where my db procedure is called. During this call I get ORA-02089. Error occurs due to completing transaction in out of main transaction and it could be solved by using an autonomous transaction in stored procedure. I would like to reproduce it on db side only without any java presence. Could you assist me with steps to reproduce?

Thanks in advance.

and Connor said...

That error is related to XA

https://docs.oracle.com/database/121/ADFNS/adfns_xa.htm#ADFNS017

so with pure PL/SQL you would not be able to replicate it, unless you went down the route of using DBMS_XA.

But yes, the resolution is to use an autonomous transaction - just be positive that this wont cause any undesirable consequences (ie, make sure that transaction truly *can* be autonomous to the outer one)

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 latest video and Chris's latest video 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