Pass an Java array to Oracle stored procedure using JdbcTemplate

Viewed 45

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.

1 Answers

What do you pass as "typeName" ? It must be a known ORACLE type of the target schema: either something like SYS.ODCIVARCHAR2LIST, ... or a TYPE you defined (e.g. a TABLE OF ...) so in your case probably "NUM_ARRAY". (NB you could have used SYS.ODCINUMBERLIST) (I don't remember if passing lowercase works, so I prefer to always use UPPERCASE) NB when using directly JDBC, there is a PreparedSteament.setArray function, not sure MapSqlParameters is doing the right thing with a SqlTypeValue because I saw it's testing "instanceof SqlParameterValue".

Related