Using Oracle COLLECT aggregate function from JDBC

Question

I'm trying to get the Oracle COLLECT() aggregation function to work from JDBC.

My schema looks like this

CREATE TABLE t_warehouse (
  id                NUMBER(5)    NOT NULL PRIMARY KEY,
  warehouse_name    VARCHAR2(64) NOT NULL
);


CREATE TABLE t_warehouse_detail (
  id              NUMBER(5)     NOT NULL PRIMARY KEY,
  warehouse_id    NUMBER(5)    NOT NULL,
  detail_value    VARCHAR2(128) NOT NULL,
  CONSTRAINT fk_warehouse 
      FOREIGN KEY (warehouse_id)
      REFERENCES  t_warehouse(id)
);

INSERT INTO t_warehouse (id, warehouse_name)
     VALUES             (1, 'warehouse 1');

INSERT INTO t_warehouse_detail (id, warehouse_id, detail_value)
     VALUES                    (1,  1,            'detail 1');

INSERT INTO t_warehouse_detail (id, warehouse_id, detail_value)
     VALUES                    (2,  1,            'detail 2');


CREATE OR REPLACE TYPE warehouse_detail_t AS TABLE OF VARCHAR2(128);

my query looks like this

SELECT wh.id, CAST(COLLECT(detail_value) AS warehouse_detail_t) AS "Details"
  FROM t_warehouse wh
  JOIN t_warehouse_detail whd ON (wh.id = whd.warehouse_id)
GROUP BY wh.id;

my code looks like this

String url = ...;
String username = ...;
String password = ...;
OracleDataSource ds = new oracle.jdbc.pool.OracleDataSource();
ds.setURL(url);
ds.setUser(username);
ds.setPassword(password);

try (Connection connection = ds.getConnection()) {

  connection.setTypeMap(Collections.singletonMap("SCHEMA_NAME.WAREHOUSE_DETAIL_T", DetailValues.class));

  try (PreparedStatement preparedStatement = connection.prepareStatement("SELECT wh.id, CAST(COLLECT(detail_value) AS warehouse_detail_t) AS \"Details\""
        + " FROM t_warehouse wh "
        + "  JOIN t_warehouse_detail whd ON (wh.id = whd.warehouse_id) "
        + " GROUP BY wh.id");
       ResultSet resultSet = preparedStatement.executeQuery()) {
    while (resultSet.next()) {
      int id = resultSet.getInt("id");
      Set<String> detailValues = Collections.emptySet();
    }
  }

and I have the following SQLData class


  public static final class DetailValues implements SQLData {

    private String typeName;

    @Override
    public String getSQLTypeName() throws SQLException {
      return this.typeName;
    }

    @Override
    public void readSQL(SQLInput stream, String typeName) throws SQLException {
      this.typeName = typeName;
      Array array = stream.readArray();
      if (array != null) {
        try {

        } finally {
          array.free();
        }
      }
      System.out.println("readSQL");
    }

    @Override
    public void writeSQL(SQLOutput stream) throws SQLException {
      System.out.println("writeSQL");
    }

  }

and I get the following exception

java.sql.SQLException: Fail to construct descriptor: Unable to resolve type "SCHEMA_NAME.WAREHOUSE_DETAIL_T"
    at oracle.sql.TypeDescriptor.getTypeDescriptor(TypeDescriptor.java:1002)
    at oracle.jdbc.driver.NamedTypeAccessor.otypeFromName(NamedTypeAccessor.java:82)
    at oracle.jdbc.driver.TypeAccessor.initMetadata(TypeAccessor.java:75)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:860)
    at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:983)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3666)
    at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1426)
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3713)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1167)
    at com.acme.CollectTests.collect(CollectTests.java:48)

This is with ojdbc8-19.7.0.0 and Oracle 19.3.

The query also fails in Oracle SQL Developer with the same exception.

Update

FINEST output from the _g JAR is:

[oracle.jdbc.pool.OracleDataSource oracle.jdbc.pool.OracleDataSource        ]          Enter: void 
[oracle.jdbc.pool.OracleDataSource setConnectionProperty                    ] 47542153 Enter: "oracle.jdbc.enableACSupport", "false" 
[oracle.jdbc.pool.OracleDataSource setConnectionProperty                    ] 47542153 Return: void 
[oracle.jdbc.pool.OracleDataSource setConnectionProperty                    ] 47542153 Exit: [0.370177 ms] 
[oracle.jdbc.pool.OracleDataSource processFastConnectionFailoverSysProperty ] 47542153 Enter: void 
[oracle.jdbc.pool.OracleDataSource processFastConnectionFailoverSysProperty ] 47542153 Return: void 
[oracle.jdbc.pool.OracleDataSource processFastConnectionFailoverSysProperty ] 47542153 Exit: [0.133466 ms] 
[oracle.jdbc.pool.OracleDataSource oracle.jdbc.pool.OracleDataSource        ]          Return: void 
[oracle.jdbc.pool.OracleDataSource oracle.jdbc.pool.OracleDataSource        ]          Exit: [0.386721 ms] 
[oracle.jdbc.pool.OracleDataSource setURL                                   ] 47542153 Enter: "jdbc:oracle:thin:@localhost:1521/ORCLPDB1?oracle.net.disableOob=true" 
[oracle.jdbc.pool.OracleDataSource setURL                                   ] 47542153 Return: void 
[oracle.jdbc.pool.OracleDataSource setURL                                   ] 47542153 Exit: [0.13673 ms] 
[oracle.jdbc.pool.OracleDataSource setUser                                  ] 47542153 Enter: "schema_name" 
[oracle.jdbc.pool.OracleDataSource setUser                                  ] 47542153 Return: void 
[oracle.jdbc.pool.OracleDataSource setUser                                  ] 47542153 Exit: [0.121062 ms] 
[oracle.jdbc.pool.OracleDataSource setPassword                              ] 47542153 Enter: "*****" 
[oracle.jdbc.pool.OracleDataSource setPassword                              ] 47542153 Enter: oracle.jdbc.internal.OpaqueString@67d0ff08 
[oracle.jdbc.pool.OracleDataSource setPassword                              ] 47542153 Return: void 
[oracle.jdbc.pool.OracleDataSource setPassword                              ] 47542153 Exit: [0.128787 ms] 
[oracle.jdbc.pool.OracleDataSource setPassword                              ] 47542153 Return: void 
[oracle.jdbc.pool.OracleDataSource setPassword                              ] 47542153 Exit: [0.379598 ms] 
[oracle.jdbc.pool.OracleDataSource getConnection                            ] 47542153 Enter: void 
[oracle.jdbc.pool.OracleDataSource getConnection                            ] 47542153 Enter: "schema_name", "*****" 
[oracle.jdbc.pool.OracleDataSource getConnection                            ] OracleDataSource.getConnection(user, passwd): URL isjdbc:oracle:thin:@localhost:1521/ORCLPDB1?oracle.net.disableOob=true 
[oracle.jdbc.pool.OracleDataSource createConnectionBuilder                  ] 47542153 Enter: void 
[oracle.jdbc.pool.OracleDataSource createConnectionBuilder                  ] 47542153 Return: oracle.jdbc.pool.OracleDataSource$1@5e316c74 
[oracle.jdbc.pool.OracleDataSource createConnectionBuilder                  ] 47542153 Exit: [2.499096 ms] 
[oracle.jdbc.pool.OracleDataSource getConnection                            ] 47542153 Enter: oracle.jdbc.pool.OracleDataSource$1@5e316c74 
[oracle.jdbc.pool.OracleDataSource makeURL                                  ] 47542153 Enter: void 
[oracle.jdbc.pool.OracleDataSource makeURL                                  ] 47542153 Return: void 
[oracle.jdbc.pool.OracleDataSource makeURL                                  ] 47542153 Exit: [0.10966 ms] 
[oracle.jdbc.pool.OracleDataSource getPhysicalConnection                    ] 47542153 Enter: {password=redacted, oracle.jdbc.enableACSupport=false, connection_url=jdbc:oracle:thin:@localhost:1521/ORCLPDB1?oracle.net.disableOob=true, user=schema_name}, null 
[oracle.jdbc.pool.OracleDataSource getPhysicalConnection                    ] OracleDataSource.getPhysicalConnection(prop): URL isjdbc:oracle:thin:@localhost:1521/ORCLPDB1?oracle.net.disableOob=true, user: schema_name, password: ***** 
[oracle.jdbc.driver.HAManager oracle.jdbc.driver.HAManager                  ]          Enter: void 
[oracle.jdbc.driver.HAManager oracle.jdbc.driver.HAManager                  ]          Return: void 
[oracle.jdbc.driver.HAManager oracle.jdbc.driver.HAManager                  ]          Exit: [0.675662 ms] 
[oracle.jdbc.driver.NoSupportHAManager oracle.jdbc.driver.NoSupportHAManager]          Enter: void 
[oracle.jdbc.driver.NoSupportHAManager oracle.jdbc.driver.NoSupportHAManager]          Return: void 
[oracle.jdbc.driver.NoSupportHAManager oracle.jdbc.driver.NoSupportHAManager]          Exit: [0.101079 ms] 
[oracle.jdbc.driver.NoSupportHAManager getInstance                          ]          Enter: void 
[oracle.jdbc.driver.NoSupportHAManager getInstance                          ]          Return: oracle.jdbc.driver.NoSupportHAManager@bccb269 
[oracle.jdbc.driver.NoSupportHAManager getInstance                          ]          Exit: [0.104732 ms] 
[oracle.jdbc.driver.HAManager enableHAIfNecessary                           ]          Enter: "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCLPDB1)))", oracle.jdbc.driver.T4CConnection@3c0be339 
[oracle.jdbc.driver.HAManager enableHAIfNecessary                           ] ========= simplefan.jar or ons.jar is not on the classpath, HA is disabled, java.lang.ClassNotFoundException: oracle.simplefan.FanManager 
[oracle.jdbc.driver.HAManager enableHAIfNecessary                           ] ========= HA is explicitly disabled 
[oracle.jdbc.driver.HAManager enableHAIfNecessary                           ]          Return: void 
[oracle.jdbc.driver.HAManager enableHAIfNecessary                           ]          Exit: [0.660915 ms] 
[oracle.jdbc.pool.OracleDataSource getPhysicalConnection                    ] 47542153 Return: oracle.jdbc.driver.T4CConnection@3c0be339 
[oracle.jdbc.pool.OracleDataSource getPhysicalConnection                    ] 47542153 Exit: [0.946502 ms] 
[oracle.jdbc.pool.OracleDataSource getConnection                            ] 47542153 Return: oracle.jdbc.driver.T4CConnection@3c0be339 
[oracle.jdbc.pool.OracleDataSource getConnection                            ] 47542153 Exit: [1.244383 ms] 
[oracle.jdbc.pool.OracleDataSource getConnection                            ] 47542153 Return: oracle.jdbc.driver.T4CConnection@3c0be339 
[oracle.jdbc.pool.OracleDataSource getConnection                            ] 47542153 Exit: [1.471857 ms] 
[oracle.jdbc.pool.OracleDataSource getConnection                            ] 47542153 Return: oracle.jdbc.driver.T4CConnection@3c0be339 
[oracle.jdbc.pool.OracleDataSource getConnection                            ] 47542153 Exit: [1.732571 ms] 
[oracle.jdbc.driver.NoSupportHAManager dropConnection                       ] BCCB269 Enter: oracle.jdbc.driver.T4CConnection@3c0be339 
[oracle.jdbc.driver.NoSupportHAManager dropConnection                       ] NoSupportHAManager.dropConnection() 
[oracle.jdbc.driver.NoSupportHAManager dropConnection                       ] BCCB269 Return: void 
[oracle.jdbc.driver.NoSupportHAManager dropConnection                       ] BCCB269 Exit: [0.180062 ms] 
Exception in thread "main" java.sql.SQLException: Fail to construct descriptor: Unable to resolve type "SCHEMA_NAME.WAREHOUSE_DETAIL_T"
    at oracle.sql.TypeDescriptor.getTypeDescriptor(TypeDescriptor.java:998)
    at oracle.jdbc.driver.NamedTypeAccessor.otypeFromName(NamedTypeAccessor.java:78)
    at oracle.jdbc.driver.TypeAccessor.initMetadata(TypeAccessor.java:71)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:856)
    at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:979)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1164)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3662)
    at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1422)
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3709)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1163)

@DouglasSurber same exception with the fully qualified type name in the exception message — Philippe Marschall
– Philippe Marschall, Commented Aug 22, 2020 at 18:27
This code has been around forever and hasn't been touched in years. It seems unlikely that there is a bug in the driver. I'm not saying it's impossible but unlikely. Some how the driver is not able to get a description of the type. I guess at this point I'd use the debug jar, ojdbc11_g.jar (or 8) and enable trace at the CONFIG level. I think that will show the SQL the driver executes to get the type description. If not FINEST should do it though it will produce a huge log file. — Douglas Surber
– Douglas Surber, Commented Aug 24, 2020 at 16:13
@DouglasSurber I'm sure I'm doing something wrong. I'll try the debug jar then. — Philippe Marschall
– Philippe Marschall, Commented Aug 26, 2020 at 7:00

Roberto Hernandez · Accepted Answer · 2020-08-11 17:51:16Z

Your code works perfectly in SQL. Perhaps, the only think I saw missing in your code is the slash at the end of the type. Keep in mind that a type is the same as a procedure, package or function, you need the /to create the object

SQL> CREATE TABLE t_warehouse (
  id                NUMBER(5)    NOT NULL PRIMARY KEY,
  warehouse_name    VARCHAR2(64) NOT NULL
);  2    3    4

Table created.

SQL> CREATE TABLE t_warehouse_detail (
  id              NUMBER(5)     NOT NULL PRIMARY KEY,
  warehouse_id    NUMBER(5)    NOT NULL,
  detail_value    VARCHAR2(128) NOT NULL,
  2    CONSTRAINT fk_warehouse
      FOREIGN KEY (ware  3  house_id)
      REFERENCES  t_warehouse(id)
  4    5    6    7    8  );

Table created.

SQL> INSERT INTO t_warehouse (id, warehouse_name)
  2       VALUES             (1, 'warehouse 1');

INSERT INTO t_warehouse_detail (id, warehouse_id, detail_value)
     VALUES                    (1,  1,            'detail 1');

INSERT INTO t_warehouse_detail (id, warehouse_id, detail_value)
     VALUES                    (2,  1,            'detail 2');
1 row created.

SQL> SQL>   2
1 row created.

SQL> SQL>   2

1 row created.

SQL> commit ;

Commit complete.

SQL> CREATE OR REPLACE TYPE warehouse_detail_t AS TABLE OF VARCHAR2(128);
  2
  3  /

Type created.

SQL> SELECT wh.id, CAST(COLLECT(detail_value) AS warehouse_detail_t) AS "Details"
  FROM t_warehouse wh
  JOIN t_warehouse_detail whd ON (wh.id = whd.warehouse_id)
GROUP BY wh.id;  2    3    4

        ID
----------
Details
--------------------------------------------------------------------------------
         1
WAREHOUSE_DETAIL_T('detail 1', 'detail 2')

I think it's a JDBC issue the type definitely exists ` SELECT * FROM user_types WHERE type_name = 'WAREHOUSE_DETAIL_T' ` returns the type.

Thomas Aregger · Accepted Answer · 2020-08-11 21:10:23Z

The issue is with implementing SQLData in your data class. Oracles JDBC Developer's Guide states the following:

Custom Java classes used for collections are referred to as custom collection classes. A custom collection class must implement the Oracle oracle.jdbc.OracleData interface. In addition, the custom class or a companion class must implement oracle.jdbc.OracleDataFactory. The standard java.sql.SQLData interface is for mapping SQL object types only.

A working example:

public static final class DetailValues implements OracleData, OracleDataFactory {

  private List<String> details;

  @Override
  public Object toJDBCObject(Connection connection) throws SQLException {
    // TODO implement when type also used in PreparedStatement.setObject
    return null;
  }

  @Override
  public OracleData create(Object o, int i) throws SQLException {
    String[] details = (String[]) ((OracleArray) o).getArray();
    this.details = List.of(details);
    return this;
  }
}

and then using ResultSet.getObject() in your example when looping over the ResultSet:

DetailValues detailValues = resultSet.getObject("details", DetailValues.class);

Collectives™ on Stack Overflow

Using Oracle COLLECT aggregate function from JDBC

2 Answers 2

1 Comment

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Related