Cursor leak in PL/SQLcall SpringMVC jdbcTemplate

Question

I have created a function that calls a PL/SQL inside a for and it is creating cursor leaks.The PL/SQL works fine, and it returns the desired data, but I have noticed that the number of cursors increases until ora-1000 maximum open cursors exceeded and ORA-00604: error occurred at recursive SQL level 1 errors are thrown. To check the number of used cursors, I have used the following SQL sentence:

select * 
from
 (select a.value, s.username, s.sid, s.serial#
 from v$sesstat a, v$statname b, v$session s
 where a.statistic# = b.statistic#  
 and s.sid=a.sid
 and b.name = 'opened cursors current')
where sid = 'mySID';

I have debugged my code and I have discovered that the cursors are created here: objectXStructResult.getAttributes()[X]. I have tried to close the resultset and the callableStatement but it isn´t working. Can anyone help me?

    function callToDao(){
      for(Object X:LstObject){
        plsqlCall(X);
      }
    }

    funcion ObjectX plsqlCall(Object X){
        Object salidaX = null;
        //Obtención de parámetros de consulta
        final String p_x1 = X.getX();
        final String p_x2 = X.getX();
        final String p_x3 = X.getX();
        final String p_x4 = X.getX();
        final String p_x5 = X.getX();
        final String p_x6 = X.getX();
        final String p_x7 = X.getX();

        try{
            CallableStatementCreator csCreator = new CallableStatementCreator() {

                @Override
                public CallableStatement createCallableStatement(Connection con) throws SQLException {
                    CallableStatement cs = null;
                    try {

                        cs = con.prepareCall("{call PK_XXXXX.XXXXXX(?, ?, ?, ?, ?, ?, ?, ?) }");

                        cs.setInt(1, Integer.parseInt(p_x1));                        
                        cs.setString(2, p_x2);                          
                        cs.setString(3, p_x3);                          
                        cs.setLong(4, Long.parseLong(p_x4));                           
                        cs.setDouble(5,Double.parseDouble(p_x5));                           
                        cs.setDouble(6, Double.parseDouble(p_x6));                          
                        cs.setDouble(7, Double.parseDouble(p_x7));

                        cs.registerOutParameter(8, OracleTypes.STRUCT,"OBJECT_PLSQL");

                    } catch (SQLException e) {
                        e.printStackTrace();                
                    }
                    return cs;
                }
            };
            CallableStatementCallback csCallback = new CallableStatementCallback() {

                public Object doInCallableStatement(CallableStatement cs) throws SQLException {
                    ObjectX ret = null;
                    ResultSet rs = null;
                    try {
                        cs.execute();
                        ret = obtainObjectX(cs, rs, objectPos, type);
                    } catch (SQLException e) {
                        e.printStackTrace();                
                    }
                    return ret;
                }
            };

            salida = (ObjectX) this.jdbcTemplate.execute(csCreator,csCallback);
        }catch (Exception e) {
            e.printStackTrace();
        }

        return salida;
    }

       private ObjectX obtainObjectX(CallableStatement cs, ResultSet rs, int objectPos, String p_tipo) throws SQLException{

        ObjectX objectX= new ObjectX();
        List<ObjectX> objectXLst= new ArrayList<ObjectX>();

        try{
            //Obtain exit parameter
            STRUCT objectXStructResult = (STRUCT)cs.getObject(objectPos);   

            //Obtain Struct data
            String att1 = (String)objectXStructResult.getAttributes()[1];
            String att2 = (String)objectXStructResult.getAttributes()[5];

            BigDecimal att3 = (BigDecimal)objectXStructResult.getAttributes()[6];
            String att4 = (String)objectXStructResult.getAttributes()[7];

            //Control de error en la obtención de datos
            if (new BigDecimal("0").equals(att3)){ 
                //Obtención de los datos de salida
                ARRAY listaDatos = (ARRAY)objectXStructResult.getAttributes()[0];
                if (listaDatos!=null){
                    rs = listaDatos.getResultSet();

                    int rowNum = 0;
                    //Recorrido del listado de datos
                    while (rs.next()) {
                        STRUCT dataStruct= (STRUCT) listaDatos.getOracleArray()[rowNum];
                        ObjectX objXFor= this.mapRow(dataStruct, rowNum);
                        objectXLst.add(objXFor);
                        rowNum++;
                    }
                }
                objectX.setAtt1(att1);
                objectX.setAtt2(att2);

                objectX.setAtt3(new Long(att3.toString()));
                objectX.setAtt4(att4);
                objectX.setData(objectXLst); 

            }
        } catch (Exception e) {
            e.printStackTrace();            
        }

        return objectX;
    }


      private ObjectX mapRow(STRUCT dataStruct, int rowNum) throws SQLException {
        Object[] objectInfo = dataStruct.getAttributes();

        //Obtención de datos de la estructura de base de datos
        BigDecimal att1= ((BigDecimal)objectInfo[0]);
        BigDecimal att2= (BigDecimal)objectInfo[1];
        String att3= (String)objectInfo[2];
        String att4= (String)objectInfo[3];

        return new ObjectX(att1, att2, null, att3, null, null, att4, null);
    }

UPDATE 1:

I have managed to reduce the creation of cursors from 5 to 1 changing the code, but I can´t still find the way to close that cursor.

From:

        String att1 = (String)objectXStructResult.getAttributes()[1];
        String att2 = (String)objectXStructResult.getAttributes()[5];

        BigDecimal att3 = (BigDecimal)objectXStructResult.getAttributes()[6];
        String att4 = (String)objectXStructResult.getAttributes()[7];

        //Control de error en la obtención de datos
        if (new BigDecimal("0").equals(att3)){ 
            //Obtención de los datos de salida
            ARRAY listaDatos = (ARRAY)objectXStructResult.getAttributes()[0];
        }

To:

        Object[] atributos = objectXStructResult.getAttributes();
        String att1 = (String)atributos[1];
        String att2 = (String)atributos[5];

        BigDecimal att3 = (BigDecimal)atributos[6];
        String att4 = (String)atributos[7];

        //Control de error en la obtención de datos
        if (new BigDecimal("0").equals(att3)){ 
            //Obtención de los datos de salida
            ARRAY listaDatos = atributos[0];
        }

mkuligowski mkuligowski · Accepted Answer · 2018-04-13T06:18:09

NOTE: Any ResultSets opened should be closed in finally blocks within the callback implementation. Spring will close the Statement object after the callback returned, but this does not necessarily imply that the ResultSet resources will be closed: the Statement objects might get pooled by the connection pool, with close calls only returning the object to the pool but not physically closing the resources.

Notice that jdbcTemplate will close csCreator and csCallback automatically, but you have to always take care of ResultSet. Remember to close them in finally block, in order to make sure that when an exception occurs, then your close() will be invoked.

So for example, your current statement:

ResultSet rs = null;
try {
    cs.execute();
    ret = obtainObjectX(cs, rs, objectPos, type);
} catch (SQLException e) {
    e.printStackTrace();
}

should be replaced with:

ResultSet rs = null;
try {
    cs.execute();
    ret = obtainObjectX(cs, rs, objectPos, type);
} catch (SQLException e) {
    e.printStackTrace();
} finally{
    if(rs != null)
        rs.close()
}

UPDATE: From oracle documentation:

By default, auto-indexing is not enabled. For a JDBC application, enable auto-indexing for ARRAY objects if random access of array elements may occur through the getArray and getResultSet methods.

So maybe this will reduce open cursors on database:

array.setAutoIndexing(true);

By the way, oracle.sql.ARRAY class is deprecated:

Deprecated. Use java.sql.Array interface for declaration instead of using concrete class oracle.sql.ARRAY.

Cursor leak in PL/SQLcall SpringMVC jdbcTemplate

1 Answers