469,293 Members | 1,319 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,293 developers. It's quick & easy.

latest row inserted

Hi friends,

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

regards
rohit
Mar 25 '08 #1
10 7970
ok.......need help

thanks
bye
Mar 25 '08 #2
amitpatel66
2,367 Expert 2GB
ok.......need help

thanks
bye
For latest row, you can use MAX(ROWID)
Mar 25 '08 #3
For latest row, you can use MAX(ROWID)

where ROWID > 20

doesn't meant that the selected rows are inserted by today
Mar 25 '08 #4
amitpatel66
2,367 Expert 2GB
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.
Mar 26 '08 #5
Saii
145 Expert 100+
Do you have any date column in your table that stores this information?
Mar 26 '08 #6
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.

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.

Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE function rowdate( rowscn in number )
  2. return date
  3. as
  4.   l_rowts  timestamp;
  5.   bad_scn  exception;
  6.   pragma exception_init( bad_scn, -8181 );
  7. begin
  8.        l_rowts := scn_to_timestamp( rowscn );
  9.        return cast( l_rowts as date);
  10.        exception when bad_scn then
  11.                      return null;
  12. end;
  13. /
  14.  
  15.  
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.
Mar 26 '08 #7
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....
Mar 27 '08 #8
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.

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.

Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE function rowdate( rowscn in number )
  2. return date
  3. as
  4.   l_rowts  timestamp;
  5.   bad_scn  exception;
  6.   pragma exception_init( bad_scn, -8181 );
  7. begin
  8.        l_rowts := scn_to_timestamp( rowscn );
  9.        return cast( l_rowts as date);
  10.        exception when bad_scn then
  11.                      return null;
  12. end;
  13. /
  14.  
  15.  
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....
Mar 27 '08 #9
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.

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.

Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE function rowdate( rowscn in number )
  2. return date
  3. as
  4.   l_rowts  timestamp;
  5.   bad_scn  exception;
  6.   pragma exception_init( bad_scn, -8181 );
  7. begin
  8.        l_rowts := scn_to_timestamp( rowscn );
  9.        return cast( l_rowts as date);
  10.        exception when bad_scn then
  11.                      return null;
  12. end;
  13. /
  14.  
  15.  
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_timestamp( ora_rowscn ) - 0 > sysdate - 1

The function "scn_to_timestamp" returns "systimestamp" type data.
And if you do:
select systimestamp - 1 from dual
it returns sysdate type data.
Mar 27 '08 #10
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_timestamp will give error:

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

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

@rohitbasu77
Jan 27 '12 #11

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by sajithamol | last post: by
reply views Thread by Gabriel Genellina | last post: by
8 posts views Thread by Tony Toews [MVP] | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Geralt96 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.