This website is now static html (since the end of 2010). The pages you see here are a simple wget spider mode crawl of the original wordpress, thus dynamic features like commenting are not working anymore.

Auto increment Integer Value on Oracle Database (10g)

Using databases it is often useful to have rows identified by a unique numeric value (mainly for ID columns), no matter what the value is. If, like me, you have a background of MySQL (where auto increment values are easy like saying AUTO_INCREMENT) but for some reasons you have to develop something on Oracle Database 10g you’ll be a little confused about the lack of a single auto increment function.

Here is a simple howto on how to have an auto incremented numeric column value on Oracle Database using a sequence and a trigger (if you find some errors please use the comments):

Tested on: Oracle Database 10g Enterprise Edition

Precondition: a numeric type (INTEGER is ok) column in a table (in this example the schema is PROVA01 and the table is UTENTI with ID as column).

  1. Log in Oracle Enterprise Manager (probably http://servername:1158/em/) as user with proper privileges (SYS as SYSDBA will work);
  2. Go to “Administration”;
  3. Under “Schemas > Database Objects” open “Sequence”;
  4. Select proper schema (in this how to is PROVA01, the table is UTENTI and the field is ID);
  5. Create a new sequence by filling:
    1. Name: choose a name, something like UTENTI_ID_SEQ should be fine;
    2. Schema: select PROVA01;
    3. Max value: no limit;
    4. Min value: enter value 1;
    5. Increment: 1;
    6. Start: 1;
    7. De-select “Use Cache”
    8. When done click “OK” button (the screenshot is in italian)
  6. Return back to administration page and go to “Programs > Triggers” and create a new trigger:
    1. Name: TRIGGER_SEQ_UTENTI is a good choice;
    2. Schema: PROVA01;
    3. Select “Replace if exists”
    4. Trigger body (in bold values you may change):
      INTO   :NEW.ID
      FROM   DUAL;
    5. Go to “Event” tab and configure the trigger:
      1. “Execute the trigger” on Table;
      2. Table: in this case PROVA01.UTENTI;
      3. Start trigger: before;
      4. Event: INSERT;
    6. Go to “Advanced” and select “FOR EACH ROW”;
  7. Save the trigger and, if any, check out the errors.

Test the sequence and trigger by inserting a new table row, the ID value will be auto-inserted (and incremented).

3 Responses to “Auto increment Integer Value on Oracle Database (10g)”

  1. karamvir singh

    thanks a lot.
    i was getting frustrated ….
    i have been working oon mysql and mam new to oracle..
    but this helped me a lot…
    thanks once again.

  2. Arjuna Del Toso

    you are welcome

  3. Dede Rahmat

    Thank’s so much
    I newbi in oracle

Leave a Reply