Friday, March 25, 2011

java.sql.SQLSyntaxErrorException: ORA-02089: COMMIT is not allowed in a subordinate session

While trying to call a stored procedure from Oracle SOA 11g BPEL it throws below error
"java.sql.SQLSyntaxErrorException: ORA-02089: COMMIT is not allowed in a subordinate session".

If you are using Global Transactions(XA) in your DB Adapter then the commit will happen only after the BPEL process completes. To avoid the above error :

1. Make sure you don't have explicit commits within the stored procedure as BPEL tries to manage the transaction commit and there is a conflict if the stored proc. has an explicit commit inside it.

2. Also you can use local transactions(non-XA) in your DB Adapter if you don't want to wait till the process is over for the commit to happen.