Hello Tom,

I just wanted to ask you if the problem that we are facing could even be related to Oracle, as we can't find a reason and we don't have access to the sources to check how the source objects are created.

Is it even possible that a select like this one is returning duplicates, when these duplicates are actually the same record but with the actual and the previous values of a particular updated field?

I mean that if you look for that record by ID, you only find the updated one (obviously), but when throwing the query we get both values, the new and the old values. I have been reading about the ora_rowscn and the flashback queries, and it makes me think that is could be related, but I don't understand how or if it is even possible without using the AS OF clause.

SELECT t1.SOME_FIELDS
      ,to_number(t1.ORA_ROWSCN) ORAROWSCN
      ,t1.MORE_FIELDS
FROM TABLE t1
WHERE (
 t1.START_DATE < TO_DATE($PARAMETER1, 'YYYYMMDD')
 OR 
        t1.START_DATE > TO_DATE($PARAMETER2, 'YYYYMMDD')
      )
      AND 
      (
        t1.END_DATE < TO_DATE($PARAMETER1, 'YYYYMMDD')
        OR 
        t1.END_DATE > TO_DATE($PARAMETER2, 'YYYYMMDD')
      )
      AND t1.ORA_ROWSCN >= 1
ORDER BY t1.ID

If you think that it could be related to Oracle I'll be glad to paste more code or whatever you ask, but I really don't know where to start right now. Otherwise, excuse me.

NOTE 1: We discarted the possibility of that record being updated during the extraction
NOTE 2: We don't know the purpose of using the ORA_ROWSCN in the filter, the query was created time ago and undocumented :(

Thanks!