Clever Geek Handbook

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

I have the following function that I want 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 not sure how to bind the Java collection to the return type of getParentsForItem.

+1


source to share


1 answer


After some googling I found this example . It uses methods exclusive to the Oracle JDBC driver, namely:

Approaching your situation, maybe this will work:



Warning: I haven't 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);
+4


source






More articles:


All Articles