latest row inserted

[rohitbasu77]

Hi friends,

how to see all the inserted rows of a particular date of a table.

regards
rohit

[rohitbasu77]

ok.......need help

thanks
bye

[amitpatel66]

ok.......need help

thanks
bye

For latest row, you can use MAX(ROWID)

[rohitbasu77]

For latest row, you can use MAX(ROWID)

where ROWID > 20

doesn't meant that the selected rows are inserted by today

[amitpatel66]

where ROWID > 20

doesn't meant that the selected rows are inserted by today

If you use ROWID > 20 in your WHERE condition, the query will error out and will not work. I suggested you to make use of ROWID = MAX(ROWID) to get the last record inserted in to the table.

[Saii]

Do you have any date column in your table that stores this information?

[PelleFork]

If you are running 10g or higher, you can use the ORA_ROWSCN pseudocolumn, which can be translated to a timestamp using the SCN_TO_TIMESTAM P function.

Note, however, that data that was imported from another instance have invalid SCN:s and will raise an exception. So, I wrote this little function that has proven quite handy; it just returns NULL whenever there's an imported row with an invalid SCN.

CREATE OR REPLACE function rowdate( rowscn in number )
return date
as
  l_rowts  timestamp;
  bad_scn  exception;
  pragma exception_init( bad_scn, -8181 );
begin
       l_rowts := scn_to_timestamp( rowscn );
       return cast( l_rowts as date);
       exception when bad_scn then
                     return null;
end;
/

So, a "select * from mytab where rowdate( ora_rowscn ) > trunc( sysdate )" will return today's rows.

Note that this will give you not only rows inserted today, but also the updated ones.

[rohitbasu77]

Do you have any date column in your table that stores this information?

find pelle's reply and try to do more with SCN....
that what the result is....

[rohitbasu77]

If you are running 10g or higher, you can use the ORA_ROWSCN pseudocolumn, which can be translated to a timestamp using the SCN_TO_TIMESTAM P function.

Note, however, that data that was imported from another instance have invalid SCN:s and will raise an exception. So, I wrote this little function that has proven quite handy; it just returns NULL whenever there's an imported row with an invalid SCN.

CREATE OR REPLACE function rowdate( rowscn in number )
return date
as
  l_rowts  timestamp;
  bad_scn  exception;
  pragma exception_init( bad_scn, -8181 );
begin
       l_rowts := scn_to_timestamp( rowscn );
       return cast( l_rowts as date);
       exception when bad_scn then
                     return null;
end;
/

So, a "select * from mytab where rowdate( ora_rowscn ) > trunc( sysdate )" will return today's rows.

Note that this will give you not only rows inserted today, but also the updated ones.

Thanks Pelle .....
Its a good reply....

[rohitbasu77]

If you are running 10g or higher, you can use the ORA_ROWSCN pseudocolumn, which can be translated to a timestamp using the SCN_TO_TIMESTAM P function.

Note, however, that data that was imported from another instance have invalid SCN:s and will raise an exception. So, I wrote this little function that has proven quite handy; it just returns NULL whenever there's an imported row with an invalid SCN.

CREATE OR REPLACE function rowdate( rowscn in number )
return date
as
  l_rowts  timestamp;
  bad_scn  exception;
  pragma exception_init( bad_scn, -8181 );
begin
       l_rowts := scn_to_timestamp( rowscn );
       return cast( l_rowts as date);
       exception when bad_scn then
                     return null;
end;
/

So, a "select * from mytab where rowdate( ora_rowscn ) > trunc( sysdate )" will return today's rows.

Note that this will give you not only rows inserted today, but also the updated ones.

yes pelle, that will work. can you check with this one:

select * from mytab where scn_to_timestam p( ora_rowscn ) - 0 > sysdate - 1

The function "scn_to_timesta mp" returns "systimesta mp" type data.
And if you do:
select systimestamp - 1 from dual
it returns sysdate type data.

[manojkmaurya]

ORA_ROWSCN is not same as SCN

Thanks for the tip.

ORA_ROWSCN of very old row might be too old to be a valid SCN.
In that case, using scn_to_timestam p will give error:

ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIM ESTAMP", line 1

But, in case of a very recent row, this will work.

yes pelle, that will work. can you check with this one:

select * from mytab where scn_to_timestam p( ora_rowscn ) - 0 > sysdate - 1

The function "scn_to_timesta mp" returns "systimesta mp" type data.
And if you do:
select systimestamp - 1 from dual
it returns sysdate type data.