title: oracle odp.net SafeMapping conversion if timestamp with timezone - how to get offset instead of zone name
body: <p>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 <a href="http://docs.oracle.com/cd/B19306_01/win.102/b14307/OracleDataAdapterClass.htm" rel="noreferrer">OracleDataAdapter</a>, 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 <a href="http://docs.oracle.com/cd/B28359_01/win.111/b28375/featSafeType.htm" rel="noreferrer">SafeMapping</a> to force conversion to string like:</p>
  
  <pre><code>dataAdapter.SafeMapping.Add("column_name", typeof(string));
  </code></pre>
  
  <p>I then indeed get the TSTZ as a string, but it's using the format <code>DD-MON-YYYY HH:MI:SS.FF AM TZR</code> like this:</p>
  
  <pre><code>23-NOV-12 08.10.12.057868000 PM ASIA/CALCUTTA
  </code></pre>
  
  <p>What I want it instead the offset (e.g. format as <code>DD-MON-YYYY HH:MI:SS.FF AM TZH:TZD</code> like:</p>
  
  <pre><code>23-NOV-12 08.10.12.057868000 PM +04:30
  </code></pre>
  
  <p>When I query oracle directly (say in Sql Developer), I can use </p>
  
  <pre><code>Alter Session Set Nls_Timestamp_Tz_Format='DD-MON-YYYY HH:MI:SS.FF AM TZH:TZM'
  </code></pre>
  
  <p>to get the format I want. 
  Using odp.net I tried both setting the format in SetSessionInfo:</p>
  
  <pre><code>connection.Open();
  OracleGlobalization glob = connection.GetSessionInfo();
  glob.TimeStampTZFormat = "DD-MON-YYYY HH:MI:SS.FF AM TZH:TZM";
  connection.SetSessionInfo(glob);
  </code></pre>
  
  <p>as well as executing the <code>alter session</code> 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. </p>
  
  <p>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. </p>
  
  <p>If this is not possible, what is the best way to convert the timezone string into offset? 
  I'm currently thinking of executing a </p>
  
  <pre><code>select TZNAME, TZABBREV, tz_offset(TZNAME) as TZOFFSET
  from V$TIMEZONE_NAMES
  </code></pre>
  
  <p>once to build a lookuptable, but would be happy if there are any better options. </p>
  
  <p>My data retrieval code, including the things I tried: </p>
  
  <pre><code>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
      }
      }
  </code></pre>
  
  <p>Thanks</p>