When calling a stored procedure from SOA Suite 11g (Bpel/OSB/BPM ….) , you might get the following error:
java.sql.SQLSyntaxErrorException: ORA-02089: COMMIT is not allowed in a subordinate session
The reason for that error(you can use one of them):
You have defined a data source base on Global Transaction (XA)
In a Global Transaction, the ‘Two-Phase Commit’ protocol for global transaction processing is automatically being selected. It means that the Weblogic server is responsible to complete all of the transactions (or non of them).
In short – adding a COMMIT to your code (being invoked) is not allowed.
There are few ways to solve this error:
- Change the data source to use Non-XA
(and check the “Supports Global Transactions” & “Emulate Two-Phase Commit” buttons)
- Delete the COMMIT from your code.
- Use the “PRAGMA AUTONOMOUS_TRANSACTION“. This will kind of create a separate transaction that will allow to use a commit.For example:CREATE PROCEDURE XXX AS