How to create an oracle.sql.ARRAY object?

Question

This question is related to my original problem. How to return an array from Java to PL / SQL? but more specific.

I read the Oracle Database JDBC Developer's Guide and

• Creating ARRAY Objects
• Server-side internal driver
• oracle.jdbc.OracleConnection
• oracle.jdbc.OracleDriver

but i still can't write the minimal code where i can create ARRAY using

ARRAY array = oracle.jdbc.OracleConnection.createARRAY(sql_type_name, elements); 

as indicated in Creating ARRAY objects .

I am using Oracle Database JVM.

I tried the following:

Example 1

 create or replace type widgets_t is table of varchar2(32767); / create or replace and compile java source named "so20j1" as public class so20j1 { public void f1() { String[] elements = new String[]{"foo", "bar", "zoo"}; oracle.sql.ARRAY widgets = oracle.jdbc.OracleConnection.createARRAY("widgets_t", elements); } }; / show errors java source "so20j1" 

Failure:

 Errors for JAVA SOURCE "so20j1": LINE/COL ERROR -------- ----------------------------------------------------------------- 0/0 so20j1:4: non-static method createARRAY(java.lang.String,java.lang.Object) cannot be referenced from a static context 0/0 1 error 0/0 ^ 0/0 oracle.sql.ARRAY widgets = oracle.jdbc.OracleConnection.createARRAY("widgets_t", elements); 

Example 2

 create or replace type widgets_t is table of varchar2(32767); / create or replace and compile java source named "so20j2" as public class so20j2 { public void f1() { String[] elements = new String[]{"foo", "bar", "zoo"}; oracle.jdbc.OracleDriver ora = new oracle.jdbc.OracleDriver(); java.sql.Connection conn = ora.defaultConnection(); oracle.sql.ARRAY widgets = conn.createARRAY("widgets_t", elements); } }; / show errors java source "so20j2" 

Failure:

 Errors for JAVA SOURCE "so20j2": LINE/COL ERROR -------- ----------------------------------------------------------------- 0/0 so20j2:6: cannot find symbol 0/0 symbol : method createARRAY(java.lang.String,java.lang.String[]) 0/0 1 error 0/0 oracle.sql.ARRAY widgets = conn.createARRAY("widgets_t", elements); 0/0 ^ 0/0 location: interface java.sql.Connection 

Disclaimer: I'm not a Java programmer (yet).

Answer 1

You are on the right track with No. 2, but you cannot create an array of oracles from a java.sql.Connection type connection. OracleConnection must use these methods.

 oracle.jdbc.OracleDriver ora = new oracle.jdbc.OracleDriver(); java.sql.Connection conn = ora.defaultConnection(); OracleConnection oraConn = conn.unwrap(OracleConnection.class); oracle.sql.ARRAY widgets = oraConn.createARRAY("widgets_t", elements); 

Answer 2

Based on the answers of Affe and Chris Mazzola, I managed to put together two examples that compile in the Oracle 11g R2 database.

Affe based example

 create or replace type widgets_t is table of varchar2(32767); / create or replace and compile java source named "so20ja1" as public class so20ja1 { public void f1() throws java.sql.SQLException { String[] elements = new String[]{"foo", "bar", "zoo"}; oracle.jdbc.OracleDriver ora = new oracle.jdbc.OracleDriver(); java.sql.Connection conn = ora.defaultConnection(); oracle.jdbc.OracleConnection oraConn = (oracle.jdbc.OracleConnection)conn; java.sql.Array widgets = oraConn.createARRAY("widgets_t", elements); } }; / show errors java source "so20ja1" 

An example based on Chris Mazzola’s answer

 create or replace type widgets_t is table of varchar2(32767); / create or replace and compile java source named "so20ja2" as public class so20ja2 { public void f1() throws java.sql.SQLException { String[] elements = new String[]{"foo", "bar", "zoo"}; oracle.jdbc.OracleDriver ora = new oracle.jdbc.OracleDriver(); java.sql.Connection conn = ora.defaultConnection(); oracle.sql.ArrayDescriptor desc = oracle.sql.ArrayDescriptor.createDescriptor("widgets_t", conn); java.sql.Array widgets = new oracle.sql.ARRAY(desc, conn, elements); } }; / show errors java source "so20ja2" 

Answer 3

 // array sample (using a stored procedure to sum two or more numbers) Connection connection = dataSource.getConnection(username,password); ArrayDescriptor desc = ArrayDescriptor.createDescriptor(schemaName + "." + arrayType, connection); // first ? is the array, second ? is the result via out parameter String sql = "call sum_numbers(?,?)"; CallableStatement cs = connection.prepareCall(sql); String[] args = {"5","15","25","35"}; // what to sum Array array = new oracle.sql.ARRAY(desc, connection, args); cs.setArray(1, array); cs.registerOutParameter(2, Types.INTEGER); cs.execute(); int result = cs.getInt(2); cs.close(); 

Answer 4

Just mention that in Java 1.6 you have connection.createArrayOf(..) , which is standard.