This discussion is archived
3 Replies Latest reply: Nov 16, 2012 9:15 AM by Mark Williams-Oracle RSS

OracleDataReader returns only one row

785962 Newbie
Currently Being Moderated
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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Employee ACE
    Currently Being Moderated
    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?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points