TechQA.

Using Oracle COLLECT aggregate function from JDBC

481 views Asked by At

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)
oracle jdbc aggregate-functions ojdbc user-defined-data-types
Original Q&A
2

There are 2 answers

1
Roberto Hernandez On

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')
1
Thomas Aregger On

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);

Related Questions in ORACLE

Related Questions in JDBC

Related Questions in AGGREGATE-FUNCTIONS

Related Questions in OJDBC

Related Questions in USER-DEFINED-DATA-TYPES

Popular Questions

Popular Tags

javascript python java c# php android html jquery c++ css ios sql mysql r reactjs node.js arrays c asp.net json

Trending Questions