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

Question

I am using Oracle.ManagedDataAccess.Core 2.18.6 to connect oracle DB.When i call con.Open() getting issue ORA-00604: error occurred at recursive SQL level 1 ORA-01882: timezone region not found...Oracle Data Provider for .NET, Managed Driver With .Net Core 2.2. After implemented below solution still getting same error.
-My Code:

DataTable dt = new DataTable();
            Console.WriteLine("Called ");
            
                using (OracleConnection Connection = new OracleConnection(utilityManager.Decrypt(ConnString)))
                {
                    Console.WriteLine("********* Before Connection Open********* ");
                    Connection.Open();

                    Console.WriteLine("********* Connection Open********* ");
                    OracleDataAdapter DataAdapter = new OracleDataAdapter();
                    using (OracleCommand cmd = new OracleCommand(strSQL, Connection))
                    {
                        cmd.BindByName = true;
                        cmd.CommandText = strSQL;
                        cmd.CommandType = CommandType.Text;
                        DataAdapter.SelectCommand = cmd;
                        DataSet ds = new DataSet();
                        DataAdapter.Fill(ds);

                        dt = ds.Tables[0];
                    }
                }
        

Solution I tried :

-Set timezone in docker file as below

ENV TZ=America/New_York
RUN ln -snf /usr/share/zoneinfo/$TZ /etc/localtime && echo $TZ > /etc/timezone

-Use OracleGlobalization class

this.Connection = new OracleConnection();
this.Connection.ConnectionString = ...
this.Connection.Open();
OracleGlobalization info = this.Connection.GetSessionInfo();
info.TimeZone = "America/New_York";
this.Connection.SetSessionInfo(info)

-asp.net core docker container using Oracle Managed Driver Core. throws ORA-00604 and ORA-01882 when opening connection

-https://serverfault.com/questions/683605/docker-container-time-timezone-will-not-reflect-changes

Answer 1

We had the same issue, when we tried to deploy our application developed in Windows to a Linux/NGNIX machine. While the code worked fine in Windows machine, it threw the error "ORA-01882: timezone region not found". After trying several solutions, we found the following solution. In the service file for your .Net application located at /etc/systemd/system/ folder, set the Environment variable for the timezone expected by Oracle. Sample service file is posted below

[Unit]
Description=******

[Service]
WorkingDirectory=/home/****/*****
ExecStart=/home/*****/*****/*****
Restart=always
RestartSec=10 # Restart service after 10 seconds if dotnet service crashes
SyslogIdentifier=offershare-web-app
Environment=ASPNETCORE_ENVIRONMENT=Development
Environment=TZ=Asia/Calcutta

[Install]
WantedBy=multi-user.target

PS: You can get the Oracle timezone by running the following query

SELECT SESSIONTIMEZONE FROM DUAL

Answer 2

For ASP.Net Core, you can add TZ to your environment variable and test before deployment on Linux

 "profiles": {
    "IIS Express": {
      "commandName": "IISExpress",
      "launchBrowser": true,
      "launchUrl": "weatherforecast",
      "environmentVariables": {
        "ASPNETCORE_ENVIRONMENT": "Development",
        "TZ" :"Africa/Lagos"

      }
    },