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?
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.
Mike, thanks for the help. I beg your pardon, but the first link I get from google is
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?
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.
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.
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, 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?