如何将数组从 Java 返回到 PL/SQL?

问题描述

我在 PL/SQL 和 Java 之间来回传递数字和字符串没有问题，但是我如何传递 数组?我正在从 PL/SQL 调用 Java - 而不是其他方式.

I have no problems to pass numbers and strings back and forth from PL/SQL to Java, but how do I pass arrays ? I'm calling Java from PL/SQL - not other way round.

以下是 get_widgets_as_string 按预期工作的示例.我如何为 so19j.get_widgets_as_array() 编写 PL/SQL 调用规范，以便我可以从 PL/SQL 调用它?

Below is an example where get_widgets_as_string works as expected. How do I write a PL/SQL call specification for so19j.get_widgets_as_array() so that I can call it from PL/SQL ?

我已阅读发布带有调用规范的 Java 类 在那里我可以看到嵌套表对应于 oracle.sql.ARRAY，但我无法让它工作.因为我不是 Java 程序员，所以我可能遗漏了一些琐碎的细节.

I have read Publishing Java Classes With Call Specifications where I can see that nested table corresponds to oracle.sql.ARRAY, but I can't get it working. I'm probably missing some trivial details because I'm not a Java programmer.

create or replace and compile java source named "so19j" as

import java.lang.*;

public class so19j {
    public static String get_widgets_as_string() {
        String widgets = "foo;bar;zoo";
        return widgets;
    }

    public static String[] get_widgets_as_array() {
        String[] widgets = new String[]{"foo", "bar", "zoo"};
        return widgets;
    }
};
/
show errors java source "so19j"

create or replace function get_widgets_as_string return varchar2 as
language java name 'so19j.get_widgets_as_string() return java.lang.String';
/
show errors

declare
  widgets constant varchar2(32767) := get_widgets_as_string;
begin
  dbms_output.put_line('widgets = ' || widgets);
end;
/

/* How to write a call specification for so19j.get_widgets_as_array so that it
can be excercised by the PL/SQL block below ? */

declare
  type widgets_t is table of varchar2(32767);
  widgets constant widgets_t := get_widgets_as_array;
begin
  for i in widgets.first .. widgets.last loop
    dbms_output.put_line('widgets(' || i || ') = ' || widgets(i));
  end loop;
end;
/

推荐答案

/* The type has to be SQL type so that it is also visible for Java. */
create or replace type widgets_t is table of varchar2(32767);
/

create or replace and compile java source named "so19ja" as
import java.lang.*;
public class so19ja {
    public static String[] get_widgets_as_array() {
        String[] widgets = new String[]{"foo", "bar", "zoo"};
        return widgets;
    }

    public static java.sql.Array array_wrapper(
        String typeName,
        Object elements
    ) throws java.sql.SQLException {
        oracle.jdbc.OracleDriver ora = new oracle.jdbc.OracleDriver();
        java.sql.Connection conn = ora.defaultConnection();
        oracle.jdbc.OracleConnection oraConn =
            (oracle.jdbc.OracleConnection)conn;
        /* Yeah - typeName have to be UPPERCASE, really. */
        java.sql.Array arr =
            oraConn.createARRAY(typeName.toUpperCase(), elements);
        return arr;
    }

    public static java.sql.Array get_widgets_as_array_wrapped()
    throws java.sql.SQLException {
        return array_wrapper("widgets_t", get_widgets_as_array());
    }
};
/
show errors java source "so19ja"

create or replace function get_widgets_as_array return widgets_t as
language java name 'so19ja.get_widgets_as_array_wrapped() return java.sql.Array';
/
show errors

declare
  widgets constant widgets_t := get_widgets_as_array;
begin
  for i in widgets.first .. widgets.last loop
    dbms_output.put_line('widgets(' || i || ') = ' || widgets(i));
  end loop;
end;
/

打印:

widgets(1) = foo
widgets(2) = bar
widgets(3) = zoo

PL/SQL procedure successfully completed.

另见:如何创建 oracle.sql.ARRAY 对象?

这篇关于如何将数组从 Java 返回到 PL/SQL?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！