ORA-01891 with TIMESTAMP(6) WITH TIMEZONE column

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
Eric_08
Posts: 31
Joined: Wed 11 Jul 2018 21:50

ORA-01891 with TIMESTAMP(6) WITH TIMEZONE column

Post by Eric_08 » Thu 01 Aug 2019 12:57

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
Posts: 31
Joined: Wed 11 Jul 2018 21:50

Re: ORA-01891 with TIMESTAMP(6) WITH TIMEZONE column

Post by Eric_08 » Thu 01 Aug 2019 13:01

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
Site Admin
Posts: 9103
Joined: Thu 14 Aug 2008 12:44

Re: ORA-01891 with TIMESTAMP(6) WITH TIMEZONE column

Post by Shalex » Mon 05 Aug 2019 11:31

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.

Post Reply