How to read DateTimeOffset from OracleObject

[pakarjon]

We have need identical to viewtopic.php?f=1&t=38496, but now with OracleObject.

It supposedly does not have any method interface to read timestamp data out of its columns.

Is there or can there be an interface to read DateTimeOffset from object's column?

[Pinturiccio]

We have reproduced the issue when the object field of the Oracle type TIMESTAMP(6) WITH TIME ZONE returns as System.DateTime. We will investigate the possibility to return value of the OracleTimeStamp type for the TIMESTAMP(6) WITH TIME ZONE values from OracleObject instead of System.DateTime and post here when we get any results.

[Pinturiccio]

You can read datetime offset. Let's consider the following example:

Code: Select all

CREATE OR REPLACE TYPE SEMENB.TAddress AS OBJECT (
  Country VARCHAR2(30),
  City VARCHAR2(30),
  Street VARCHAR2(30),
  Apartment NUMBER,
  offset TIMESTAMP(6) WITH TIME ZONE
);

CREATE TABLE EmpObject (
  Code NUMBER PRIMARY KEY,
  Person VARCHAR2(40),
  Address TAddress,
  Job VARCHAR2(9)
);

INSERT INTO EmpObject (Code, Person, Address, Job) VALUES (1, 'SMITH', TAddress('UK', 'London', 'Street', 12, '07-AUG-2017 2:00:00 PM +6:00'), 'CLERK');

INSERT INTO EmpObject (Code, Person, Address, Job) VALUES (2, 'JONES', TAddress('USA', 'New York', 'Street', 418, '09-AUG-2017 2:00:00 PM -04:00'), 'MANAGER');

And use the following code:

Code: Select all

OracleConnection connection = new OracleConnection("your connection string");
OracleCommand command = new OracleCommand("SELECT * FROM EmpObject", connection);
connection.Open();
OracleDataReader dataReader = command.ExecuteReader();
int index = dataReader.GetOrdinal("Address");
while (dataReader.Read())
{
    OracleObject oraObj = dataReader.GetOracleObject(index);
    if (!oraObj.IsNull)
    {
        string country = (string)oraObj["Country"];
        string city = (string)oraObj["City"];
        string street = (string)oraObj["Street"];
        decimal Apartment = (decimal)oraObj["Apartment"];
        Console.WriteLine(oraObj["offset"].GetType()); //System.DateTime
        OracleTimeStamp offset = (OracleTimeStamp)oraObj.GetOracleValue("offset");
        Console.WriteLine(offset.TimeZoneOffset);
    }
}
connection.Close();

[pakarjon]

We tried to read timestamps the way you advised.
This however seems not to work in a case where timestamp value in the database has been set using using 'TZR' timezone format.

Example:

My SqlDeveloper has timestamp format: DD.MM.RRRR HH24:MI:SSXFF TZR.

I have items that has been inserted into the database through devart library.
Then after insert I use Oracle SqlDeveloper and modify those objects somehow and a db trigger will change their modified timestamp.

Code: Select all

TRIGGER TRG_BF_UP_SET_MODIFIED BEFORE UPDATE ON "ELEMENT" FOR EACH ROW
BEGIN
   :NEW.MODIFIED := CURRENT_TIMESTAMP;
END;

Modifying items through SqlDeveloper leads to the following situation:

Code: Select all

{
   "items": [
      {
         "created": "07.05.2019 08:53:55,365082000 +03:00",
         "modified": "07.05.2019 08:55:54,527512000 EUROPE\/HELSINKI"
      },
      {
         "created": "07.05.2019 08:53:53,629522000 +03:00",
         "modified": "07.05.2019 08:55:54,532500000 EUROPE\/HELSINKI"
      },
      {
         "created": "07.05.2019 08:53:55,346704000 +03:00",
         "modified": "07.05.2019 08:55:54,536629000 EUROPE\/HELSINKI"
      }
   ]
}

Here I have the original created timestamp in absolute time zome format and modified timestamp in time zone region format.

When I read this data through devart, it will lead to an exception when reading "modified" timestamp.

Code: Select all

var oracleTimeStamp = (OracleTimeStamp)obj.GetOracleValue(columnName);
return new DateTimeOffset(oracleTimeStamp.Value, oracleTimeStamp.TimeZoneOffset);

That is because after reading it, the modified timestmap has invalid offset: "7.5.2019 7:55:54 -123:12".

[Pinturiccio]

We have reproduced the issue. We will investigate it and post here about the results as soon as possible.

[Pinturiccio]

We have fixed the bug with reading incorrect timestamp's timezone and offset from an Oracle object field when the timezone is presented as a string value. We will post here when the corresponding build of dotConnect for Oracle is available for download.

[Pinturiccio]

New build of dotConnect for Oracle 9.7.770 is available for download.
It can be downloaded from https://www.devart.com/dotconnect/oracle/download.html (trial version) or from Devart Account (for users with valid subscription only).
For more information, please refer to viewtopic.php?t=38884

[pakarjon]

I tried my above example with 9.7.770 version. Now when reading 'MODIFIED' timestamps the OracleTimeStamp has TimeZone="EUROPE/HELSINKI" but Offset="00:00". Practically this means that timestamps don't have the necessary offset information to properly convert them to DateTimeOffset.

Is this a bug or should there be some way to convert the time zone name to offset? I couln't found any

[Pinturiccio]

We have reproduced the issue with the Direct mode. We will investigate it and post here about the results as soon as possible.

You can use the OCI mode as a temporary workaround if this suits your scenario.

[Pinturiccio]

We have fixed the bug with reading incorrect timestamp's timezone and offset in the Direct connection mode when the timezone is presented as a string value. We will post here when the corresponding build of dotConnect for Oracle is available for download.

[Pinturiccio]

New build of dotConnect for Oracle 9.7.790 is available for download.
It can be downloaded from https://www.devart.com/dotconnect/oracle/download.html (trial version) or from Devart Account (for users with valid subscription only).
For more information, please refer to viewtopic.php?t=38981

[slmnn]

We are using the earlier example to read DateTimeOffset from Oracle:

You can read datetime offset. Let's consider the following example:

Code: Select all

...
        OracleTimeStamp offset = (OracleTimeStamp)oraObj.GetOracleValue("offset");
        Console.WriteLine(offset.TimeZoneOffset);
...

After updating DevArt dotConnect for Oracle to 9.13.1098 reading offset in TZR format seems to be broken. The timestamp in the DB is

Code: Select all

03.12.2020 11:02:08,598895000 EUROPE/HELSINKI

but we are reading

Code: Select all

3.12.2020 9:02:08 +02:00

The same issue persists with the latest 9.13.1127.

With version 9.12.1064 the timestamp is read correctly. Also reading timestamps with offset in TZH:TZM seems to be working fine.

[Shalex]

We have reproduced the bug with retrieving the TIMESTAMP WITH LOCAL TIME ZONE values in the TZR (time zone region) format in the Direct mode and will notify you when it is fixed.

[Shalex]

The bug with retrieving the TIMESTAMP WITH LOCAL TIME ZONE values in the TZR (time zone region) format in the Direct mode is fixed in v9.14.1160: viewtopic.php?f=1&t=44320.