| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: question on automating sequence numbers
On Tue, 01 Jul 1997 20:35:03 -0600, rboucher_at_focalink.com wrote:
>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")
>
Assuming you created the sequence 'myseq', you can do one of the two:
....
insert into users (id, fname, lname)
values ( myseq.nextval, 'Larry', 'Ellison' );
select myseq.currval into :n from dual;
....
The select myseq.currval is like accessing @@identity.
Alternatively, you can automate the assignment of the sequence as such:
.....
SQL>
SQL> create table users
2 ( id number primary key, fname varchar2(20), lname varchar2(20) );
Table created.
SQL>
SQL> create or replace trigger users_bifer
2 before insert on users
3 for each row
4 begin
5 select myseq.nextval into :new.id from dual;
6 end;
7 /
Trigger created.
SQL>
SQL> insert into users ( fname, lname ) values ( 'Larry', 'Ellison' );
1 row created.
SQL>
SQL> select myseq.currval from dual;
CURRVAL
3
SQL>
SQL> select * from users;
ID FNAME LNAME
---------- -------------------- --------------------
3 Larry Ellison
.......
So, currval will get you what you want....
>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
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |