Announcement Announcement Module
Collapse
No announcement yet.
Open Cursors at Oracle end - Spring using RowMapper Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Open Cursors at Oracle end - Spring using RowMapper

    Hi,

    I'm using the SpringJDBCDao Support and extending the StoredProcedure class. The cursor that is returned is caught in a row mapper and is processed by method maprow.

    The connection is created by Spring by passing the datasources object to the StoreProcedure constructor. The datasource is looked up from the weblogic connection pool using JNDI.

    The connection pool points to Oracle 10g database. We are using ojdbc14.jar to compile our application.

    nowhere is the application explicitly opening a connection nor closing it.
    Nowhere is the application opening a cursor or closing it, same with statements.

    Is there a way to explicitly close datasource, resultset or statement i n the java code.

    at the weblogic connnection pool,

    Statement cache =0
    Inactive connection timeout=3 [seconds]
    Statement timeout=60 [seconds]

    I'm getting open cursors at the weblogic end.
    the query to check open cursors is :


    select max(a.value) as highest_open_cur,s.sid, s.username, oc.sql_text, s.logon_time, s.status, s.machine
    from v$sesstat a, v$statname b, v$parameter p, v$session s, v$open_cursor oc
    where a.statistic# = b.statistic#
    and b.name = 'opened cursors current'
    and p.name= 'open_cursors'
    and username in ('USER_ID')
    and s.sid = a.sid
    -- and s.status <> 'KILLED'
    and oc.sid = s.sid
    group by s.sid,s.username, oc.sql_text, s.logon_time, s.status, s.machine
    order by s.logon_time desc



    =================================

    code which implements StoredProcedure



    import java.sql.SQLException;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;

    import javax.sql.DataSource;

    import oracle.jdbc.OracleTypes;

    import org.springframework.jdbc.core.SqlOutParameter;
    import org.springframework.jdbc.core.SqlParameter;
    import org.springframework.jdbc.object.StoredProcedure;

    public class CallGetBillingLevels extends StoredProcedure {

    private static final String STORED_PROC_NAME = "OPNSUTIL.SEARCH_MODULE.sp_get_billing_levels" ;
    public CallGetBillingLevels(DataSource ds) throws SQLException {

    super(ds, STORED_PROC_NAME);
    BillingLevelsRowMapper billLevelMapper = new BillingLevelsRowMapper();
    declareParameter(new SqlParameter("i_corp", OracleTypes.VARCHAR));
    declareParameter(new SqlParameter("i_fleet", OracleTypes.VARCHAR));
    declareParameter(new SqlParameter("i_level2", OracleTypes.VARCHAR));
    declareParameter(new SqlParameter("i_level3", OracleTypes.VARCHAR));
    declareParameter(new SqlParameter("i_level4", OracleTypes.VARCHAR));
    declareParameter(new SqlParameter("i_level5", OracleTypes.VARCHAR));
    declareParameter(new SqlParameter("i_level6", OracleTypes.VARCHAR));
    declareParameter(new SqlParameter("i_lvlnbr", OracleTypes.VARCHAR));
    declareParameter(new SqlParameter("i_userid", OracleTypes.VARCHAR));
    declareParameter(new SqlParameter("i_user_type", OracleTypes.VARCHAR));
    declareParameter(new SqlOutParameter("o_billing_levels", OracleTypes.CURSOR, billLevelMapper));
    declareParameter(new SqlOutParameter("o_error_message", OracleTypes.VARCHAR));
    declareParameter(new SqlOutParameter("o_error_code", OracleTypes.VARCHAR));
    compile();
    }

    public String execute(String corp, String fleet, String l2, String l3, String l4, String l5, String lNo, String uid, String uType) throws SQLException,Exception {
    Map<String, String> inParams = new HashMap<String, String>(3);
    inParams.put("i_corp", corp);
    inParams.put("i_fleet", fleet);
    inParams.put("i_level2", l2);
    inParams.put("i_level3", l3);
    inParams.put("i_level4", l4);
    inParams.put("i_level5", l5);
    inParams.put("i_level6", "");
    inParams.put("i_lvlnbr", lNo);
    inParams.put("i_userid", uid);
    inParams.put("i_user_type", uType);

    logger.debug(new StringBuilder(corp).append(" ").append(fleet).append(" ").append(l2).append(" ").append(l3).append(" ").append(l4).append(" ").append(l5).append(" ").append(lNo).append(" ").append(uid).append(" ").append(uType));

    StringBuilder xmlOut= new StringBuilder(30);
    logger.debug("execute(inParams)" + inParams);
    Map resultMap = execute(inParams);
    logger.debug("#### executed, return o_billing_levels ==[" + resultMap.get("o_billing_levels"));
    List resultList = (List) resultMap.get("o_billing_levels");

    for(Object s:resultList){
    String level = (String)s;
    if (level.indexOf('&') != -1)
    level = level.substring(0, level.indexOf('&')) + "&amp;" + level.substring(level.indexOf('&') + 1);
    xmlOut.append("<L>").append(level).append("</L>");
    }
    return xmlOut.toString();
    }

    }

    =============================================

    Rowmapper classs


    import java.sql.ResultSet;
    import java.sql.SQLException;

    import org.springframework.jdbc.core.RowMapper;

    public class BillingLevelsRowMapper implements RowMapper {
    public BillingLevelsRowMapper() {

    }

    public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
    return rs.getString(1);
    }

    }

    ===========================================

    I tried closing the resultset in the maprow function... but then Spring call this method for every row returned and even if i do mange to close it throws an error.
    ============================================

    DAO extending JDBCDAOSupport
    --------------------------------


    import org.springframework.jdbc.core.support.JdbcDaoSuppo rt;

    public abstract class AbstractDAO extends JdbcDaoSupport {

    }

  • #2
    sorry,
    I'm getting open cursors at oracle end. And it stays there for hours.

    Comment


    • #3
      Same here with oracle 10G and spring 1.2.9

      I'm having the exact same problem. I debugged the application and can trace it down to a stored procedure call with a cursor out parameter.
      Did you ever solve this ? Anyone ?

      Comment

      Working...
      X