After scouring the forum I have found a solution to my problem. I could not find the exact solution because I had to change the procedure from using an Index table to an array.
I made the following changes to my code:
1) I moved the type definitions outside of the Oracle package.
2) I removed index by binary_integer from the type definitions
3) I found a piece of code that shows how to create an Oracle array descriptor for the parameters declared in the stored procedure.
4) Modified stored procedure to be initialized with a JdbcTemplate instead of a datasource. I did this because I needed access to the native jdbc objects and you can only do this by setting the NativeJdbcExtractor on the JdbcTemplate.
Any comments or suggestion would be appreciated.
The following forum topics helped with the solution:
http://forum.springframework.org/vie...cedure+arr ay
http://forum.springframework.org/viewtopic.php?t=833
New stored procdure
Code:
CREATE OR REPLACE TYPE T_nRECORD_UID IS TABLE OF NUMBER(10);
/
CREATE OR REPLACE TYPE TYPE_TNARRAY IS TABLE OF NUMBER(10);
/
CREATE OR REPLACE TYPE TP_vArray IS TABLE OF VARCHAR2(10);
/
CREATE OR REPLACE TYPE T_ERR_INDEX IS TABLE OF NUMBER(3);
/
CREATE OR REPLACE PACKAGE TEST_PKG IS
PROCEDURE SP_INS_T (
nRECORD_UID IN T_nRECORD_UID,
nArray IN TP_nArray,
vArray IN TP_vArray,
vStatus OUT NUMBER,
vErrorString OUT VARCHAR2,
vErrorArray IN OUT T_ERR_INDEX
);
END TEST_PKG;
/
CREATE OR REPLACE PACKAGE BODY TEST_PKG is
PROCEDURE SP_INS_T (
nRECORD_UID IN T_nRECORD_UID,
nArray IN TP_nArray,
vArray IN TP_vArray,
vStatus OUT NUMBER,
vErrorString OUT VARCHAR2,
vErrorArray IN OUT T_ERR_INDEX
)
IS
e_InsertBulkFailed EXCEPTION;
Extended_error VARCHAR2(2000);
bulk_errors EXCEPTION;
error_count NUMBER;
error_length NUMBER;
PRAGMA exception_init(bulk_errors, -24381);
BEGIN
-- body
END SP_INS_T;
END TEST_PKG;
/
New Spring Stored Procedure:
Code:
package loader.dao.jdbc;
import java.sql.Types;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.StoredProcedure;
import loader.model.TestDataLists;
public class SpBulkInsertTest extends StoredProcedure {
private static final String SQL = "TEST_PKG.SP_INS_T";
public SpBulkInsertTest(JdbcTemplate ds) {
setJdbcTemplate(ds);
setSql(SQL);
setFunction(false);
declareParameter(new SqlParameter("nRECORD_UID", Types.ARRAY,
"T_NRECORD_UID"));
declareParameter(new SqlParameter("nArray", Types.ARRAY,
"TP_NARRAY"));
declareParameter(new SqlParameter("vArray", Types.ARRAY,
"TP_VARRAY"));
declareParameter(new SqlOutParameter("vStatus", Types.INTEGER));
declareParameter(new SqlOutParameter("vErrorString", Types.VARCHAR));
declareParameter(new SqlOutParameter("vErrorArray", Types.ARRAY,
"T_ERR_INDEX"));
compile();
}
public Map bulkLoadData(List listOfData) {
Map results = new HashMap();
TestDataLists testDataLists = new TestDataLists();
int start = 0;
while ((start < listOfData.size()) || (start < 0)) {
start = testDataLists.addData(listOfData, start);
if (start < 0) {
break;
}
Map inParams = new HashMap();
inParams.put("nRECORD_UID", testDataLists.getRecordUids());
inParams.put("nArray", testDataLists.getNumbers());
inParams.put("vArray", testDataLists.getValues());
inParams.put("vErrorArray", testDataLists.getErrorCodes());
results = execute(inParams);
printMap(results);
}
return results;
}
private static void printMap(Map r) {
Iterator i = r.entrySet().iterator();
while (i.hasNext()) {
System.out.println((String) i.next().toString());
}
}
}
Test data lists:
Code:
package loader.model;
import java.util.ArrayList;
import java.util.List;
import jdbc.support.OracleSqlArray;
public class TestDataLists {
private final int MAX_ITEMS = 100;
private OracleSqlArray recordUids;
private OracleSqlArray numbers;
private OracleSqlArray values;
private OracleSqlArray errorCodes;
public TestDataLists() {
super();
}
public int addData(List listOfData, int start) {
int result = -1;
recordUids = null;
numbers = null;
values = null;
if (listOfData != null) {
int arraySize = 0;
int amtToAdd = listOfData.size() - start;
if (amtToAdd <= MAX_ITEMS) {
arraySize = amtToAdd;
}
if (amtToAdd > MAX_ITEMS) {
arraySize = MAX_ITEMS;
}
ArrayList newRecordUids = new ArrayList(arraySize);
ArrayList newNumbers = new ArrayList(arraySize);
ArrayList newValues = new ArrayList(arraySize);
ArrayList newErrorCodes = new ArrayList(arraySize);
for (int i = 0; i < arraySize; i++) {
int idx = i + start;
TestData testData = (TestData) listOfData.get(idx);
newRecordUids.add(new Long(idx));
newNumbers.add(testData.getNumber());
newValues.add(testData.getValue());
newErrorCodes.add(new Integer(-1));
}
recordUids = new OracleSqlArray(newRecordUids);
numbers = new OracleSqlArray(newNumbers);
values = new OracleSqlArray(newValues);
errorCodes = new OracleSqlArray(newErrorCodes);
result = start + arraySize;
}
return result;
}
public OracleSqlArray getNumbers() {
return numbers;
}
public void setNumbers(OracleSqlArray numbers) {
this.numbers = numbers;
}
public OracleSqlArray getValues() {
return values;
}
public void setValues(OracleSqlArray values) {
this.values = values;
}
public OracleSqlArray getRecordUids() {
return recordUids;
}
public void setRecordUids(OracleSqlArray ids) {
recordUids = ids;
}
public OracleSqlArray getErrorCodes() {
return errorCodes;
}
public void setErrorCodes(OracleSqlArray errorCodes) {
this.errorCodes = errorCodes;
}
}
New OracleSqlArray type:
Code:
package jdbc.support;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import oracle.jdbc.OracleConnection;
import org.springframework.jdbc.core.support.AbstractSqlTypeValue;
public class OracleSqlArray extends AbstractSqlTypeValue {
private ArrayList values;
public OracleSqlArray(ArrayList values) {
this.values = values;
}
public Object createTypeValue(Connection con, int sqlType, String typeName)
throws SQLException {
OracleConnection oracle = (OracleConnection) con;
oracle.sql.ArrayDescriptor desc = new oracle.sql.ArrayDescriptor(
typeName, oracle);
Object[] a = values.toArray();
Object o = new oracle.sql.ARRAY(desc, oracle, a);
return o;
}
}
Test Code:
Code:
package loader.dao.jdbc;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.apache.log4j.Logger;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.jdbc.object.StoredProcedure;
import org.springframework.jdbc.support.nativejdbc.SimpleNativeJdbcExtractor;
import loader.model.TestData;
public class TestStoredProcedure {
private static final Logger logger = Logger
.getLogger(TestStoredProcedure.class);
public static void main(String[] args) {
TestStoredProcedure t = new TestStoredProcedure();
t.test();
System.out.println("Done!");
}
void test() {
logger.info("setting up driver manager data source.");
DriverManagerDataSource ds;
JdbcTemplate jt;
try {
ds = new DriverManagerDataSource();
ds.setDriverClassName("oracle.jdbc.driver.OracleDriver");
ds.setUrl("jdbc:oracle:thin:@xxx:1521:xxx");
ds.setUsername("xxx");
ds.setPassword("xxx");
SimpleNativeJdbcExtractor nje = new SimpleNativeJdbcExtractor();
nje.setNativeConnectionNecessaryForNativeCallableStatements(true);
nje.setNativeConnectionNecessaryForNativePreparedStatements(true);
nje.setNativeConnectionNecessaryForNativeStatements(true);
jt = new JdbcTemplate(ds);
jt.setNativeJdbcExtractor(nje);
logger.info("Calling bulk insert stored procedure:");
List testDataList = new ArrayList();
SpBulkInsertTest bulkInsertTest = new SpBulkInsertTest(jt);
TestData testData;
for (int i = 0; i < 1001; i++) {
testData = getTestData(i, "VAL"+i);
testDataList.add(testData);
}
logger.info("call string: " + bulkInsertTest.getCallString());
res = bulkInsertTest.bulkLoadData(testDataList);
printMap(res);
} catch (Exception e) {
logger.error(e);
}
}
private static void printMap(Map r) {
Iterator i = r.entrySet().iterator();
while (i.hasNext()) {
System.out.println((String) i.next().toString());
}
}
private static TestData getTestData(int id, String name) {
TestData result = new TestData();
result.setNumber(new Long(id));
result.setValue(name);
return result;
}
}
StoredProcedure Oracle/setplsqlindextable
Reply With Quote