Take the 2-minute tour ×

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

share|improve this question
add comment

4 Answers

up vote 19 down vote accepted

insert ... returning.

   s2 number;
   insert into seqtest2(x) values ('aaa') returning seq into s2;

"seq" here refers to the name of the column whose value you want to store into variable s2.

in python:

curs.prepare("insert into seqtest2(x) values('bbb') returning seq into :x")
curs.execute(None, x=myseq)
print int(myseq.getvalue())
share|improve this answer
add comment

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)
    new_id names.id%TYPE;
    SELECT names_sequence.nextVal INTO new_id FROM dual;
    INSERT INTO names(id, name) VALUES(new_id, new_name);
    new_name_id := new_id;

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.

share|improve this answer
add comment

This has a comprehensive solution.

share|improve this answer
it's a comprehensive summary of sequences, but doesn't have the answer to the question, i.e. "insert ... returning". –  Mark Harrison Dec 12 '08 at 21:09
add comment

My answer in C#, considering that table "testtable" has an "ID" collumn as PK with an autoinc sequence and field "testname" is a varchar field.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Oracle.DataAccess.Client;
using System.Data;

namespace ConsoleApplication3
class Program

    public static void Main(string[] args)

        OracleConnection cn = new OracleConnection("your connection string here");

        string sql = "INSERT INTO testtable(testname) VALUES('testing2') RETURNING id INTO :LASTID";
        OracleParameter lastId = new OracleParameter(":LASTID", OracleDbType.Int32);

        lastId.Direction = ParameterDirection.Output;

        using (OracleCommand cmd = new OracleCommand(sql, cn))
            Console.WriteLine("Last ID: " + lastId.Value.ToString());

share|improve this answer
add comment

Your Answer


