I have to make C application with OCI which retrieve new rows from database, I mean: rows added in time from last session to current. ora_rowscn is not solution: this value is changed for blocks so that few different rows can have same SCN.
On example I have table with dates:
- 03.05.2015
- 05.05.2015
- 07.05.2015
I can make structure:
struct Bounds {
Timestamp start, end;
};
03.05.2015 is as start and 07.05.2015 is as end.
Checking rows after Bounds.end is simple. But it could be some delay or transaction after my last query and I can have new values.
- 03.05.2015
- 04.05.2015
- 05.05.2015
- 06.05.2015
- 07.05.2015
These new rows count can be detected by query (STARD and END are values of structure):
select count(*) from logs where log_time > START and log_time < END
Then I have 3 rows and 5 after it. My application have only read persmission.
Oracle database is concurrent environment. So generally there is no way how to tell what is the "last" inserted row because there technically is no last inserted row.
AFAIK you have two options
Use Continuous Query Notification. This bypasses SQL query interface and uses special API dedicated for this particular purpose.
The other option is to query current databases SCN and start a transaction with this SCN. See OCIStmtExecute, this function has two parameters snap_in/snap_out. Theoretically you can use them to track you view on databases SCN. But I'm not sure I never used that.
In Oracle readers do not block writers and vice-versa. So a row inserted on 06.05.2015 (but commited on 08.05.2015) will be visible AFTER 7.5.2015. Oracle is parallel database and it does not guarantee any serialization.
Maybe if you used row level ora_rowsncn, then it would work. But this requires redefinition of the source table.