3

Possible Duplicate:
How to get the last row in a table - Oracle 11g?

I would like to know how we can get the last inserted record values back as I want to retrieve and see the details that are inserted at last in the table

Community
  • 1
  • 1
user1673382
  • 39
  • 1
  • 1
  • 2
  • please post the table structure, do you have an Id field or a datetime that is added when records are added? – Taryn Sep 15 '12 at 12:45
  • What do you mean by "details"? Is this just a general "give me the most recent record"? Or do you want to know the values of defaulted or derived values at the point of insertion? – APC Sep 16 '12 at 05:42

2 Answers2

8
insert into mytable (...) values (...)
returning id into v_id;
Vikram Jain
  • 5,498
  • 1
  • 19
  • 31
  • 2
    I think you should use `.CURRVAL` to get the sequence value. – Guillaume Poussel Sep 15 '12 at 14:24
  • Both the answer and comment will only work in the same session as the insert, and I *think* that's not what the OP wanted. (Though that's partly based on the plural 'details' and not specifically asking for the ID, so quite prepared to be proven wrong!) – Alex Poole Sep 15 '12 at 14:44
  • @GuillaumePoussel - CURRVAL requires a second query whereas the RETURNING clause is more elegant. – APC Sep 16 '12 at 05:39
0

If you have a primary key:

select * from YOURTABLE
    where primary_key=(select max(primary_key) from YOURTABLE)
bonsvr
  • 2,262
  • 5
  • 22
  • 33
  • 1
    `rowid` is something completely different than "*rownumber*". But worse: `max(rowid)` will ***not*** give you the "latest" row. –  Sep 15 '12 at 14:25
  • Thank you. It was a typo and corrected. About `rowid`, I thought Oracle assigns a `rowid` to inserted rows according to their insert order. Because I sometimes use it and it gives the last inserted rows. Can you please explain? – bonsvr Sep 15 '12 at 14:32
  • `rowid` values relate to the physical storage of the data, and can be reused, e.g. if a row is delted and another later inserted - the newly inserted row will have a 'lower' ID than older ones. See [the documentation](http://docs.oracle.com/cd/E14072_01/server.112/e10592/pseudocolumns008.htm). – Alex Poole Sep 15 '12 at 14:40
  • Thanks for explanation. I work in an OLAP environment and my tables mostly don't change. i.e. there are not much deletes and reinserts. That's why I think `rowid` worked for me so far. – bonsvr Sep 15 '12 at 14:47
  • 2
    Even when you only insert into your table, there is absolutely no guarantee that rowid will reflect the insert order. If it worked for you so far, then you were simply lucky. –  Sep 15 '12 at 14:53
  • @bonsvr, this is usually not a good practise. In this particular case may work (not sure what exactly question author means), but by using MAX you will get wrong results if someone else inserts the record after your insert, but before getting MAX id (unless the isolation level is high enough, i.e. ISOLATION). Besides, that would work only for monotonical number keys. – WojtusJ Sep 15 '12 at 21:10