问题描述
我有一个Oracle存储过程,其中数组作为输入参数,而数组作为输出参数.尽管输入参数已经可以正常工作,但是我总是返回一个空值数组(尽管该数组的长度是我所期望的).
I have an Oracle stored procedure with an array as input parameter and an array as output parameter. While the input parameter already works fine, I always get back an array of null-values (although the length of the array is what I expected).
这只是一个测试环境,所以它是一个简单的示例:存储过程仅采用输入数组并将值复制到输出数组和varchar2字段中,因此我可以看到从输入数组复制到varchar2字段工作正常,但不适用于输出数组.
It is only a test environment so it is a trivial example: the stored procedure only takes the input array and copy the values to the output array and to a varchar2 field, so I can see that the copy from the input array to the varchar2 field works fine but not to the output-array.
我的Java代码如下:
My Java Code is the following:
DriverManager.registerDriver(new OracleDriver());
Connection conn = DriverManager.getConnection(
"<ConnectionString>", "<user>", "<password>");
conn.setAutoCommit(false);
OracleConnection oracleConnection = (OracleConnection)conn;
OracleCallableStatement stmt = (OracleCallableStatement)oracleConnection.prepareCall("call MYPACKAGE.TABLE_IN_TABLE_OUT( ?, ?, ? )");
String[] inputStringArray = { "1", "2", "3", "4" };
Array inputArray = oracleConnection.createOracleArray("MYPACKAGE.CHAR_TABLE", inputStringArray);
stmt.setArray(1, inputArray);
stmt.registerOutParameter(2, Types.ARRAY, "MYPACKAGE.ERG_TABLE");
stmt.registerOutParameter(3, Types.VARCHAR);
stmt.executeUpdate();
Array resultArray = stmt.getArray(2);
String [] resultStringArray = (String[])resultArray.getArray();
String resultString = stmt.getString(3);
System.out.println(resultString);
for (String result : resultStringArray) {
System.out.println(result);
}
conn.commit();
conn.close();
存储过程
create or replace PACKAGE MYPACKAGE IS
TYPE CHAR_TABLE IS TABLE OF CHAR(01) INDEX BY BINARY_INTEGER;
TYPE ERG_TABLE IS TABLE OF CHAR(01) INDEX BY BINARY_INTEGER;
PROCEDURE TABLE_IN_TABLE_OUT(
inputArray IN CHAR_TABLE,
outputArray OUT ERG_TABLE,
resultString OUT VARCHAR2
);
END MYPACKAGE;
存储过程的执行:
create or replace PACKAGE BODY MYPACKAGE AS
PROCEDURE TABLE_IN_TABLE_OUT(
inputArray IN CHAR_TABLE,
outputArray OUT ERG_TABLE,
resultString OUT VARCHAR2) AS
BEGIN
FOR i IN 0..inputArray.last loop
outputArray(i) := inputArray(i);
end loop;
resultString := '';
FOR i IN 0..outputArray.last loop
resultString := resultString || outputArray(i);
end loop;
END TABLE_IN_TABLE_OUT;
END MYPACKAGE;
这是输出:
VARCHAR2 result: 1234
Array result: null, null, null, null,
在互联网上和这个论坛上进行了大量搜索之后,我真的没有发现我在做什么错.
After searching a lot on the internet and in this forum I really did not find out what I am doing wrong.
推荐答案
最后(经过数小时的研究),我还找到了返回按表索引的方法,该方法现在可以使用.因为这是一种非常痛苦的方式,所以我想在这里分享我的解决方案:
Finally (after hours of researching) I found also the way to return index-by Tables which works now. Since it was a very painful way I want to share my solution here:
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection(
"<connectionString>", "<user>", "<password>");
conn.setAutoCommit(false);
OracleConnection oracleConnection = (OracleConnection)conn;
OracleCallableStatement stmt = (OracleCallableStatement)oracleConnection.prepareCall("BEGIN MYPACKAGE.TABLE_IN_TABLE_OUT( ?, ?, ? ); END;");
String[] inputStringArray = { "1", "2", "3", "4", "5", "6"};
Array inputArray = oracleConnection.createOracleArray("MYPACKAGE.CHAR_TABLE", inputStringArray);
stmt.setArray(1, inputArray);
stmt.registerIndexTableOutParameter(2, 100, OracleTypes.VARCHAR, 100);
stmt.registerOutParameter(3, Types.VARCHAR);
stmt.execute();
String resultString = stmt.getString(3);
String[] resultArray = (String[])stmt.getPlsqlIndexTable(2);
System.out.println("VARCHAR2 result: " + resultString);
System.out.print("Array result: ");
for (String result : resultArray) {
System.out.print(result + ", ");
}
我已更改的最重要的事情:
The most important things that I have changed:
-
我从以下位置更改了呼叫字符串:
I changed the Call String from:
oracleConnection.prepareCall("call MYPACKAGE.TABLE_IN_TABLE_OUT( ?, ?, ? )");
到
oracleConnection.prepareCall("BEGIN MYPACKAGE.TABLE_IN_TABLE_OUT( ?, ?, ? ); END;");
因为使用方法"registerIndexTableOutParameter"时遇到了"ORA-01484:数组只能绑定到PL/SQL语句"(请参见下一点).
because I was running into an "ORA-01484: array can only be bound to PL/SQL statements" when using the Method "registerIndexTableOutParameter" (see next point).
而不是通过这种方式注册数组:
Instead of registering the Array this way:
stmt.registerOutParameter(2, Types.ARRAY, "MYPACKAGE.ERG_TABLE");
我现在就这样:
stmt.registerIndexTableOutParameter(2, 100, OracleTypes.VARCHAR, 100);
要获取数组,我必须使用以下代码:
To get the array I had to use the following code:
String[] resultArray = (String[])stmt.getPlsqlIndexTable(2);
仅此而已.希望这对其他人有帮助.
And thats all. Hopefully this helps others.
这篇关于获取一个pl/sql数组(作为out参数)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!