Re: How to get the last insert id

On Sep 7, 12:16 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> On Sep 7, 10:33 am, Norbert Pürringer <thalio..._at_graffiti.net> wrote:
>
> > Hello,
>
> > does anyone know how to get the id of the last inserted dataset in
> > Oracle?
>
> > SQL Server does have Scope_Idendity, MySQL has LastInsertID
>
> > Thanks,
> > Norbert
>
> Usually one uses MAX() on the id column. There is no other way,
> unless you're running the session performing the insert and you're
> using a sequence. You can then use
>
> select <sequence_name>.currval from dual;
>
> to return that last ID. You can also use the RETURNING clause of the
> insert statement to return the ID of the currently inserted row. For
> example from SQL*Plus you can do this:
>
> variable empid number
>
> INSERT INTO employees
> (employee_id, last_name, email, hire_date, job_id, salary)
> VALUES
> (employees_seq.nextval, 'Doe', 'john...._at_oracle.com',
> SYSDATE, 'SH_CLERK', 2400)
> RETURNING employee_id into :empid;
>
> print empid
>
> and have the last employee_id you inserted returned to you. This does
> not work across sessions, so if someone else is also inserting data to
> the employees table using the same sequence at the same time you won't
> see that ID until it's committed. Thus to get the relatively absolute
> last ID (meaning the last inserted and committed ID value) you'd query
> the table with the MAX() function. Remember you can't 'see' values
> from uncommitted inserts or updates executed by other sessions. The
> best you can do is to query the table and retrieve the greatest ID
> value. And even that may be misleading as that record may be in the
> process of deletion as you query the table:
>
> Session 1 inserts a new employee:
>
> SQL> INSERT
> 2 INTO EMP VALUES
> 3 (8010, 'SMITH', 'CLERK', 7902,
> 4 TO_DATE('17-DEC-1999', 'DD-MON-YYYY'), 900, NULL, 20);
>
> 1 row created.
>
> SQL> commit;
>
> Commit complete.
>
> [we pause here to allow session 2 to retrieve the max(empno) and then
> delete the newly inserted record but not yet commit the change]
>
> Session 2:
>
> SQL> select max(empno) from emp;
>
> MAX(EMPNO)
> ----------
> 8010
>
> SQL> delete from emp where empno = 8010;
>
> 1 row deleted.
>
> [again we pause to allow session 1 to get the 'max' empno]
>
> Session 1:
>
> SQL> select max(empno) from emp;
>
> MAX(EMPNO)
> ----------
> 8010
>
> SQL>
>
> [we now commit the delete in session 2 and return the current 'max' as
> this session knows it]
>
> Session 2:
>
> SQL> commit;
>
> Commit complete.
>
> SQL> select max(empno)
> 2 from emp;
>
> MAX(EMPNO)
> ----------
> 7934
>
> SQL>
>
> We don't get the same value. Which may be why Oracle hasn't decided
> to provide such a functionality.
>
> David Fitzjarrell

Nice example David.

Norbert, the idea of last row in a heap table does not make a lot of sense since by definition a heap table is an unordered collection.

You will need a timestamp or sequence number to identify the rows so that you can find a 'most recently inserted' or 'highest value' row. Then you might be able to use select for update or a user lock to serialize table access (a topic Daniel mentioned) but you have to keep the read consistency model in mind as David showed and you can ruin the scalability of your application if you do not design your process carefully.

HTH -- Mark D Powell -- Received on Fri Sep 07 2007 - 14:47:47 CDT