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
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN …
Good Luck!
Oracle Fusion Middleware 
Thank you, this helped me to understand and solve my problem! 🙂
Great.
I’m glad to hear 🙂
This knowledge solve my problem. Thank very much for knowledge .
Unny,
I’m glad to hear it helped you.
Arik
Hi Arik,
We are using DB Adapter as poller on database table to trigger the business process (BPEL) in SOA 11g. We want to perform explicit commit (commit;) after the poller updates the records(i.e. logical delete). Is there any way to do so ?
Hi,
When polling from DB, the commit is being done when the message hits the receive activity….
Or maybe I didn’t understand your questions..?
Arik
Oh my goodness! Incredible article dude! Many thanks, However I am experiencing issues with your RSS.
I don’t understand why I am unable to subscribe to it.
Is there anybody getting the same RSS problems?
Anybody who knows the answer will you kindly respond?
Thanx!!
Hi Arik,
I am polling from a table to start transaction and i use column STATUS for logical delete. The status is N when I poll I mark it to P once data is read. In case BPEL process succeeds we mark status as S and in case of error we mark it as E. But for success and error we do not see S or E updated in the table. though the BPEL flow completes successfully. I am using XA transaction throughout.
Avik,
You are using actually 2 DBAdapters with same connection?
If so, mark the “GetActiveUnitOfWork” flag.
Let me know if it helped.
Arik
Thanks a lot , it worked for me, I was not able to change my DataSource Non -XA as it was giving me two phase commit error, Pragma Autonomous helped me.
@ Arik Lalo @Avik Mazumdar
Issue : I am polling from a table to start transaction and i use column STATUS for logical delete. The status is N when I poll I mark it to P once data is read. In case BPEL process succeeds we mark status as S and in case of error we mark it as E. But for success and error we do not see S or E updated in the table. though the BPEL flow completes successfully. I am using XA transaction throughout.
What is the solution that worked ? I am having the same issue
Can we use the statement “PRAGMA AUTONOMOUS_TRANSACTION” for XA connection?
Hi Arun,
As stated in the post, you can.
Arik
Issue : I am polling from a table to start transaction and i use column STATUS for logical delete. The status is N when I poll I mark it to P once data is read. In case osb succeeds we mark status as S and in case of error we mark it as E. But for success and error we do not see S or E updated in the table. though the osb flow completes successfully. I am using Non XA Datasource throughout.
I am facing similar issue in OSB 12c. Please let me know if there is any way i can commit the session after polling the data.
Hi Jane,
Using NonXa in your scenario will not effect the process. Use xa, and route to for the custom update. It should break the transaction and perform the 2 commits.
Arik
Arik,
In my use case i cannot use XA datasource since it is MS SQL Server and the status is getting changed for the next transaction.
Is there is any way to commit the transaction after the record is being polled.
Thanks,
Jane
Jane, did you change the flow to use route to?