lamp2lapo

Oracle as such does not have an auto increment field type. You can however emulate the auto increment field type using a trigger and a sequence. You can also retrieve the auto incremented value back from an insert statement. This article will show you all you need to emulate auto increment fields in Oracle.

The example MySQL table that we will convert to an Oracle table with an emulated auto increment field. (Example used taken from MySQL 5.0 Reference Manual, 3.6.9 Using AUTO_INCREMENT)

CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
 );

INSERT INTO animals (name) VALUES
    ('dog'),('cat'),('penguin'),
    ('lax'),('whale'),('ostrich');

SELECT * FROM animals;

+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+

The table definition for Oracle:

CREATE TABLE animals (
    id NUMBER,
    name VARCHAR2(30) NOT NULL,
    PRIMARY KEY (id)
);

Create a sequence that will hold and provide the numbers for the auto increment field:

CREATE SEQUENCE animals_seq
    START WITH 1
    INCREMENT BY 1
    NOMAXVALUE
    NOMINVALUE
    CACHE 10;

To CACHE or to NOCACHE, ORDER or NOORDER the sequence.

The CACHE option determines how many sequence values will be created and stored in memory. On an application using many concurrent connections this can gain a small increase in performance. However if a system failure occurs all unused cached sequence values will be lost. This will then result in a gap in the used sequence numbers.

For most applications this will not be a problem as the auto increment field is generally only used to create a unique identifier. If you need a system that will not create any gaps in the used sequence and generate the numbers in the order requested, then the NOCACHE and ORDER options are here to help you.

The NOCACHE option will generate the next number in the sequence.

The ORDER option will generate the next number in the sequence when called and guarantees that sequence numbers are generated in order of request. The ORDER option implies NOCACHE.

Create the trigger that will be used to insert the sequence number into the table:

CREATE TRIGGER animals_trig
    BEFORE INSERT ON animals REFERENCING NEW AS NEW OLD AS OLD
        FOR EACH ROW
            BEGIN
                SELECT animals_seq.nextval into :NEW.id FROM DUAL;
            END;
/

On all inserts into the animals table the trigger will be used.

Insert some data:

INSERT INTO animals (name) VALUES ('dog');
INSERT INTO animals (name) VALUES ('cat');
INSERT INTO animals (name) VALUES ('penguin');
INSERT INTO animals (name) VALUES ('lax');
INSERT INTO animals (name) VALUES ('whale');
INSERT INTO animals (name) VALUES ('ostrich');

SELECT * FROM animals;

ID NAME
---------- ------------------------------
         1 dog
         2 cat
         3 penguin
         4 lax
         5 whale
         6 ostrich

A trigger and sequence number will be needed for every auto increment field that you wish to use.

Getting back the auto increment number

In Oracle you can add the RETURNING clause to an INSERT statement that can be used to return the auto incremented number into a bind variable.

Below is an example showing how to retrieve the auto increment number in PHP:

$query = "INSERT INTO animals (name) VALUES ('snake') RETURNING id INTO :i";
$dbh = oci_connect('scott', 'tiger', 'XE');
$stmt = oci_parse($dbh, $query);

// Create a bind variable of the correct type
$animal_id = oci_new_descriptor($dbh, OCI_DTYPE_ROWID);

// Bind the variable to the parsed statement
oci_bind_by_name($stmt, ":i", $animal_id);
oci_execute($stmt, OCI_DEFAULT);

// Commit the change
oci_commit($dbh);

// Free resources associated with the statement
oci_free_statement($stmt);

// Show that we have the returned id
echo "Animal id: $animal_id";