SQL Persistence will run on Oracle XE. However it is strongly recommended to use commercial versions for production systems. It is also recommended to ensure that Oracle support agreements are in place.
Supported database versions
SQL persistence supports Oracle 11g Release 2 and above.
Usage
Using the Oracle.ManagedDataAccess NuGet Package.
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);
});
In Versions 2.2.0 and above it's possible to specify custom schema using the following code:
var persistence = endpointConfiguration.UsePersistence<SqlPersistence>();
persistence.SqlVariant(SqlVariant.Oracle);
persistence.Schema("custom_schema");
The ODP.NET managed driver requires the Enlist=false
or Enlist=dynamic
setting in the Oracle connection string to allow the persister to enlist in a Distributed Transaction at the correct moment.
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 than in other databases, notably SQL Server and PostgreSQL. By default, when schema is not specified, SQL persistence uses the context of the logged-in user when referring to database objects. When a schema is specified, that schema is used instead of the logged-in user 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.
The SQL Persistence provides autonomy between 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.
The SQL persistence will throw an exception in case the name length is too long.
Table Names
For a complete example of the schema created by the SQL Persistence for Oracle, see Oracle Scripts.
For storing subscriptions, timeouts, and outbox data, SQL Persistence will reserve 24 characters for the endpoint name, leaving 3 characters for the persistence type, and additional 3 characters for an index type. Names are then constructed as {EndpointName}{PersistenceTypeSuffix}{KeyType}
.
The following table shows table names created for an endpoint named My.
:
Persistence type | Suffix | Table Name | Indexes |
---|---|---|---|
Subscriptions | _SS | MY_ENDPOINT_SS | MY_ENDPOINT_SS_PK |
Timeouts | _TO | MY_ENDPOINT_TO | MY_ENDPOINT_TO_PK MY_ENDPOINT_TK MY_ENDPOINT_SK |
Outbox | _OD | MY_ENDPOINT_OD | MY_ENDPOINT_OD_PK MY_ENDPOINT_IX |
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 for the saga name as possible, the table prefix is omitted from the saga table name.
Saga Class Name | Table Name | Primary Key |
---|---|---|
OrderPolicy | ORDERPOLICY | ORDERPOLICY_PK |
A 3-character suffix is not enough to uniquely identify multiple correlation properties in a deterministic way, so unfortunately index names for sagas cannot be named after the owner table in the same way as for other persistence types.
Index names for correlation properties are constructed using the prefix SAGAIDX_
plus a deterministic hash of the saga name and 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 the saga name or correlation property name changes, the name of the index will also change.
If a saga name is longer than 27 characters, an exception will be thrown, and a substitute table name must be specified.
Custom Finders
Because Oracle 11g does not support any JSON query capability, custom saga finders that locate saga data by querying into the JSON payload of the saga are not supported by SQL Persistence when using Oracle.