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
select [SequenceName].nextval into :new.[ColumnName] from dual;
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).
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…