Size limit in OracleCallableStatement setARRAY

[Vikram Phale]

Sorry for posting this article somewhere else before.

I am trying to pass an array object of type oracle.sql.ARRAY to Oracle stored procedure; most of the times I am successful at that. It was found after a customer reported an error that when the array had certain number of elements in it, the code failed. I didnt find out the exact threshold value at which the code broke. Upto around 1000 elements in the array it worked fine. At 1500 it failed. I am working with WebLogic Server 8.1 SP4, Oracle at client 9.2.0.5 and at server 9.2.0.6. I am using direct JDBC OCI connection and not the Weblogic connection pool. Below is the code which is causing the problem.

import com.pearson.assessment.foundation.vendorspecific.log4j.Log4JCategoryFactory;
import com.pearson.assessment.persistence.utils.Constants;
import com.pearson.assessment.persistence.utils.DBConnection;
import com.pearson.commonsys.app.persistence.ConnectionFetchException;
import com.pearson.commonsys.app.persistence.DataAccessException;
import com.pearson.commonsys.app.persistence.SqlStringLoader;
import com.pearson.commonsys.app.persistence.SqlStringLoadingException;
import com.pearson.commonsys.exception.ExceptionUtil;
import com.pearson.report.domain.*;
import com.pearson.report.integration.ReportCriteriaTO;
import com.pearson.report.integration.StandardsTO;
import com.pearson.security.util.WSF_UserContext;
import oracle.jdbc.OracleTypes;
import oracle.jdbc.driver.OracleCallableStatement;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;
import org.apache.log4j.Category;
 
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Connection;
import java.util.StringTokenizer;
 
 
public class StudentMasteryDAO
{
    private static Category log = Log4JCategoryFactory.getCategoryInstanceFor(StudentMasteryDAO.class.getName());
 
    private com.pearson.common.persistence.DBConnectionManager directCAMRConnMgr;
    private SqlStringLoader sqlStringLoader = null;
    private static final String SQL_XML_FILE = "ReportsSQL";
    private static final String GET_STUDENT_MASTERY_DATA = "Get_Student_Mastery_Data";
    private static final String GET_STANDARDS_FOR_ASSIGNMENTS = "Get_Standards_For_Assignments";
    
    public StudentMasteryDAO() throws DataAccessException
    {
            try
            {
                DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
                sqlStringLoader = SqlStringLoader.getInstance();
                directCAMRConnMgr = com.pearson.common.persistence.SqlBeanDBPool
                        .getInstance("StudentMasteryDAO", Constants.CAMR_PROPERTIES_PATH).getDbConnMgr();
            }
            catch (SQLException sqlex)
            {
                log.error("Caught SQLException in StudentMasteryDAO() constructor: " + sqlex);
                throw new DataAccessException("Unable to create student mastery data access object.", sqlex);
            }
            catch (ConnectionFetchException cfe)
            {
                log.error("Caught ConnectionFetchException in StudentMasteryDAO() constructor: " + cfe);
                throw new DataAccessException("Unable to create student mastery data access object.", cfe);
            }
    }
    
    public StudentMasteryDO getStudentMasteryData(WSF_UserContext UserContext, ReportCriteriaTO reportCriteriaTO)
            throws DataAccessException
    {
 
        //debugging
        boolean debug = log.isDebugEnabled();
        
        DBConnection con = null;
        java.sql.Connection m_connection = null;
        OracleCallableStatement cstmtReportData = null;
        ResultSet rsStudentAssignment = null;
        ResultSet rsStandardDetails = null;
        ResultSet rsStudentStandard = null;
        ResultSet rsStudentDetails = null;
        ArrayList studentMasteryHeaderDOList = new ArrayList();
        ArrayList studentAssignmentDOList = new ArrayList();
        ArrayList studentStandardDOList = new ArrayList();
 
        StudentMasteryHeaderDO studentMasteryHeaderDO;
        StudentAssignmentDO studentAssignmentDO = null;
        StudentStandardDO studentStandardDO = null;
        StandardDetailDO standardDetailDO = null;
 
        /*DECLARING SQL ARRAY HERE*/
        oracle.sql.ARRAY courseSQLArray = null;
        
        // todo
        // pass assignment oids to the proc as SQL Array (so do not delete the related code)
        // at the moment assignment oids are passed as comma separated values
        oracle.sql.ARRAY assignmentSQLArray = null;
        oracle.sql.ARRAY standardSQLArray = null;
 
        String beginDate = reportCriteriaTO.getFromDate();
        String endDate = reportCriteriaTO.getToDate();;
 
        StudentMasteryDO studentMasteryDO = new StudentMasteryDO();
 
        try
        {
            con = directCAMRConnMgr.getConnection(10, 1000);
            m_connection = con.getConnection();
            m_connection = m_connection.getMetaData().getConnection();
 
            String strQuery = sqlStringLoader.getSQLQuery(SQL_XML_FILE, GET_STUDENT_MASTERY_DATA);
            cstmtReportData = (OracleCallableStatement) m_connection.prepareCall(strQuery);
 
            /*getSqlArrayObjForOID RETURNS SQL ARRAY FOR COMMA SEPARATED OIDS*/
            /*getSqlArrayObjForOID code is at the end*/
            courseSQLArray = getSqlArrayObjForOID(reportCriteriaTO.getCourseOID(), m_connection);
            
            assignmentSQLArray = getSqlArrayObjForOID(reportCriteriaTO.getAssignmentOID(), m_connection);
            standardSQLArray = getSqlArrayObjForInteger(reportCriteriaTO.getStandardOID(), m_connection);
            cstmtReportData.setInt(1, reportCriteriaTO.getStudentGroupID());
            cstmtReportData.setInt(2, reportCriteriaTO.getYearTag());
            cstmtReportData.setString(3, reportCriteriaTO.getSchoolOID());
            cstmtReportData.setString(4, reportCriteriaTO.getPersonOID());
            
            [B]/*SETTING courseSQLArray HERE*/
            /*AND THIS IS WHERE THE CODE BREAKS*/[/B]
            cstmtReportData.setARRAY(5, courseSQLArray);
            
            cstmtReportData.setString(6, reportCriteriaTO.getAssignmentOID());
            //cstmtReportData.setARRAY(6, assignmentSQLArray);
            cstmtReportData.setString(7, reportCriteriaTO.getMasteryRule());
            cstmtReportData.setInt(8, reportCriteriaTO.getCustomerID());
            cstmtReportData.setString(9, reportCriteriaTO.getEthnicity());
            cstmtReportData.setString(10, reportCriteriaTO.getGender());
            cstmtReportData.setString(11, reportCriteriaTO.getInstrSetting());
            cstmtReportData.setString(12, reportCriteriaTO.getLunchStatus());
            cstmtReportData.setString(13, reportCriteriaTO.getMigrantStatus());
            cstmtReportData.setString(14, reportCriteriaTO.getPrimaryLanguage());
            cstmtReportData.setString(15, reportCriteriaTO.getSchool());
            cstmtReportData.setString(16, beginDate);
            cstmtReportData.setString(17, endDate);
            cstmtReportData.setARRAY(18, standardSQLArray);
            cstmtReportData.registerOutParameter(19, OracleTypes.CURSOR);
            cstmtReportData.registerOutParameter(20, OracleTypes.CURSOR);
            cstmtReportData.registerOutParameter(21, OracleTypes.CURSOR);
            cstmtReportData.registerOutParameter(22, OracleTypes.CURSOR);
 
            /*STATEMENT EXECUTED HERE*/
            cstmtReportData.execute();
            
            rsStudentAssignment = (ResultSet) cstmtReportData.getObject(19);
            rsStudentDetails = (ResultSet) cstmtReportData.getObject(20);
            rsStudentStandard = (ResultSet) cstmtReportData.getObject(21);
            rsStandardDetails = (ResultSet) cstmtReportData.getObject(22);
            
            /*PROCESS THE RESULTSETS*/
            }
            catch (ConnectionFetchException cfe)
    {
log.error("Caught ConnectionFetchException in getStudentMasteryData: " + cfe);
throw new DataAccessException("Unable to get student mastery data.", cfe);
    }
    catch (SqlStringLoadingException ssle)
    {
log.error("Caught SqlStringLoadingException in getStudentMasteryData: " + ssle);
throw new DataAccessException("Unable to get student mastery data.", ssle);
    }
    catch (SQLException sqlex)
    {
log.error("Caught SQLException in getStudentMasteryData: " + sqlex);
throw new DataAccessException("Unable to get student mastery data.", sqlex);
    }
    finally
    {
try
{
    if (rsStudentDetails != null)
    {
rsStudentDetails.close();
rsStudentDetails = null;
    }
    if (rsStudentAssignment != null)
    {
rsStudentAssignment.close();
rsStudentAssignment = null;
    }
    if (rsStudentStandard != null)
    {
rsStudentStandard.close();
rsStudentStandard = null;
    }
    if (rsStandardDetails != null)
    {
rsStandardDetails.close();
rsStandardDetails = null;
    }
    if (cstmtReportData != null)
    {
cstmtReportData.close();
cstmtReportData = null;
    }
    if (con != null)
    {
directCAMRConnMgr.freeDirectConnection(con);
    }
}
catch (SQLException ignore)
{
    //swallow this exception in the finally block, but log it (shouldn't happen)
    ExceptionUtil.ignoreFinallyBlockException(ignore);
}
    }
        
return studentMasteryDO;
    }
    
    private ARRAY getSqlArrayObjForOID(String csvOfOIDs, Connection m_connection) throws SQLException
    {
int index = 0;
int numberOfOIDs = 0;
oracle.sql.STRUCT OIDStruct = null;
oracle.sql.ARRAY OIDSQLArray = null;
StringTokenizer OIDStrTk = null;
 
// write array descriptor logic hare
// define a StructDescriptor (for column value)
// define a ArrayDescriptor (collection of column values/TABLE)
// these descriptors are generic for any 32 bit OID type
StructDescriptor OIDStructDesc = StructDescriptor.createDescriptor("CAMR.OID_OBJTYP", m_connection);
ArrayDescriptor OIDArrayDescriptor = ArrayDescriptor.createDescriptor("CAMR.OID_TABTYP", m_connection);
 
// loop through all the comma separated OIDs
// and fill them one by one in StructDescriptor, ArrayDescriptor
// to make a array/table of OIDs
if (csvOfOIDs != null)
{
OIDStrTk = new StringTokenizer(csvOfOIDs, ",");
numberOfOIDs = OIDStrTk.countTokens();
 
Object OIDStructArr[] = new Object[numberOfOIDs];   // arrya of OID structures
 
while (OIDStrTk.hasMoreTokens())                    // or i < numberOfOIDs; i++
{
    String OID = OIDStrTk.nextToken();
    Object OIDObj[] = new Object[1];                // object to store one OID
    OIDObj[0] = OID;
 
    OIDStruct = new STRUCT(OIDStructDesc, m_connection, OIDObj);    // OID structure
    OIDStructArr[index++] = OIDStruct;              // add OID structure to array
}
 
// finally create sql array of OIDs
OIDSQLArray = new ARRAY(OIDArrayDescriptor, m_connection, OIDStructArr);
 
}
 
return OIDSQLArray;
    }
}

Even before the OracleCallableStatement is executed it breaks right at the setARRAY method.

The exception thrown is:
Caused by: java.sql.SQLException: Invalid argument(s) in call
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269)
at oracle.jdbc.dbaccess.DBItem.setArrayData(DBItem.java:262)
at oracle.jdbc.dbaccess.DBDataSetImpl.setADTBindItem(DBDataSetImpl.java:2738)
at oracle.jdbc.driver.OraclePreparedStatement.setARRAY(OraclePreparedStatement.java:1760)
at com.pearson.report.persistence.StudentMasteryDAO.getStudentMasteryData(StudentMasteryDAO.java:116)
... 8 more

Can someone please help me understand why this exception is thrown? How can I get over this problem?

Thanks,
Vikram Phale.

[ April 10, 2007: Message edited by: Vikram Phale ]
[ April 10, 2007: Message edited by: Vikram Phale ]

[Vikram Phale]

It is a bug introduced in 9.2.0.5.
Bug 4057147 - JDBC OCI returns invalid argument exception when binding over 64kb array.
Using JDBC Thin drive works.