Timezone region not found

Question

I'm creating a C# MVC project that uses Oracle Database. While trying to connect to the DB, I get following error:

ORA-00604: error occurred at recursive SQL level 1
ORA-01882: timezone region not found.

When I try to connect via SQL developer it works fine. Is there any way that I can solve this problem ?

NOTE: I'm using IIS express for testing

This is not an error i get while debugging. This occurs while I'm trying to generate the connection by using credentials. Therefore i don't have any stack trace to share.. This is what i got — Sandaru, Nov 24 '17 at 08:28
Whats the use of it ? ... I appreciate if you can focus on the question. Thanks — Sandaru, Nov 24 '17 at 08:46
It helps others that search for this error text to find your answer. BTW: Simply pressing `Ctrl`+`C` on the error message box puts the text onto the clipboard. — Uwe Keim, Nov 24 '17 at 08:47
Which driver/provider do you use? What is the version of your driver and database? — Wernfried Domscheit, Nov 24 '17 at 09:22
"ODAC" means "Oracle Data Access Components" and is just a set of downloads. Which provider do you use? Which versions? (DB and provider) — Wernfried Domscheit, Nov 24 '17 at 10:08
Why are you not able to answer my questions? Do you like to get any for you problem? Again: Which provider? Which versions? Show us your code you use to connect. — Wernfried Domscheit, Nov 24 '17 at 10:17
What is the timezone file at your database? Check with `SELECT * FROM V$TIMEZONE_FILE;` — Wernfried Domscheit, Nov 24 '17 at 10:24
@Wernfried, there is no any code for this, I'm just trying to connect oracle DB with MVC application. for that i use visual studios functionalities. I don't have any data access driver specifically installed in my project .. I'm just using ODP.net which enables me to connect with DB directly via a connection string. And for your final question .. I don't have enough access to run queries in DB. I think this will help you — Sandaru, Nov 25 '17 at 04:24
What about the version and time Zone file? Ask your DBA if you don't know. — Wernfried Domscheit, Nov 25 '17 at 06:56

score 5 · Answer 1 · answered May 08 '19 at 13:05

5

I was facing the same issue using ODP.NET for .NET Core on Pivotal Cloud Foundry. The core issue is missing timezone information.

Added a timezone by setting "TZ" environment variable for my instance. That fixed the issue for me.

answered May 08 '19 at 13:05

BBC

396
4
12

This worked for my team. Added the env var to our container and now we can connect to Oracle, TY! I believe this is the best correct answer for addressing this issue from a .Net program. – Joshua Gunder Sep 26 '19 at 18:29
1

@JoshuaGunder could you share the `docker statement` to the dockerfile to run it? Thank you – Felipe Oriani Nov 15 '19 at 19:57

score 2 · Accepted Answer · answered Feb 20 '18 at 11:20

2

Actually, this error because of some issue with application Oracle driver which you use and Oracle version of the DB.

NOTE: Please check Both of them and use Updated Oracle versions for your application.

ORA-01882: timezone region not found

answered Feb 20 '18 at 11:20

Dhananjaya

372
1
6
22

score 2 · Answer 3 · edited Jun 09 '20 at 12:51

2

Had the same problem in .Net Core 3.1 API with controllers.

had the error on debug, and solved it adding TZ on \Properties\launchSettings.json:

"Docker": {
...
   "environmentVariables": {
        "TZ": "WET"
      }
}

edited Jun 09 '20 at 12:51

mybrave

1,662
3
20
37

answered Jun 09 '20 at 10:27

Eduardo Coutinho

51
3

Wernfried Domscheit · Answer 4 · 2020-01-21T14:00:05.897

This code does not provide an answer but it may help to find your problem. The code is too big for a comment.

Please execute this program. What is the output? Where does it fail?

  static void Main(string[] args)
  {
     Console.WriteLine("OracleGlobalization.TimeZone = {0}", Oracle.DataAccess.Client.OracleGlobalization.GetClientInfo().TimeZone);
     Console.WriteLine(String.Empty);

     Console.WriteLine("TimeZone.CurrentTimeZone.StandardName = {0}", TimeZone.CurrentTimeZone.StandardName);
     Console.WriteLine("TimeZone.CurrentTimeZone.DaylightName = {0}", TimeZone.CurrentTimeZone.DaylightName);
     Console.WriteLine(String.Empty);

     Console.WriteLine("TimeZoneInfo.Local.DisplayName = {0}", TimeZoneInfo.Local.DisplayName);
     Console.WriteLine("TimeZoneInfo.Local.Id = {0}", TimeZoneInfo.Local.Id);
     Console.WriteLine("TimeZoneInfo.Local.StandardName = {0}", TimeZoneInfo.Local.StandardName);
     Console.WriteLine("TimeZoneInfo.Local.DaylightName = {0}", TimeZoneInfo.Local.DaylightName);
     Console.WriteLine(String.Empty);

     var str = new Oracle.DataAccess.Client.OracleConnectionStringBuilder();
     str.UserID = "<username>";
     str.Password = "<password>";
     str.DataSource = "<database name>";
     using ( var con = new Oracle.DataAccess.Client.OracleConnection(str.ConnectionString) ) {
        con.Open();
        Console.WriteLine("Oracle.DataAccess: OracleConnection -> SessionInfo.TimeZone = {0}", con.GetSessionInfo().TimeZone);
        Console.WriteLine("Oracle.DataAccess: Version = {0}", FileVersionInfo.GetVersionInfo(con.GetType().Assembly.Location).FileVersion.ToString());

        var tz = new Oracle.DataAccess.Client.OracleCommand("SELECT SESSIONTIMEZONE FROM dual", con).ExecuteScalar();
        Console.WriteLine("Oracle.DataAccess: SESSIONTIMEZONE = {0}", tz.ToString());
        con.Close();
     }
     Console.WriteLine(String.Empty);

     var strm = new Oracle.ManagedDataAccess.Client.OracleConnectionStringBuilder();
     str.UserID = "<username>";
     str.Password = "<password>";
     str.DataSource = "<database name>";
     using ( var con = new Oracle.ManagedDataAccess.Client.OracleConnection(str.ConnectionString) ) {
        con.Open();
        Console.WriteLine("Oracle.ManagedDataAccess: OracleConnection -> SessionInfo.TimeZone = {0}", con.GetSessionInfo().TimeZone);
        Console.WriteLine("Oracle.ManagedDataAccess: Version = {0}", FileVersionInfo.GetVersionInfo(con.GetType().Assembly.Location).FileVersion.ToString());

        var tz = new Oracle.ManagedDataAccess.Client.OracleCommand("SELECT SESSIONTIMEZONE FROM dual", con).ExecuteScalar();
        Console.WriteLine("Oracle.ManagedDataAccess: SESSIONTIMEZONE = {0}", tz.ToString());
        con.Close();
     }
  }

Update

According to your profile you are based in Sri Lanka. Time zone of Sri Lanka seems to be fairly "volatile", see text from IANA TimeZone Database:

Sri Lanka

From Paul Eggert (2013-02-21): Milne says "Madras mean time use from May 1, 1898. Prior to this Colombo mean time, 5h. 4m. 21.9s. F., was used." But 5:04:21.9 differs considerably from Colombo's meridian 5:19:24, so for now ignore Milne and stick with Shanks and Pottenger.

From Paul Eggert (1996-09-03): "Sri Lanka advances clock by an hour to avoid blackout" (http://www.virtual-pc.com/lankaweb/news/items/240596-2.html, 1996-05-24, no longer available as of 1999-08-17) reported "the country's standard time will be put forward by one hour at midnight Friday (1830 GMT) 'in the light of the present power crisis'."

From Dharmasiri Senanayake, Sri Lanka Media Minister (1996-10-24), as quoted by Shamindra in Daily News - Hot News Section (1996-10-26): With effect from 12.30 a.m. on 26th October 1996 Sri Lanka will be six (06) hours ahead of GMT.

From Jesper Nørgaard Welen (2006-04-14), quoting Sri Lanka News Online http://news.sinhalaya.com/wmview.php?ArtID=11002 (2006-04-13): 0030 hrs on April 15, 2006 (midnight of April 14, 2006 +30 minutes) at present, become 2400 hours of April 14, 2006 (midnight of April 14, 2006).

From Peter Apps and Ranga Sirila of Reuters (2006-04-12) in: http://today.reuters.co.uk/news/newsArticle.aspx?type=scienceNews&storyID=2006-04-12T172228Z_01_COL295762_RTRIDST_0_SCIENCE-SRILANKA-TIME-DC.XML [The Tamil Tigers] never accepted the original 1996 time change and simply kept their clocks set five and a half hours ahead of Greenwich Mean Time (GMT), in line with neighbor India.

From Paul Eggert (2006-04-18): People who live in regions under Tamil control can use [TZ='Asia/Kolkata'], as that zone has agreed with the Tamil areas since our cutoff date of 1970.

From Sadika Sumanapala (2016-10-19): According to http://www.sltime.org (maintained by Measurement Units, Standards & Services Department, Sri Lanka) abbreviation for Sri Lanka standard time is SLST.

From Paul Eggert (2016-10-18): "SLST" seems to be reasonably recent and rarely-used outside time zone nerd sources. I searched Google News and found three uses of it in the International Business Times of India in February and March of this year when discussing cricket match times, but nothing since then (though there has been a lot of cricket) and nothing in other English-language news sources. Our old abbreviation "LKT" is even worse. For now, let's use a numeric abbreviation; we can switch to "SLST" if it catches on.

I assume your database does not recognize your current local time zone of your computer. There might be three possible solutions:

Change your computer locale settings to a more stable one, e.g. India
Update database with latest version of time zone file, see Upgrading the Time Zone File and Timestamp with Time Zone Data
In case you use the ODP.NET Unmanaged driver Oracle.DataAccess.dll (unfortunately you did not tell us) you can set ORA_SDTZ environment variable or Registry key HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_<Oracle home name>\ORA_SDTZ / HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE\KEY_<Oracle home name>\ORA_SDTZ to desired time zone value. Note, the ODP.NET Managed Driver does not read this Registry values.

score 1 · Answer 5 · answered Nov 02 '22 at 15:06

According to this straight from an Oracle employee, set UseHourOffsetForUnsupportedTimezone property for the Connection to true, before opening it.

con.UseHourOffsetForUnsupportedTimezone = true;
con.Open();

This worked for me as I did not have control on the OracleDB server, and in my crossplatform application, the environment variable usage was not being consistent across linux distros. I had ran Weinfried's snippet on all of them to test and there was no timezone descrepency with OracleDB in my case(both on UTC, no DST) but was still failing on the same plank.

score 0 · Answer 6 · answered Apr 07 '23 at 12:10

I encountered similar problem few days ago and had to go through multiple answers to arrive at a solution that worked for me.

In summary, this is what I observed: When running your C# application in development/debug mode, you are very likely not to encounter this issue. It's mostly when you deploy to a server in production that this happens.

So this is what I suggest you do:

Find out the time zone of the remote Oracle DB you are trying to connect to. You don't have to physically log into the Db server to do that. Since you can run queries using SQL Developer, run the query below to get the time zone that your deployed environment needs to be in sync with to prevent this error:

SELECT sessiontimezone FROM DUAL;

See sample screenshot of output below:

Adjust the time zone (TZ) of your deployment host machine to match that of the OracleDb server.

If you are on windows/mac, that may be as easy as going into your datetime settings and adjusting the time zone to match the output of the query in step 1.

If your deployment is on a docker container; using docker-compose:

  api.gateway:
    #restart: always
    environment:
      - ASPNETCORE_ENVIRONMENT=Development
      - ASPNETCORE_URLS=http://+:80
      - TZ=Africa/Luanda

Running your docker file in command line:

docker run -e TZ=Africa/Luanda debian:jessie date

Hope this provides a bit of details for someone, as it did for me.

Karthick Raju · Answer 7 · 2017-11-24T09:30:55.413

-1

According to this and this:

In C:\Program Files\datamodeler 3\datamodeler\bin\datamodeler.conf add

AddVMOption -Duser.timezone="+02:00"

edited Nov 24 '17 at 09:30

answered Nov 24 '17 at 08:27

Karthick Raju

757
8
29

I already tried those solutions mentioned in your answer. but i don't have ether of such folders in my computer. Thanks for the answer – Sandaru Nov 24 '17 at 08:31
TO says, it is working in SQL-Developer, any hint to make SQL-Developer working is useless. – Wernfried Domscheit Nov 24 '17 at 09:19
I initially answered for Oracle. Then edited it for SQL also by forgetting it worked for SQL. Answer edited now – Karthick Raju Nov 24 '17 at 09:27

Timezone region not found

7 Answers7

Linked