oracle/oracle-r2dbc oracle/oracle-r2dbc

  • Stars
    star
    183
  • Rank 210,154 (Top 5 %)
  • Language
    Java
  • License
    Other
  • Created over 3 years ago
  • Updated about 1 year ago
Twitter logo Share LinkedIn logo Share Facebook logo Share
oracle/oracle-r2dbc
github

oracle

Reviews

There are no reviews yet. Be the first to send feedback to the community and the maintainers!

Repository Details

R2DBC Driver for Oracle Database

About Oracle R2DBC

The Oracle R2DBC Driver is a Java library that supports reactive programming with Oracle Database.

Oracle R2DBC implements the R2DBC Service Provider Interface (SPI) as specified by the Reactive Relational Database Connectivity (R2DBC) project. The R2DBC SPI exposes Reactive Streams as an abstraction for remote database operations. Reactive Streams is a well defined standard for asynchronous, non-blocking, and back-pressured communication. This standard allows an R2DBC driver to interoperate with other reactive libraries and frameworks, such as Spring, Project Reactor, RxJava, and Akka Streams.

Learn More About R2DBC:

R2DBC Project Home Page

R2DBC Javadocs v1.0.0.RELEASE

R2DBC Specification v1.0.0.RELEASE

Learn More About Reactive Streams:

Reactive Streams Project Home Page

Reactive Streams Javadocs v1.0.3

Reactive Streams Specification v1.0.3

About This Version

The 1.1.0 release Oracle R2DBC implements version 1.0.0.RELEASE of the R2DBC SPI.

Fixes in this release:

New features in this release:

Integration with Spring and Other Libraries

Oracle R2DBC only interoperates with libraries that support the 1.0.0.RELEASE version of the R2DBC SPI. When using libraries like Spring and r2dbc-pool, be sure to use a version which supports the 1.0.0.RELEASE of the SPI.

Oracle R2DBC depends on the JDK 11 build of Oracle JDBC 21.7.0.0. Other libraries may depend on a different version of Oracle JDBC which is incompatible. To resolve this incompatibility, it may be necessary to explicitly declare the dependency in your project, ie:

<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc11</artifactId>
    <version>21.7.0.0</version>
</dependency>

Installation

Oracle R2DBC can be obtained from Maven Central.

<dependency>
  <groupId>com.oracle.database.r2dbc</groupId>
  <artifactId>oracle-r2dbc</artifactId>
  <version>1.0.0</version>
</dependency>

Oracle R2DBC can also be built from source using Maven: mvn clean install -DskipTests=true

If -DskipTests=true is omitted from the command above, then it will execute end-to-end tests which connect to an Oracle Database. Tests read the connection configuration from src/test/resources/config.properties.

Oracle R2DBC is compatible with JDK 11 (or newer), and has the following runtime dependencies:

  • R2DBC SPI 1.0.0.RELEASE
  • Reactive Streams 1.0.3
  • Project Reactor 3.4.18
  • Oracle JDBC 21.7.0.0 for JDK 11 (ojdbc11.jar)

The Oracle R2DBC Driver has been verified with Oracle Database versions 18, 19, and 21.

Code Examples

The following method returns an Oracle R2DBC ConnectionFactory

  static ConnectionFactory getConnectionFactory() {
    String user = getUser();
    char[] password = getPassword();
    try {
      return ConnectionFactories.get(
        ConnectionFactoryOptions.builder()
          .option(ConnectionFactoryOptions.DRIVER, "oracle")
          .option(ConnectionFactoryOptions.HOST, "db.host.example.com")
          .option(ConnectionFactoryOptions.PORT, 1521)
          .option(ConnectionFactoryOptions.DATABASE, "db.service.name")
          .option(ConnectionFactoryOptions.USER, user)
          .option(ConnectionFactoryOptions.PASSWORD, CharBuffer.wrap(password))
          .build());
    }
    finally {
      Arrays.fill(password, (char)0);
    }
  }

The following method uses Project Reactor's Flux to open a connection, execute a SQL query, and then close the connection:

Flux.usingWhen(
  getConnectionFactory().create(),
  connection ->
    Flux.from(connection.createStatement(
      "SELECT 'Hello, Oracle' FROM sys.dual")
      .execute())
      .flatMap(result ->
        result.map(row -> row.get(0, String.class))),
  Connection::close)
  .doOnNext(System.out::println)
  .doOnError(Throwable::printStackTrace)
  .subscribe();

When executed, the code above will asynchronously print the result of the SQL query.

The next example uses a named parameter marker, :locale_name, in the SQL command:

Flux.usingWhen(
  getConnectionFactory().create(),
  connection ->
    Flux.from(connection.createStatement(
      "SELECT greeting FROM locale WHERE locale_name = :locale_name")
      .bind("locale_name", "France")
      .execute())
      .flatMap(result ->
        result.map(row ->
          String.format("%s, Oracle", row.get("greeting", String.class)))),
  Connection::close)
  .doOnNext(System.out::println)
  .doOnError(Throwable::printStackTrace)
  .subscribe();

Like the previous example, executing the code above will asynchronously print a greeting message. "France" is set as the bind value for locale_name, so the query should return a greeting like "Bonjour" when row.get("greeting") is called.

Additional code examples can be found here.

Help

For help programming with Oracle R2DBC, ask questions on Stack Overflow tagged with [oracle] and [r2dbc]. The development team monitors Stack Overflow regularly.

Issues may be opened as described in our contribution guide.

Contributing

This project welcomes contributions from the community. Before submitting a pull request, please review our contribution guide.

Security

Please consult the security guide for our responsible security vulnerability disclosure process.

License

Copyright (c) 2021, 2023 Oracle and/or its affiliates.

This software is dual-licensed to you under the Universal Permissive License (UPL) 1.0 as shown at https://oss.oracle.com/licenses/upl or Apache License 2.0 as shown at http://www.apache.org/licenses/LICENSE-2.0. You may choose either license.

Documentation

This document specifies the behavior of the R2DBC SPI as implemented for the Oracle Database. This SPI implementation is referred to as the "Oracle R2DBC Driver" or "Oracle R2DBC" throughout the remainder of this document.

The Oracle R2DBC Driver implements behavior specified by the R2DBC 1.0.0.RELEASE Specification and Javadoc

Publisher objects created by Oracle R2DBC implement behavior specified by the Reactive Streams 1.0.3 Specification and Javadoc

The R2DBC and Reactive Streams specifications include requirements that are optional for a compliant implementation. The remainder of this document specifies the Oracle R2DBC Driver's implementation of these optional requirements.

Connection Creation

The Oracle R2DBC Driver is identified by the name "oracle". The driver implements a ConnectionFactoryProvider located by an R2DBC URL identifing "oracle" as a driver, or by a DRIVER ConnectionFactoryOption with the value of "oracle".

Support for Standard R2DBC Options

The following standard ConnectionFactoryOptions are supported by Oracle R2DBC:

  • DRIVER
  • HOST
  • PORT
  • DATABASE
    • The database option is interpreted as the service name of an Oracle Database instance. System Identifiers (SID) are not recognized.
  • USER
  • PASSWORD
  • SSL
  • CONNECT_TIMEOUT
  • STATEMENT_TIMEOUT.
  • PROTOCOL
    • Accepted protocol values are "tcps", "ldap", and "ldaps"

Support for Extended R2DBC Options

Oracle R2DBC extends the standard set of R2DBC options to offer functionality that is specific to Oracle Database and the Oracle JDBC Driver. Extended options are declared in the OracleR2dbcOptions class.

Configuring an Oracle Net Descriptor

The oracle.r2dbc.OracleR2dbcOptions.DESCRIPTOR option may be used to configure an Oracle Net Descriptor of the form (DESCRIPTION=...). If this option is used to configure a descriptor, then it is invalid to specify any other option that conflicts with information in the descriptor. Conflicting options include HOST, PORT, DATABASE, and SSL. These options all conflict with information that appears in a descriptor.

The DESCRIPTOR option has the name oracle.r2dbc.descriptor. This name can be used to configure a descriptor in the query section of an R2DBC URL:

r2dbc:oracle://?oracle.r2dbc.descriptor=(DESCRIPTION=...)

The DESCRIPTOR constant may also be used to configure a descriptor programmatically:

ConnectionFactoryOptions.builder()
  .option(OracleR2dbcOptions.DESCRIPTOR, "(DESCRIPTION=...)")

The DESCRIPTOR option may be set to an aliased entry of a tnsnames.ora file. Use the TNS_ADMIN option to specify the directory where tnsnames.ora is located:

r2dbc:oracle://?oracle.r2dbc.descriptor=myAlias&TNS_ADMIN=/path/to/tnsnames/

Configuring an LDAP URL

Use ldap or ldaps as the URL protocol to have an Oracle Net Descriptor retrieved from an LDAP server:

r2dbc:oracle:ldap://ldap.example.com:7777/sales,cn=OracleContext,dc=com
r2dbc:oracle:ldaps://ldap.example.com:7778/sales,cn=OracleContext,dc=com

Use a space separated list of LDAP URIs for fail over and load balancing:

r2dbc:oracle:ldap://ldap1.example.com:7777/sales,cn=OracleContext,dc=com%20ldap://ldap2.example.com:7777/sales,cn=OracleContext,dc=com%20ldap://ldap3.example.com:7777/sales,cn=OracleContext,dc=com

Space characters in a URL must be percent encoded as %20

An LDAP server request will block a thread for network I/O when Oracle R2DBC creates a new connection.

Configuring a java.util.concurrent.Executor

The oracle.r2dbc.OracleR2dbcOptions.EXECUTOR option configures a java.util.concurrent.Executor for executing asynchronous callbacks. The EXECUTOR option may be used to configure an Executor programmatically:

ConnectionFactoryOptions.builder()
  .option(OracleR2dbcOptions.EXECUTOR, getExecutor())

There is no way to configure an executor with a URL query parameter

If this option is not configured, then the common java.util.concurrent.ForkJoinPool is used as a default.

Configuring Oracle JDBC Connection Properties

A subset of Oracle JDBC's connection properties are also supported by Oracle R2DBC. These connection properties may be configured as options having the same name as the Oracle JDBC connection property, and may have CharSequence value types.

For example, the following URL configures the oracle.net.wallet_location connection property:

r2dbcs:oracle://db.host.example.com:1522/db.service.name?oracle.net.wallet_location=/path/to/wallet/

The same property can also be configured programmatically:

 ConnectionFactoryOptions.builder()
  .option(OracleR2dbcOptions.TLS_WALLET_LOCATION, "/path/to/wallet")

The following is a list of all Oracle JDBC connection properties that are supported by Oracle R2DBC:

Thread Safety and Parallel Execution

Oracle R2DBC's ConnectionFactory and ConnectionFactoryProvider are the only classes that have a thread safe implementation. All other classes implemented by Oracle R2DBC are not thread safe. For instance, it is not safe for multiple threads to concurrently access a single instance of Result.

It is recommended to use a Reactive Streams library such as Project Reactor or RxJava to manage the consumption of non-thread safe objects

Oracle Database does not allow multiple database calls to execute in parallel over a single Connection. If an attempt is made to execute a database call before a previous call has completed, then Oracle R2DBC will enqueue that call and only execute it after the previous call has completed.

To illustrate, the following code attempts to execute two statements in parallel:

Flux.merge(
  connection.createStatement(
    "INSERT INTO example (id, value) VALUES (0, 'x')")
    .execute(),
  connection.createStatement(
    "INSERT INTO example (id, value) VALUES (1, 'y')")
    .execute())

When the publisher of the second statement is subscribed to, Oracle R2DBC will enqueue a task for sending that statement to the database. The enqueued task will only be executed after the publisher of the first statement has completed.

Reactive Streams

Every method implemented by Oracle R2DBC that returns a Publisher has a JavaDoc which specifies the Publisher's behavior with regard to deferred execution and support for multiple Subscribers.

Oracle R2DBC's implementation of Publishers that emit one or zero items will typically defer execution until a Subscriber subscribes, support multiple Subscribers, and cache the result of a database call (the same result of the same call is emitted to each Subscriber).

Oracle R2DBC's implementation of Publishers that emit multiple items will typically defer execution until a Subscriber signals demand, and not support multiple subscribers.

Errors and Warnings

Oracle R2DBC creates R2dbcExceptions having the same ORA-XXXXX error codes used by Oracle Database and Oracle JDBC. The Database Error Messages document provides a reference for all ORA-XXXXX error codes.

Warning messages from Oracle Database are emitted as oracle.r2dbc.OracleR2dbcWarning segments. These segments may be consumed using Result.flatMap(Function):

result.flatMap(segment -> {
  if (segment instanceof OracleR2dbcWarning) {
    logWarning(((OracleR2dbcWarning)segment).getMessage());
    return emptyPublisher();
  }
  else if (segment instanceof Result.Message){
    ... handle an error ...
  }
  else {
    ... handle other segment types ...
  }
})

Unlike the errors of standard Result.Message segments, if a warning is not consumed by flatMap, then it will be silently discarded when a Result is consumed using the map or getRowsUpdated methods.

Transactions

Oracle R2DBC uses READ COMMITTED as the default transaction isolation level.

Oracle R2DBC also supports the SERIALIZABLE isolation level. If SERIALIZABLE isolation is configured, then the oracle.r2dbc.OracleR2dbcOptions.ENABLE_QUERY_RESULT_CACHE option must also be configured as false to avoid phantom reads.

READ COMMITTED and SERIALIZABLE are the only isolation levels supported by Oracle Database

Oracle Database does not support a lock wait timeout that is configurable within the scope of a transaction or session. Oracle R2DBC implements SPI methods that configure a lock wait timeout to throw UnsupportedOperationException.

Statements

Oracle R2DBC supports SQL execution with the Statement SPI.

Parameter Markers

A SQL command passed to Connection.createStatement(String) may include named parameter markers, unnamed parameter markers, or both.

Unnamed parameter markers may appear in SQL as a question mark (?):

connection.createStatement(
  "SELECT value FROM example WHERE id=?")
  .bind(0, 99)

The bind method must be called with a zero-based index to set the value of an unnamed parameter.

Named parameter markers may appear in SQL as a colon character (:) followed by an alpha-numeric name:

connection.createStatement(
  "SELECT value FROM example WHERE id=:id")
  .bind("id", 99)

The bind method may be called with a String valued name, or with zero-based index, to set the value of a named parameter. Parameter names are case-sensitive.

Batch Execution

The Statement.add() method may be used execute a DML command multiple times with a batch of different bind values. Oracle Database only supports batch execution for DML type SQL commands (INSERT/UPDATE/DELETE). Attempting to execute a SELECT query with a batch of bind values will result in an error.

Returning Generated Values

The Statement.returnGeneratedValues(String...) method may be called to return generated values from basic forms of INSERT and UPDATE statements.

If an empty set of column names is passed to returnGeneratedValues, the Statement will return the ROWID of each row affected by an INSERT or UPDATE.

Programmers are advised not to use the ROWID as if it were a primary key. The ROWID of a row change, or be reassigned to a different row. See https://asktom.oracle.com/pls/apex/asktom.search?tag=is-it-safe-to-use-rowid-to-locate-a-row for more information.

Returning generated values is only supported for INSERT and UPDATE commands in which a RETURNING INTO clause would be valid. For example, if a table is declared as:

CREATE TABLE example (
  id NUMBER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  value VARCAHR(100))

Returning generated values is supported for the following statement:

connection.createStatement(
  "INSERT INTO example(value) VALUES (:value)")
  .bind("value", "x")
  .returningGeneratedValues("id")

This statement is supported because the INSERT could be written to include a RETURNING INTO clause:

INSERT INTO example(value) VALUES (:value) RETURING id INTO :id

As a counter example, returning generated values is not supported for the following statement:

connection.createStatement(
  "INSERT INTO example (value) SELECT 'y' FROM sys.dual")
  .returningGeneratedValues("id")

This statement is not supported because it can not be written to include a RETURNING INTO clause.

The Oracle Database SQL Language Reference specifies the INSERT and UPDATE commands for which a RETURNING INTO clause is supported.

For the INSERT syntax, see: https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/INSERT.html

For the UPDATE syntax, see: https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/UPDATE.html

Procedural Calls

The SQL string passed to Connection.createStatement(String) may execute a PL/SQL call:

connection.createStatement("BEGIN sayHello(:name_in, :greeting_out); END;")

OUT parameters are registered by invoking Statement.bind(int, Object) or Statement.bind(String, Object) with an instance of io.r2dbc.spi.Parameter implementing the io.r2dbc.spi.Parameter.Out marker interface:

statement.bind("greeting_out", Parameters.out(R2dbcType.VARCHAR))

Likewise, an IN OUT parameter would be registered by invoking Statement.bind(int, Object) or Statement.bind(String, Object) with an instance of io.r2dbc.spi.Parameter implementing both the io.r2dbc.spi.Parameter.Out and io.r2dbc.spi.Parameter.In marker interfaces.

OUT parameters are consumed by invoking Result.map(Function):

result.map(outParameters -> outParameters.get("greeting_out", String.class))

If a procedural call returns multiple results, the publisher returned by Statement.execute() emits one Result for each cursor returned by DBMS_SQL.RETURN_RESULT in the procedure. The order in which each Result is emitted corresponds to the order in which the procedure returns each cursor.

If a procedure returns cursors, and also has out parameters, then the Result for the out parameters is emitted last, after the Result for each cursor.

Type Mappings

Oracle R2DBC supports type mappings between Java and SQL for non-standard data types of Oracle Database.

Oracle SQL Type Java Type
JSON javax.json.JsonObject or oracle.sql.json.OracleJsonObject
DATE java.time.LocalDateTime
INTERVAL DAY TO SECOND java.time.Duration
INTERVAL YEAR TO MONTH java.time.Period
SYS_REFCURSOR io.r2dbc.spi.Result

Unlike the standard SQL type named "DATE", the Oracle Database type named "DATE" stores values for year, month, day, hour, minute, and second. The standard SQL type only stores year, month, and day. LocalDateTime objects are able to store the same values as a DATE in Oracle Database.

BLOB, CLOB, and NCLOB

Oracle R2DBC allows large objects (LOBs) to be read and written as a reactive stream, or as a fully materialized value.

Prefetched LOB Data

When a SQL query returns a LOB column, only a portion of the LOB's content is received in the response from Oracle Database. The portion received in the SQL query response is referred to as "prefetched data". Any content remaining after the prefetched portion must be fetched with additional database calls.

For example, if a SQL query returns a LOB that is 100MB in size, then the response might prefetch only the first 1MB of the LOB's content. Additional database calls would be required to fetch the remaining 99MB of content.

By default, Oracle R2DBC attempts to prefetch the entire content of a LOB. Oracle R2DBC will request up to 1GB of prefetched data from Oracle Database when executing a SQL query.

Materialzed Type Mapping

The Row.get(...) method allows LOB values to be mapped into materialized types like ByteBuffer and String. If the entire LOB has been prefetched, then Row.get(...) can return a ByteBuffer/String without any additional database calls. However, if the LOB value is larger than the prefetch size, then Row.get(...) must execute a blocking database call to fetch the remainder of that value.

Streamed Type Mapping

In a system that consumes very large LOBs, a very large amount of memory will be consumed if the entire LOB is prefetched. When a LOB is too large to be prefetched entirely, a smaller prefetch size can be configured using the oracle.jdbc.defaultLobPrefetchSize option, and the LOB can be consumed as a stream. By mapping LOB columns to Blob or Clob objects, the content can be consumed as a reactive stream.

ARRAY

Oracle Database supports ARRAY as a user defined type only. A CREATE TYPE command is used to define an ARRAY type:

CREATE TYPE MY_ARRAY AS ARRAY(8) OF NUMBER

Oracle R2DBC defines oracle.r2dbc.OracleR2dbcType.ArrayType as a Type for representing user defined ARRAY types. A Parameter with a type of ArrayType must be used when binding array values to a Statement.

Publisher<Result> arrayBindExample(Connection connection) {
  Statement statement =
    connection.createStatement("INSERT INTO example VALUES (:array_bind)");

  // Use the name defined for an ARRAY type:
  // CREATE TYPE MY_ARRAY AS ARRAY(8) OF NUMBER
  ArrayType arrayType = OracleR2dbcTypes.arrayType("MY_ARRAY");
  Integer[] arrayValues = {1, 2, 3};
  statement.bind("arrayBind", Parameters.in(arrayType, arrayValues));

  return statement.execute();
}

A Parameter with a type of ArrayType must also be used when binding OUT parameters of a PL/SQL call.

Publisher<Result> arrayOutBindExample(Connection connection) {
  Statement statement =
    connection.createStatement("BEGIN; exampleCall(:array_bind); END;");

  // Use the name defined for an ARRAY type:
  // CREATE TYPE MY_ARRAY AS ARRAY(8) OF NUMBER
  ArrayType arrayType = OracleR2dbcTypes.arrayType("MY_ARRAY");
  statement.bind("arrayBind", Parameters.out(arrayType));

  return statement.execute();
}

ARRAY values may be consumed from a Row or OutParameter as a Java array. The element type of the Java array may be any Java type that is supported as a mapping for the SQL type of the ARRAY. For instance, if the ARRAY type is NUMBER, then a Integer[] mapping is supported:

Publisher<Integer[]> arrayMapExample(Result result) {
  return result.map(readable -> readable.get("arrayValue", Integer[].class));
}

OBJECT

Oracle Database supports OBJECT as a user defined type. A CREATE TYPE command is used to define an OBJECT type:

CREATE TYPE PET AS OBJECT(
  name VARCHAR(128),
  species VARCHAR(128),
  weight NUMBER,
  birthday DATE)

Oracle R2DBC defines oracle.r2dbc.OracleR2dbcType.ObjectType as a Type for representing user defined OBJECT types. A Parameter with a type of ObjectType may be used to bind OBJECT values to a Statement.

Use an Object[] to bind the attribute values of an OBJECT by index:

Publisher<Result> objectArrayBindExample(Connection connection) {
  Statement statement =
    connection.createStatement("INSERT INTO petTable VALUES (:petObject)");

  // Bind the attributes of the PET OBJECT defined above
  ObjectType objectType = OracleR2dbcTypes.objectType("PET");
  Object[] attributeValues = {
    "Derby",
    "Dog",
    22.8,
    LocalDate.of(2015, 11, 07)
  };
  statement.bind("petObject", Parameters.in(objectType, attributeValues));

  return statement.execute();
}

Use a Map<String,Object> to bind the attribute values of an OBJECT by name:

Publisher<Result> objectMapBindExample(Connection connection) {
  Statement statement =
    connection.createStatement("INSERT INTO petTable VALUES (:petObject)");

  // Bind the attributes of the PET OBJECT defined above
  ObjectType objectType = OracleR2dbcTypes.objectType("PET");
  Map<String,Object> attributeValues = Map.of(
    "name", "Derby",
    "species", "Dog",
    "weight", 22.8,
    "birthday", LocalDate.of(2015, 11, 07));
  statement.bind("petObject", Parameters.in(objectType, attributeValues));

  return statement.execute();
}

A Parameter with a type of ObjectType must be used when binding OUT parameters of OBJECT types for a PL/SQL call:

Publisher<Result> objectOutBindExample(Connection connection) {
  Statement statement =
    connection.createStatement("BEGIN; getPet(:petObject); END;");

  ObjectType objectType = OracleR2dbcTypes.objectType("PET");
  statement.bind("petObject", Parameters.out(objectType));

  return statement.execute();
}

OBJECT values may be consumed from a Row or OutParameter as an oracle.r2dbc.OracleR2dbcObject. The OracleR2dbcObject interface is a subtype of io.r2dbc.spi.Readable. Attribute values may be accessed using the standard get methods of Readable. The get methods of OracleR2dbcObject support all SQL to Java type mappings defined by the R2DBC Specification:

Publisher<Pet> objectMapExample(Result result) {
  return result.map(row -> {
    OracleR2dbcObject oracleObject = row.get(0, OracleR2dbcObject.class); 
    return new Pet(
      oracleObject.get("name", String.class),
      oracleObject.get("species", String.class),
      oracleObject.get("weight", Float.class),
      oracleObject.get("birthday", LocalDate.class));
  });
}

Instances of OracleR2dbcObject may be passed directly to Statement bind methods:

Publisher<Result> objectBindExample(
  OracleR2dbcObject oracleObject, Connection connection) {
  Statement statement =
    connection.createStatement("INSERT INTO petTable VALUES (:petObject)");
  
  statement.bind("petObject", oracleObject);

  return statement.execute();
}

Attribute metadata is exposed by the getMetadata method of OracleR2dbcObject:

void printObjectMetadata(OracleR2dbcObject oracleObject) {
  OracleR2dbcObjectMetadata metadata = oracleObject.getMetadata();
  OracleR2dbcTypes.ObjectType objectType = metadata.getObjectType();
  
  System.out.println("Object Type: " + objectType);
  metadata.getAttributeMetadatas()
    .stream()
    .forEach(attributeMetadata -> {
      System.out.println("\tAttribute Name: " + attributeMetadata.getName()));
      System.out.println("\tAttribute Type: " + attributeMetadata.getType()));
    });
}

REF Cursor

Use the oracle.r2dbc.OracleR2dbcTypes.REF_CURSOR type to bind SYS_REFCURSOR out parameters:

Publisher<Result> executeProcedure(Connection connection) {
  connection.createStatement(
    "BEGIN example_procedure(:cursor_parameter); END;")
  .bind("cursor_parameter", Parameters.out(OracleR2dbcTypes.REF_CURSOR))
  .execute()
}

A SYS_REFCURSOR out parameter can be mapped to an io.r2dbc.spi.Result:

Publisher<Result> mapOutParametersResult(Result outParametersResult) {
  return outParametersResult.map(outParameters ->
    outParameters.get("cursor_parameter", Result.class));
}

The rows of a SYS_REFCURSOR may be consumed from the Result it is mapped to:

Publisher<ExampleObject> mapRefCursorRows(Result refCursorResult) {
  return refCursorResult.map(row ->
    new ExampleObject(
      row.get("id_column", Long.class),
      row.get("value_column", String.class)));
}

Secure Programming Guidelines

The following security related guidelines should be adhered to when programming with the Oracle R2DBC Driver.

Defend Against SQL Injection Attacks

  • Always specify the parameters of a SQL command using the bind methods of io.r2dbc.spi.Statement.
    • Do not use String concatenation to specify parameters of a SQL command.
    • Do not use format Strings to specify parameters of a SQL command.

Protect Passwords

  • Do not hard code passwords in your source code.
  • Avoid hard coding passwords in the R2DBC URL.
    • When handling URL strings in code, be aware that a clear text password may appear in the user info section.
  • Use a sensitive io.r2dbc.spi.Option to specify passwords.
    • If possible, specify the Option's value as an instance of java.nio.CharBuffer or java.lang.StringBuffer and clear the contents immediately after ConnectionFactories.get(ConnectionFactoryOptions) has returned. Oracle R2DBC's implementation of ConnectionFactory does not retain a reference to the clear text password.

Protect Network Communications

  • Use SSL/TLS if possible. Use any of the following methods to enable SSL/TLS:
    • Specify the boolean value of true for io.r2dbc.spi.ConnectionFactoryOptions.SSL
    • Specify "r2dbcs:" as the R2DBC URL schema.
    • Specify "ssl=true" in the query section of the R2DBC URL.
  • Use Option.sensitiveValueOf(String) when creating an Option that specifies a password.
    • Option.sensitiveValueOf(OracleConnection.CONNECTION_PROPERTY_WALLET_PASSWORD)
      • An SSO wallet does not require a password.
    • Option.sensitiveValueOf(OracleConnection.CONNECTION_PROPERTY_THIN_JAVAX_NET_SSL_KEYSTOREPASSWORD)
    • Option.sensitiveValueOf(OracleConnection.CONNECTION_PROPERTY_THIN_JAVAX_NET_SSL_TRUSTSTOREPASSWORD)

Defend Against Denial-of-Service Attacks

  • Use a connection pool and configure a maximum size to limit the number of database sessions created by ConnectionFactory.create()
  • Enforce a maximum batch size to limit invocations of Statement.add() or Batch.add(String).
  • Enforce a maximum fetch size to limit values supplied to Statement.fetchSize(int).
  • Enforce a maximum buffer size to limit memory usage when reading Blob and Clob objects.

More Repositories

1

graal

GraalVM compiles Java applications into native executables that start instantly, scale fast, and use fewer compute resources 🚀
Java
20,237
star
2

docker-images

Official source of container configurations, images, and examples for Oracle products and projects
Shell
6,160
star
3

opengrok

OpenGrok is a fast and usable source code search and cross reference engine, written in Java
Java
3,971
star
4

truffleruby

A high performance implementation of the Ruby programming language, built on GraalVM.
Ruby
3,018
star
5

helidon

Java libraries for writing microservices
Java
2,596
star
6

visualvm

VisualVM is an All-in-One Java Troubleshooting Tool
Java
2,563
star
7

node-oracledb

Oracle Database driver for Node.js maintained by Oracle Corp.
JavaScript
2,174
star
8

graaljs

A ECMAScript 2022 compliant JavaScript implementation built on GraalVM. With polyglot language interoperability support. Running Node.js applications!
C++
1,418
star
9

tribuo

Tribuo - A Java machine learning library
Java
1,211
star
10

railcar

RailCar: Rust implementation of the Open Containers Initiative oci-runtime
Rust
1,115
star
11

oracle-db-examples

Examples of applications and tool usage for Oracle Database
Java
958
star
12

graalpython

A Python 3 implementation built on GraalVM
Python
957
star
13

mysql-operator

Create, operate and scale self-healing MySQL clusters in Kubernetes
Go
868
star
14

python-cx_Oracle

Python interface to Oracle Database now superseded by python-oracledb
C
861
star
15

vagrant-projects

Vagrant projects for Oracle products and other examples
Shell
840
star
16

graphpipe

Machine Learning Model Deployment Made Simple
Makefile
725
star
17

terraform-provider-oci

Terraform Oracle Cloud Infrastructure provider
Go
622
star
18

bpftune

bpftune uses BPF to auto-tune Linux systems
C
615
star
19

smith

Smith: A microcontainer builder
Go
602
star
20

fastr

A high-performance implementation of the R programming language, built on GraalVM.
Java
598
star
21

oraclejet

Oracle JET is a modular JavaScript Extension Toolkit for developers working on client-side applications.
479
star
22

db-sample-schemas

Oracle Database Sample Schemas
PLSQL
435
star
23

coherence

Oracle Coherence Community Edition
Java
408
star
24

dotnet-db-samples

.NET code samples for Oracle database developers #OracleDotNet
C#
381
star
25

apex

Official Oracle APEX repo for sample code, starter apps, plug-ins, and more! #orclapex
354
star
26

graalvm-reachability-metadata

Repository which contains community-driven collection of GraalVM reachability metadata for open-source libraries.
Java
352
star
27

oci-cli

Command Line Interface for Oracle Cloud Infrastructure
Python
343
star
28

centos2ol

Script and documentation to switch CentOS/Rocky Linux to Oracle Linux
Shell
330
star
29

oci-python-sdk

Oracle Cloud Infrastructure SDK for Python
Python
303
star
30

crashcart

CrashCart: sideload binaries into a running container
Rust
275
star
31

oracle-db-tools

This project is a repository of sample code that will demonstrate various concepts to assist developers in building applications around Oracle Database technologies. SDKs and scripts will be available to integrate with SQL Developer, Data Modeler, Oracle REST Data Services and DBaaS.
JavaScript
274
star
32

python-oracledb

Python driver for Oracle Database conforming to the Python DB API 2.0 specification. This is the renamed, new major release of cx_Oracle
Python
256
star
33

oci-designer-toolkit

OCI designer toolKIT (OKIT) is a set of tools for enabling design, deploy and visualise OCI environments through a graphical web based interface.
JavaScript
254
star
34

linux-uek

Oracle Linux UEK: Unbreakable Enterprise Kernel
253
star
35

odpi

ODPI-C: Oracle Database Programming Interface for Drivers and Applications
C
235
star
36

weblogic-kubernetes-operator

WebLogic Kubernetes Operator
Java
225
star
37

netsuite-suitecloud-sdk

SuiteCloud Software Development Kit (SuiteCloud SDK) are the set of tools that allow you to customize accounts and create SuiteApps through SuiteCloud Development Framework (SDF).
JavaScript
184
star
38

javavscode

Java platform support for Visual Studio Code for full featured Java development (edit-compile-debug & test cycle)
TypeScript
179
star
39

terraform-kubernetes-installer

Terraform Installer for Kubernetes on Oracle Cloud Infrastructure
HCL
178
star
40

oci-java-sdk

Oracle Cloud Infrastructure SDK for Java
Java
163
star
41

pgql-lang

PGQL is an SQL-based query language for property graphs
Java
158
star
42

speedle

Speedle is an open source project for access control.
Go
156
star
43

oci-ansible-collection

Oracle Cloud Infrastructure Ansible Collection provides an easy way to provision and manage resources in Oracle Cloud using Ansible.
Python
152
star
44

oci-go-sdk

Go SDK for Oracle Cloud Infrastructure
Go
150
star
45

wookiee

Scala based lightweight service framework using zookeeper, gRPC, and other popular technologies.
Scala
143
star
46

cordova-plugin-wkwebview-file-xhr

Cordova Plugin for WebView File XHR
JavaScript
140
star
47

weblogic-deploy-tooling

WebLogic Deploy Tooling
Python
140
star
48

solaris-userland

Open Source software in Solaris using gmake based build system to drive building various software components.
C
137
star
49

macaron

Macaron is an extensible supply-chain security analysis framework from Oracle Labs that supports a wide range of build systems and CI/CD services. It can be used to prevent supply chain attacks, detect malicious Python packages, or check conformance to frameworks, such as SLSA. Documentation:
Python
134
star
50

sd4j

Stable diffusion pipeline in Java using ONNX Runtime
Java
131
star
51

analytical-sql-examples

NO LONGER MAINTAINED. Code samples for Oracle's analytical SQL features
PLSQL
127
star
52

container-images

Oracle Linux container images
124
star
53

oracle-database-operator

The Oracle Database Operator for Kubernetes (a.k.a. OraOperator) helps developers, DBAs, DevOps and GitOps teams reduce the time and complexity of deploying and managing Oracle Databases. It eliminates the dependency on a human operator or administrator for the majority of database operations.
Go
120
star
54

oracle-linux

Scripts, examples, and tutorials to get started with Oracle Linux
Shell
115
star
55

kernel-fuzzing

Fuzzers for the Linux kernel
Hack
109
star
56

oci-cloud-controller-manager

Kubernetes Cloud Controller Manager implementation for Oracle Cloud Infrastucture
Go
109
star
57

oci-ansible-modules

DEPRECATED - Please migrate to the new OCI Ansible collection (https://github.com/oracle/oci-ansible-collection).
Python
106
star
58

cloud-native-devops-workshop

Oracle's Cloud Native and DevOps Workshop on Oracle Cloud
JavaScript
106
star
59

weblogic-monitoring-exporter

WebLogic Monitoring Exporter exposes metrics and monitoring data through REST APIs for consumption by other tools (e.g. Prometheus)
Java
105
star
60

macest

Model Agnostic Confidence Estimator (MACEST) - A Python library for calibrating Machine Learning models' confidence scores
Jupyter Notebook
99
star
61

hospitality-api-docs

This repository stores REST API specifications and accompanying Postman collections for Oracle Hospitality APIs.
HTML
99
star
62

cloudtestdrive

HTML
95
star
63

font-apex

Font APEX is an open source icon library from the Oracle APEX team.
CSS
93
star
64

coherence-operator

Oracle Coherence Operator
Go
93
star
65

ktf

Kernel Test Framework - a unit test framework for the Linux kernel
Shell
88
star
66

kubernetes-vault-kms-plugin

Go
74
star
67

dtrace-utils

DTrace-utils contains the DTrace port to Linux
C
74
star
68

oci-grafana-metrics

Grafana datasource plugin for OCI metrics
Go
71
star
69

hiq

HiQ - Observability And Optimization In Modern AI Era
Python
68
star
70

accelerated-data-science

ADS is the Oracle Data Science Cloud Service's python SDK supporting, model ops (train/eval/deploy), along with running workloads on Jobs and Pipeline resources.
Python
67
star
71

graphpipe-go

GraphPipe for go
Go
66
star
72

soda-for-java

SODA (Simple Oracle Document Access) for Java is an Oracle library for writing Java apps that work with JSON (and not only JSON!) in the Oracle Database. SODA allows your Java app to use the Oracle Database as a NoSQL document store.
Java
65
star
73

oci-typescript-sdk

Oracle Cloud Infrastructure SDK for TypeScript and JavaScript
TypeScript
64
star
74

oracle-functions-samples

Examples demonstrating how to use Oracle Functions
61
star
75

yo

A fast and simple command line OCI client
Python
60
star
76

solaris-ips

Solaris IPS: Image Packaging System
Python
57
star
77

weblogic-image-tool

WebLogic Image Tool
Java
57
star
78

nvm-direct

A C library to support applications that map Non-Volatile Memory into their address space for load/store access.
C
56
star
79

microservices-datadriven

Sample code of application examples to build microservices with converged Oracle database and multi-cloud / hybrid cloud services
CSS
56
star
80

bots-node-sdk

Oracle Bots Node.js SDK
TypeScript
51
star
81

db-appdev-vm

Database Application Development Virtual Machine
Shell
50
star
82

souffle

DEPRECATED. Soufflé is a translator of declarative Datalog programs into the C++ language.
C++
49
star
83

xml-sample-demo

Oracle Database XMLDB Code samples
VBScript
48
star
84

oci-service-broker

Oracle Cloud Infrastructure Service Broker is an open source implementation of Open service broker API Spec for OCI services. Customers can use this implementation to install Open Service Broker in Oracle Container Engine for Kubernetes or in other Kubernetes clusters.
Java
48
star
85

free

Free Oracle technologies for Developers
HTML
47
star
86

oraclesolaris-contrib

oraclesolaris-contrib is a repository focussed on the Oracle Solaris 11.4 StatsStore, using methodologies like REST to connect to Oracle Solaris 11.4 and the new features being introduced in Oracle Solaris 11.4 OS.
Jupyter Notebook
46
star
87

oci-utils

Oracle Cloud Infrastructure utilities
Python
45
star
88

content-and-experience-toolkit

The Oracle Content Management Toolkit and SDKs help you develop custom applications that consume content that is managed in the OCM repository. These applications can be developed in the Content Management Cloud or using 3rd party tools.
JavaScript
45
star
89

navarkos

Enables a Kubernetes federation to automatically manage multi-cluster infrastructure
Go
44
star
90

nosql-examples

This is a top level repository for code examples related to the use of Oracle NoSQL Database.
HTML
44
star
91

rwloadsim

RWP*Load Simulator - your tool for scripting, simulation and much more. Like having a bit of bash and SQL, a nip of C or Java, a dash of awk, a grain of sed plus drops of secret sauce in one single tool. See https://blogs.oracle.com/database/rwploadsim-oracle-db-performance-simluator for the announcement on the Oracle database blog.
C
44
star
92

fmw-chef-cookbook

Official repository of samples that show how to use Chef to provision Oracle Fusion Middleware (FMW) products.
Ruby
43
star
93

oci-dotnet-sdk

Oracle Cloud Infrastructure SDK for .NET
C#
43
star
94

graphpipe-py

GraphPipe for python
Python
42
star
95

terraform-examples

Terraform Examples for Oracle Cloud Infrastructure and Platfrom
41
star
96

heatwave-tpch

SQL scripts for HeatWave benchmarking
41
star
97

oci-data-science-ai-samples

This repo contains a series of tutorials and code examples highlighting different features of the OCI Data Science and AI services, along with a release vehicle for experimental programs.
Jupyter Notebook
41
star
98

oracle-db-appdev-monitoring

Metrics exporter and samples for unified observability for data-centric app dev and microservices
Go
41
star
99

dbt-oracle

dbt (data build tool) adapter for Oracle Autonomous Database
Python
40
star
100

offline-persistence-toolkit

Offline Persistence Toolkit for Javascript Applications
JavaScript
40
star