Using Oracle Sequences

Oracle® and similar databases implement a feature called sequences. Sequences are objects that can be created within the database and used to generate unique identifiers. Those unique identifiers can then be used as unique primary keys when performing inserts into tables. Unlike SQL Server identity columns, sequences must be manually retrieved and used as part of the INSERT statement.

In some cases, this can be very useful to ensure unique identifiers are generated for a particular table. While Oracle® does not guarantee that the values are continuous (that is, there may be gaps in the numbers that are allocated), it does guarantee that the values are unique. Frequently sequences are used to generate a unique identifier for some data, and then this identifier is subsequently used as a foreign key when inserting into other tables that are related in some way.

Using sequences in the Rhapsody Database Configuration Editor is fairly simple using compound SQL statements. The first statement retrieves the next available sequence number, and then an INSERT statement is used to insert this value into the table. This example retrieves a new identifier from a sequence called PatientID, and then inserts it as the first column into the Patient table:

SELECT identifiers.nextval As PatientID FROM dual;

INSERT INTO Patient(PatientID, FamilyName, GivenName, Title, Gender, DateOfBirth)
  VALUES (
    @PID.PatientID,
    @PID.PatientName[0].FamilyName.Surname,
    @PID.PatientName[0].GivenName,
    @PID.PatientName[0].Prefix,
    @PID.Sex,
    $DateOfBirth
)

Once this is done the identifier is then available for subsequent statements to use, they would reference it as @PatientID, or for Result Substitution.