I am passing 500 records to procedure using oracle.sql.ARRAY as --.
private Map<String, Object> saveRecords(Object[][] outputTypeArray) {
Map<String, Object> output = null;
Object[] outputArray;
Map<String, Object> spInputs = new HashMap<String, Object>();
Connection connection = null;
Connection con = null;
ArrayDescriptor arrayDescriptor = null;
try {
outputArray = new Object[outputTypeArray.length];
LOGGER.info("outputTypeArray.length " + outputTypeArray.length);
connection = this.getJdbcTemplate().getDataSource().getConnection();
con = DBUtils.getProcedureConnection(connection);
LOGGER.info("Connection details : " + con);
for (int i = 0; i < outputTypeArray.length; i++) {
outputArray[i] = new STRUCT(StructDescriptor.createDescriptor(
DBConstants.SP_LC_OBJ, con), con, outputTypeArray[i]);
}
LOGGER.info("connection out " + connection);
arrayDescriptor = ArrayDescriptor.createDescriptor(
DBConstants.SP_LC_TBL, con);
LOGGER.info("output array out " + outputArray.length);
ARRAY mDataArray = new ARRAY(arrayDescriptor, con, outputArray);//problem is here
LOGGER.info("marray size " + mDataArray.length());
spInputs.put(DBConstants.SP_LC_IN_PARAM, mDataArray);
output = super.execute(spInputs);
} catch (SQLException e) {
LOGGER.error();
} catch (Exception e) {
LOGGER.error();
}
}
public static Connection getProcedureConnection(Connection conn) {
// For LOCAL
Connection oracleConnection = conn;
if (conn instanceof org.apache.commons.dbcp.DelegatingConnection) {
// This returns a org.apache.commons.dbcp.PoolableConnection
Connection pc =
((org.apache.commons.dbcp.DelegatingConnection)conn).getDelegate();
// The PoolableConnection is a itself - get the delegate (the Oracleconnection)
if(null != pc){ oracleConnection =
((org.apache.commons.dbcp.DelegatingConnection)pc).getDelegate();
}else { oracleConnection = conn; }
}
return oracleConnection;
}
all working fine but i am facing performance issue for--
ARRAY mDataArray = new ARRAY(arrayDescriptor, con, outputArray);
this particular line takes approximately 4.5 minute to execute,its seems to much of time so please suggest some solution to reduce this execution time
Thanks
con = DBUtils.getProcedureConnection(connection);
do?StructDescriptor
definition?