SQL & PL/SQL

Hi all,

Using DB 10.2.0.5, I've encountered a strange behaviour today while trying to compare ORA_ROWSCN with a previous SCN sored in a column.

SELECT
  h.id HID,
  h.ora_rowscn HSCN,
  o.id OID,
  o.scn OSCN,
  h.ora_rowscn-o.scn DIFF
FROM
  har@REMOTE h LEFT JOIN other o ON (h.id=o.id)
WHERE
  h.ORA_ROWSCN > o.scn
ORDER BY 5 desc ;


       HID       HSCN        OID       OSCN       DIFF
---------- ---------- ---------- ---------- ----------
...{snip}...
      4213 5093868663       4213 5092916724     951939

1157 rows

While casting o.scn to a number gives me another resultset (this one is correct):

SELECT
  h.id HID,
  h.ora_rowscn HSCN,
  o.id OID,
  o.scn OSCN,
  h.ora_rowscn-o.scn DIFF
FROM
  har@REMOTE h LEFT JOIN other o ON (h.id=o.id)
WHERE
  h.ORA_ROWSCN > to_number(o.scn)
ORDER BY 5 desc ;


       HID       HSCN        OID       OSCN       DIFF
---------- ---------- ---------- ---------- ----------
...{snip}...
     70949 5093865558      70949 5093847070      18488

2114 rows

I got the same result if I use NVL(o.scn,0) rather than TO_NUMBER(o.scn).

I can't find out why this happens. Obviously, the ">" condition doesn't match when the difference is too small.
AFAIK, ora_rowscn is a NUMBER while my "scn" column is a NUMBER(12) (which should be sufficient to store my DB or remote DB's SCN).

Does anyone has an explanation?

Thanks!