How to create an oracle.sql.ARRAY object?

Question

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

I have been reading Oracle Database JDBC Developer's Guide and

• Creating ARRAY objects
• Server-Side Internal Driver
• oracle.jdbc.OracleConnection
• oracle.jdbc.OracleDriver

but I still fail to write a minimum code where I can create ARRAY using

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

as instructed in Creating ARRAY objects.

I'm using Oracle Database JVM.

I have tried 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"

Fails with:

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"

Fails with:

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're on the right track with #2, but you can't create an oracle Array from a connection of type java.sql.Connection. It has to be an OracleConnection to be able to use those 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

Here is a working example tested with Oracle 19:

final BigDecimal[] plainJavaArray = new BigDecimal[]{BigDecimal.valueOf(1L), BigDecimal.valueOf(2L)};
final oracle.jdbc.OracleConnection oracleConnection = (oracle.jdbc.OracleConnection) jdbcConnection;
final java.sql.Array array = oracleConnection.createOracleArray("SYS.ODCINUMBERLIST", plainJavaArray);

Keep in mind some very important aspects:

• oracle.jdbc.OracleConnection.createOracleArray() expects the array type name, not the element type name unlike java.sql.Connection.createArrayOf().
• java.sql.Connection.createArrayOf() throws a java.sql.SQLFeatureNotSupportedException.
• The first parameter is case-sensitive; if you pass the correct array type name with the wrong case, you'll get an SQLException complaining about a wrong model name.
• You can use SYS.ODCIVARCHAR2LIST or SYS.FI_CATEGORICALS for VARCHAR2, SYS.ODCIDATELIST for DATE, etc. SYS.ODCIGRANULELIST is twice bigger than SYS.ODCINUMBERLIST and if it's not enough, use SYS.FI_NUMERICALS. I haven't tested with user-defined types but CREATE TYPE ARRAY_OF_THING IS TABLE OF THING should work.
• Don't use deprecated methods, they'll stop working or be removed sooner or later.

Answer 3

Based on answers of Affe and Chris Mazzola I have succeeded to build two examples that compile in Oracle 11g R2 database.

Example based on Affe's answer

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"

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 4

// 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 5

Map<String, Object> params = new HashMap<>();

        params.put("input_to_sp", new ExampleArrayMapper (new String[] { "ABC" }));

        Map<String, Object> results = spObject.execute(params);

        final List<Object> output = (List<Object>) results.get("po_out_cur");


    public class ExampleArrayMapper extends AbstractSqlTypeValue {
        private String[] customObject;

        public ExampleArrayMapper (String[] customObject) {
            this.customObject= customObject;
        }

        public String getSQlTypeName() throws SQLException {
            return "NAME_OF_TYPE_IN_SQL";
        }

        @Override
        protected Object createTypeValue(Connection con, int sqlType, String typeName) throws SQLException {
            try {
                con = dataSource.getConnection().unwrap(OracleConnection.class);
                Array reportsArray = ((OracleConnection) con).createOracleArray(SQL_TYPE_NAME, customObject);
                return reportsArray;
            } finally {
                con.close();
            }
        }
    }

Answer 6

Oracle 12.2 version:

oracle.jdbc.OracleArray  v_arr ;
String[] v_list ;


v_arr = ((oracle.jdbc.OracleConnection)DriverManager.getConnection("jdbc:default:connection:")).createARRAY ( "T_STRING_ARRAY" , v_list ) ;

Answer 7

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