Oracle Caveats

Component: Sql Persistence
NuGet Package NServiceBus.Persistence.Sql (2.x)
Target NServiceBus Version: 6.x

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

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.Endpoint:

Persistence typeSuffixTable NameIndexes
Subscriptions_SSMY_ENDPOINT_SSMY_ENDPOINT_SS_PK
Timeouts_TOMY_ENDPOINT_TOMY_ENDPOINT_TO_PK
MY_ENDPOINT_TK
MY_ENDPOINT_SK
Outbox_ODMY_ENDPOINT_ODMY_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.

In order to accommodate as many characters for the saga name as possible, the table prefix is omitted from the saga table name.

Saga Class NameTable NamePrimary Key
OrderPolicyORDERPOLICYORDERPOLICY_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 saga name or correlation property name change, 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.


Last modified