Passing String array into Oracle stored procedure with string_varray type

Question

I have a Store Procedure in Oracle 12c DB with following signature:

create or replace PROCEDURE myproc(param1 IN NUMBER, param2 IN STRING_ARRAY, param3 IN STRING_ARRAY, outparam OUT BOOLEAN) IS

Here STRING_ARRAY defined in following way:

create or replace TYPE STRING_ARRAY AS VARRAY(1000) OF VARCHAR2(4000);

In my dao layer I am invoking it in following way:

SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate).withProcedureName("myschema.myproc");


    SqlTypeValue param2ArrayValue = new AbstractSqlTypeValue() {
        protected Object createTypeValue(Connection conn, int sqlType, String typeName) throws SQLException {
            ARRAY array = ((OracleConnection) conn).createARRAY("myschema.STRING_ARRAY", param2Values);
            return array;
        }
    };


    SqlTypeValue param3ArrayValue = new AbstractSqlTypeValue() {
        protected Object createTypeValue(Connection conn, int sqlType, String typeName) throws SQLException {
            ARRAY array = ((OracleConnection) conn).createARRAY("myschema.STRING_ARRAY", param3Values);
            return array;
        }
    };

    SqlParameterSource inParams = new MapSqlParameterSource().addValue("param1", param1Value)
                                                             .addValue("param2", param2ArrayValue)
                                                             .addValue("param3", param3ArrayValue);

    Map<String, Object> outParams = simpleJdbcCall.execute(inParams);
    logger.info("RetFlag:::::" + outParams.get("outparam"));

I am getting following exception::

org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{call myschema.myproc(?, ?, ?, ?)}]; SQL state [99999]; error code [17004]; Invalid column type: 1111; nested exception is java.sql.SQLException: Invalid column type: 1111 
Caused by: java.sql.SQLException: Invalid column type: 1111

I have referred the following link:: Spring Forum link 1

Can anyone provide any solution to this??

Answer 1

I have followed the following steps:

I had change the data type of my StoredProcedure OUT param from boolean to number:

create or replace PROCEDURE myproc(param1 IN NUMBER, param2 IN STRING_ARRAY, param3 IN STRING_ARRAY, return_flag OUT NUMBER) IS

Created a Custom Class by inheriting StoredProcedureclass of spring-jdbc module

public class MyCustomProcedure extends StoredProcedure{

    public MyCustomProcedure(JdbcTemplate jdbcTemplate, String procedureName) {
      super(jdbcTemplate, procedureName);
      declareParameter(new SqlParameter("param1", Types.NUMERIC));
      declareParameter(new SqlParameter("param2", Types.ARRAY, "STRING_ARRAY"));
      declareParameter(new SqlParameter("param3", Types.ARRAY, "STRING_ARRAY"));
      declareParameter(new SqlOutParameter("return_flag", Types.NUMERIC));
    }

    public boolean execute(Long param1, String[] param2, String[] param3) {
       Boolean resultFlag= null;
       BigDecimal outputReturned  = null;
       Map resultMap = null;
       AbstractSqlTypeValue customArrayTypeParam2 = null;
       AbstractSqlTypeValue customArrayParamType3 = null;

       try (final Connection connectionWrapper = getJdbcTemplate().getDataSource().getConnection()) {
        customArrayTypeParam2 = new MyCustomOracleArrayType(connectionWrapper, param2);
        customArrayTypeParam3 = new MyCustomOracleArrayType(connectionWrapper, param3);
           resultMap = super.execute(param1, customArrayTypeParam2, customArrayTypeParam3);
         }catch(Exception e){
            e.printStackTrace();
         }

        if (MapUtils.isNotEmpty(resultMap)) {
             outputReturned = (BigDecimal) resultMap.get("return_flag");
        if ((Integer.valueOf(outputReturned.intValue())).equals(Integer.valueOf(1))) {
            resultFlag= true;
        }
    } else {
        resultFlag= false;
    }
    return resultFlag;
   }
 }

Please find below my CustomOracleArrayType class

public class MyCustomOracleArrayType extends AbstractSqlTypeValue {
    private final Connection oracleCon;
    private final Object[] values;

    public MyCustomOracleArrayType(final Connection oracleCon, final Object[] values) {
        this.oracleCon = oracleCon;
        this.values = values;
    }

    @Override
    protected Object createTypeValue(final Connection con, final int sqlType, final String typeName)
            throws SQLException {
        Array array =  ((OracleConnection)oracleCon).createOracleArray("STRING_ARRAY", values);
        return array;
    }
  }

I have done following modification in Spring Configuration file for injecting MyCustomProcedure class:

<bean id="myCustomProcedure" class="mypackage.MyCustomProcedure">
   <constructor-arg index="0" type="org.springframework.jdbc.core.JdbcTemplate" ref="jdbcTemplate"/>
   <constructor-arg index="1" type="java.lang.String" value="myproc"/>
</bean>
<bean id="myService" class="mypackage.MyServiceImpl">
  <property name="myCustomProcedure" ref="myCustomProcedure"/>
</bean>
<!--jdbcTemplate already defined in my spring config file along with dataSource -->

That's all,I just invoke execute() method of MyCustomProcedure in traditional way from my service layer.