Calling StoredProcedure with Oracle Plsq Index-Table in Java: Alternatives to deprecated set/getPlsqlIndexTable do not work

I am trying to call a simple Oracle Stored-Procedure that takes a plsql-index-table and retursn a plsql-index-table as OUT parameter. My old approach works perfectly but the methods I used there are deprectad now. That was my old Approach:

    OracleConnection oracleConnection = connection.unwrap(OracleConnection.class);
    CallableStatement callableStatement = oracleConnection.prepareCall("BEGIN STORED_PROC_IBT_PACKAGE.SIMPLE_INANDOUT_NUMBER_DEC(?,?); END;");
    OracleCallableStatement oracleCallableStatement = callableStatement.unwrap(OracleCallableStatement.class);
    BigDecimal[] input = new BigDecimal[] {BigDecimal.valueOf(1), BigDecimal.valueOf(2),BigDecimal.ZERO,BigDecimal.ZERO,BigDecimal.ZERO,BigDecimal.ZERO,BigDecimal.ZERO,
            BigDecimal.ZERO,BigDecimal.ZERO,BigDecimal.ZERO};
    
    oracleCallableStatement.setPlsqlIndexTable(1, input,10, input.length,Types.DECIMAL, 10);
    oracleCallableStatement.registerIndexTableOutParameter(2, 10, Types.DECIMAL, 10);
    
    oracleCallableStatement.execute();
    
    BigDecimal[] plsqlIndexTable = (BigDecimal[])oracleCallableStatement.getPlsqlIndexTable(2);
    Arrays.stream(plsqlIndexTable).forEach(System.out::println);

I am not sure if my new approach is correct by using an array and using ‘setObject’, ‘getObject’ and ‘createOracleArray’ but it works nearly perfect. My new approach:

    OracleConnection oracleConnection = connection.unwrap(OracleConnection.class);
    CallableStatement callableStatement = oracleConnection.prepareCall("BEGIN STORED_PROC_IBT_PACKAGE.SIMPLE_INANDOUT_NUMBER_DEC(?,?); END;");
    OracleCallableStatement oracleCallableStatement = callableStatement.unwrap(OracleCallableStatement.class);
    BigDecimal[] input = new BigDecimal[] {BigDecimal.valueOf(1), BigDecimal.valueOf(2),BigDecimal.ZERO,BigDecimal.ZERO,BigDecimal.ZERO,BigDecimal.ZERO,BigDecimal.ZERO,
            BigDecimal.ZERO,BigDecimal.ZERO,BigDecimal.ZERO};
    
    oracleCallableStatement.setObject(1, oracleConnection.createOracleArray("DBACCESSTESTDB.STORED_PROC_IBT_PACKAGE.NUMBER_TABLE_INDEX", input));
    oracleCallableStatement.registerOutParameter(2, Types.ARRAY, "DBACCESSTESTDB.STORED_PROC_IBT_PACKAGE.NUMBER_TABLE_INDEX");

    oracleCallableStatement.execute();

    Array plsqlIndexTable = (Array)oracleCallableStatement.getObject(2);
    BigDecimal[] results = (BigDecimal[])plsqlIndexTable.getArray();
    Arrays.stream(results).forEach(System.out::println);

The problem with new approach is that only 9 elements come back instead of 10. The reasons seams to be that the first element (in Java with index 0) is lost somewhere. I don’t no if it is lost when sending in to the Stored-Proceudure or when retrieving it. Is this an Oracle Bug or is my new approach somehow wrong?

Edit: Okay now I know that the problems occurs when sending the array to the storedProcedure. When I use the old approach with “setPlsqlIndexTable” for setting the IN-parameter and the new approach (‘registerOutParameter’ and ‘getObject’) for retrieving the OUT-parameter it works. But I still not know how to fix the problem. Still the first element of the array is lost

Answer

Okay i think I found the problem. My stored-procedure was declared like this:

    PROCEDURE SIMPLE_INANDOUT_NUMBER_DEC
(
  NUMBER_TABLE_INDEX_IN IN NUMBER_TABLE_INDEX,
  NUMBER_TABLE_INDEX_OUT OUT NUMBER_TABLE_INDEX
)
  AS
    BEGIN
      FOR i IN 1..NUMBER_TABLE_INDEX_IN.last  loop
        NUMBER_TABLE_INDEX_OUT(i) := NUMBER_TABLE_INDEX_IN(i) + 1;
      end loop;
    END SIMPLE_INANDOUT_NUMBER_DEC;

With the old approach setPlSqlIndexTable it was correct to start with index 1. Starting with index 0 would lead to an ORA_1403 No-Data.

With the new approach ('setObject' and 'createOracleArray') the index in PLSQL starts at 0. So to get it work for both approaches it is best to define the start index with NUMBER_TABLE_INDEX_IN.first.

    PROCEDURE SIMPLE_INANDOUT_NUMBER_DEC
(
  NUMBER_TABLE_INDEX_IN IN NUMBER_TABLE_INDEX,
  NUMBER_TABLE_INDEX_OUT OUT NUMBER_TABLE_INDEX
)
  AS
    BEGIN
      FOR i IN NUMBER_TABLE_INDEX_IN.first..NUMBER_TABLE_INDEX_IN.last  loop
        NUMBER_TABLE_INDEX_OUT(i) := NUMBER_TABLE_INDEX_IN(i) + 1;
      end loop;
    END SIMPLE_INANDOUT_NUMBER_DEC;

This is the only way that I found, that works for both approaches for me.