I am trying to pass an integer array to an Oracle stored procedure.
This is my Oracle side:
-- Package Declaration
TYPE num_array IS TABLE OF NUMBER;
PROCEDURE test_arrays(p_array IN num_array);
-- Package Body
PROCEDURE test_arrays(p_array IN num_array)
IS
BEGIN
FOR i IN 1 .. p_array.count
LOOP
INSERT INTO GENERAL.TEST_ARRAYS VALUES (p_array(i));
END LOOP;
END;
So simple. Now, I need to pass an integer array from the database. This is my code:
public void testArrays() {
int[] intArray = new int[100];
for(int i=0; i<100; i++) {
intArray[i] = i;
}
SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(this.jdbcTemplate)
.withSchemaName("general")
.withCatalogName("sms_package")
.withProcedureName("test_arrays");
SqlParameterSource parameterSource = new MapSqlParameterSource()
.addValue("p_array", intArray, Types.ARRAY);
simpleJdbcCall.execute(parameterSource);
}
This throws an invalid column type.
After a little research, I saw that I need to create some array descriptors, I tried some code:
SqlTypeValue pvCodScg = new AbstractSqlTypeValue() {
protected Object createTypeValue(Connection conn, int sqlType, String typeName) throws SQLException {
ArrayDescriptor arrayDescriptor = new ArrayDescriptor(typeName, conn);
return new ARRAY(arrayDescriptor, conn, intArray);
}
};
I passed it as:
SqlParameterSource parameterSource = new MapSqlParameterSource()
.addValue("p_array", pvCodScg);
This still doesn't work.