Re: How to get the last insert id

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.doe_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 Received on Fri Sep 07 2007 - 11:16:44 CDT