SERIAL Columns - Informix to Oracle Migration

In Informix, you can use the SERIAL data type to automatically generates IDs (auto-increment values) for columns.

Informix:

  CREATE TABLE teams
  (
     id SERIAL NOT NULL,
     name VARCHAR(70)
  );
 
  -- Let's insert a few rows
  INSERT INTO teams VALUES (0, 'AC Milan');
  INSERT INTO teams VALUES (0, 'Manchester City');

When 0 value is inserted into a SERIAL column, Informix inserts the next ID (a 32-bit integer value):

Table data

Oracle does not have SERIAL data type or IDENTITY (AUTO_INCREMENT) column property, but you can use a sequence and a trigger to implement this functionality.

Why trigger, not just DEFAULT? Oracle does not allow using a sequence in the column DEFAULT clause.

Oracle:

  CREATE TABLE teams
  (
     id NUMBER(10) NOT NULL,
     name VARCHAR(70)
  );
 
  CREATE SEQUENCE teams_seq START WITH 3 INCREMENT BY 1;
 
  // Trigger to automatically assign ID values
  CREATE OR REPLACE TRIGGER teams_seq_tr
    BEFORE INSERT ON teams FOR EACH ROW
  WHEN (NEW.id IS NULL OR NEW.id = 0)
  BEGIN
    SELECT teams_seq.NEXTVAL INTO :NEW.id FROM dual;
  END;
  /

Note that we started the Oracle sequence with 3 taking into account existing data migrated from Informix. See Get Next SERIAL Value to find out how to define the next ID in Informix.

• SQLines - Database and SQL Conversion Tool
• SQLines Data - Data Transfer and Database Schema Migration Tool
• Free Online SQL Conversion Tool

SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.

Written by Dmitry Tolpeko, dmtolpeko@sqlines.com - February 2013.