JDBC: Oracle stored procedure returns nested table

Question

I have a stored procedure in oracle(12c):

PROCEDURE work(a OUT VARCHAR2, b OUT SYS_REFCURSOR)

When I call the procedure the result of cursor contains rows, the row consists of some primitive values + a custom type:

CREATE TYPE ELEM AS OBJECT(ElemID INTEGER))
CREATE TYPE LIST AS TABLE OF ELEM

So, the result is something like:

vcursor:
------------------
COL1 COL2 LIST
------------------
A    B    LIST([ELEM],[ELEM])
C    D    LIST([ELEM],[ELEM],[ELEM])
E    F    LIST([ELEM])

There is a tutorial for collections Oracle tutorial (in case link is lost, google for "Working with Oracle Collections"). Nothing worked for me so far from this tutorial.

Here is what I do in the java:

Connection conn = ...
CallableStatement statement = conn.prepareCall("{call work()}");
statement.registerOutParameter(1, OracleTypes.VARCHAR);
statement.registerOutParameter(2, OracleTypes.CURSOR);
statement.execute();

ResultSet rs = statement.getResultSet(2);
// here should be the code which retrieves the nested table elements
rs.next()
Array list = rs.getArray(3); //oracle.sql.ARRAY
Object[] elems = (Object[]) list.getArray(); // doesn't work
// I also tried list.getResultSet()

Under debug I do see that if I cast to oracle.sql.ARRAY and call getOracleArray() then I see the correct size and Datum[] array with Struct[], however plain sql methods give me nothing. For example getResultSet from array returns a ResultSet with next always returning true, but not having metadata and actual data. getArray() returns array with numElements = -1, etc..

I tried to play with typeMap with no luck:

Map map = conn.getTypeMap();
map.put("ELEM", Elem.class); 
//map.put("SCHEMA.ELEM", Elem.class); 
//map.put("LIST", Elem.class); 
//map.put("SCHEMA.LIST", Elem.class); 
conn.setTypeMap(map);

Please help and advice. Thanks in advance.

Answer 1

I'm afraid I'm struggling to reproduce the behaviour you're seeing. I tried to put together some code that would reproduce your problem, but it worked. The code is below, I hope is it of some use to you.

I used a type map entry for the type ELEM but not for LIST. I also couldn't find the getResultSet(int) method on the CallableStatement interface, so I used getObject(int) instead and casted that to ResultSet.

In case it matters, I'm using Oracle 11g XE 11.2.0.2.0, Java 1.8.0_60, ojdbc7.jar version 12.1.0.2.0 on Windows 10 x64. I also tried versions of ojdbc6.jar and ojdbc5.jar, and they had the same result.

SQL:

CREATE TYPE elem AS OBJECT(ElemID INTEGER);
/

CREATE TYPE list AS TABLE OF elem;
/

CREATE OR REPLACE PROCEDURE work(a OUT VARCHAR2, b OUT SYS_REFCURSOR)
AS
BEGIN
  a := 'test 1234';
  OPEN b FOR
    SELECT 'A' AS col1, 'B' AS col2, list(elem(14), elem(17)) FROM DUAL
     UNION ALL
    SELECT 'C' AS col1, 'D' AS col2, list(elem(8), elem(4), elem(11)) FROM DUAL
     UNION ALL
    SELECT 'E' AS col1, 'F' AS col2, list(elem(-1)) FROM DUAL;
END work;
/

Elem.java:

import java.sql.*;

public class Elem implements SQLData {

    private Integer elementId;

    public String getSQLTypeName() { return "ELEM"; }

    public void readSQL(SQLInput input, String typeName) throws SQLException {
        elementId = input.readInt();
    }

    public void writeSQL(SQLOutput output) {
        throw new UnsupportedOperationException();
    }

    public String toString() {
        return "<Element " + elementId + ">";
    }
}

JDBCStructs.java:

import java.sql.*;
import java.util.*;

public class JDBCStructs {
    public static void main(String[] args) throws Exception {
        Connection c = ...

        Map<String, Class<?>> map = c.getTypeMap();
        map.put("ELEM", Elem.class);
        c.setTypeMap(map);        

        CallableStatement cs = c.prepareCall("{call work(?, ?)}");
        cs.registerOutParameter(1, Types.VARCHAR);
        cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
        cs.execute();
        String a = cs.getString(1);
        System.out.println("Got string " + a);
        ResultSet rSet = (ResultSet)cs.getObject(2);
        while (rSet.next()) {
            String col1 = rSet.getString(1);
            String col2 = rSet.getString(2);
            Array list = (Array)rSet.getObject(3);
            System.out.println("Got " + col1 + ", " + col2 + " and " + Arrays.toString((Object[])list.getArray()));
        }
    }
}

Output:

Got string test 1234
Got A, B and [<Element 14>, <Element 17>]
Got C, D and [<Element 8>, <Element 4>, <Element 11>]
Got E, F and [<Element -1>]