Skip to content

Instantly share code, notes, and snippets.

@jsbattig
Created April 25, 2016 20:19
Show Gist options
  • Save jsbattig/98acae6aef483f15bc13fb4fe7f23e45 to your computer and use it in GitHub Desktop.
Save jsbattig/98acae6aef483f15bc13fb4fe7f23e45 to your computer and use it in GitHub Desktop.
Issue trying to pull deltas in Oracle using date field
using System;
using System.Data;
using Oracle.DataAccess.Client;
namespace oradatetest
{
class Program
{
static void Main(string[] args)
{
using (var conn1 = new OracleConnection("Data Source=P11D;User Id=ceenv64;Password=ce1099;"))
{
conn1.Open();
/* First let's clean up out table if we run this test before.
CREATE TABLE CNVLOG
( "USRNAME" VARCHAR2(50 BYTE) NOT NULL ENABLE,
"LOGCODE" NUMBER(10,0) NOT NULL ENABLE,
"LOGDATE" DATE NOT NULL ENABLE,
... other fields here...
) */
using (var qr1 = new OracleCommand()
{
Connection = conn1,
CommandText = "delete from CNVLOG where LOGCODE=9999 and extract(day from LOGDATE) = extract(day from :CUR_DATE)",
CommandType = CommandType.Text
})
{
qr1.Parameters.Add("CUR_DATE", DateTime.Now);
qr1.ExecuteNonQuery();
}
/* Now let's add one record using current date and time from .NET */
using (var qr1 = new OracleCommand()
{
Connection = conn1,
CommandText = "insert into CNVLOG (USRNAME, LOGCODE, LOGDATE) values ('JSBATTIG', 9999, :LOGDATE)",
CommandType = CommandType.Text
})
{
qr1.Parameters.Add("LOGDATE", DateTime.Now);
qr1.ExecuteNonQuery();
}
/* Let's create a query, and select ONLY records matching the current day. This is key, you could do the same test here selecting
data with time resolution to a millisecond if you want */
using (var qr2 = new OracleCommand()
{
Connection = conn1,
CommandText = "select USRNAME from CNVLOG where LOGCODE=9999 and extract(day from LOGDATE) = extract(day from :CUR_DATE)",
CommandType = CommandType.Text
})
{
qr2.Parameters.Add("CUR_DATE", DateTime.Now);
/* Let's effectively open the query */
using (var reader = qr2.ExecuteReader())
{
while (reader.Read())
{
/* We will print here the first column of the result set for the ONE record we fetched */
Console.WriteLine(reader.GetString(0));
/* Now, within the loop, we insert one more record. DAY will be the same day we opened the query, but this record
will not be seen by the reader when we loop thorough. As I said before, this will be equally valid even if our
resolution is milliseconds.
In essence, this record won't be fetched if we start fetching data from resolution + 1 ( tomorrow in our case ) */
using (var qr3 = new OracleCommand()
{
Connection = conn1,
CommandText = "insert into CNVLOG (USRNAME, LOGCODE, LOGDATE) values ('JSBATTIG-2', 9999, :LOGDATE)",
CommandType = CommandType.Text
})
{
qr3.Parameters.Add("LOGDATE", DateTime.Now);
qr3.ExecuteNonQuery();
}
}
}
}
}
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment