ODP.NET

I reported this on the github issues for .net examples, but wanted to point it out here in case Oracle reads these forums first. I couldn't find where to actually send any sort of bug report.

I am testing time and date code and encountered an issue when trying to fetch TIMESTAMP WITH LOCAL TIMEZONE oracle column type to DateTimeOffset CLR type. I am working with a database first approach and according to the Oracle Documentation the TIMESTAMP WITH LOCAL TIMEZONE should be mapping to DateTimeOffset. When testing with inserting data, it works as expected.

When fetching data in the sample code below with context.TimeTesting.ToList(), an InvalidCastException is thrown.

CREATE TABLE TIME_TESTING 
( 
ID NUMBER GENERATED ALWAYS AS IDENTITY NOT NULL 
, TIME_LOCAL TIMESTAMP(7) WITH LOCAL TIME ZONE 
, DATE_TYPE DATE 
, CONSTRAINT TIME_TESTING_PK PRIMARY KEY 
( 
ID 
) 
ENABLE 
);

[Table("TIME_TESTING")] 
public class TimeTesting 
{ 
[Key] 
[Column("ID")] 
[DatabaseGenerated(DatabaseGeneratedOption.Identity)] 
public int Id { get; set; } 

[Column("TIME_LOCAL")] 
public DateTimeOffset TimeLocal { get; set; } 

[Column("DATE_TYPE")] 
public DateTime DateType { get; set; } 
} 



string dateMarchTest = "2024-03-10T06:59:48.734Z"; 
var dateMarchOffset = DateTimeOffset.Parse(dateMarchTest); 

var dbContextFactory = _serviceProvider.GetService<IDbContextFactory<SMADbContext>>(); 
using (var context = dbContextFactory!.CreateDbContext()) 
{ 
TimeTesting marchTest = new TimeTesting() 
{ 
TimeLocal = dateMarchOffset, 
DateType = dateMarchOffset.LocalDateTime 
}; 

context.Add(marchTest); 
context.SaveChanges(); 
} 


using (var context = dbContextFactory!.CreateDbContext()) 
{ 
var test = context.TimeTesting.ToList(); 
}

Doing additional digging, I decompiled the Oracle.ManagedDataAccess DLL and looked at the GetDateTimeOffset(Int32) function that is throwing the exception. I have included that function below (omitted some of the unncessary code).

The main thing to notice is the expected column type to be OraType.ORA_TIMESTAMP_TZ_DTY (type 181) or OraType.ORA_TIMESTAMP_TZ (type 188) in the switch statement. Otherwise, InvalidCastException is thrown.

If you look at the documentation for Oracle Built-in Data Types, you will see that TIMESTAMP WITH LOCAL TIME ZONE is type 231 (OraType.ORA_TIMESTAMP_LTZ_DTY) and is not handled by the GetDateTimeOffset(Int32) within the function. I believe this may be a bug since the GetDateTimeOffset(Int32) should be handling this oracle data type.

public DateTimeOffset GetDateTimeOffset(int i) { 
if (ProviderConfig.m_bTraceLevelPublic) 
Trace.Write(OracleTraceLevel.Public, OracleTraceTag.Entry, OracleTraceClassName.OracleDataReader, OracleTraceFuncName.GetDateTimeOffset); 
try 
{ 
if (!this.m_bInternalCall && this.IsDBNull(i)) 
throw new InvalidCastException(OracleStringResourceManager.GetErrorMesg(ResourceStringConstants.DR_NULL_COL_DATA)); 
switch (this.m_readerImpl.m_accessors[i].m_internalType) 
{ 
case OraType.ORA_TIMESTAMP_TZ_DTY: 
case OraType.ORA_TIMESTAMP_TZ: 
//ommitted 
default: 
throw new InvalidCastException(); 
} 
} catch (Exception ex) 
{ 
OracleException.HandleError(OracleTraceLevel.Public, OracleTraceTag.Error, OracleTraceClassName.OracleDataReader, OracleTraceFuncName.GetDateTimeOffset, ex); 
throw; 
} finally 
{ 
  if (ProviderConfig.m_bTraceLevelPublic) 
Trace.Write(OracleTraceLevel.Public, OracleTraceTag.Exit, OracleTraceClassName.OracleDataReader, OracleTraceFuncName.GetDateTimeOffset); 
} 
}