This content has been marked as final. Show 7 replies
When I Google on the error message that you are reporting :
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?
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?