Oracle Collections implementation

[blafond]

Motivation:

Potential fix and test for #1392 : Support for Oracle Collections

ORM's recent support for Oracle arrays has added a custom array method to wrap an OracleConnection.

Hibernate Reactive uses vertx-sql-client to execute queries and does not use actual db client connections. This PR proposes a change in the vertx-sql-client to add a similar change to your OracleConnection interface and implementation to provide Hibernate Reactive access to the correct oracle array info/structure.

Commits include the proposed changes to the Oracle sql client and a test that currently fails

Caused by: java.sql.SQLException: ORA-17074: Invalid name pattern: StringArrayType
https://docs.oracle.com/error-help/db/ora-17074/
    at oracle.jdbc.oracore.OracleTypeADT.initMetadata12(OracleTypeADT.java:636)
    at oracle.jdbc.oracore.OracleTypeADT.initMetadata(OracleTypeADT.java:571)
    at oracle.jdbc.oracore.OracleTypeADT.init(OracleTypeADT.java:500)
    at oracle.sql.ArrayDescriptor.initPickler(ArrayDescriptor.java:1722)
    at oracle.sql.ArrayDescriptor.<init>(ArrayDescriptor.java:381)
    at oracle.sql.ArrayDescriptor.<init>(ArrayDescriptor.java:357)
    at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:217)
    at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:102)
    at oracle.jdbc.driver.PhysicalConnection.createARRAY(PhysicalConnection.java:7755)
    at io.vertx.oracleclient.impl.OracleJdbcConnection.createArray(OracleJdbcConnection.java:62)
    ... 30 more

[tsegismont]

Thank you @blafond , I'll come back to you asap

[tsegismont]

@blafond the test passes with a couple of changes:

Index: vertx-oracle-client/src/test/java/io/vertx/oracleclient/test/OracleArrayTest.java
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/vertx-oracle-client/src/test/java/io/vertx/oracleclient/test/OracleArrayTest.java b/vertx-oracle-client/src/test/java/io/vertx/oracleclient/test/OracleArrayTest.java
--- a/vertx-oracle-client/src/test/java/io/vertx/oracleclient/test/OracleArrayTest.java (revision 50ccabcc771b6c68ba27cda9d3d746efca1ecd42)
+++ b/vertx-oracle-client/src/test/java/io/vertx/oracleclient/test/OracleArrayTest.java (date 1710258134775)
@@ -37,8 +37,8 @@
   public void testStringArray(TestContext ctx) {
     String[] elements = {"str1", "str2", "str3"};
     pool.withConnection(conn -> {
-      Object stringsArray = ((OracleConnection)conn).createArray( "StringArrayType", elements );
-      String insertSql = "INSERT INTO StringsArrayTable( id, stringsArray) VALUES (?, ?)";
+      Object stringsArray = ((OracleConnection)conn).createArray( "STRINGARRAYTYPE", elements );
+      String insertSql = "INSERT INTO StringsArrayTable( id, stringarrayelement) VALUES (?, ?)";
       return conn.preparedQuery( insertSql ).execute( Tuple.of(1, stringsArray) );
     }).onComplete( ctx.asyncAssertSuccess() );
   }

1. The array type name must be uppercase
2. The column name is stringarrayelement

[blafond]

@tsegismont I tried your changes above and it just changed the case of the error:

+      Object stringsArray = ((OracleConnection)conn).createArray( "STRINGARRAYTYPE", elements );
+      String insertSql = "INSERT INTO StringsArrayTable( id, stringarrayelement) VALUES (?, ?)";

java.lang.RuntimeException: java.sql.SQLException: ORA-17074: Invalid name pattern: STRINGARRAYTYPE
https://docs.oracle.com/error-help/db/ora-17074/

Did the test work for you?

[tsegismont]

Did the test work for you?

Yes it did

[tsegismont]

@blafond I could reproduce your problem but still not able to fix it. I'll keep you informed

[tsegismont]

So it doesn't work when the collection and dependent table are created in the init sql file. It does work if both are created with the Oracle client...

I failed to understand to why so I reported my problem.

I'll keep you informed

[DavideD]

Interesting, but I don't think in Hibernate Reactive we are creating then entries using the init sql.

@blafond switched to a new role, I'm going to have another look at this as soon as I have some time.

[tsegismont]

For the tests, in this PR, we do create the collection type and dependent table in the init sql file

[DavideD]

For the tests, in this PR, we do create the collection type and dependent table in the init sql file

Yes, but it means that it should work in HR. I need to retest it there.