Support for Oracle collections

[DavideD]

This is actually a question.

In order to support the mapping of fields of array type, Hibernate ORM run some queries to create a custom type. For example, for supporting integer arrays, it runs:

create or replace type IntegerArray as varying array(127) of number(10,0)

create or replace function IntegerArray_cmp(a in IntegerArray, b in IntegerArray) return number deterministic is begin if a is null or b is null then return null; end if; for i in 1 .. least(a.count,b.count) loop if a(i) is null or b(i) is null then return null;elsif a(i)>b(i) then return 1;elsif a(i)<b(i) then return -1; end if; end loop; if a.count=b.count then return 0; elsif a.count>b.count then return 1; else return -1; end if; end;
create or replace function IntegerArray_distinct(a in IntegerArray, b in IntegerArray) return number deterministic is begin if a is null and b is null then return 0; end if; if a is null or b is null or a.count <> b.count then return 1; end if; for i in 1 .. a.count loop if (a(i) is null)<>(b(i) is null) or a(i)<>b(i) then return 1; end if; end loop; return 0; end;
create or replace function IntegerArray_position(arr in IntegerArray, elem in number, startPos in number default 1) return number deterministic is begin if arr is null then return null; end if; if elem is null then for i in startPos .. arr.count loop if arr(i) is null then return i; end if; end loop; else for i in startPos .. arr.count loop if arr(i)=elem then return i; end if; end loop; end if; return 0; end;
create or replace function IntegerArray_length(arr in IntegerArray) return number deterministic is begin if arr is null then return null; end if; return arr.count; end;
create or replace function IntegerArray_concat(arr0 in IntegerArray,arr1 in IntegerArray,arr2 in IntegerArray default IntegerArray(),arr3 in IntegerArray default IntegerArray(),arr4 in IntegerArray default IntegerArray()) return IntegerArray deterministic is res IntegerArray; begin if arr0 is null or arr1 is null or arr2 is null or arr3 is null or arr4 is null then return null; end if; select * bulk collect into res from (select * from table(arr0) union all select * from table(arr1) union all select * from table(arr2) union all select * from table(arr3) union all select * from table(arr4)); return res; end;
create or replace function IntegerArray_contains(haystack in IntegerArray, needle in IntegerArray, nullable in number) return number deterministic is found number(1,0); begin if haystack is null or needle is null then return null; end if; for i in 1 .. needle.count loop found := 0; for j in 1 .. haystack.count loop if nullable = 1 and needle(i) is null and haystack(j) is null or needle(i)=haystack(j) then found := 1; exit; end if; end loop; if found = 0 then return 0; end if;end loop; return 1; end;
create or replace function IntegerArray_overlaps(haystack in IntegerArray, needle in IntegerArray, nullable in number) return number deterministic is begin if haystack is null or needle is null then return null; end if; if needle.count = 0 then return 1; end if; for i in 1 .. needle.count loop for j in 1 .. haystack.count loop if nullable = 1 and needle(i) is null and haystack(j) is null or needle(i)=haystack(j) then return 1; end if; end loop; end loop; return 0; end;
create or replace function IntegerArray_get(arr in IntegerArray, idx in number) return number deterministic is begin if arr is null or idx is null or arr.count < idx then return null; end if; return arr(idx); end;
create or replace function IntegerArray_set(arr in IntegerArray, idx in number, elem in number) return IntegerArray deterministic is res IntegerArray:=IntegerArray(); begin if arr is not null then for i in 1 .. arr.count loop res.extend; res(i) := arr(i); end loop; for i in arr.count+1 .. idx loop res.extend; end loop; else for i in 1 .. idx loop res.extend; end loop; end if; res(idx) := elem; return res; end;
create or replace function IntegerArray_remove(arr in IntegerArray, elem in number) return IntegerArray deterministic is res IntegerArray:=IntegerArray(); begin if arr is null then return null; end if; if elem is null then for i in 1 .. arr.count loop if arr(i) is not null then res.extend; res(res.last) := arr(i); end if; end loop; else for i in 1 .. arr.count loop if arr(i) is null or arr(i)<>elem then res.extend; res(res.last) := arr(i); end if; end loop; end if; return res; end;
create or replace function IntegerArray_remove_index(arr in IntegerArray, idx in number) return IntegerArray deterministic is res IntegerArray:=IntegerArray(); begin if arr is null or idx is null then return arr; end if; for i in 1 .. arr.count loop if i<>idx then res.extend; res(res.last) := arr(i); end if; end loop; return res; end;
create or replace function IntegerArray_slice(arr in IntegerArray, startIdx in number, endIdx in number) return IntegerArray deterministic is res IntegerArray:=IntegerArray(); begin if arr is null or startIdx is null or endIdx is null then return null; end if; for i in startIdx .. least(arr.count,endIdx) loop res.extend; res(res.last) := arr(i); end loop; return res; end;
create or replace function IntegerArray_replace(arr in IntegerArray, old in number, elem in number) return IntegerArray deterministic is res IntegerArray:=IntegerArray(); begin if arr is null then return null; end if; if old is null then for i in 1 .. arr.count loop res.extend; res(res.last) := coalesce(arr(i),elem); end loop; else for i in 1 .. arr.count loop res.extend; if arr(i) = old then res(res.last) := elem; else res(res.last) := arr(i); end if; end loop; end if; return res; end;
create or replace function IntegerArray_trim(arr in IntegerArray, elems number) return IntegerArray deterministic is res IntegerArray:=IntegerArray(); begin if arr is null or elems is null then return null; end if; if arr.count < elems then raise_application_error (-20000, 'number of elements to trim must be between 0 and '||arr.count); end if;for i in 1 .. arr.count-elems loop res.extend; res(i) := arr(i); end loop; return res; end;
create or replace function IntegerArray_fill(elem in number, elems number) return IntegerArray deterministic is res IntegerArray:=IntegerArray(); begin if elems is null then return null; end if; if elems<0 then raise_application_error (-20000, 'number of elements must be greater than or equal to 0'); end if;for i in 1 .. elems loop res.extend; res(i) := elem; end loop; return res; end;
create or replace function IntegerArray_positions(arr in IntegerArray, elem in number) return sdo_ordinate_array deterministic is res sdo_ordinate_array:=sdo_ordinate_array(); begin if arr is null then return null; end if; if elem is null then for i in 1 .. arr.count loop if arr(i) is null then res.extend; res(res.last):=i; end if; end loop; else for i in 1 .. arr.count loop if arr(i)=elem then res.extend; res(res.last):=i; end if; end loop; end if; return res; end;
create or replace function IntegerArray_to_string(arr in IntegerArray, sep in varchar2) return varchar2 deterministic is res varchar2(4000):=''; begin if arr is null or sep is null then return null; end if; for i in 1 .. arr.count loop if arr(i) is not null then if length(res)<>0 then res:=res||sep; end if; res:=res||arr(i); end if; end loop; return res; end;

With the Oracle Vert.x SQL client, I can do the same and I can create the table with the new defined type.

The problem arise when I try to insert an array in the table. I have the exception:

java.sql.SQLException: ORA-17004: Invalid column type

In the JDBC driver, there are dedicated methods for the creation of a new array and it's possible to specify the name of the custom type when setting the value in the prepared statement.

Is it possible to do something similar with the Vert.x client?

[tsegismont]

Thanks for bringing this up @DavideD

It's not possible currently unfortunately

[tsegismont]

@DavideD I've read about Oracle collections in https://docs.oracle.com/en/database/oracle/oracle-database/21/jjdbc/Oracle-collections.html#GUID-338038DB-AE3A-4C82-BB8F-8C87F2D130C4

It seems an OracleArray object needs to be created. How is this implemented with Hibernates ORM?

[DavideD]

The JDBC driver takes care of it.
You can check the OracleArrayJdbcType class in Hibernate ORM.

The class in the JDBC driver is called oracle.jdbc.OracleArray.
There's also a method in java.sql.Connection for the creation of arrays:

Array createArrayOf(String typeName, Object[] elements) throws
SQLException;

At first glance, I'd say the Vert.x client needs something similar.

[tsegismont]

The JDBC driver takes care of it.
You can check the OracleArrayJdbcType class in Hibernate ORM.

Thanks for the pointer. I understand something must create an OracleArray instance, and from what I see Hibernate ORM does it in OracleArrayJdbcType. Shouldn't Hibernate Reactive do the same ?

[DavideD]

I'm not sure I understand, OracleArray is an interface in the JDBC driver. The Vert.x SQL client doesn't have a similar interface (or does it?).

We create an ArrayAdaptor that works for all the other databases, though

[DavideD]

Anyway, maybe you can show me an example that only use Vert.x and I can adapt it to Hibernate Reactive.

[tsegismont]

I'm not sure I understand, OracleArray is an interface in the JDBC driver. The Vert.x SQL client doesn't have a similar interface (or does it?).

The Vert.x Reactive Oracle, unlike other Vert.x Reactive SQL clients, is based on the Oracle JDBC driver and its Reactive extensions (the Oracle DB protocol is not open so we can't create our own codec).

So when you execute a preparedQuery with the Vert.x API, the client creates a JDBC PreparedStatement and executes it with Oracle Reactive extensions.

So yes, I think in Hibernate Reactive you need to create an OracleArray instance like in Hibernate ORM and submit it in the Tuple

[DavideD]

The Vert.x Reactive Oracle, unlike other Vert.x Reactive SQL clients, is based on the Oracle JDBC driver

So it shouldn't be hard to delegate the creation of the array to the underlying driver by calling the createOracleArray method. Why should a user duplicate the logic that already exists in the driver? And how a user would be aware of this?
I don't think this is a Hibernate Reactive issue only.

Hibernate Reactive you need to create an OracleArray instance like in Hibernate ORM

Hibernate ORM doesn't create an instance of OracleArray, it's the JDBC driver that creates it. Hibernate ORM just receive an object of type java.sql.Array. The driver just so happen to know which one is the correct implementation to use.

You can see this by checking the signature of the method oracle.jdbc.OracleConnection#createOracleArray:

Array createOracleArray(String var1, Object var2) throws SQLException;

[tsegismont]

I understand Hibernate ORM doesn't create an instance of OracleArray itself, but it asks the driver to do so in OracleArrayJdbcType with all the knowledge required (array type name).

This, is what I think should be done in Hibernate Reactive too. And then the Tuple submitted to the Vert.x Client may contain the the sql array instance.