input binding with table of udt

[kumark92]

I'm trying to bind the input parameters of table of udt using OracleR2dbcTypes.arrayType("ADDRESS_TABLE") but getting error ORA-17059: Failed to convert internal representation: {CITY=Newyork}.
Does this support binding the table of udt

and I have this in Db
CREATE OR REPLACE TYPE ADDRESS_TYPE AS OBJECT (
CITY VARCHAR2(60)
);

CREATE OR REPLACE TYPE ADDRESS_TABLE AS TABLE OF ADDRESS_TYPE;

databaseClient.sql("SELECT * FROM TABLE(GET_Persons(:address_in))")
.bind("address_in", Parameters.in(OracleR2dbcTypes.arrayType("ADDRESS_TABLE"), new Object[]{Map.of("CITY", "Newyork")}))
.map((row,meta)) -> {
//process the result
}
.all()
.collectList();

[Michael-A-McMahon]

Thanks for creating this issue @kumark92 !

I think there's a defect in Oracle R2DBC here, because it's not checking for an ARRAY type'd bind that has OBJECT type'd elements. I'm working on a fix, and also seeing if I can find you a workaround for the interim.

I'm hoping to have an update on this in the next few days.

[kumark92]

Thanks for the response @Michael-A-McMahon . It will be great if it supports the nested udt array like array of udt referring inside the udt

[Michael-A-McMahon]

I appreciate your patience as I've had to prioritize other work over the last few weeks. I'm hoping to get a real fix in for this soon.

Since it will be some time before we release a fix, I'd like to share the following code which demonstrates a workaround. Essentially, the workaround is to bind a java.sql.Struct, rather than a Map:

import io.r2dbc.spi.Connection;
import io.r2dbc.spi.ConnectionFactories;
import io.r2dbc.spi.ConnectionFactory;
import io.r2dbc.spi.Parameters;
import oracle.r2dbc.OracleR2dbcTypes;
import reactor.core.publisher.Flux;
import reactor.core.publisher.Mono;

import java.util.stream.Stream;

public class UdtTest {

  public static void main(String[] args) throws Exception {

    ConnectionFactory connectionFactory =
      ConnectionFactories.get("r2dbc:oracle://test");
    Connection connection = Mono.from(connectionFactory.create()).block();

    try (
      AutoCloseable closeConnection =
        () -> Mono.from(connection.close()).block();

      AutoCloseable dropType =
        () -> Mono.from(connection.createStatement(
          "DROP TYPE ADDRESS_TYPE")
          .execute())
          .block();

      AutoCloseable dropTable =
        () -> Mono.from(connection.createStatement(
          "DROP TYPE ADDRESS_TABLE")
          .execute())
          .block();

      AutoCloseable dropFunction =
        () -> Mono.from(connection.createStatement(
          "DROP FUNCTION GET_Persons")
          .execute())
          .block();
      ) {

      Flux.from(connection.createBatch()
        .add(
          "CREATE OR REPLACE TYPE ADDRESS_TYPE AS OBJECT (CITY VARCHAR2(60))")
        .add(
          "CREATE OR REPLACE TYPE ADDRESS_TABLE AS TABLE OF ADDRESS_TYPE")
        .add(
          "CREATE OR REPLACE TYPE PERSON_TABLE AS TABLE OF VARCHAR(1000)")
        .add(
          "CREATE OR REPLACE FUNCTION GET_Persons(address_in ADDRESS_TABLE)"
            + " RETURN PERSON_TABLE"
            + " IS "
            + " persons PERSON_TABLE := PERSON_TABLE();"
            + " BEGIN"
            + " persons.EXTEND(2);"
            + " persons(1) := 'kumark92 likes ' || address_in(1).city;"
            + " persons(2) := 'Michael likes ' || address_in(2).city;"
            + " RETURN persons;"
            + " END;")
        .execute())
        .blockLast();

      // Can't bind a Map as an ADDRESS_TYPE OBJECT due to a Oracle R2DBC defect.
      // Get instances of java.sql.Struct as a workaround. A Struct can be bound
      // as an ADDRESS_TYPE OBJECT.
      OracleR2dbcTypes.ObjectType addressType =
        OracleR2dbcTypes.objectType("ADDRESS_TYPE");
      java.sql.Struct[] addressStructs =
        Stream.of("New York", "San Francisco")
            .map(cityName ->
              connection.createStatement(
                "BEGIN :address_out := ADDRESS_TYPE(:city_name); END;")
                .bind("address_out", Parameters.out(addressType))
                .bind("city_name", cityName)
                .execute())
          .flatMap(publisher ->
            Flux.from(publisher)
              .flatMap(result ->
                result.map(row ->
                  row.get("address_out", java.sql.Struct.class)))
              .toStream()) // <-- Blocks this thread
          .toArray(java.sql.Struct[]::new);


      OracleR2dbcTypes.ArrayType addressTableType =
        OracleR2dbcTypes.arrayType("ADDRESS_TABLE");
      Flux.from(connection.createStatement(
        "SELECT * FROM TABLE(GET_Persons(:address_in))")
        .bind(
          "address_in",
          Parameters.in(addressTableType, addressStructs))
        .execute())
        .flatMap(result ->
          result.map(row -> row.get(0)))
        .toStream()
        .forEach(System.out::println);
    }
  }

}

In terms of functionally, this works. However, it is not the best solution in terms of performance because we need the database to execute ADDRESS_TYPE constructor. That's a remote operation which introduces network latency, and consumes compute resources on the database host. It would be more optimal to just bind the Map as a local operation.

Some ideas on how to optimize this:

• We could have PL/SQL functions/procedures that accept a JSON type argument. We might bind a JSON array of a JSON objects. We can use an OracleJsonFactory to create our JSON array/object binds as a local operation.
• In this particular example, where ADDRESS_TYPE has just one VARCHAR field, we might think about having an ARRAY of VARCHAR instead of ADDRESS_TABLE. This way, we can bind an ARRAY of String objects as a local operation.