Returning a table type from a PL?SQL function called via JDBC?

Question

I've got the following function that I wish to call:

CREATE OR REPLACE PACKAGE utils AS

TYPE item_list IS TABLE of items.item_id%TYPE;

FUNCTION getParentsForItem(p_item_id IN items.items_id%TYPE)
RETURN item_list;

END utils;

But I'm unsure of how to bind a java Collection to the return type of getParentsForItem.

Answer 1

After some Google searching, I found this example. It makes use of methods exclusive to the Oracle JDBC driver, namely:

• OracleCallableStatement.registerIndexTableOutParameter(int, int, int, int)
• OracleCallableStatement.getPlsqlIndexTable(int)

After suiting it to your situation, perhaps this will work:

Warning: I have not compiled this myself.

int itemId = ...;

// This feature is only supported by the OCI driver:
Connection connection = DriverManager.getConnection("jdbc:oracle:oci8:@[HOST]", "[USER]", "[PASSWORD]");

CallableStatement callableStatement = connection.prepareCall("{? = utils.getParentsForItem(p_item_id => ?)}");

OracleCallableStatement oracleCallableStatement = (OracleCallableStatement) callableStatement;

int maximumElementsInTable = 150; // the maximum possible number of elements.
int elementSqlType = Types.INTEGER; // index table element SQL type (as defined in java.sql.Types or OracleTypes).
int elementMaxLen = 100; // maximum length of the element. If not specified, maximum length allowed for that type is used.
oracleCallableStatement.registerIndexTableOutParameter(
        1,
        maximumElementsInTable,
        elementSqlType,
        elementMaxLen
    );

oracleCallableStatement.setInt(2, itemId);
oracleCallableStatement.execute();

int[] parentItemIds = oracleCallableStatement.getPlsqlIndexTable(1);