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

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.