question on automating sequence numbers

Coming from an Informix/Sybase/SQL Server background, I'm spoiled by having the Serial / Identity data type to use as IDs in tables.

In searching Usenet, I found that Oracle has 'sequences', which allow an application to get a unique number.

Unfortunately, the logic to use the sequence must be coded into each application that wants to insert into one of these tables. I would like to shield this behavior from my application developers.

I could do this in a trigger, which would take

        insert into users (fname, lname) values ("Larry", "Ellison")

and translate that into:

        insert into users (id, fname, lname) values (1234, "Larry", "Ellison")

but there doesn't seem to be a way to return the user id back to the application. Sybase/SQL Server get around this by setting a global variable called @@IDENTITY which can then be selected; Informix is similarly simple.

Anybody know how to automate this functionality such that it will be in place for all applications which may use the database?

Thanks in advance,

• robert.

p.s. Please send a copy of your reply via email, since it seems to take a while for dejanews to get the current articles and we don't yet have a local news server.

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet