How to write a java.sql.Array to a java.sql.SQLOutput within SQLData.writeSQL() for Oracle

Here's what I did to workaround this issue. It's not pretty, but it works.

I added a method in my class implementing SQLData that receives a java.sql.Connection and setups the corresponding java.sql.ARRAY objects.

Something like this:

public class MyObject01 implements SQLData {   private String value;   private MyObject02[] details; // do note that details is a java array   // ... also added getters and setters for these two properties   private Array detailsArray;   public void setupArrays(oracle.jdbc.OracleConnection oconn)      throws SQLException   {       detailsArrays = oconn.createARRAY(MyObject02.ORACLE_OBJECT_ARRAY_NAME, getDetails());       // MyObject02.ORACLE_OBJECT_ARRAY_NAME must be the name of the oracle "table of" type name       // Also note that in Oracle you can't use JDBC's default createArray       // since it's not supported. That's why you need to get a OracleConnection       // instance here.    }          @Override   public void writeSQL(Stream stream) throws SQLException {       stream.writeString(getValue());       stream.writeArray(detailsArray); // that's it   }   @Override   public void readSQL(Stream stream) throws SQLException {       setValue(stream.readString());       Array array = stream.readArray();       if (array != null) {           setDetails((MyObject02[])array.getArray());       }   }

That's the first part.

Then, BEFORE using that object in a procedure call, invoke setupArrays method on that object. Example:

public class DB {    public static String executeProc(Connection conn, MyObject01 obj)        throws SQLException    {        CalllableStatement cs = conn.prepareCall(" { ? = call sch.proc(?) }");        cs.registerOutParameter(1, Types.VARCHAR);        obj.setupArrays((oracle.jdbc.Connection)conn);        cs.setObject(2, obj, Types.STRUCT);        cs.executeUpdate();        String ret = cs.getString(1);        cs.close();        return ret;    }}

Of course, upon connection, you need to register your types properly:

Connection conn = DriverManager.getConnection("jdbc:oracle://localhost:1521/XE", "scott", "tiger" );conn.getTypeMap().put(MyObject01.ORACLE_OBJECT_NAME, MyObject01.class);conn.getTypeMap().put(MyObject02.ORACLE_OBJECT_NAME, MyObject02.class);conn.getTypeMap().put(MyObject02.ORACLE_OBJECT_ARRAY_NAME, MyObject02[].class);

Hope it helps.

Have a look at this SO answer.One can get the connection from the SQLOutput:

    final OracleSQLOutput out = (OracleSQLOutput) sqlOutput;    OracleConnection conn = (OracleConnection) out.getSTRUCT().getJavaSqlConnection();