从通过JDBC调用的PL?SQL函数返回表类型? [英] Returning a table type from a PL?SQL function called via JDBC?

问题描述

我想调用以下函数:

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;

但是我不确定如何将java集合绑定到getParentsForItem的返回类型.

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

推荐答案

在Google搜索后，我发现

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

这篇关于从通过JDBC调用的PL?SQL函数返回表类型?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！