I am faced with a peculiar problem.
When I fire a SQL query using Toad, I get correct results but firing the same query using JDBC Driver - ojdbc14.jar (ORacle 11g R1) - we are getting lesser number of records.
Our SQL pulls around 300k of records and buckets them based on some column value into different chunks. We use NTILE function and it shows right results in TOAD.
Using same sql with JDBC, We are losing 100 rows per bucket.
T.CTLG_ITM_ID as catalogItemIds,
NTILE(300) over (ORDER BY T.CTLG_ITM_ID) AS bucketId
T.CTLG_ITM_ID = TP.CTLG_ITM_ID AND
TP.PAR_CTLG_ITM_ID = P.CTLG_ITM_ID AND
C.BRD_CD = '10'
As per toad, each bucket should get us 1038 records but Thin JDBC Driver is fetching us only 898 records.
Any idea why this discrepancy?
When I fire a SQL query using Toad, I get correct results but firing the same query using JDBC Driver - ojdbc14.jar (ORacle 11g R1)
Seeing the downloads page:
There IS no ojdbc14.jar for Oracle 11g. The minimum is Java 5. So correct me if I'm wrong, but you seem to be using a very old driver here. I would try a newer one if I were you.
An assumption is wrong. Some possible examples. There could be others.
- Not using the same database
- Uncommited data
- The SQL is not the same.
- The 'way' you verify the result in java is incorrect.