I'm asked to help a developer figure out why the "random" data we should be getting from Oracle is exactly the same results each time you run it, rather than being actually random. SQL 2012, Oracle 11g, linked server is using OLE DB connector type.
When they run the following query directly in the Oracle DB, they get proper "random" results. Through the linked server in SQL, they'll always get the same rows (and I've verified this by running the linked server query myself).
select * from openquery(ORA_DB, 'select XX_NUMBER, XX_CODE, UNIT, XX_serial, XX_date, XX_area, building, location, XX_status, owner_code, qa_XX_no from qa_eligible_XX where qa_XX_no = 4 and owner_code <> ''G'' and last_trans_code = ''OL'' and XX_cycle = ''90 - 120 Periodic'' and XX_date < sysdate - 90 and rownum <= 200 order by dbms_random.value')
Note: I'm not an Oracle DBA but I work with one; he didn't see an issue with the Oracle part of the above query. Also, I edited a lot of stuff out and replaced with XX for privacy.
Each time we run this query in SSMS, we get the same 200 rows. I even connected from another SQL server that has the same linked server and ran this query, and got the same 200 rows (the same Oracle user is used for both connections). This leads me to believe Oracle may be caching the results; but somehow it's not doing this when the query's run in Oracle directly. Is there a way to force it to return new results through openquery? (so that the 200 rows it returns are a truly random sampling each time)