Calling stored procedures can leave open cursors

[Timmeeeey]

Executing multiple stored procedures via @Procedure can lead to an oracle exception (ORA-01000: maximum open cursors exceeded) because Hibernate keeps cursors open until the statement is closed.

Example:

@Repository
public interface MyRepository extends JpaRepository<MyEntity, Long> {
    @Procedure(procedureName = "myprocedure", outputParameterName = "output")
    int myProcedure();
}

To free the resources following code has to be executed after the outputs are retrieved:

StoredProcedureQuery query = ...;
try {
    query.execute();
    Long output = (Long) query .getOutputParameterValue("output");
} finally {
    query.unwrap(ProcedureOutputs.class).release(); // <- releases resources
}

This probably has to be implemented in following class:
org.springframework.data.jpa.repository.query.JpaQueryExecution.ProcedureExecution

see https://vladmihalcea.com/best-way-call-stored-procedure-jpa-hibernate/

I'm using spring-data-jpa 2.7.10

[gregturn]

Looks like Vlad also succeeded at adding AutoCloseable to Hibernate 6's ProcedureCall via https://hibernate.atlassian.net/browse/HHH-13215 (back in 2019).

Trying to mimic the pattern shown in the article is actually convoluted considering we support both Hibernate and EclipseLink. However, we can detect the existence of AutoCloseable and simply invoke it, ensuring proper cleanup is performed.

[gregturn]

Since this involves Hibernate 6, this should also be backported to Spring Data JPA 3.0 as well.