Access-style Autonumber fields in Oracle

One of the things I like about MS Access is the AutoNumber field type. And SQL Server has its Identity field type. Very handy for primary keys. But from time to time I convert applications into a more heavy-duty database, such as Oracle, where to do the same thing, you need to select from a sequence first. Here’s how to do an Autonumber-style field without mucking about with the sequence.

You do, of course, need the sequence, to track unique IDs.

CREATE SEQUENCE [Schema].[SequenceName] START WITH [Number] INCREMENT BY [Number] MINVALUE [Number] NOCACHE NOCYCLE;

NoCache will avoid skipping values due to Oracle creating any numbers in advance. NoCycle avoids the number repeating itself. I generally name my sequence after the field it’s being used for, with an _SEQ suffix.

Putting the sequence number into the field is done by a trigger:

CREATE TRIGGER [TriggerName]
before insert on [TableName]
for each row
begin
select [SequenceName].nextval into :new.[ColumnName] from dual;
end;

Easy!

There is a catch. While in Access or SQL Server there are easy ways to find out the value of the AutoNumber/Identity field in the row you just added (through @@IDENTITY in SQL Server or in Jet 4.0 or later), this isn’t so easy in Oracle.

You can query the column’s MAX value or the sequence CURRVAL afterwards — but this isn’t reliable if yours is not the only process inserting data. Would it work if you placed your code in a transaction? I’m not convinced.

So the trigger method should only be used when you don’t need to know the number entered straight afterwards. If you need to know, then forget the trigger — just select your Sequence value first, then use that in your insert(s).

If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.

1 thought on “Access-style Autonumber fields in Oracle

  1. Chrisotpher Cundill

    Hi there,

    Just thought I’d let you know that quering the column’s MAX value to get the value of the newly-inserted id within the right type of transaction (i.e. an exclusive lock) should be fine. I’ve certainly used this before.

    Anyhow, you’d think that such a common problem – i.e. needing a unique number for a primary key (identity field) – would have a more succint solution by now. Come on Oracle – add an autonumber feature into your next release! Save us some lines of code…

    Regards,

    Chris Cundill.

Comments are closed.