How to pass String array into Oracle stored proc with string_varray type

[jacek99]

We have an Oracle stored proc that looks like this:

PROCEDURE TEST_PROC (
in_provider_id IN NUMBER,
in_id_ids IN string_varray,
in_id_values IN string_varray,
in_platform_id IN NUMBER,
in_major_ver IN NUMBER,
in_minor_ver IN NUMBER,
in_build_number IN NUMBER,
in_srv_pack IN NUMBER,
in_soft_pkg_ids IN string_varray,
in_install_id IN VARCHAR,
out_dev_pk OUT NUMBER);

I am trying to specify the SqlParameter for the array fields, e.g.

declareParameter(new SqlParameter("in_id_ids",Types.ARRAY);

I tried setting the native Jdbc extractor as well, since that was mentioned on a blog:

getJdbcTemplate().setNativeJdbcExtractor(new SimpleNativeJdbcExtractor());

But when I execute the stored proc we always get an exception like this:

Caused by: java.sql.SQLException: Fail to convert to internal representation: [Ljava.lang.String;@dd0f87
at oracle.jdbc.driver.SQLStateMapping.newSQLException (SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(D atabaseError.java:133)
at oracle.jdbc.driver.DatabaseError.throwSqlException (DatabaseError.java:199)
at oracle.jdbc.driver.DatabaseError.throwSqlException (DatabaseError.java:263)
at oracle.jdbc.driver.DatabaseError.throwSqlException (DatabaseError.java:271)
at oracle.sql.ARRAY.toARRAY(ARRAY.java:178)
at oracle.jdbc.driver.OraclePreparedStatement.setObje ctCritical(OraclePreparedStatement.java:7921)
at oracle.jdbc.driver.OraclePreparedStatement.setObje ctInternal(OraclePreparedStatement.java:7509)
at oracle.jdbc.driver.OraclePreparedStatement.setObje ctInternal(OraclePreparedStatement.java:7999)
at oracle.jdbc.driver.OracleCallableStatement.setObje ct(OracleCallableStatement.java:4103)
at oracle.jdbc.driver.OraclePreparedStatementWrapper. setObject(OraclePreparedStatementWrapper.java:238)
at org.springframework.jdbc.core.StatementCreatorUtil s.setValue(StatementCreatorUtils.java:351)
at org.springframework.jdbc.core.StatementCreatorUtil s.setParameterValueInternal(StatementCreatorUtils. java:217)
at org.springframework.jdbc.core.StatementCreatorUtil s.setParameterValue(StatementCreatorUtils.java:128 )
at org.springframework.jdbc.core.CallableStatementCre atorFactory$CallableStatementCreatorImpl.createCal lableStatement(CallableStatementCreatorFactory.jav a:213)
at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:930)

Any help would be appreciated...

[jacek99]

Sorry, forgot to mention..I tried this as well...(passing in the explicit type)

declareParameter(new SqlParameter("in_id_ids",Types.ARRAY,"STRING_VARRA Y"));

but with the same results...

[trisberg]

Oracle requires a database specific array representation for any array values passed in. You can't pass in the Array directly.

You can use a SqlTypeValue to create a native array representation to pass in to the stored proc.

Here is an example:

SqlTypeValue arrayValue = new AbstractSqlTypeValue() {
    protected Object createTypeValue(Connection conn, int sqlType, String typeName) throws SQLException {
        ArrayDescriptor arrayDescriptor = new ArrayDescriptor(typeName, conn);
        ARRAY array =
                new ARRAY(arrayDescriptor, conn, yourArray);
        return array;
    }
};

When Spring sees the SqlTypeValue object it will call the createTypeValue method and use the returned object as the parameter value.

[jacek99]

Thank you, that worked perfectly.