Java Database Connectivity (JDBC)

On the server side, I'm using the latest 23c free version from here: https://hub.docker.com/r/gvenzl/oracle-free

With this example package:

CREATE PACKAGE p AS 
  TYPE t IS RECORD (a NUMBER); 
  TYPE tt IS TABLE OF t; 
  TYPE ttt IS TABLE OF tt; 
  FUNCTION f RETURN ttt; 
END p; 

CREATE PACKAGE BODY p AS 
  FUNCTION f RETURN ttt IS 
  BEGIN 
    RETURN ttt(tt(t(1))); 
  END f; 
END p; 

It used to be possible using com.oracle.database.jdbc:ojdbc11:21.9.0.0 to read function results like this:

try (CallableStatement c = connection.prepareCall("{ ? = call test.p.f }")) { 
  c.registerOutParameter(1, OracleTypes.ARRAY, "TEST.P.TTT"); 
  c.executeUpdate(); 
  Array a1 = c.getArray(1); 
  Object[] o1 = (Object[]) a1.getArray(); 
  Array a2 = (Array) o1[0]; 
  Object[] o2 = (Object[]) a2.getArray(); 
  Struct a3 = (Struct) o2[0]; 
  Object[] o3 = a3.getAttributes(); 
  System.out.println(Arrays.asList(o3)); 
}

This would then print:

[1]

But with ojdbc versions 23.2.0.0 or 23.3.0.23.09, this no longer works. The following exception is thrown instead, from within the driver:

Exception in thread "main" java.sql.SQLException: ORA-01403: no data found
ORA-06512: at line 1

    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:702)
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:608)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1335)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:1041)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:443)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:533)
    at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:216)
    at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1346)
    at oracle.jdbc.driver.OracleStatement.executeSQLStatement(OracleStatement.java:1877)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1520)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3751)
    at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:4180)
    at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4203)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1015)
    at oracle.jdbc.oracore.OracleTypeCOLLECTION.initCollElemTypeName(OracleTypeCOLLECTION.java:1003)
    at oracle.jdbc.oracore.OracleTypeCOLLECTION.getAttributeType(OracleTypeCOLLECTION.java:1067)
    at oracle.jdbc.oracore.OracleTypeADT.createStructDescriptor(OracleTypeADT.java:3288)
    at oracle.jdbc.oracore.OracleTypeADT.unpickle81(OracleTypeADT.java:2244)
    at oracle.jdbc.oracore.OracleTypeUPT.unpickle81UPT(OracleTypeUPT.java:295)
    at oracle.jdbc.oracore.OracleTypeUPT.unpickle81rec(OracleTypeUPT.java:252)
    at oracle.jdbc.oracore.OracleTypeCOLLECTION.unpickle81ImgBodyElements(OracleTypeCOLLECTION.java:643)
    at oracle.jdbc.oracore.OracleTypeCOLLECTION.unpickle81ImgBody(OracleTypeCOLLECTION.java:562)
    at oracle.jdbc.oracore.OracleTypeCOLLECTION.unpickle81(OracleTypeCOLLECTION.java:378)
    at oracle.jdbc.oracore.OracleTypeCOLLECTION.unlinearizeInternal(OracleTypeCOLLECTION.java:244)
    at oracle.jdbc.oracore.OracleTypeCOLLECTION.unlinearize(OracleTypeCOLLECTION.java:223)
    at oracle.sql.ArrayDescriptor.toJavaArray(ArrayDescriptor.java:932)
    at oracle.jdbc.driver.OracleArray.getArray(OracleArray.java:246)
    at oracle.sql.ARRAY.getArray(ARRAY.java:363)
    at org.jooq.testscripts.JDBC.main(JDBC.java:47)
Caused by: Error : 1403, Position : 0, SQL = DECLARE   the_owner VARCHAR2(100);   the_type  VARCHAR2(100); BEGIN  SELECT TABLE_NAME, TABLE_OWNER INTO THE_TYPE, THE_OWNER  FROM ALL_SYNONYMS  WHERE TABLE_NAME IN (SELECT TYPE_NAME FROM ALL_TYPES)  START WITH SYNONYM_NAME = :1 AND OWNER = 'PUBLIC'  CONNECT BY NOCYCLE PRIOR TABLE_NAME = SYNONYM_NAME AND TABLE_OWNER = OWNER; OPEN :2 FOR SELECT ELEM_TYPE_NAME, ELEM_TYPE_OWNER FROM ALL_COLL_TYPES  WHERE TYPE_NAME = THE_TYPE and OWNER = THE_OWNER; END;, Original SQL = DECLARE   the_owner VARCHAR2(100);   the_type  VARCHAR2(100); BEGIN  SELECT TABLE_NAME, TABLE_OWNER INTO THE_TYPE, THE_OWNER  FROM ALL_SYNONYMS  WHERE TABLE_NAME IN (SELECT TYPE_NAME FROM ALL_TYPES)  START WITH SYNONYM_NAME = :1 AND OWNER = 'PUBLIC'  CONNECT BY NOCYCLE PRIOR TABLE_NAME = SYNONYM_NAME AND TABLE_OWNER = OWNER; OPEN :2 FOR SELECT ELEM_TYPE_NAME, ELEM_TYPE_OWNER FROM ALL_COLL_TYPES  WHERE TYPE_NAME = THE_TYPE and OWNER = THE_OWNER; END;, Error Message = ORA-01403: no data found
ORA-06512: at line 1

    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:710)
    ... 28 more

Here's a formatted version of that PL/SQL block:

DECLARE 
  the_owner VARCHAR2(100); 
  the_type VARCHAR2(100); 
BEGIN 
  SELECT TABLE_NAME, TABLE_OWNER 
  INTO THE_TYPE, THE_OWNER 
  FROM ALL_SYNONYMS 
  WHERE TABLE_NAME IN ( 
    SELECT TYPE_NAME FROM ALL_TYPES 
  ) 
  START WITH SYNONYM_NAME = :1 
  AND OWNER = 'PUBLIC' 
  CONNECT BY NOCYCLE 
  PRIOR TABLE_NAME = SYNONYM_NAME 
  AND TABLE_OWNER = OWNER; 

  OPEN :2 FOR 
  SELECT ELEM_TYPE_NAME, ELEM_TYPE_OWNER 
  FROM ALL_COLL_TYPES 
  WHERE TYPE_NAME = THE_TYPE 
  and OWNER = THE_OWNER; 
END;

With a debugger, I found that the bind variable :1 has value P.TT (weird), so I reverse engineered the following hack to make the query above no longer fail:

CREATE TYPE "PUBLIC.X" AS OBJECT(o INT);     -- To satisfy the IN (SELECT TYPE_NAME ..) subquery
CREATE PUBLIC SYNONYM "P.TT" FOR "PUBLIC.X"; -- To satisfy the recursion

With these dummy objects in place, the latest ojdbc versions no longer fail, and the above Java program again prints:

[1]