The JPA createStoredProcedureQuery throws "ORA-01000: maximum open cursors exceeded" when using Hibernate

Question

I try to call Oracle stored procedure using "createStoredProcedureQuery" of EntityManager by this way:

@Transactional(readOnly = false, propagation = Propagation.REQUIRED, isolation = Isolation.READ_COMMITTED)
public void saveMeterVol(Meter meter, Double vol1, Chng chng, User user, Date dt1, Date dt2) {
    StoredProcedureQuery qr = em.createStoredProcedureQuery("mt.P_METER.meter_vol_ins_upd_java");
    qr.registerStoredProcedureParameter(1, Integer.class, ParameterMode.OUT);
    qr.registerStoredProcedureParameter(2, Integer.class, ParameterMode.IN);
    qr.registerStoredProcedureParameter(3, Integer.class, ParameterMode.IN);
    qr.registerStoredProcedureParameter(4, Double.class, ParameterMode.IN);
    qr.registerStoredProcedureParameter(5, Date.class, ParameterMode.IN);
    qr.registerStoredProcedureParameter(6, Date.class, ParameterMode.IN);
    qr.registerStoredProcedureParameter(7, String.class, ParameterMode.IN);

    qr.setParameter(2, meter.getId());
    qr.setParameter(3, chng.getId());
    qr.setParameter(4, vol1);
    qr.setParameter(5, dt1);
    qr.setParameter(6, dt2);
    qr.setParameter(7, user.getCd());
    qr.execute();
}

When I call this method over 300 times, Oracle fall into exception: ORA-01000: maximum open cursors exceeded

As I understand, Java doesn't close Oracle cursor after call my procedure, but I don't understand why?

I tried to do

em.close();

but it didn't help.

I use:

<spring-framework.version>5.0.5.RELEASE</spring-framework.version>
<hibernate.version>5.1.0.Final</hibernate.version>
<java.version>1.8</java.version>

Answer 1

The default CallableStatement handling mechanism

When calling the execute method on the JPA StoredProcedureQuery or outputs().getCurrent() on the Hibernate ProcedureCall, Hibernate executes the following actions:

Notice that a JDBC CallableStatement is prepared and stored in the associated ProcedureOutputsImpl object. When calling the getOutputParameterValue method, Hibernate will use the underlying CallableStatement to fetch the OUT parameter.

For this reason, the underlying JDBC CallableStatement remains open even after executing the stored procedure and fetching the OUT or REF_CURSOR parameters.

Now, by default, the CallableStatement is closed upon ending the currently running database transaction, either via calling commit or rollback.

Closing the JDBC statement as soon as possible

Therefore, to close the JDBC CallableStatement as soon as possible, you should call release after fetching all the data that you wanted from the stored procedure:

StoredProcedureQuery query = entityManager
.createStoredProcedureQuery("count_comments")
.registerStoredProcedureParameter(
    "postId",
    Long.class,
    ParameterMode.IN
)
.registerStoredProcedureParameter(
    "commentCount",
    Long.class,
    ParameterMode.OUT
)
.setParameter("postId", 1L);
 
try {
    query.execute();
     
    Long commentCount = (Long) query
    .getOutputParameterValue("commentCount");
 
    assertEquals(Long.valueOf(2), commentCount);
} finally {
    query.unwrap(ProcedureOutputs.class).release();
}

Calling the release method on the associated ProcedureOutputs object in the finally block ensures that the JDBC CallableStatement is closed no matter the outcome of the stored procedure call.

Hibernate 6 onwards

Now, calling release manually is a little bit tedious, so I decided to create the HHH-13215 Jira issue which, from Hibernate ORM 6 onwards, allows you to rewrite the previous example like this:

Long commentCount = doInJPA(entityManager -> {
    try(ProcedureCall query = entityManager
            .createStoredProcedureQuery("count_comments")
            .unwrap(ProcedureCall.class)) {
             
        return (Long) query
        .registerStoredProcedureParameter(
            "postId",
            Long.class,
            ParameterMode.IN
        )
        .registerStoredProcedureParameter(
            "commentCount",
            Long.class,
            ParameterMode.OUT
        )
        .setParameter("postId", 1L)
        .getOutputParameterValue("commentCount");
    }
});

Answer 2

Eventually I found the solution, I replaced the line

qr.execute();

with

qr.executeUpdate();

According documetation: "When executeUpdate is called on a StoredProcedureQuery object, the provider will call execute on an unexecuted stored procedure query followed by getUpdateCount. The results of executeUpdate will be those of getUpdateCount"

But they didn't say anything conserning closing cursors but my method works well now.

Answer 3

Here is my solution:

int           retValue = -1;
ProcedureCall query    = null;

try {
  query = (ProcedureCall)entityManager.createNamedStoredProcedureQuery("MyStoredProcedure");

  query
    .setParameter("param1", value1)
    .setParameter("param2", value2)
    .execute();

  retValue = (int)query.getOutputParameterValue("outParam");
} finally {
  query.getOutputs().release();
}