Here's a bizarre one for you. I'm running a simple query against a table that looks like this:
select expiration_date from Tbl where expiration_date > sysdate - 1
In SQL Developer, this is returning 35 rows. A VB.NET application, calling the exact same SQL statement, using the OraOLEDB.Oracle provider only returns 30 rows.
Here's my connection string (broken up for legibility):
Provider=OraOLEDB.Oracle;
Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)
(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=xe)));
User Id=rps;
Password=*******;
OLEDB.NET=True;
And here's sample code (we're in the ProcessRequest section of a generic ASHX handler):
Dim conn As OleDbConnection = New OleDbConnection(ConfigurationManager.AppSettings("ConnectionString"))
conn.Open()
Dim cmd As OleDbCommand = conn.CreateCommand()
Dim sql As String = "select expiration_date from Tbl where expiration_date > sysdate - 1"
cmd.CommandText = sql
Dim reader As OleDbDataReader = cmd.ExecuteReader()
context.Response.ContentType = "text/plain"
context.Response.Write("RESULTS: " & vbCrLf)
Dim i As Int32 = 1
While reader.Read()
context.Response.Write(i.ToString() & ". " & reader.GetValue(0).ToString() & vbCrLf)
i = i + 1
End While
reader.Close()
cmd.Dispose()
conn.Close()
conn.Dispose()
If I change the query to say ... where expiration_date < sysdate + 15 it works fine.
What on earth is going on? This is ODAC 11.2.0.3 if it helps.
Update 1: I removed ODAC 11.2.0.3 and upgraded to ODAC 12.1.0.1 (ORAOLEDB12.DLL). Behavior unchanged.
Update 2: Well this is interesting. I switched to ODP.NET just to see what would happen. Guess what? SAME EXACT RESULTS. I cannot even blame the provider anymore. I need a drink.
Update 3: Finally had the bright idea to see what dates were being excluded. There are five expiration_dates that occur in the month of November after today that should be included in the results. Those are precisely the ones that are excluded. So sysdate-1 is not doing what you'd expect when called from C#. Time for another drink.
I'm an idiot. Oracle SQL Developer runs everything in a transaction, and I wasn't COMMITting anything. I hang my head in shame and can only hope this will serve as a warning to others.