Announcement Announcement Module
No announcement yet.
How to pass String array into Oracle stored proc with string_varray type Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

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

    We have an Oracle stored proc that looks like this:

    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 (
    at oracle.jdbc.driver.DatabaseError.newSQLException(D
    at oracle.jdbc.driver.DatabaseError.throwSqlException (
    at oracle.jdbc.driver.DatabaseError.throwSqlException (
    at oracle.jdbc.driver.DatabaseError.throwSqlException (
    at oracle.sql.ARRAY.toARRAY(
    at oracle.jdbc.driver.OraclePreparedStatement.setObje ctCritical(
    at oracle.jdbc.driver.OraclePreparedStatement.setObje ctInternal(
    at oracle.jdbc.driver.OraclePreparedStatement.setObje ctInternal(
    at oracle.jdbc.driver.OracleCallableStatement.setObje ct(
    at oracle.jdbc.driver.OraclePreparedStatementWrapper. setObject(
    at org.springframework.jdbc.core.StatementCreatorUtil s.setValue(
    at org.springframework.jdbc.core.StatementCreatorUtil s.setParameterValueInternal(StatementCreatorUtils. java:217)
    at org.springframework.jdbc.core.StatementCreatorUtil s.setParameterValue( )
    at org.springframework.jdbc.core.CallableStatementCre atorFactory$CallableStatementCreatorImpl.createCal lableStatement(CallableStatementCreatorFactory.jav a:213)
    at org.springframework.jdbc.core.JdbcTemplate.execute (

    Any help would be appreciated...

  • #2
    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...


    • #3
      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.


      • #4
        Thank you, that worked perfectly.