Unleashing the Power of Vector Data Types with Oracle R2DBC: Accelerating Generative AI Solutions with Reactive Streams and the Oracle AI Database

8 min readMar 5, 2025

Introduction

R2DBC stands for Reactive Relational Database Connectivity, an open specification for asynchronous, non-blocking communication with relational databases. It adopts a reactive programming model for interacting with databases, allowing developers to work with streams of data rather than traditional blocking calls, aligning with the Reactive Streams specification.

If you need an introduction to R2DBC, please check my previous blog post — Getting Started with Reactive Relational Database Connectivity and the Oracle R2DBC Driver

This blog post focuses on exploring vector data type support in the Oracle R2DBC driver, as it opens the door to creating Generative AI applications that can combine vector-based similarity searches as well as reactive streams. Please note that the 1.3.0 release of Oracle R2DBC implements version 1.0.0.RELEASE of the R2DBC SPI.

New features in this release (1.3.0):

So, without further ado, let’s get started!

Prerequisites

JDK — Java Development Kit
Oracle Database Free Release — Container Image
Oracle JDBC Driver
Your preferred Java IDE — Eclipse, IntelliJ, VS Code
Apache Maven

How to use the Vector data type with Oracle R2DBC 1.3.0

Here’s a practical, hands-on guide to integrating Oracle R2DBC 1.3.0 with VECTOR data type support into your Java applications. We’ll assume you’re familiar with Java, Maven, and reactive programming concepts (Project Reactor, RxJava, Vert.x, Akka, etc.).

Once again, if you need an introduction to R2DBC, please check my previous blog post — Getting Started with Reactive Relational Database Connectivity and the Oracle R2DBC Driver

Step 1: Set Up Your Maven Project

To use Oracle R2DBC 1.3.0, you’ll need to configure your Maven (pom.xml) file with the appropriate dependencies.

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.release>25</maven.compiler.release>
    <jdbc.version>23.26.2.0.0</jdbc.version>
  </properties>
  <dependencies>
    <dependency>
      <groupId>com.oracle.database.r2dbc</groupId>
      <artifactId>oracle-r2dbc</artifactId>
      <version>1.3.0</version>
    </dependency>
    <dependency>
      <groupId>io.projectreactor</groupId>
      <artifactId>reactor-core</artifactId>
      <version>3.8.6</version>
    </dependency>
    <dependency>
      <groupId>com.oracle.database.jdbc</groupId>
      <artifactId>ojdbc17</artifactId>
      <version>${jdbc.version}</version>
    </dependency>
  </dependencies>

Step 2: Configure Your Oracle Database Connection

Ensure you have an Oracle Database instance (check the section below), as it supports VECTOR data types. As usual, update your configuration properties file (src/main/resources/config.properties) with the JDBC connection details.

HOST=<HOSTNAME>
PORT=<ORACLE_DB_PORT>
DATABASE=<ORACLE_DATABASE_NAME>
USER=<ORACLE_DATABASE_USER>
PASSWORD=<ORACLE_DABASE_PASSWORD>
DRIVER=oracle
DB_TABLE_NAME=<DB_TABLE_NAME>

Step 3: Create a Table with a Vector Column

VECTOR data type with a database column that allows you to store multidimensional arrays directly in the database. Execute the following SQL DDL statement to create a table. A hand script is available as well.

CREATE TABLE VECTOR_USER.vector_data (
  id NUMBER(10) NOT NULL,
  embedding VECTOR(3, FLOAT32) NOT NULL,
  CONSTRAINT vector_data_pk PRIMARY KEY (id)
);

Step 4: Write Code with Oracle R2DBC to Insert and Query Vectors

Here’s our sample class on using Oracle R2DBC to insert and retrieve vector data. The code is relatively straightforward if you’re familiar with reactive programming and Project Reactor.

Please note that the Java method VECTOR.ofFloat32Values(java.lang.Object object) is required to obtain the proper vector data representation.

Below is the complete source code. You can also get a Maven project with a complete code sample on GitHub.

R2dbcVectorDemo.java

package com.oracle.dev.jdbc.r2dbc;

import java.sql.SQLException;
import java.util.Arrays;
import java.util.Locale;
import java.util.function.Consumer;

import io.r2dbc.spi.Connection;
import io.r2dbc.spi.ConnectionFactory;
import io.r2dbc.spi.Result;
import io.r2dbc.spi.Statement;
import oracle.sql.VECTOR;
import reactor.core.publisher.Flux;
import reactor.core.publisher.Mono;

public class R2dbcVectorDemo {

  private static final int DEMO_ID = 1001;
  private static final float[] CREATED_VECTOR = new float[] { 1.0f, 2.0f, 3.0f };
  private static final float[] UPDATED_VECTOR = new float[] { 3.0f, 2.0f, 1.0f };

  private final ConnectionFactory connectionFactory;
  private final String tableName;

  public static void main(String[] args) {
    new R2dbcVectorDemo().runCrudScenario().block();
  }

  public R2dbcVectorDemo() {
    this.connectionFactory = DatabaseConfig.getConnectionFactory();
    this.tableName = qualifiedTableName(DatabaseConfig.getUser(), DatabaseConfig.getDbTableName());
  }

  private Mono<Void> runCrudScenario() {
    return Flux.usingWhen(connectionFactory.create(), this::runCrudScenario, Connection::close).then();
  }

  private Mono<Void> runCrudScenario(Connection connection) {
    return Mono.from(connection.beginTransaction()).then(deleteVector(connection, DEMO_ID))
        .then(createVector(connection, new VectorRecord(DEMO_ID, CREATED_VECTOR))
            .doOnNext(rowsUpdated -> printRowsUpdated("Created", DEMO_ID, rowsUpdated)))
        .then(retrieveVector(connection, DEMO_ID).doOnNext(record -> System.out.println("Retrieved: " + record)))
        .then(updateVector(connection, new VectorRecord(DEMO_ID, UPDATED_VECTOR))
            .doOnNext(rowsUpdated -> printRowsUpdated("Updated", DEMO_ID, rowsUpdated)))
        .then(retrieveVector(connection, DEMO_ID)
            .doOnNext(record -> System.out.println("Retrieved after update: " + record)))
        .then(deleteVector(connection, DEMO_ID)
            .doOnNext(rowsUpdated -> printRowsUpdated("Deleted", DEMO_ID, rowsUpdated)))
        .then(retrieveVector(connection, DEMO_ID).map(record -> "Unexpected vector after delete: " + record)
            .defaultIfEmpty("Verified delete: no vector found for id " + DEMO_ID).doOnNext(System.out::println))
        .then(Mono.from(connection.commitTransaction()))
        .onErrorResume(error -> Mono.from(connection.rollbackTransaction()).onErrorResume(rollbackError -> Mono.empty())
            .then(Mono.error(error)));
  }

  private Mono<Long> createVector(Connection connection, VectorRecord vectorRecord) {
    String sql = "INSERT INTO " + tableName + " (id, embedding) VALUES (:id, :embedding)";

    return executeUpdate(connection, sql,
        statement -> statement.bind("id", vectorRecord.id()).bind("embedding", toVector(vectorRecord.embedding())));
  }

  private Mono<VectorRecord> retrieveVector(Connection connection, int id) {
    String sql = "SELECT id, embedding FROM " + tableName + " WHERE id = :id";

    return Flux.from(connection.createStatement(sql).bind("id", id).execute())
        .flatMap(result -> result.map(
            (row, metadata) -> new VectorRecord(row.get("id", Integer.class), row.get("embedding", float[].class))))
        .singleOrEmpty();
  }

  private Mono<Long> updateVector(Connection connection, VectorRecord vectorRecord) {
    String sql = "UPDATE " + tableName + " SET embedding = :embedding WHERE id = :id";

    return executeUpdate(connection, sql,
        statement -> statement.bind("embedding", toVector(vectorRecord.embedding())).bind("id", vectorRecord.id()));
  }

  private Mono<Long> deleteVector(Connection connection, int id) {
    String sql = "DELETE FROM " + tableName + " WHERE id = :id";

    return executeUpdate(connection, sql, statement -> statement.bind("id", id));
  }

  private Mono<Long> executeUpdate(Connection connection, String sql, Consumer<Statement> bindParameters) {
    Statement statement = connection.createStatement(sql);
    bindParameters.accept(statement);

    return Flux.from(statement.execute()).flatMap(Result::getRowsUpdated).reduce(0L, Long::sum);
  }

  private static VECTOR toVector(float[] values) {
    try {
      return VECTOR.ofFloat32Values(values);
    } catch (SQLException sqlException) {
      throw new IllegalArgumentException("Unable to convert values to an Oracle FLOAT32 VECTOR", sqlException);
    }
  }

  private static String qualifiedTableName(String schema, String table) {
    return requireOracleIdentifier(schema, "schema") + "." + requireOracleIdentifier(table, "table");
  }

  private static String requireOracleIdentifier(String value, String label) {
    if (value == null || !value.matches("[A-Za-z][A-Za-z0-9_$#]*")) {
      throw new IllegalStateException("Invalid Oracle " + label + " identifier: " + value);
    }

    return value.toUpperCase(Locale.ROOT);
  }

  private static void printRowsUpdated(String action, int id, long rowsUpdated) {
    System.out.printf("%s vector id %d (%d row%s updated)%n", action, id, rowsUpdated, rowsUpdated == 1 ? "" : "s");
  }

  private record VectorRecord(int id, float[] embedding) {

    @Override
    public String toString() {
      return "ID: " + id + ", Vector: " + Arrays.toString(embedding);
    }
  }
}

DatabaseConfig.java

package com.oracle.dev.jdbc.r2dbc;

import java.io.IOException;
import java.io.InputStream;
import java.nio.CharBuffer;
import java.nio.file.Files;
import java.nio.file.Path;
import java.util.Arrays;
import java.util.Objects;
import java.util.Properties;

import io.r2dbc.spi.ConnectionFactories;
import io.r2dbc.spi.ConnectionFactory;
import io.r2dbc.spi.ConnectionFactoryOptions;

public class DatabaseConfig {

  private static final Properties CONFIG = new Properties();

  static {
    try (InputStream fileStream = openConfig()) {
      CONFIG.load(fileStream);
    } catch (IOException e) {
      throw new ExceptionInInitializerError(e);
    }
  }

  private static InputStream openConfig() throws IOException {
    InputStream classpathConfig = DatabaseConfig.class.getClassLoader()
        .getResourceAsStream("config.properties");

    if (classpathConfig != null) {
      return classpathConfig;
    }

    Path sourceConfig = Path.of("src/main/resources/config.properties");
    return Files.newInputStream(sourceConfig);
  }

  private static final String DRIVER = CONFIG.getProperty("DRIVER");

  private static final String USER = CONFIG.getProperty("USER");

  private static final String PASSWORD = CONFIG.getProperty("PASSWORD");

  private static final String HOST = CONFIG.getProperty("HOST");

  private static final String PORT = CONFIG.getProperty("PORT");

  private static final String DATABASE = CONFIG.getProperty("DATABASE");

  private static final String DB_TABLE_NAME = CONFIG.getProperty("DB_TABLE_NAME");

  public static ConnectionFactory getConnectionFactory() {

    char[] password = new String(DatabaseConfig.getPassword()).toCharArray();
    try {
      ConnectionFactoryOptions options = ConnectionFactoryOptions.builder()
          .option(ConnectionFactoryOptions.DRIVER, DatabaseConfig.getDriver())
          .option(ConnectionFactoryOptions.USER, DatabaseConfig.getUser())
          .option(ConnectionFactoryOptions.PASSWORD, CharBuffer.wrap(password))
          .option(ConnectionFactoryOptions.HOST, DatabaseConfig.getHost())
          .option(ConnectionFactoryOptions.PORT, DatabaseConfig.getPort())
          .option(ConnectionFactoryOptions.DATABASE, DatabaseConfig.getDatabase()).build();
      return ConnectionFactories.get(options);

    } finally {
      Arrays.fill(password, (char) 0);
    }
  }

  public static String getDbTableName() {
    return Objects.requireNonNull(DB_TABLE_NAME, "DB_TABLE_NAME must be configured");
  }

  public static String getDriver() {
    return Objects.requireNonNull(DRIVER, "DRIVER must be configured");
  }

  public static String getUser() {
    return Objects.requireNonNull(USER, "USER must be configured");
  }

  public static String getPassword() {
    return Objects.requireNonNull(PASSWORD, "PASSWORD must be configured");
  }

  public static String getHost() {
    return Objects.requireNonNull(HOST, "HOST must be configured");
  }

  public static int getPort() {
    return Integer.parseInt(Objects.requireNonNull(PORT, "PORT must be configured"));
  }

  public static String getDatabase() {
    return Objects.requireNonNull(DATABASE, "DATABASE must be configured");
  }

}

Step 6: Test the sample app

Now, you can run and test the application using your preferred IDE. You may want to set breakpoints to query the database and check the records. You can also use Maven from the CLI if you prefer, as shown below:

mvn exec:java

You will see that the vector data records were stored, retrieved, updated, and deleted correctly, as shown below.

Easy, isn’t it? Working with the VECTOR data type and the Oracle R2DBC driver makes it extremely easy to add Oracle AI Vector Search to your Reactive Streams applications!

Oracle Database

If you want to test everything, start a database with a lightweight Docker container. The summarized steps are provided below. First, run the docker pull command below (for Windows):

docker pull container-registry.oracle.com/database/free:latest

Now, you can run the docker images command below to confirm that it was pulled properly:

Press enter or click to view image in full size

Oracle Database — container image

Then, run the command below from the Windows Command Prompt (cmd):

docker run -d -p 1521:1521 -e ORACLE_PWD=<your_password> -v oracle-volume:/opt/oracle/oradata container-registry.oracle.com/database/free:latest

Replace <your password> above with your chosen password as required. The command above will run a new persistent database container (data is kept throughout container lifecycles).

If everything goes well, you can run the command docker ps -al to confirm that your container instance is starting as expected.

Please note that the Oracle Database will be ready to use when the STATUS field shows (healthy), as below.

Press enter or click to view image in full size

Container status — healthy

There are some additional options if you want to configure environment variables to support your connection details, such as username, password, and other variables. You can also check more details about the specific database user (SYS, SYSTEM, PDBADMIN) you may want to use.

Wrapping it up

You learned about the Oracle R2DBC Driver, released as an open-source component by Oracle. We explored using the VECTOR data type support in the Oracle R2DBC driver, as it opens the door to creating Generative AI applications that can combine vector-based similarity searches with many Reactive Streams frameworks in Java!

As one last tip, I invite you to check out the Database Navigator tool published at JetBrains Marketplace. In a nutshell, it features VECTOR support, MCP tools, a robust SQL and PL/SQL editor with advanced capabilities for managing database connections, executing scripts, editing database objects, and modifying data and code with IntelliJ!

I will soon post more examples of scenarios involving LangChain4J, the OracleEmbeddingStore, GraalVM, Micronaut, Quarkus, and other GenAI and Agentic AI-related topics!

That’s it! I hope you liked this blog post, so stay tuned!

References

Oracle Developers and Oracle OCI Free Tier

Join our Oracle Developers channel on Slack to discuss Java, GenAI, Agentic AI, JDK, JDBC, GraalVM, Micronaut, Spring Boot, Helidon, Quarkus, Reactive Streams, Cloud, DevOps, SRE, IaC, and other topics!

Build, test, and deploy your applications on Oracle Cloud — for free! Get access to OCI Cloud Free Tier!

Oracle Developers