5 Replies Latest reply: Oct 4, 2011 10:30 PM by 890370 RSS

    Thin JDBC Driver is missing some rows from resultset

    890370

      Hi All
      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.

      Any ideas?

      SQL is

      SELECT
      T.CTLG_ITM_ID as catalogItemIds,
      T.INV_STAT_TYP_ID,
      T.OORD_DT,
      T.LST_UPDT_DTTM,
      T.CTLG_ITM_SELL_TYP_ID,
      P.OORD_IND,
      C.BKORD_ELIG,
      NTILE(300) over (ORDER BY T.CTLG_ITM_ID) AS bucketId
      FROM
      INV_STAT_ROLLUP_T T,
      PRD_T P,
      CTLG_T C,
      CTLG_TREE_PATH_T TP
      WHERE
      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?

      Thanks
      Saroj