8

I am receiving the below exception when trying to connect to an oracle database using the Oracle Managed Data Access for dotnet core (https://www.nuget.org/packages/Oracle.ManagedDataAccess.Core/) from inside a docker container. I do not receive the exception outside of docker

Steps to Reproduce:

  1. Open VS 2017
  2. File > New > Project...
  3. Visual C# > .Net Core > ASP.Net Core Web Application
  4. Click Ok
  5. Select 'Web Application (Model-View-Controller)'
  6. uncheck 'Enabled Docker Support'
  7. uncheck 'Configure for HTTPS'
  8. Click Ok
  9. In Package Manager Console execute Install-Package Oracle.ManagedDataAccess.Core -Source nuget.org -Version 2.18.3
  10. Paste Code into HomeController.Index method
  11. Set breakpoint on line con.Open();
  12. Click Debug "IIS Express" button
  13. No exception is thrown when trying to open connection.
  14. Stop debugging
  15. Right Click on Web Project in Solution Explorer > Add > Docker Support
  16. Select 'Linux' Radio button and click OK
  17. Right Click on Web Project in Solution Explorer > Add > Container Orchestrator Support
  18. In the dropdown select 'Docker Compose' and click OK (depending on the version of Visual Studio 2017 installed this may differ)
  19. Click Yes if any popups are displayed asking to overwrite files
  20. Click Debug "Docker Compose" button
  21. An exception will be thrown when trying to open connection

Code: 

var strm = new Oracle.ManagedDataAccess.Client.OracleConnectionStringBuilder();
strm.UserID = "<username>";
strm.Password = "<password>";
strm.DataSource = "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=<db_host>)(PORT = 1521))) (CONNECT_DATA=(SERVICE_NAME=<service_name>)))";
using (var con = new Oracle.ManagedDataAccess.Client.OracleConnection(strm.ConnectionString))
{
     con.Open(); // Exception thrown here.
}

Exception:

Exception in VS

Oracle.ManagedDataAccess.Client.OracleException
  HResult=0x80004005
  Message=ORA-00604: error occurred at recursive SQL level 1
ORA-01882: timezone region not found
  Source=Oracle Data Provider for .NET, Managed Driver
  StackTrace:
   at OracleInternal.ConnectionPool.PoolManager`3.Get(ConnectionString csWithDiffOrNewPwd, Boolean bGetForApp, OracleConnection connRefForCriteria, String affinityInstanceName, Boolean bForceMatch)
   at OracleInternal.ConnectionPool.OraclePoolManager.Get(ConnectionString csWithNewPassword, Boolean bGetForApp, OracleConnection connRefForCriteria, String affinityInstanceName, Boolean bForceMatch)
   at OracleInternal.ConnectionPool.OracleConnectionDispenser`3.Get(ConnectionString cs, PM conPM, ConnectionString pmCS, SecureString securedPassword, SecureString securedProxyPassword, OracleConnection connRefForCriteria)
   at Oracle.ManagedDataAccess.Client.OracleConnection.Open()
   at WebApplication8.Controllers.HomeController.Index() in C:\Users\me\source\repos\WebApplication8\WebApplication8\Controllers\HomeController.cs:line 22
   at Microsoft.Extensions.Internal.ObjectMethodExecutor.Execute(Object target, Object[] parameters)
   at Microsoft.AspNetCore.Mvc.Internal.ActionMethodExecutor.SyncActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeActionMethodAsync>d__12.MoveNext()

Update:

I was doing some additional testing based on @silent answer below and figured out something interesting. If I rolled back to version 2.12.0-beta3 of the ODP.Net core (https://www.nuget.org/packages/Oracle.ManagedDataAccess.Core) and removed the TZ=America/Denver environment variable I am able to open a connection without error. It looks like something was introduced into 2.18.3 that's causing the requirement for the TZ environment variable when opening a connection inside a docker container.

Matt M
  • 592
  • 1
  • 5
  • 27
  • What is the version of `Oracle.ManagedDataAccess.dll` and what is the version of the database? – Wernfried Domscheit Nov 28 '18 at 06:59
  • Have a loot at this one: https://stackoverflow.com/a/47526565/3027266 and/or https://stackoverflow.com/a/28020376/3027266 – Wernfried Domscheit Nov 28 '18 at 07:25
  • I'm having the same issue. None of the ideas in the references worked for me either. – silent Nov 28 '18 at 10:51
  • What are the versions and which time zone did you set in `ORA_SDTZ` Environment variable? – Wernfried Domscheit Nov 28 '18 at 11:05
  • Oracle DB 11.2.0.4.0 and nuget package Oracle.ManagedDataAccess.Core 2.18.3. But see my solution below, that works now for me – silent Nov 28 '18 at 11:42
  • @WernfriedDomscheit Oracle.ManagedDataAccess.Core version is 2.18.3. Database version is 12.2.0.1.0 (select version from v$instance;). I saw both of those stack overflow threads. Do you have any documentation for the ORA_SDTZ environment variable – Matt M Nov 28 '18 at 16:58
  • See [Datetime Datatypes and Time Zone Support](https://docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datetime.htm#i1006728) – Wernfried Domscheit Nov 28 '18 at 18:52

2 Answers2

10

I just came to the solution in in similar context (Oracle DB 11.2.0.4.0 and NuGet package Oracle.ManagedDataAccess.Core 2.18.3):

Add an environment variable called TZ to your container and set the value to your timezone, e.g. CET

This allowed me to open the connection. Then I can also use the part from this solution to set the session info

this.Connection = new OracleConnection();
this.Connection.ConnectionString = ...
this.Connection.Open();
OracleGlobalization info = this.Connection.GetSessionInfo();
info.TimeZone = "America/New_York";
this.Connection.SetSessionInfo(info);
Matt M
  • 592
  • 1
  • 5
  • 27
silent
  • 14,494
  • 4
  • 46
  • 86
  • setting the TZ enviroment variable works. Do you have any documentation that explains why this is needed. I was under the impression the host machines timezone and clock information was used inside the container. We are planning on hosting services all over the world and managing the timezone in our docker compose files is going to be a huge pain. – Matt M Nov 28 '18 at 17:02
  • After lots of googeling I found the hint here: https://github.com/docker-library/tomcat/issues/83 and just tried it out... so no, sorry, can't really explain why this is needed/helps. Maybe open a separate question for that – silent Nov 28 '18 at 17:10
  • I was doing some additional testing based on this answer and figured out something interesting. If I rolled back to version 2.12.0-beta3 of the ODP.Net core (https://www.nuget.org/packages/Oracle.ManagedDataAccess.Core) and removed the TZ=America/Denver environment variable I am able to open a connection without error. It looks like something was introduced into 2.18.3 that's causing the requirement for the TZ environment variable when opening a connection inside a docker container. – Matt M Dec 12 '18 at 19:12
  • Yeah a colleague told me that in the meantime as well. Who’s volunteering to open a bug report with Oracle? *duckandrun* – silent Dec 13 '18 at 09:51
  • 1
    Took me a while to figure out how to add the environment variable. In VS2019 for .NET Core 3.0, locate the Properties/launchSettings.json file and add the variable there. Example: "Docker": { "commandName": "Docker", "launchBrowser": true, "launchUrl": "{Scheme}://{ServiceHost}:{ServicePort}/api", "environmentVariables": { "TZ":"GMT" }, "httpPort": 63221 } – Marson Nov 13 '19 at 09:34
  • i got this error with Oracle.EntityFrameworkCore 5.21.5 and Oracle.ManagedDataAccess.Core 3.21.50. Setting TZ helped. – volkit Feb 21 '22 at 16:51
0

I leave the command to add the TZ variable in the container:

docker run -e TZ=America/Montevideo -d -p 7991:80 --name name imagename

also add the varible in the launchSettings of the project:

"IIS Express": {
  "commandName": "IISExpress",
  "launchBrowser": true,
  "launchUrl": "swagger",
  "environmentVariables": {
    "ASPNETCORE_ENVIRONMENT": "Development",
    "TZ": "America/Montevideo"
  }
}

I hope it works for you

user29471
  • 1