如何获取作为数组的存储过程输出参数来工作? [英] How do I get a stored procedure output parameter that is an array to work?

问题描述

我一直在开发对存储过程的 Java/JDBC 调用并取得了一些成功.但是，当输出参数是字符串数组时，我被卡住了.我已经成功地使用标量类型作为输入和输出参数，以及一个字符串数组作为输入参数.

I have been developing Java/JDBC calls to stored procedures with some success. However I am stuck when an output parameter is an array of strings. I have been successful with scalar types as input and output parameters, as well as an array of strings as an input parameter.

这是我拥有的 PL/SQL 代码:

Here's the PL/SQL code I have:

TYPE StringArray IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;

create or replace package body 
test is
  procedure upper(
    in_array     in StringArray,
    out_array    out StringArray
  ) is
    tmp StringArray := in_array;
  begin
    for i in 0..(tmp.count-1) loop
      tmp(i) := UPPER(tmp(i));
    end loop;
    out_array := tmp;
  end;
end test;

这是我的 Java 代码:

Here's the Java code I have:

public void testString(Connection connection) {
    String[] values = { "alpha", "beta", "gamma" };
    try {
       CallableStatement callableStatement = connection.prepareCall("begin test.upper(?, ?); end;");
       DelegatingConnection<OracleConnection> delegatingConnection = (DelegatingConnection<OracleConnection>) new DelegatingConnection(connection);
       OracleConnection oracleConnection = (OracleConnection) delegatingConnection.getInnermostDelegate();
       Array input oracleConnection.createOracleArray("STRINGARRAY", values);
       callableStatement.setObject("in_array", input);
       callableStatement.registerOutParameter("out_array", Types.ARRAY, "STRINGARRAY");
       callableStatement.execute();
       Array output = (Array)callableStatement.getObject("out_array");
       String[] result = (String[])output.getArray();
       System.out.println("Length: " + result.length);  // Prints "Length: 3"
       System.out.println("First: " + result[0]);       // Prints "First: null"
    } (catch SQLException e) {
       // Handle error
    }
}

如果我直接从 SQL 脚本调用 PL/SQL 存储过程，它就可以工作.所以我认为存储过程本身是可以的.

If I call the PL/SQL stored procedure from an SQL script directly it works. So I think the stored procedure itself is okay.

如果我通过 JDBC 调用存储过程，它会正常完成.使用调试语句，我已确认 values 已从 Java 客户端正确发送到存储过程中的 in_array.也就是说，接收到具有适当值的长度为 3 的数组.据我所知，out_array 被发送回 Java 客户端.但是，出了点问题.result 的大小为 3，但所有元素都是 null.

If I call the stored procedure via JDBC it completes normally. Using debug statements, I have confirmed that values is correctly sent from the Java client to the stored procedure into in_array. That is, an array of length 3 with the appropriate values is received. As best as I can tell, out_array is sent back to the Java client. However, something goes wrong. result is of size 3 but all the elements are null.

如果我检查 output，我可以看到它在内部有一个长度为 38 的 byte[].这些字节的子序列映射到ALPHA"，"BETA"和伽玛".所以看起来数据使它返回到客户端，但它没有正确转换为 String[].

If I inspect output, I can see that internally it has a byte[] of length 38. Sub-sequences of these bytes map to "ALPHA", "BETA" and "GAMMA". So it looks like the data makes it back to the client but it is not converted into a String[] correctly.

我做错了什么?

推荐答案

不要使用关联数组 - 使用集合:

Do not use an Associative Array - use a Collection:

CREATE TYPE StringArray IS TABLE OF VARCHAR2(4000);
CREATE TYPE CLOBArray   IS TABLE OF CLOB;

然后你可以这样做:

public void testString(Connection connection) {
  String[] values = { "alpha", "beta", "gamma" };
  try {
    OracleConnection oc = (OracleConnection) connection;

    ARRAY stringArray = oc.createARRAY( "STRINGARRAY", values ); // Upper case identifier

    OracleCallableStatement st = (OracleCallableStatement) oc.prepareCall(
      "begin test.upper( :in_array, :out_array ); end;"
    );

    st.setARRAYAtName( "in_array", stringArray );
    st.registerOutParameter( "out_array", Types.ARRAY, "STRINGARRAY"); // Upper case again
    st.execute();

    String[] result = (String[])st.getARRAY( 2 ).getArray();

    System.out.println("Length: " + result.length);
    System.out.println("First: " + result[0]);
  } (catch SQLException e) {
    // Handle error
  }
}

如果必须使用 PL/SQL 关联数组作为过程的输入，请编写一个函数，该函数接受一个集合并输出适当类型的关联数组，然后调用它:

If you must use a PL/SQL associative array as an input to your procedure then write a function that takes a collection and outputs an associative array of the appropriate type and then call that:

BEGIN TEST.UPPER( TO_ASSOC_ARRAY( :in_collection ), :out_array ); END;

注意:此答案假设使用 ojdbc6.jar 似乎没有 OracleConnection.createOracleArray() 方法 但它有望将 oracle.sql.ARRAY 更改为 java.sql.Array 并使用较新的方法.

Note: This answer is assuming using the Oracle driver in ojdbc6.jar which does not appear to have the OracleConnection.createOracleArray() method but it will, hopefully, be portable changing oracle.sql.ARRAY to java.sql.Array and using the newer methods.

这篇关于如何获取作为数组的存储过程输出参数来工作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！