Oracle dialect

Supported database versions

SQL persistence supports Oracle 11g Release 2 and above.

Usage

Oracle.ManagedDataAccess NuGet package is required, and the persister can be configured as follows:

var connection = "Data Source=localhost;User Id=username;Password=pass;Enlist=false;";
var persistence = endpointConfiguration.UsePersistence<SqlPersistence>();
var subscriptions = persistence.SubscriptionSettings();
subscriptions.CacheFor(TimeSpan.FromMinutes(1));
persistence.SqlDialect<SqlDialect.Oracle>();
persistence.ConnectionBuilder(
    connectionBuilder: () =>
    {
        return new OracleConnection(connection);
    });

Unicode support

SQL persistence itself supports Unicode characters, however data may become corrupted during saving if the database settings are incorrect. If Unicode support is required, follow the guidelines for each database engine. In particular set the correct character set and collation for databases storing persistence data.

Refer to the dedicated Oracle documentation for details.

Schema support

The notion of schemas is slightly different from that in other databases, notably SQL Server and PostgreSQL. By default, when no schema is specified, SQL persistence uses the logged-in user's context when referring to database objects. When a schema is specified, that schema is used instead of the logged-in user's context when referring to database tables.

var persistence = endpointConfiguration.UsePersistence<SqlPersistence>();
var dialect = persistence.SqlDialect<SqlDialect.Oracle>();
dialect.Schema("MySchema");

Supported name lengths

SQL persistence automatically generates names of database objects such as tables, indexes and procedures used internally. Every database engine has its own rules and limitations regarding maximum allowed name length.

SQL Persistence provides autonomy across endpoints by using separate tables for each endpoint, based on the endpoint name. However, due to Oracle's 30-character limit on table names and index names in Oracle 12.1 and below, the SQL Persistence must make some compromises.

Table Names

For storing subscriptions, timeouts, and outbox data, SQL Persistence will reserve 24 characters for the endpoint name, 3 for the persistence type, and an additional 3 for an index type. Names are then constructed as {EndpointName}{PersistenceTypeSuffix}{KeyType}.

The following table shows table names created for an endpoint named My.Endpoint:

If an endpoint name is longer than 24 characters, an exception will be thrown, and a substitute table prefix must be specified in the endpoint configuration:

var persistence = endpointConfiguration.UsePersistence<SqlPersistence>();
persistence.TablePrefix("ThePrefix");

Sagas

Tables generated for sagas reserve 27 characters for the saga name, leaving 3 characters for the _PK suffix for the table's primary key.

To accommodate as many characters as possible in the saga name, the table prefix is omitted from the saga table name.

A 3-character suffix is not sufficient to uniquely identify multiple correlation properties deterministically, so saga index names cannot be based on the owner table, as they are for other persistence types.

Index names for correlation properties are constructed using the prefix SAGAIDX_ followed by a deterministic hash of the saga name and the correlation property name. The owning table for a particular index can be discovered by querying the database:

select TABLE_NAME
from ALL_INDEXES
where INDEX_NAME = 'SAGAIDX_525D1D4DC0C3DCD96947E1';

If a saga name exceeds 27 characters, an exception will be thrown, and a substitute table name must be specified.

Custom Finders

Because Oracle 11g does not support JSON queries, custom saga finders that locate saga data by querying the saga's JSON payload are not supported by SQL Persistence when using Oracle.