How to check rows that got inserted today

I got a reaquest where a user wanted to check all the records created today or between a date range. This was not achievable from the application perspective as it was not having any column like that.

Luckily I got reference to one of the reply from Pelle in a forum regarding this -

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_TIMESTAMP function.

select ID from SCHEMA.TABLE where scn_to_timestamp( ora_rowscn ) - 0 > sysdate - 1;

But there can be a situation where we insert the data imported from another instance. Since it will have an invalid SCNs, it will raise an exception. So, here is a small function that 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;
/

and then you can use the following SQL to see the records added to the table but be advised that this will give you not only rows inserted today, but also the updated ones.

select ID from SCHEMA.TABLE where rowdate( ora_rowscn ) > trunc( sysdate );