How to return an array from Java to PL / SQL?

Question

I have no problem passing numbers and strings from PL / SQL to Java, but how do I pass arrays? I call Java from PL / SQL - not the other way around.

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

I read the publication of Java classes with call specifications , where I see that the nested table matches oracle.sql.ARRAY , but I can’t get it working. I probably missed 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; / 

Answer 1

 /* 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; / 

Print

 widgets(1) = foo widgets(2) = bar widgets(3) = zoo PL/SQL procedure successfully completed. 

See also: How to create an oracle.sql.ARRAY object?

Answer 2

I don't know about pl / sql, but what you can do is process the result from PL / SQL and paste the result into the set method, and then later you can get it.