Pass an array of numbers (TABLE OF NUMBER) Input Param to a Stored Proc call in SpringJDBC

Question

In SpringJDBC I need to call a SP whose parameter is a TABLE OF NUMBER type, i.e. an array of numbers.

  PROCEDURE MY_SP(              PARAM1                IN TABLE OF NUMBER,
                                PARAM2                IN NUMBER,

An input of [1] (1-element integer array) is valid. But when I try to pass it in directly per the declaration of this param as OracleTypes.ARRAY

    declareParameter(new SqlParameter("PARAM1", OracleTypes.ARRAY));

I get

Caused by: java.sql.SQLException: Fail to convert to internal representation: [Ljava.lang.Integer;@16a25973
    at oracle.sql.ARRAY.toARRAY(ARRAY.java:308)

There's an old example here which uses the deprecated ArrayDescriptor and builds a SQL Array. That must be from an older SpringJDBC. ArrayDescriptor is deprecated, and also I don't have access to connection. I'm using the modern SpringJDBC approach of implementing the StoredProcedure interface (with compile())which doesn't expose a connection. Any thoughts?

@Component
public class MySP extends StoredProcedure {
    
    private RowMapper<List<Object>> rowMapper = new MyMapper();
    
    public MySP(@Autowired DataSource dataSource) {
        super(dataSource, "MYPACKAGE.MY_SP");

        declareParameter(new SqlParameter("PARAM1", OracleTypes.ARRAY));
        //...
        compile();
   }

   public String callSP(Integer[] intArray) {
        //intArray contains a 1-element integer array, e.g. [1]
        Map<String, Object> inputParameters = new HashMap<>();
        inputParameters.put("PARAM1", intArray);

        Map<String, Object> output = super.execute(inputParameters);
        // Process results...
   }

Answer 1

If you have a custom Array type defined in Oracle as e.g. below:

create or replace TYPE   "REF_TYPE_ARRAY"  IS TABLE OF NUMBER

Then in Java's StoredProcedure implementation you can do the following: you (1) unwrap the OracleConnection and then (2) do createOracleArray.

@Component
public class MyStoredProcedure extends StoredProcedure {

    public MyStoredProcedure(@Autowired DataSource dataSource) {
        super(dataSource, "MY_PKG.MY_STORED_PROC"); // Name

        declareParameter(new SqlParameter("itemIds", OracleTypes.ARRAY, "REF_TYPE_ARRAY"));
        // etc. other parameters

}

public void execute(ParamObject paramObject) throws Exception{
    Map<String, Object> inputParameters = new HashMap<>();
    
    OracleConnection oracleConnection = getJdbcTemplate().getDataSource().getConnection().unwrap(OracleConnection.class);
    Array array = oracleConnection.createOracleArray("REF_TYPE_ARRAY", referralTypeId);
    
    inputParameters.put("itemIds", array); 
    // etc. other params

This works, but we ran into some occasional leaked connections and JDBC issues with this approach. We had to re-write our SP as a simple CallableStatement where you specify the array directly as

private static final String SQL = 
" {call MY_PKG.MY_SP(REF_TYPE_ARRAY(?),?,?) } ";

try(Connection conn = dataSource.getConnection(); CallableStatement cs = conn.prepareCall(SQL)) {
                    
        cs.setString(1, itemIds);
        //etc.