3 Replies Latest reply: Nov 16, 2012 11:15 AM by Mark Williams-Oracle RSS

    OracleDataReader returns only one row

    785962
      Hi guys,

      I'm trying to loop through a cursor. When I execute the command (denoted as sql - see below) directly in pl/sql I'm getting result set with more rows. But when I run this code, I'm getting only one row:

      using (OracleConnection conn = new OracleConnection(connstring))
      {
      conn.Open();
      string sql = "select close, ts from dpr@price where qot_id=2029543939 and ts>='" + start + "' and ts<='" + end + "'";
      using (OracleCommand comm = new OracleCommand(sql, conn))
      {
      using (OracleDataReader rdr = comm.ExecuteReader())
      {
      while (rdr.Read())
      {                               
      Console.WriteLine(rdr.GetOracleDecimal(0));
      }

      }
      }
      }

      The code doesn't throw any exception(at least not in an usual way; I mean it doesn't stop or writes stack trace). However I can see these lines by reader Object while debugging(I don't really think it is relevant here, but showing the output "just in case"):

      InitialLONGFetchSize = 'rdr.InitialLONGFetchSize' threw an exception of type 'System.NullReferenceException'

      InitialLOBFetchSize = 'rdr.InitialLOBFetchSize' threw an exception of type 'System.NullReferenceException'

      The results should have only types DateTime and Number (10,4). And the table has no fields with type lob or long.

      This is the table scheme:

      QOT_ID NUMBER
      TS DATE
      CLOSE NUMBER
      OPEN NUMBER
      HIGH NUMBER
      LOW NUMBER
      KASSE NUMBER
      VOLUME NUMBER
      CLOSE_BID NUMBER
      SPL_BEREINIGT VARCHAR2
      OPEN_INTEREST NUMBER
      TRADES NUMBER
      TURNOVER NUMBER
      HIGH_BID NUMBER
      LOW_ASK NUMBER

      The query is correct and the entries exist. The returned row seems to be random one... It is not the first or last row. I'm using ODT with ODAC 11. I have VS2012 and working under windows 7(32-bit). I'm connected to Oracle 10g .

      Any clue what am I doing wrong?

      Edited by: 782959 on 16.11.2012 06:22

      Edited by: 782959 on 16.11.2012 06:22
        • 1. Re: OracleDataReader returns only one row
          636190
          I would suspect the "TS" values within your select .... one would think that: TO_DATE(char [, fmt [, 'nlsparam' ] ]) would have to be done to the "start" and "end"...
          r,
          dennis
          • 2. Re: OracleDataReader returns only one row
            785962
            No, even when I hardcode it like

            select dpr_close, dpr_ts from dpr@price where dpr_qot_id=2029543939.

            i still get wrong number of rows (well this time i get 2 rows instead of 1) but in pl/sql Im getting 873.
            • 3. Re: OracleDataReader returns only one row
              Mark Williams-Oracle
              782959 wrote:
              Hi guys,

              I'm trying to loop through a cursor. When I execute the command (denoted as sql - see below) directly in pl/sql I'm getting result set with more rows. But when I run this code, I'm getting only one row:

              using (OracleConnection conn = new OracleConnection(connstring))
              {
              conn.Open();
              string sql = "select close, ts from dpr@price where qot_id=2029543939 and ts>='" + start + "' and ts<='" + end + "'";
              using (OracleCommand comm = new OracleCommand(sql, conn))
              {
              using (OracleDataReader rdr = comm.ExecuteReader())
              {
              while (rdr.Read())
              {                               
              Console.WriteLine(rdr.GetOracleDecimal(0));
              }

              }
              }
              }

              The code doesn't throw any exception(at least not in an usual way; I mean it doesn't stop or writes stack trace). However I can see these lines by reader Object while debugging(I don't really think it is relevant here, but showing the output "just in case"):

              InitialLONGFetchSize = 'rdr.InitialLONGFetchSize' threw an exception of type 'System.NullReferenceException'

              InitialLOBFetchSize = 'rdr.InitialLOBFetchSize' threw an exception of type 'System.NullReferenceException'

              The results should have only types DateTime and Number (10,4). And the table has no fields with type lob or long.

              This is the table scheme:

              QOT_ID NUMBER
              TS DATE
              CLOSE NUMBER
              OPEN NUMBER
              HIGH NUMBER
              LOW NUMBER
              KASSE NUMBER
              VOLUME NUMBER
              CLOSE_BID NUMBER
              SPL_BEREINIGT VARCHAR2
              OPEN_INTEREST NUMBER
              TRADES NUMBER
              TURNOVER NUMBER
              HIGH_BID NUMBER
              LOW_ASK NUMBER

              The query is correct and the entries exist. The returned row seems to be random one... It is not the first or last row. I'm using ODT with ODAC 11. I have VS2012 and working under windows 7(32-bit). I'm connected to Oracle 10g .

              Any clue what am I doing wrong?
              I'm in agreement with Dennis. You are passing a character string for the date using an unknown (at least to us) format. If the default format values (i.e. NLS_DATE_FORMAT) differ between client, server, and the actual value passed I would not be surprised to see "incorrect" results.

              Also, you are just gluing in values which opens up the possibility for SQL Injection. Any reason for not using parameters?