java.sql.SQLRecoverableException: Closed Connection; State=08003; ErrorCode=17008

Question

I am getting the below error while inserting some rows in oracle database table by using loops java my loop will go nearly 25000 times:

java.sql.SQLRecoverableException: Closed Connection; State=08003; ErrorCode=1700
    8
            at oracle.jdbc.driver.PhysicalConnection.prepareStatement(PhysicalConnec
    tion.java:3331)
            at com.mchange.v2.c3p0.impl.NewProxyConnection.prepareStatement(NewProxy
    Connection.java:275)
            at org.compiere.db.PreparedStatementProxy.init(PreparedStatementProxy.ja
    va:71)
            at org.compiere.db.PreparedStatementProxy.<init>(PreparedStatementProxy.
    java:44)
            at org.compiere.db.ProxyFactory.newCPreparedStatement(ProxyFactory.java:
    54)
            at org.compiere.util.DB.executeUpdate(DB.java:1007)
            at org.compiere.util.DB.executeUpdate(DB.java:877)
            at org.compiere.util.DB.executeUpdate(DB.java:864)
            at org.compiere.report.FinReport.insertLineSource(FinReport.java:1033)
            at org.compiere.report.FinReport.insertLineDetail(FinReport.java:844)
            at org.compiere.report.FinReport.doIt(FinReport.java:306)
            at org.compiere.process.SvrProcess.process(SvrProcess.java:147)
            at org.compiere.process.SvrProcess.startProcess(SvrProcess.java:105)

Below is my code:

private void insertLineSource (int line)
{
    log.info("Line=" + line + " - " + m_lines[line]);

    //  No source lines
    if (m_lines[line] == null || m_lines[line].getSources().length == 0)
        return;
    String variable = m_lines[line].getSourceColumnName();
    if (variable == null)
        return;
    log.fine("Variable=" + variable);

    //  Insert
    StringBuffer insert = new StringBuffer("INSERT INTO T_Report "
        + "(AD_PInstance_ID, PA_ReportLine_ID, Record_ID,Fact_Acct_ID,LevelNo ");
    for (int col = 0; col < m_columns.length; col++)
        insert.append(",Col_").append(col);
    //  Select
    insert.append(") SELECT ")
        .append(getAD_PInstance_ID()).append(",")
        .append(m_lines[line].getPA_ReportLine_ID()).append(",")
        .append(variable).append(",0,");
    if (p_DetailsSourceFirst)
        insert.append("-1 ");
    else
        insert.append("1 ");

    //  for all columns create select statement
    for (int col = 0; col < m_columns.length; col++)
    {
        insert.append(", ");
        //  No calculation
        if (m_columns[col].isColumnTypeCalculation())
        {
            insert.append("NULL");
            continue;
        }

        //  SELECT SUM()
        StringBuffer select = new StringBuffer ("SELECT ");
        if (m_lines[line].getPAAmountType() != null)                //  line amount type overwrites column
            select.append (m_lines[line].getSelectClause (true));
        else if (m_columns[col].getPAAmountType() != null)
            select.append (m_columns[col].getSelectClause (true));
        else
        {
            insert.append("NULL");
            continue;
        }

        if (p_PA_ReportCube_ID > 0) {
            select.append(" FROM Fact_Acct_Summary fb WHERE DateAcct ");
        }  //report cube
        else {
        //  Get Period info
            select.append(" FROM Fact_Acct fb WHERE TRUNC(DateAcct, 'DD') ");
        }
        FinReportPeriod frp = getPeriod (m_columns[col].getRelativePeriod());
        if (m_lines[line].getPAPeriodType() != null)            //  line amount type overwrites column
        {
            if (m_lines[line].isPeriod())
                select.append(frp.getPeriodWhere());
            else if (m_lines[line].isYear())
                select.append(frp.getYearWhere());
            else if (m_lines[line].isNatural())
                select.append(frp.getNaturalWhere("fb"));
            else
                select.append(frp.getTotalWhere());
        }
        else if (m_columns[col].getPAPeriodType() != null)
        {
            if (m_columns[col].isPeriod())
                select.append(frp.getPeriodWhere());
            else if (m_columns[col].isYear())
                select.append(frp.getYearWhere());
            else if (m_columns[col].isNatural())
                select.append(frp.getNaturalWhere("fb"));
            else
                select.append(frp.getTotalWhere());
        }
        //  Link
        select.append(" AND fb.").append(variable).append("=x.").append(variable);
        //  PostingType
        if (!m_lines[line].isPostingType())     //  only if not defined on line
        {
            String PostingType = m_columns[col].getPostingType();
            if (PostingType != null && PostingType.length() > 0)
                select.append(" AND fb.PostingType='").append(PostingType).append("'");
            // globalqss - CarlosRuiz
            if (PostingType.equals(MReportColumn.POSTINGTYPE_Budget)) {
                if (m_columns[col].getGL_Budget_ID() > 0)
                    select.append(" AND GL_Budget_ID=" + m_columns[col].getGL_Budget_ID());
            }
            // end globalqss
        }
        //  Report Where
        String s = m_report.getWhereClause();
        if (s != null && s.length() > 0)
            select.append(" AND ").append(s);
        //  Limited Segment Values
        if (m_columns[col].isColumnTypeSegmentValue())
            select.append(m_columns[col].getWhereClause(p_PA_Hierarchy_ID));

        //  Parameter Where
        select.append(m_parameterWhere);
    //  System.out.println("    c=" + col + ", l=" + line + ": " + select);
        //
        insert.append("(").append(select).append(")");
    }
    //  WHERE (sources, posting type)
    StringBuffer where = new StringBuffer(m_lines[line].getWhereClause(p_PA_Hierarchy_ID));
    String s = m_report.getWhereClause();
    if (s != null && s.length() > 0)
    {
        if (where.length() > 0)
            where.append(" AND ");
        where.append(s);
    }
    if (where.length() > 0)
        where.append(" AND ");
    where.append(variable).append(" IS NOT NULL");

    if (p_PA_ReportCube_ID > 0)
        insert.append(" FROM Fact_Acct_Summary x WHERE ").append(where);
    else
    //  FROM .. WHERE
    insert.append(" FROM Fact_Acct x WHERE ").append(where);    
    //
    insert.append(m_parameterWhere)
        .append(" GROUP BY ").append(variable);

    int no = DB.executeUpdate(insert.toString(), get_TrxName());
    if (CLogMgt.isLevelFinest())
        log.fine("Source #=" + no + " - " + insert);
    if (no == 0)
        return;

    //  Set Name,Description
    StringBuffer sql = new StringBuffer ("UPDATE T_Report SET (Name,Description)=(")
        .append(m_lines[line].getSourceValueQuery()).append("T_Report.Record_ID) "
        //
        + "WHERE Record_ID <> 0 AND AD_PInstance_ID=").append(getAD_PInstance_ID())
        .append(" AND PA_ReportLine_ID=").append(m_lines[line].getPA_ReportLine_ID())
        .append(" AND Fact_Acct_ID=0");
    no = DB.executeUpdate(sql.toString(), get_TrxName());
    if (CLogMgt.isLevelFinest())
        log.fine("Name #=" + no + " - " + sql.toString());

    if (m_report.isListTrx())
        insertLineTrx (line, variable);
}   //  insertLineSource

Answer 1

Application code should not pass a cached connection handle from one instance of a data access client to another client instance. Transferring the connection handle between client instances creates the problematic contingency of one instance using a connection handle that is referenced by another. For example, when the application code of a client instance that receives a transferred handle closes the handle and the client instance that retains the original reference to the handle tries to reclaim it, the application server issues an exception. shown some exceptions expected in this case. Exception description: An exception was detected cleaning up the ManagedConnection for a destroy operation. Refer to the error reported by the database software to help determine the cause of the error. Exception

0000004d WSRdbManagedC W   DSRA0180W: Exception detected during 
ManagedConnection.destroy().  The exception is:  
com.ibm.ws.exception.WsException: DSRA0080E: An exception was received by 
 the Data Store Adapter Invalid operation: Connection is closed. ERRORCODE=- 
4470, SQLSTATE=08003. With SQL State: 08003 SQL Code : -4470

Read this for more unertstanding

Answer 2

Maybe the code spends too much time doing stuff between opening the database connection and preparing the PreparedStatement, or between other database operations and preparing the next PreparedStatement, so that the connection is closed due to inactivity.

The connection might be either closed by Oracle, or by C3P0, so check your C3P0 configuration for timeout settings. Also consider getting the connection later in the execution.

Answer 3

Maybe you have hit a bug in C3P0 related to Oracle connections.

Answer 4

Maybe you have a transient network error connecting to the database instance (this assumes that the database is located on another computer; this is not going to be the case if the database is on the same computer).

To avoid this problem stopping execution, you should catch such RecoverableExceptions, get a new connection from the connection pool, and then retry the database operation.