CODEWITHSUNDEEP
sqloracle-database
emx
emx

Reputation: 1313

Oracle incremental query in a table with no ID or timestamp

I need to regularly extract data from an Oracle 11 table using sqlplus. For example, I need every day to extract the new rows inserted into that table.

On a table with a primary key such as RECORD_ID (assuming it is inserted incrementally), that query would be:

SELECT * from TABLE WHERE RECORD_ID > &LAST_RECORD_ID_FROM_PREVIOUS_QUERY

On a table with a RECORD_DATE timestamp, this could similarly done like:

SELECT * from TABLE WHERE RECORD_DATE > &LAST_RECORD_DATE_FROM_PREVIOUS_QUERY

My question is: how do you do this when you have no timestamps and no incremental column you could use? Can this be achieved with ROWID?

Upvotes: 0

Views: 481

Answers (2)

emx
emx

Reputation: 1313

As I suspected there isn't any easy solution. It has to be one of:

  • Adding an identity or timestamp column
  • Do a diff using flashback
  • Add a trigger on insert on the table

Unfortunately none of which is practical in my environment. Case closed!

Upvotes: 0

MT0
MT0

Reputation: 167972

One way would be to enable flashback and then you could do:

SELECT * FROM table_name
MINUS
SELECT * FROM table_name AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '1' DAY;

Upvotes: 2

Related Questions