ORA-01891 with TIMESTAMP(6) WITH TIMEZONE column

[Eric_08]

I have an application that uses .NET Core 2.1 with DevArt 9.6.646 Oracle EF Core driver running RHEL 7.5. It connects to Oracle 12x database. In one environment, I get no errors from the application. In another environment, I get this error when the program tries to insert a record into a table that has TIMESTAMP(6) WITH TIMEZONE column:

Code: Select all

ORA-01891: Datetime/Interval internal

I'm at a loss why this error happens on one database server, but not on another. The database server where it works is 12.1, and where it fails, it's 12.2, although I believe I have another database server where it seems to work even against Oracle 12.2 database server, so I'm really not sure what's going on here. Could it be something with database server is configured or something in the way DevArt driver generates SQL statement that's different?

Thanks,
Eric

[Eric_08]

I forgot to mention that TIMESTAMP(6) WITH TIMEZONE database column is mapped to DateTimeOffset in .NET and the code attempts to put this value:

Code: Select all

DateTimeOffset.Now.AddDays(-1).Date

[Shalex]

1. Does the issue persist if you run your .NET Core application on a different platform (e.g.: Windows 10, Ubuntu, CentOS) but against the same Oracle 12.2 that generates "ORA-01891: Datetime/Interval internal"?

2. Try running this test code in a simple console application against Oracle 12.2 that fails:

Code: Select all

    var connection = new Devart.Data.Oracle.OracleConnection(@"Direct=True;Server=...;Sid=...;User Id=...;Password=...;license key=...;");
    connection.Open();

    var cmd = connection.CreateCommand("select :p from dual");
    cmd.Parameters.Add("p", OracleDbType.TimeStampTZ, DateTimeOffset.Now, ParameterDirection.Input);
    using (var reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            var result = reader.GetDateTimeOffset(0);
            Console.WriteLine(result);
        }
    }
    Console.ReadKey();

Can you reproduce "ORA-01891: Datetime/Interval internal" with this code as well?

3. Specify your connection string (mark confidential information with asterisks).

4. Execute "SELECT * FROM NLS_DATABASE_PARAMETERS" and compare NLS parameters of Oracle 12.2 that works with Oracle 12.2. that fails.