【oracle】【excel导入】ORA-01000: 超出打开游标的最大数

最新推荐文章于 2024-03-04 16:10:16 发布

原创最新推荐文章于 2024-03-04 16:10:16 发布 · 1.3k 阅读

0 ·

CC 4.0 BY-SA版权

oarcle 专栏收录该内容

2 篇文章

订阅专栏

这篇博客主要记录了一个在使用MyBatis进行批量数据插入时遇到的问题，即Oracle数据库中出现'ORA-00604:递归SQL级别1出现错误'和'ORA-01000:超出打开游标的最大数'的错误。作者通过调整代码，将数据分批提交，每次提交99条，成功解决了这个问题。同时，展示了修改后的代码实现，提高了批量插入的效率。

报错信息

2021-04-13 14:48:06,154 ERROR (ExceptionHandler.java:49) - An error occurred in com.bosssoft.helpPool.controller.backend.CapitalController#readImpFile

org.apache.ibatis.exceptions.PersistenceException:

### Error committing transaction. Cause: org.apache.ibatis.executor.BatchExecutorException: com.bosssoft.helpPool.mapper.CapitalMapper.saveCapital (batch index #298) failed. 297 prior sub executor(s) completed successfully, but will be rolled back. Cause: java.sql.BatchUpdateException: ORA-00604: 递归 SQL 级别 1 出现错误

ORA-01000: 超出打开游标的最大数

ORA-01000: 超出打开游标的最大数

### Cause: org.apache.ibatis.executor.BatchExecutorException: com.bosssoft.helpPool.mapper.CapitalMapper.saveCapital (batch index #298) failed. 297 prior sub executor(s) completed successfully, but will be rolled back. Cause: java.sql.BatchUpdateException: ORA-00604: 递归 SQL 级别 1 出现错误

ORA-01000: 超出打开游标的最大数

ORA-01000: 超出打开游标的最大数

at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:26) ~[mybatis-3.2.8.jar:3.2.8]

at org.apache.ibatis.session.defaults.DefaultSqlSession.commit(DefaultSqlSession.java:177) ~[mybatis-3.2.8.jar:3.2.8]

at org.apache.ibatis.session.defaults.DefaultSqlSession.commit(DefaultSqlSession.java:169) ~[mybatis-3.2.8.jar:3.2.8]

at com.bosssoft.helpPool.service.impl.CapitalServiceImpl.saveCapital(CapitalServiceImpl.java:174) ~[help-pool-service-impl-0.0.1-SNAPSHOT.jar:?]

at com.bosssoft.helpPool.service.impl.CapitalServiceImpl.impData(CapitalServiceImpl.java:969) ~[help-pool-service-impl-0.0.1-SNAPSHOT.jar:?]

解决：一次提交99条

@Transactional
public void saveCapitalAdmin(Map<String, Integer> map, List<Capital> capitalList, String tableName) {
    //由于框架自身的mybatis在批量新增的时候，每次都要新建一个sqlsession，效率很低，并且它不读mybatis-config，所以
    //这里采用手动获取批处理sqlsession的方式来处理
    SqlSession sqlSession=getBatchSession();
    CapitalMapper capitalMapper1=sqlSession.getMapper(CapitalMapper.class);
    long startTime = System.currentTimeMillis();
    int i = 0;
    for (Capital capital : capitalList) {
        String uuid = NBUtil.createUUID();
        capital.setCapitalId(uuid);
        capital.setOperationTime(new Date());
        capital.setOperationUserId(TokenManager.getUserCode());
        capital.setOperationRelName(TokenManager.getToken().getUserName());
        capital.setIdcard(capital.getIdcard().toUpperCase());
        capital.setName(capital.getName().replaceAll("　", ""));
        capital.setIsFormula("01");
        // 判断是否含有字母，含有字母只去前后空格
        if (NBUtil.judgeContainsStr(capital.getName())){
             capital.setName(capital.getName().trim());
        }else{
            capital.setName(capital.getName().replaceAll(" ", ""));
        }
        capitalMapper1.saveCapital(capital, tableName);
        //统计年份
        map.put(capital.getGrantYear(), null == map.get(capital.getGrantYear()) ? 1 : map.get(capital.getGrantYear()) + 1);
        ///99 条提交一次
        i++;
        if(i==99){
            sqlSession.commit();
            logger.info("》》》》》》》》》》》》提交99条");
            i=0;
        }
    }
    long endTime = System.currentTimeMillis();
    sqlSession.commit();
    System.out.println("程序运行时间：" + (endTime - startTime) + "ms");
    sqlSession.close();
}