Oracle: How do I get the sequence number of the row just inserted?

Question

How do I get the sequence number of the row just inserted?

Answer 1

insert ... returning.

declare
   s2 number;
 begin
   insert into seqtest2(x) values ('aaa') returning seq into s2;
   dbms_output.put_line(s2);
 end;

in python:

myseq=curs.var(cx_Oracle.NUMBER)
curs.prepare("insert into seqtest2(x) values('bbb') returning seq into :x")
curs.execute(None, x=myseq)
print int(myseq.getvalue())

Answer 2

Edit: as Mark Harrison pointed out, this assumes that you have control over how the id of your inserted record is created. If you have full control and responsibility for it, this should work...

---

Use a stored procedure to perform your insert and return the id.

eg: for a table of names with ids:

PROCEDURE insert_name(new_name    IN   names.name%TYPE, 
                      new_name_id OUT  names.id%TYPE)
IS
    new_id names.id%TYPE;
BEGIN
    SELECT names_sequence.nextVal INTO new_id FROM dual;
    INSERT INTO names(id, name) VALUES(new_id, new_name);
    new_name_id := new_id;
END;

Using stored procedures for CRUD operations is a good idea regardless if you're not using an ORM layer, as it makes your code more database-agnostic, helps against injection attacks and so on.

Answer 3

This has a comprehensive solution.

Answer 4

If I had any credibility points, I'd have given points to Mark Harrison's solution. The RETURNING clause is definitely the way to go.

Answer 5

But this solution is more sophisticated and solid. The RETURNING clause is NOT supported from distributed queries. This stored procedure is working over a dlink!