oracle odp.net SafeMapping conversion if timestamp with timezone - how to get offset instead of zone name

Question

From c#, using odp.net, I call an oracle function that returns a cursor. Some of the columns are of type "timestamp with time zone" (TSTZ). If I directly use an OracleDataAdapter, those columns are converted to System.DateTime and the timezone information are lost. This is expected behavior and the recommendation seems to be to use SafeMapping to force conversion to string like:

dataAdapter.SafeMapping.Add("column_name", typeof(string));

I then indeed get the TSTZ as a string, but it's using the format DD-MON-YYYY HH:MI:SS.FF AM TZR like this:

23-NOV-12 08.10.12.057868000 PM ASIA/CALCUTTA

What I want it instead the offset (e.g. format as DD-MON-YYYY HH:MI:SS.FF AM TZH:TZD like:

23-NOV-12 08.10.12.057868000 PM +04:30

When I query oracle directly (say in Sql Developer), I can use

Alter Session Set Nls_Timestamp_Tz_Format='DD-MON-YYYY HH:MI:SS.FF AM TZH:TZM'

to get the format I want. Using odp.net I tried both setting the format in SetSessionInfo:

connection.Open();
OracleGlobalization glob = connection.GetSessionInfo();
glob.TimeStampTZFormat = "DD-MON-YYYY HH:MI:SS.FF AM TZH:TZM";
connection.SetSessionInfo(glob);

as well as executing the alter session command using the same connection, but neither has any effect. I assume this is because the conversion to string takes place at a later stage and the connection settings have no effect.

Is there any other way to have odp.net provide me the offset directly? I cannot change the oracle db function, so using for example tz_offset in the method is not an option.

If this is not possible, what is the best way to convert the timezone string into offset? I'm currently thinking of executing a

select TZNAME, TZABBREV, tz_offset(TZNAME) as TZOFFSET
from V$TIMEZONE_NAMES

once to build a lookuptable, but would be happy if there are any better options.

My data retrieval code, including the things I tried:

using (var connection = new OracleConnection(this.connectionString))
{
connection.Open();
OracleGlobalization glob = connection.GetSessionInfo();
glob.TimeStampTZFormat = "DD-MON-YYYY HH:MI:SS.FF AM TZH:TZM";
connection.SetSessionInfo(glob);
string sql = "ALTER SESSION " +
 "SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD\"T\"HH24:MI:SS\"Z\"'";

using (var cmd = connection.CreateCommand())
{
    cmd.CommandText = sql;
    cmd.ExecuteNonQuery();
}    

using (var cmd = connection.CreateCommand())
{
    cmd.CommandText = "fn_name";
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.BindByName = false;
    var output = cmd.Parameters.Add("return_value", OracleDbType.RefCursor);
    output.Direction = ParameterDirection.ReturnValue;
    cmd.Parameters.Add("id", id).Direction = ParameterDirection.Input;

    using (var dataAdapter = new OracleDataAdapter(cmd))
    {

        dataAdapter.SafeMapping.Add("TZ_COLUMN", typeof(string));
        dataAdapter.TableMappings.Add("Table", "Table");
        OracleGlobalization glob2 = connection.GetSessionInfo();
        glob2.TimeStampTZFormat = "DD-MON-YYYY HH:MI:SS.FF AM TZH:TZM";
        connection.SetSessionInfo(glob2);
        dataAdapter.Fill(dataSet);
    }
    foreach (DataRow row in dataSet.Tables[0].Rows)
    {
  // column is string with timezone name, I want offset
    }
    }

Thanks

Answer 1

Looks like a very simple solution is to set

dataAdapter.ReturnProviderSpecificTypes = true;

This seems to make ODP.net not perform the lossy converstion to .net System.DateTime but instead use it's own datetypes.

Answer 2

Perhaps the following link can help?

http://docs.oracle.com/html/A96160_01/oratyp10.htm#1130922

This tells about OracleTimeStampTZ type which can store the timezone information also. The timezone property returns information about the time zone.