This discussion is archived
4 Replies Latest reply: Jan 8, 2006 7:14 AM by 27876 RSS

Retrieving Latest Value by Pl/SqL Command

472578 Newbie
Currently Being Moderated
Hi.
I have a query. I am working on a project. The main this is like this, there are some messages coming through air lines and they are getting stored in the database. Millions history messages are also stored already. I just wana pick that data, that is recently inserted in the table.

I want to know how to get the most latest record which is inserted in the table,
Can someone tell me the query please?

Thanks.
Sincerely,
ben
  • 1. Re: Retrieving Latest Value by Pl/SqL Command
    475167 Newbie
    Currently Being Moderated
    Hi,

    you have to use a timestamp (or sequenze) which is part of the row and filled when the row is inserted or a trigger which stores a timestamp (or sequenze) an the rows PK in another table.

    Dim
  • 2. Re: Retrieving Latest Value by Pl/SqL Command
    32685 Expert
    Currently Being Moderated
    Hello

    This is something that comes up quite a lot on the forums. The key points to understand are:
    1. Oracle does not automatically keep track of when rows are inserted into a table
    2. ROWIDs can be reused and are therefore not a reliable way to tell when a row was inserted as this example demonstrates:
    SQL> create table dt_test_rowid (id number, inserted date) tablespace av_datas;

    Table created.

    SQL> insert into dt_test_rowid values(1,sysdate);

    1 row created.

    SQL> insert into dt_test_rowid values(2,sysdate);

    1 row created.

    SQL> insert into dt_test_rowid values(3,sysdate);

    1 row created.

    SQL> insert into dt_test_rowid values(4,sysdate);

    1 row created.

    SQL> insert into dt_test_rowid values(5,sysdate);

    1 row created.

    SQL> delete from dt_test_rowid where id=3;

    1 row deleted.

    SQL> insert into dt_test_rowid values(6,sysdate);

    1 row created.

    SQL> commit;

    Commit complete.

    SQL> alter session set nls_date_format='hh24:mi:ss';

    Session altered.

    SQL> select id,inserted,rowid from dt_test_rowid;

            ID INSERTED ROWID
    ---------- -------- ------------------
             1 10:50:54 AAAuzdAAOAAAXACAAA
             2 10:50:56 AAAuzdAAOAAAXACAAB
             4 10:51:02 AAAuzdAAOAAAXACAAD
             5 10:51:05 AAAuzdAAOAAAXACAAE
             6 10:51:23 AAAuzdAAOAAAXACAAF

    SQL> insert into dt_test_rowid values(7,sysdate);

    1 row created.

    SQL> insert into dt_test_rowid values(8,sysdate);

    1 row created.

    SQL> select id,inserted,rowid from dt_test_rowid;

            ID INSERTED ROWID
    ---------- -------- ------------------
             1 10:50:54 AAAuzdAAOAAAXACAAA
             2 10:50:56 AAAuzdAAOAAAXACAAB
    8 10:52:20 AAAuzdAAOAAAXACAAC
             4 10:51:02 AAAuzdAAOAAAXACAAD
             5 10:51:05 AAAuzdAAOAAAXACAAE
             6 10:51:23 AAAuzdAAOAAAXACAAF
    7 10:52:16 AAAuzdAAOAAAXACAAG

    7 rows selected.
    Notice that at first it looked like rowid was being incremented for each new row that was inserted. However, when ids 7 and 8 were inserted, this was not the case.

    The most reliable way to find out when a row was inserted is to keep track of it yourself with a date or a timestamp column that is set when the row is inserted, possibly by a trigger. Alternatively, if you have a primary key being populated by a sequence, and that sequence does not cycle, you could use that.

    HTH

    David
  • 3. Re: Retrieving Latest Value by Pl/SqL Command
    423405 Newbie
    Currently Being Moderated
    David,
    Now that the table is created and there is no way to keep track of the date is there any other way we can know latest entered record in the table.


    TIA
    Shekar
  • 4. Re: Retrieving Latest Value by Pl/SqL Command
    27876 Newbie
    Currently Being Moderated
    You could:
    1). alter the table to add a sequence and a timestamp.
    2). populate these columns for existing records with sequence zero and timestamp in the past.
    3). add code to populate these columns.
    4). wait for at least one insert to happen.

    From then on, you would always know your last inserted record.