CODEWITHSUNDEEP
javaplsqloracle11g
Greg
Greg

Reputation: 2627

Oracle ORA-02089 with Java

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

It tests fine from Oracle. Does anyone have any experience with this?

Upvotes: 10

Views: 29890

Answers (3)

Sai Ye Yan Naing Aye
Sai Ye Yan Naing Aye

Reputation: 6738

Try this ways;

  • 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 …

Upvotes: 13

steve
steve

Reputation: 6020

What does the oracle documentation say about the error:

COMMIT was issued in a session that is not the two-phase commit global coordinator.

Basically you are executing a distributed transaction. As part of a distributed transaction you are trying to invoke an autonomous transaction. This is not possible as distributed transactions are required to do a 2PC.

Upvotes: 4

Greg
Greg

Reputation: 2627

Hmm, I think it's related to XA. It works fine when I bracket the stored procedure with 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;

Upvotes: 0

Related Questions