Re: How to select the last item's ID

From: Galen Boyer <galenboyer_at_hotpop.com>
Date: 4 Sep 2001 11:32:11 -0500
Message-ID: <uwv3eiwgi.fsf@verizon.net>

On Tue, 4 Sep 2001, pritino2020_at_yahoo.com.tw wrote:

> Because there might be another SQL script also inserting into
> this table at the same time, it is not guaranteed that I will
> get the correct ID by selecting right after inserting.  

You misunderstand sequences. A sequence is an object in the database that gets called by a session. Oracle makes sure that the sequence gets incremented correctly for each sequence.

Try this.

,----
| SQL> create sequence test1;
|
| Sequence created.

`----

Now, open two sqlplus windows. I have mine named sq11 and sql2. In the order I've given, see how the two sessions got the next sequence number available?

,----
| SQL1> select test1.nextval from dual;
|
| NEXTVAL
| ----------
| 1
|
| SQL2> select test1.nextval from dual;
|
| NEXTVAL
| ----------
| 2
|
| SQL1> select test1.nextval from dual;
|
| NEXTVAL
| ----------
| 3
|
| SQL2> select test1.nextval from dual;
|
| NEXTVAL
| ----------
| 4

`----

> Is there a function for this

1. Assumes you are using standard out to capture output.

    LOGIN

        insert/update tbl set sequence_fld = seq.nextval;
        select seq.currval from dual;

    LOGOUT 2) If PL/SQL, then

    DECLARE
            next_seq integer;
    BEGIN

            insert/update tbl set sequence_fld = seq.nextval;
            next_seq := select seq.currval from dual;
            Do something with next_seq;

    END;

-- 
Galen Boyer
It seems to me, I remember every single thing I know.

Received on Tue Sep 04 2001 - 11:32:11 CDT