User-Defined Sequence

An integration may require use of a unique reference number to identify a transaction. For that purpose the Oracle Insurance Gateway provides the capability to configure use case specific database sequences that guarantee use of unique numbers across multiple exchanges.

This section of the guide explains how to configure these user defined sequences.

Maintain User-defined Sequences

Use the "/generic/usersequences" resource in order to maintain user defined sequences.

For example, to configure a user defined sequence with (unique) code "customer_import" POST the following payload:

{
  "code": "customer_import"
}

From the moment it is defined, this can be used to generate unique numbers starting with 1 with an interval of 1 between values.

A GET request to "/generic/usersequences" returns an overview of the configured user defined sequences. As is the case with any generic HTTP API, the system provides extensive metadata that documents the API. The following paragraph explains sequence-specific settings.

It is not possible to update an existing sequence. To redefine an existing sequence delete it first and re-create it. Please note that it may be needed to specify a startWith value that takes into account numbers that were used already.

Sequence Settings

Next to the required unique code that identifies a sequence number generator, the following optional attributes may be specified, with the constraints mentioned:

  • incrementBy: to specify the interval between sequence numbers. This integer value can be any positive or negative integer, but it cannot be 0. This value can have 28 or fewer digits for an ascending sequence and 27 or fewer digits for a descending sequence. The absolute of this value must be less than the difference of maxValue and minValue. If this value is negative, then the sequence descends. If the value is positive, then the sequence ascends. If the incrementBy value is omitted then the interval defaults to 1.

  • startWith: to specify the first sequence number to be generated. Use this clause to start an ascending sequence at a value greater than its minimum or to start a descending sequence at a value less than its maximum. For ascending sequences, the default value is the minimum value of the sequence. For descending sequences, the default value is the maximum value of the sequence. This integer value can have 28 or fewer digits for positive values and 27 or fewer digits for negative values. For example, use startWith to start number generation from the latest value that was used to date (perhaps in a different system).

  • minValue: to specify the minimum value of the sequence. This integer value can have 28 or fewer digits for positive values and 27 or fewer digits for negative values. minValue must be less than or equal to startWith and must be less than maxValue.

  • maxValue: to specify the maximum value the sequence can generate. This integer value can have 28 or fewer digits for positive values and 27 or fewer digits for negative values. maxValue must be equal to or greater than startWith and must be greater than minValue.

For validation, the system creates a sequence in the Oracle database with the given values or, when applicable, using default values if attributes are unspecified. If creation of the sequence in the database fails, then the system returns HTTP 422 response. If attributes are not defined when the sequence is created, in which case default values are applied by the database, then these do not show up as values when data is retrieved.

Note that if the sequence can be created that does not always mean it is usable. For example, the Oracle database creates a sequence for the combinations of attribute values that are listed in the following table. But getting a number from such a sequence results in an exception as the database reports the error that is listed in the last column:

incrementBy

startWith

minValue

maxValue

Database Error

-2

-1

0

100

ORA-08004: sequence OHI_UDS_SEQ1.NEXTVAL goes below MINVALUE and cannot be instantiated

2

20

10

18

ORA-08004: sequence OHI_UDS_SEQ2.NEXTVAL exceeds MAXVALUE and cannot be instantiated