Oracle ORA-02089 with Java

Question

I get the following error when trying to call a PL / SQL stored procedure from Java: ORA-02089: COMMIT is not allowed in a subordinate session

He is well versed in Oracle. Does anyone have any experience?

Answer 1

Try these methods;

• Change the data source to use Non-XA (and check the "Support Global Transactions" and "Emulate Two-Phase Commit" boxes)
• Remove COMMIT from your code.
• Use "PRAGMA AUTONOMOUS_TRANSACTION". This will allow you to create a separate transaction that allows you to use commit. Example: CREATION PROCEDURE XXX AS PRAGMA AUTONOMOUS_TRANSACTION; TO BEGIN ...

Answer 2

What says about error in oracle documentation:

COMMIT was released at a session that is not a biphasic global coordinator.

Basically you are doing a distributed transaction. As part of a distributed transaction, you are trying to invoke an autonomous transaction. This is not possible since distributed transactions are necessary for 2PC to work.

Answer 3

Hmm, I think this is due to XA. It works fine when I copy the stored procedure using AUTONOMOUS_TRANSACTION Pragma :

 PROCEDURE foo (val IN VARCHAR2(4000)) is PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO tbl1 VALUES (val); DELETE FROM tbl2; COMMIT; END foo;