Issues with DbFunctions.CreateDateTime

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
efcoyote
Posts: 5
Joined: Tue 19 Aug 2014 12:22

Issues with DbFunctions.CreateDateTime

Post by efcoyote » Thu 15 Jan 2015 08:49

I have some simple linq queries, which results in illegal SQL code.

Code: Select all

var query = from e in base.Context.Event
            let startTime = e.TimeStart ?? DateTime.Now
            select DbFunctions.CreateDateTime(startTime.Year, startTime.Month, startTime.Day, startTime.Hour, startTime.Minute, startTime.Second);
ORA-30076: invalid extract field for extract source

Also the using of OracleFunctions-method will not help:

Code: Select all

var query = from e in base.Context.Event
            let startTime = e.TimeStart ?? OracleFunctions.CurrentTimestamp().Value
            select DbFunctions.CreateDateTime(startTime.Year, startTime.Month, startTime.Day, startTime.Hour, startTime.Minute, startTime.Second);
ORA-01830: date format picture ends before converting entire input string

I try also other DbFunctions-methods and OracleFunctions-method regarding to datetime but getting all kinds of Oracle-exceptions. Seems to be that these functions are not tested at all.

PS: EntityFramework 6.1.1 / Devart Oracle 8.4.215.0 / Oracle Server 11g (11.2.0.3.0)

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Issues with DbFunctions.CreateDateTime

Post by Shalex » Fri 16 Jan 2015 14:41

We can reproduce the "ORA-30076: invalid extract field for extract source" error only in this case with dotConnect for Oracle v8.4.333:

Code: Select all

var query = from e in base.Context.Event
            // let startTime = e.TimeStart ?? DateTime.Now
            let startTime = DateTime.Now
            select DbFunctions.CreateDateTime(startTime.Year, startTime.Month, startTime.Day, startTime.Hour, startTime.Minute, startTime.Second);
We will notify you when the issue is fixed.
efcoyote wrote:

Code: Select all

var query = from e in base.Context.Event
            let startTime = e.TimeStart ?? DateTime.Now
            select DbFunctions.CreateDateTime(startTime.Year, startTime.Month, startTime.Day, startTime.Hour, startTime.Minute, startTime.Second);
ORA-30076: invalid extract field for extract source

Code: Select all

var query = from e in base.Context.Event
            let startTime = e.TimeStart ?? OracleFunctions.CurrentTimestamp().Value
            select DbFunctions.CreateDateTime(startTime.Year, startTime.Month, startTime.Day, startTime.Hour, startTime.Minute, startTime.Second);
ORA-01830: date format picture ends before converting entire input string
We cannot reproduce these problems with dotConnect for Oracle v8.4.333. Please tell us
a) the value of the e.TimeStart property when you are running the code
b) the datatype of the column in database which corresponds to e.TimeStart
c) enable dbMonitor and specify the corresponding SQL when errors occur

efcoyote
Posts: 5
Joined: Tue 19 Aug 2014 12:22

Re: Issues with DbFunctions.CreateDateTime

Post by efcoyote » Tue 20 Jan 2015 16:19

a) the critical rows are when e.TimeStart is null (date expression is used)
b) the datatype of the column is "DATE"
c) here are the corresponding SQL outputs from Context.Database.Log without "e.TimeStart ?? ":

first query

Code: Select all

SELECT 
TO_TIMESTAMP(EXTRACT(YEAR FROM (CURRENT_DATE)) || '-' || EXTRACT(MONTH FROM (CURRENT_DATE)) || '-' || EXTRACT(DAY FROM (CURRENT_DATE)) || ' ' || EXTRACT(HOUR FROM (CURRENT_DATE)) || ':' || EXTRACT(MINUTE FROM (CURRENT_DATE)) || ':' || TO_NUMBER(EXTRACT(SECOND FROM (CURRENT_DATE))), 'yyyy-mm-dd hh24:mi:ss') AS C1
FROM DUAL
second query

Code: Select all

SELECT 
TO_TIMESTAMP(EXTRACT(YEAR FROM (CURRENT_TIMESTAMP)) || '-' || EXTRACT(MONTH FROM (CURRENT_TIMESTAMP)) || '-' || EXTRACT(DAY FROM (CURRENT_TIMESTAMP)) || ' ' || EXTRACT(HOUR FROM (CURRENT_TIMESTAMP)) || ':' || EXTRACT(MINUTE FROM (CURRENT_TIMESTAMP)) || ':' || TO_NUMBER(EXTRACT(SECOND FROM (CURRENT_TIMESTAMP))), 'yyyy-mm-dd hh24:mi:ss') AS C1
FROM DUAL

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Issues with DbFunctions.CreateDateTime

Post by Shalex » Fri 23 Jan 2015 11:50

We have reproduced both errors. We will investigate the issues and notify you about the result.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Issues with DbFunctions.CreateDateTime

Post by Shalex » Thu 29 Jan 2015 15:53

The bug with using the Hour, Minute, and Second canonical functions with DATE arguments is fixed in the latest (8.4.342) build of dotConnect for Oracle.

Post Reply