Error with JPA transaction when calling a stored procedure

Question

I have a stored procedure in an Oracle DB, which I wish to call from my EJB AS (websphere) using the following JPA code. The procedure includes some "COMMIT" in its body. The point is that when I remove the COMMIT of the procedure, JPA is able to invoque the procedure correctly. If I leave the COMMIT then I get an exception telling me that the call cannot be executed.

The java code is placed in a session bean with the transaction set up as REQUIRED by default.

    public void updateProc() {
       Query query = entityManager.createNativeQuery("call UPDATE_MYPROC()");
       query.executeUpdate();
    }

I think the problem has to do with the transaction, but not sure. Can anybody help? Thanks!

Yes, problem is in transactions. What locking mode are you using? — Alexander Fedyukov, Mar 18 '15 at 10:18
And look at [link](http://stackoverflow.com/questions/3572626/calling-stored-procedure-from-java-jpa) — Alexander Fedyukov, Mar 18 '15 at 10:28
If you don't want to remove COMMIT from the procedure, try to set NOT_SUPPORTED for the method in the session bean. — Gas, Mar 18 '15 at 12:33
With NOT_SUPPORTED I get the same problem: ora-02089 commit is not allowed in a subordinate session — narko, Mar 19 '15 at 11:07

score 3 · Accepted Answer · answered Mar 24 '15 at 17:43

You are using the container to manage persistence. By using transaction REQUIRED, JPA will start a new transaction for each call to the database. Therefore it's not necessary to start or commit a transaction within the stored procedure. In fact, it's a bad idea.

You don't mention if you start a new transaction in the stored procedure, just that you have a commit in there. If you don't start a (new) transaction, that commit will cause problems. Even if you do, it's unnecessary, this is the sort of stuff we use JPA for to start with.

Error with JPA transaction when calling a stored procedure

1 Answers1

Linked