Vikram Phale

Greenhorn

+ Follow

news 3 Posts 2 Threads

since Aug 23, 2004

Merit badge: grant badges

For More

Cows and Likes

Cows

Total received

In last 30 days

Forums and Threads

Recent posts by Vikram Phale

Size limit in OracleCallableStatement setARRAY

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.

18 years ago

JDBC and Relational Databases

Size limit in OracleCallableStatement setARRAY

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 ]

18 years ago

JDBC and Relational Databases

Size limit on OracleCallableStatement setARRAY ?

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;
}
}

18 years ago

Web Services