Java调用Oracle存储过程传递PL/SQL索引表：废弃的set/getPlsqlIndexTable替代方案异常

I’ve run into this exact issue before when migrating from deprecated PL/SQL index table methods to the newer createOracleArray approach, so I know exactly what’s going on here.

The Root Cause #

PL/SQL associative arrays (index tables) default to starting at index 1, while Java arrays are zero-indexed. The old setPlsqlIndexTable method handled this index discrepancy automatically—it mapped Java's index 0 directly to PL/SQL's index 1.

When you use createOracleArray, however, JDBC passes array elements as-is: Java's index 0 gets mapped to PL/SQL's index 0. Since your PL/SQL index table is configured to start at index 1, it ignores the element at index 0 entirely. That’s why you’re missing your first input element and only seeing 9 results returned.

The Fix #

You need to adjust your Java array to account for the PL/SQL index starting point. The simplest way is to create an adjusted array where your original elements start at index 1 (leaving index 0 as a placeholder that PL/SQL will ignore):

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};

// Adjust input array to start at index 1 for PL/SQL compatibility
BigDecimal[] adjustedInput = new BigDecimal[input.length + 1];
System.arraycopy(input, 0, adjustedInput, 1, input.length);

oracleCallableStatement.setObject(1, oracleConnection.createOracleArray("DBACCESSTESTDB.STORED_PROC_IBT_PACKAGE.NUMBER_TABLE_INDEX", adjustedInput));
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();

// Slice the results to skip the placeholder at index 0
Arrays.stream(results, 1, results.length).forEach(System.out::println);

Alternative: Use OracleArrayDescriptor for Explicit Index Control #

If you want more control over the array’s index behavior, you can use OracleArrayDescriptor to explicitly define the starting index for the PL/SQL array:

ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor("DBACCESSTESTDB.STORED_PROC_IBT_PACKAGE.NUMBER_TABLE_INDEX", oracleConnection);
// Explicitly set the array to start at index 1
descriptor.setLowerBound(1);
OracleArray oracleArray = new OracleArray(descriptor, oracleConnection, input);

oracleCallableStatement.setObject(1, oracleArray);
// Rest of the code remains the same

Why Your Hybrid Approach Worked #

When you used the old setPlsqlIndexTable for the IN parameter, it handled the index mapping correctly, so PL/SQL received all 10 elements. The OUT parameter handling with registerOutParameter works because JDBC automatically maps PL/SQL's index 1 back to Java's index 0 in the returned array.

内容的提问来源于stack exchange，提问作者Bernhard Eischer