java-使用数组调用PL/SQL存储过程 [英] java - Calling a PL/SQL Stored Procedure With Arrays

问题描述

我有一个PL/SQL存储过程，类似于我需要在Java中调用的以下存储过程:

I have a PL/SQL stored procedure similar to the following that I need to call in Java:

TYPE AssocArrayVarchar20_t   is table of VARCHAR2(20)   index by BINARY_INTEGER
TYPE AssocArrayVarchar4100_t is table of VARCHAR2(4100) index by BINARY_INTEGER
TYPE AssocArrayNumber_t      is table of NUMBER         index by BINARY_INTEGER

PROCEDURE DATA_WRITE( I_NAME IN AssocArrayVarchar20_t,
                      I_NUM  IN AssocArrayNumber_t,
                      I_NOTE IN AssocArrayVarchar4100_t)
    // Do Stuff
END DATA_WRITE;

我在Java中尝试了以下方法:

I tried the following in Java:

CallableStatement stmt = conn.prepareCall("begin DATA_WRITE(?, ?, ?); end;");
stmt.setArray(0, conn.createArrayOf("VARCHAR", new String[]{ name }));
stmt.setArray(1, conn.createArrayOf("NUMBER", new Integer[]{ num }));
stmt.setArray(2, conn.createArrayOf("VARCHAR2", new String[]{ notes }));
stmet.execute;

执行此操作时，在createArrayOf()方法上得到一个SQLException: Unsupported Feature".我也尝试过setObject()和createArrayOf内:"varchar"，"AssocArrayVarchar20_t"，"varchar_t".似乎没有什么改变这种结果.

When I do this I get a SQLException: Unsupported Feature" on the createArrayOf() method. I've also tried setObject() and inside of createArrayOf: "varchar", "AssocArrayVarchar20_t", "varchar_t". Nothing seems to change that outcome.

有人知道我在做什么错吗?我似乎无法使其正常工作.

Does anyone know what I'm doing wrong? I can't seem to get it to work.

更新:成功！

OracleCallableStatement pStmt = (OracleCallableStatement) conn.prepareCall("begin DATA_WRITE(?, ?, ?); end;");
pStmt.setPlsqlIndexTable(1, new String[]{ name }, 1, 1, OracleTypes.VARCHAR, 20);
pStmt.setPlsqlIndexTable(2, new Integer[]{ num }, 1, 1, OracleTypes.NUMBER, 0);
pStmt.setPlsqlIndexTable(3, new String[]{ notes }, 1, 1, OracleTypes.VARCHAR, 4100);
pStmt.execute();

推荐答案

您应该能够将CallableStatement下放到OracleCallableStatement.从那里可以调用 setPlsqlIndexTable方法，您应该可以传入Java数组.

You should be able to downcast the CallableStatement to an OracleCallableStatement. From there you can call the setPlsqlIndexTable method and you should be able to pass in a Java array.

以IN参数模式绑定PL/SQL索引表参数.

Binds a PL/SQL index-by table parameter in the IN parameter mode.

这篇关于java-使用数组调用PL/SQL存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！