使用JPA createStoredProcedureQuery调用Oracle存储过程触发ORA-01000游标耗尽错误

我通过EntityManager的createStoredProcedureQuery方法调用Oracle存储过程，代码实现如下：

@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();
}

当调用该方法超过300次后，Oracle抛出ORA-01000: maximum open cursors exceeded异常。我推测Java未在调用存储过程后关闭Oracle游标，但不清楚具体原因。尝试调用em.close()也未能解决问题。

使用的环境版本：

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

请问该如何解决这个游标耗尽的问题？

这个问题我之前做项目时也碰到过，核心原因是Hibernate对存储过程查询的底层JDBC资源（比如游标）没有及时释放，尤其是高频循环调用的场景下，资源堆积就触发了游标耗尽。给你几个实操性强的解决思路：

1. 手动释放StoredProcedureQuery的底层资源 #

StoredProcedureQuery是Hibernate的包装类，底层持有JDBC的CallableStatement对象。你可以在执行完后手动获取并关闭这个原生对象，或者调用Hibernate提供的release方法强制释放资源。修改后的代码如下：

@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");
    try {
        // 注册参数、设置参数的逻辑保持不变
        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();
        
        // 如果需要获取OUT参数，在这里处理
        // Integer result = (Integer) qr.getOutputParameterValue(1);
    } finally {
        // 方式1：调用Hibernate的release方法释放资源
        if (qr instanceof org.hibernate.procedure.ProcedureCall) {
            ((org.hibernate.procedure.ProcedureCall) qr).release();
        }
        // 方式2：直接获取JDBC原生CallableStatement并关闭（双重保障）
        try {
            CallableStatement cs = qr.unwrap(CallableStatement.class);
            if (cs != null && !cs.isClosed()) {
                cs.close();
            }
        } catch (SQLException e) {
            // 只记录日志，不要抛出异常影响主流程
            e.printStackTrace();
        }
    }
}

2. 调整Hibernate配置强制资源释放 #

在Hibernate的配置文件（比如hibernate.cfg.xml或Spring的application.properties）中添加以下配置，优化资源回收策略：

• hibernate.jdbc.batch_size=50：设置批量处理大小，减少连接和游标创建次数
• hibernate.connection.release_mode=after_statement：执行完语句后立即释放连接资源（注意：Spring事务管理下，这个配置会受事务传播行为影响，需结合实际场景调整）
• hibernate.proc.param_null_passing=true：避免因参数为null导致的资源泄漏（这是Hibernate处理存储过程参数的一个常见坑）

3. 复用StoredProcedureQuery对象（循环场景下） #

如果你的调用是在循环中执行的，不要每次循环都创建新的StoredProcedureQuery，可以复用同一个对象，只更新参数即可：

// 初始化一次查询对象，注册所有参数
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);
// ... 注册其他参数

// 循环中复用查询对象
for (...) {
    qr.setParameter(2, meter.getId());
    qr.setParameter(3, chng.getId());
    // ... 设置其他参数
    qr.execute();
    // 重置参数绑定，避免下一次循环参数残留
    ((org.hibernate.procedure.ProcedureCall) qr).getParameterBindings().clear();
}

4. 临时调整Oracle游标上限（应急方案） #

如果需要快速缓解问题，可以临时调大Oracle的open_cursors参数（默认是300），但这只是治标不治本，核心还是要解决资源泄漏：

ALTER SYSTEM SET open_cursors = 1000 SCOPE=BOTH;

5. 升级Hibernate版本 #

你使用的Hibernate 5.1.0.Final确实存在一些存储过程资源泄漏的已知bug，升级到5.2.x及以上版本（比如5.2.18.Final）可以修复这些问题，这是最彻底的解决方案之一。

建议先尝试第一种手动释放资源的方法快速解决当前问题，再逐步排查配置和版本层面的优化点。

内容的提问来源于stack exchange，提问作者Leo