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

For me I was able to fix this by moving the ENV TZ= piece after the ENTRYPOINT in my Dockerfile. For some reason it wasn't taking the Environment variable from anywhere else. — xbrady

anishpsla anishpsla · Accepted Answer · 2020-10-31T11:54:36

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

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

2 Answers