SERIAL data types

CREATE TABLE tab ( k SERIAL); -- internal counter = 0
INSERT INTO tab VALUES ( 0 ); -- internal counter = 1
INSERT INTO tab VALUES ( 10 ); -- internal counter = 10
INSERT INTO tab VALUES ( 0 ); -- internal counter = 11
DELETE FROM tab; -- internal counter = 11
INSERT INTO tab VALUES ( 0 ); -- internal counter = 12

Solution

The SERIAL data type can be emulated with sequences used in INSERT triggers or with the DEFAULT ON NULL clause.

The method used to emulate SERIAL types is defined by the ifxemul.datatype.serial.emulation FGLPROFILE parameter:

dbi.database.dbname.ifxemul.datatype.serial.emulation = {"native"|"native2"|"regtable"}

• native: uses insert triggers with sequences.
• native2: uses DEFAULT ON NULL column clause with sequences.
• regtable: uses insert triggers with the SERIALREG table.

The default emulation technique is "native".

This entry must be used in conjunction with:

dbi.database.dbname.ifxemul.datatype.serial = {true|false}

If the datatype.serial entry is set to false, the emulation method is ignored.

Notes common to all serial emulation modes

When a BDL program executes a CREATE [TEMP] TABLE with a SERIAL column, the Oracle interface automatically creates the additional SQL objects (column clauses, sequences or triggers) to generate numbers when an INSERT statement is performed.

Users executing programs which create tables with SERIAL columns must have the CONNECT and RESOURCE roles assigned to create triggers and sequences.

SERIAL[(n)] data types are converted to NUMBER(10,0), while SERIAL8[(n)] and BIGSERIAL[(n)] are replaced by NUMBER(20,0).

For SERIAL types, the SQLCA.SQLERRD[2] register is filled as expected with the last generated serial value. However, since SQLCA.SQLERRD[2] is defined as an INTEGER, it cannot hold values from BIGSERIAL (NUMBER(20,0)) auto-incremented columns. If you are using BIGSERIAL columns, you must the fetch the sequence pseudo-column CURR_VAL or fetch the LASTSERIAL column from the SERIALREG table, if used.

Check whether your application uses tables with a SERIAL column that can contain a NULL value: INSERT statements using NULL for the SERIAL column will produce a new serial value:

INSERT INTO tab ( col1, col2 ) VALUES ( NULL, 'data' )

This behavior is mandatory in order to support INSERT statements that do not use the serial column:

INSERT INTO tab (col2) VALUES ('data')

For SQL portability, INSERT statements should be reviewed to remove the SERIAL column from the list. For example, the following statement:

INSERT INTO tab (col1,col2) VALUES (0, p_value)

can be converted to:

INSERT INTO tab (col2) VALUES (p_value)

Static SQL INSERT using records defined from the schema file must also be reviewed:

DEFINE rec LIKE tab.* INSERT INTO tab VALUES (rec.*) -- will use the serial column

can be converted to:

INSERT INTO tab VALUES rec.* -- without braces, serial column is removed

When using the Static SQL INSERT or UPDATE syntax using record.* without braces, make sure that you database schema files contain information about serials: This information can be lost when extracting the schema from an Oracle database. See Database Schema for more details about the serial flag in column type encoding (data type code must be 6).

If the "native" or "regtable" emulation is used, inserting rows with ORACLE tools like SQL*Plus or SQL*Loader will execute the INSERT triggers. When loading big tables, you can disable triggers with ALTER TRIGGER [ENABLE | DISABLE] (see ORACLE documentation for more details). After reactivation of the serial triggers, the SERIAL sequences must be re-initialized (use serialpkg.create_sequence('tab','col')) or re-execute the PL/SQL script containing the sequence and trigger creation.

Using the native serial emulation

Each table having a SERIAL column needs an INSERT TRIGGER and a SEQUENCE dedicated to SERIAL generation.

To know how to write those sequences and triggers, you can create a small Genero program that creates a table with a SERIAL column. Set the FGLSQLDEBUG environment variable and run the program. The debug output will show you the native SQL commands to create the sequence and the trigger.

For temporary tables, the trigger and the sequence are dropped automatically after a "DROP TABLE temptab" or when the program disconnects from the database.

Using the native2 serial emulation

With this emulation, a SERIAL type is converted to a DEFAULT ON NULL clause using a sequence is created automatically by the database driver, for example:

CREATE TABLE t1 ( mykey SERIAL(100), .... )

is converted to:

CREATE SEQUENCE t1_srl INCREMENT BY 1 START WITH 100

CREATE TABLE t1 (mykey NUMBER(10,0) DEFAULT ON NULL t1_srl.nextval , ...

For temporary tables, the sequence is dropped automatically after a "DROP TABLE temptab" or when the program disconnects from the database.

Using the regtable serial emulation

Each table having a SERIAL column needs an INSERT TRIGGER which uses the SERIALREG table dedicated to SERIAL registration.

First, you must prepare the database and create the SERIALREG table as follows:

CREATE TABLE serialreg (
    tablename VARCHAR2(50) NOT NULL,
    lastserial NUMBER(20,0) NOT NULL,
    PRIMARY KEY ( tablename )
)

In database creation scripts, all SERIAL[(n)] data types must be converted to INTEGER data types and you must create one trigger for each table. SERIAL8/BIGSERIAL columns must be converted to NUMBER(20,0). To know how to write those triggers, you can create a small Genero program that creates a table with a SERIAL column. Set the FGLSQLDEBUG environment variable and run the program. The debug output will show you the native trigger creation command.

The serial production is based on the SERIALREG table which registers the last generated number for each table. If you delete rows of this table, sequences will restart at start values and you might get duplicated values.

For temporary tables, the trigger is dropped automatically after a "DROP TABLE temptab" or when the program disconnects from the database.