如何获得作为数组工作的存储过程输出参数? [英] 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"和"GAMMA".因此，看起来数据已将其返回给客户端，但未正确转换为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;

然后您可以执行以下操作:

Then you can do:

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;

注意:此答案假设在

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屋！