Last inserted row

[aniketk]

How can i get last inserted record. if i don't have any date column in table?

[rski]

What about using MAX(ROWID) in the query.

[magicwand]

aniketk,

you can't.


rski,

unfortunately your recommendation will not work:

SQL> create table test (a number, b varchar(32));

Table created.

SQL> insert into test select rownum, 'row#' ||rownum from all_objects where rownum < 1001;

1000 rows created.

SQL> select rowid, a,b from test where rowid = (select max(rowid) from test);

ROWID                       A B
------------------ ---------- --------------------------------
AAAR/2AAEAAAAC2ACF       1000 row#1000

SQL> delete test where a between 3 and 995;

993 rows deleted.

SQL> insert into test values (20000, 'Last ins. row');

1 row created.

SQL> select rowid, a,b from test where rowid = (select max(rowid) from test);

ROWID                       A B
------------------ ---------- --------------------------------
AAAR/2AAEAAAAC2ACF       1000 row#1000

SQL>

[rski]

Magic you're of course right .

What about trying to compare SCN of rows? Ankietk did you commit every? Or do some miltiple inserts and then commit it?

[magicwand]

rski,

I'm afraid, SCN wouldn't work either. It's boud to the transaction, not to the modification of a single row.
Although Log Mining might lead to a result in an isolated case, it is for sure not feasable for an application transaction management.
(And committing after each insert would be the perfect way to destroy the scalability of every application)

There are two ways I see to accomplish this without having to change the application:

1.) Adding a TIMESTAMP column with an INSERT trigger to the table
2.) Audit the inserts on this table

[rski]

Magic I know it is bounded to comit that's why i've asked about multiple DMLs.
I think that aniketk has already some data and need help with it.