How to convert Java List into an Oracle ARRAY to enter as a param in an Oracle Stored procedure?

Question

I have the following list of Person Objects:

List<Person> persons;

The Person object is as follows:

@XmlRootElement
@XmlAccessorType(XmlAccessType.NONE)
public class Person
{
    @XmlElement
    private int id;
    @XmlElement
    private String name;

    public Person(){

    }

}

A Stored Procedure I am invoking takes a list of Person Objects as an Oracle ARRAY.

How can I convert my list of Java Objects into an Oracle ARRAY in order to pass it into the Stored Procedure?

I have seen the CallableStatement.setArray() method, but I do not know how to convert my Java List into an Oracle ARRAY first.

Also duplicate of http://stackoverflow.com/questions/3626061/how-to-call-oracle-stored-procedure-which-include-user-defined-type-in-java — MT0, Dec 12 '16 at 09:48

Davide Lorenzo MARINO · Accepted Answer · 2016-12-12T09:41:55.377

0

You can create an sql Array using the method createARRAY.

Creates an ARRAY object with the given type name and elements.

Here a simple example

    String[] array = new String[]{"a", "b", "c"};
    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 sqlArray = oraConn.createARRAY("typeName", array);

If you start from a List of Object

List<Object> list = ...
...
Object[] array = list.toArray();
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 sqlArray = oraConn.createARRAY("typeName", array);

edited Dec 12 '16 at 09:41

answered Dec 12 '16 at 09:36

Davide Lorenzo MARINO

26,420
4
39
56

Thanks, although will this work for an ArrayList of Objects ? – java123999 Dec 12 '16 at 09:38
And also, what would the "typeName" be in my case, can you please show a complete example? – java123999 Dec 12 '16 at 09:41
@java123999 I edited my answer to show how to use a List – Davide Lorenzo MARINO Dec 12 '16 at 09:43
Thanks for edit. So in my case what would the typeName be for a list of Person Objects? – java123999 Dec 12 '16 at 09:44
Is the sql type. I don't know what name you used, probably Person? – Davide Lorenzo MARINO Dec 12 '16 at 09:45
ah ok, the SQL type that is stated in the Stored Procedure? – java123999 Dec 12 '16 at 09:46

How to convert Java List into an Oracle ARRAY to enter as a param in an Oracle Stored procedure?

1 Answers1