This discussion is archived
7 Replies Latest reply: Dec 16, 2010 10:47 AM by mikereiche RSS

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

766624 Newbie
Currently Being Moderated
Hi guys,

My scenario is: I have a bunch of stored procedures mapped as physical data services in ODSI. Now I'm developing a logical service with some operations. Each operation involves calling a few of the procedures (physical services). When I tried to run it, I got the error

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

Researching a bit, I found out that it happened because the procedures had inner commit statements, and because I'm using a transactional driver with the logical service, the commit causes it to raise an error.
Changing the procedures (so they no longer state commits) is not an option. The way I see it, the only other option would be to change the driver to a non-transactional one - each physical service (procedure) would have and transaction of it own. Off course, this means I would lost the atomicity of my logical services, since one procedure could commit and the other not. Any thoughts on the matter?

Thanks in advance,

Pedro Ivo
  • 1. Re: ORA-02089: COMMIT is not allowed in a subordinate session
    mikereiche Pro
    Currently Being Moderated
    When I Google on the error message that you are reporting :

    http://www.google.com/search?q=ORA-02089%3A+COMMIT+is+not+allowed+in+a+subordinate+session+

    The first search result describes how to create a new transaction in PL/SQL which would allow the COMMIT without affecting the global transaction. .Keep in mind that if the global transaction was rolled back, the work down in the new transaction would not be. You have not stated if that is the semantics you want - but it's the only semantics possible.
  • 2. Re: ORA-02089: COMMIT is not allowed in a subordinate session
    766624 Newbie
    Currently Being Moderated
    Mike, thanks for the help. I beg your pardon, but the first link I get from google is
    http://ora-02089.ora-code.com/
    which just describes the problem. The second one is
    ORA-02089: COMMIT is not allowed in a subordinate session
    where I can see a way to run DDL commands from within a global transaction, and that won't help me, as the procedures I'm dealing with are stating an explicit commit. Could you point me to the exact site you're referring?
    Furthermore, I'm not sure what do you mean with your suggestion. If the global transaction manager (ODSI) cannot rollback the transactions within the procedures, then wouldn't it be the same semantic as having no global transaction at all, ie no atomicity within the operations?
  • 3. Re: ORA-02089: COMMIT is not allowed in a subordinate session
    mikereiche Pro
    Currently Being Moderated
    Do you want these explicit commits to be rolled back when the global transaction rolls back? Because that seems to be impossible - even outside ODSI. If you can describe how it would be implemented outside of ODSI, then we can see if we can get the same inside of ODSI.
  • 4. Re: ORA-02089: COMMIT is not allowed in a subordinate session
    766624 Newbie
    Currently Being Moderated
    Mike, I don't have a clue as how it would be implemented outside ODSI.. You're right, it's probably impossible. I was just hoping there could be a way to disable the local transactions, but clearly there's not. Thanks for your help anyway, I appreciate it very much.

    Pedro
  • 5. Re: ORA-02089: COMMIT is not allowed in a subordinate session
    mikereiche Pro
    Currently Being Moderated
    You still haven't described what you want to happen. If you want the work that is committed in the stored procedures to be rolled-back when the global transaction is rolled back - that does not seem to be not possible (even outside of ODSI). If leaving that work committed when the global transaction is rolled back - it seems that execute immediate as described in link returned by the Google search would work. (you seem to have made an assumption that execute immediate only works with ddl - although that is what was in the example, I don't know that is actually the case).

    - Mike
  • 6. Re: ORA-02089: COMMIT is not allowed in a subordinate session
    766624 Newbie
    Currently Being Moderated
    Mike, yes, what I wanted was to, when the global transaction rollback, so do the procedures rollback. In other words, I was hoping there would be some way to disable the local transactions and leave everything to the global transaction, managed by ODSI. As you've putted it, there is no way to do this even outside ODSI, so I'm back in square one. I could even try to do what you say and add the 'execute immediatelly' clause to my procedures, but:
    A - I'm not allowed to modify the procedures, and
    B - for me this solution seems to have the same semantics as not having a global transaction at all, so why bother? It seems easier to just change the datasource driver to a non-XA one. Or am I missing something here?
  • 7. Re: ORA-02089: COMMIT is not allowed in a subordinate session
    mikereiche Pro
    Currently Being Moderated
    I'm not allowed to modify the procedures
    Find someone that is.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points