Announcement Announcement Module
Collapse
No announcement yet.
How to pass String array into Oracle stored proc with string_varray type Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • 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:

    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...

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

    Comment


    • #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:

      Code:
      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.

      Comment


      • #4
        Thank you, that worked perfectly.

        Comment

        Working...
        X