0

I am trying to connect to an Oracle DB using the following method:

OracleConnection con = new OracleConnection();
con.ConnectionString = "User ID=ID;Password=PASSWORD;Data Source=SOURCE";
con.Open();
write("connected to oracle " + con.ServerVersion);

But every time I run the program I get the error:

[2018-11-01 09:29:19.705] System.BadImageFormatException: Could not load file or assembly 'Oracle.DataAccess, Version=2.112.1.0, Culture=neutral, PublicKeyToken=PUBLIC_KEY' or one of its dependencies. An attempt was made to load a program with an incorrect format. File name: 'Oracle.DataAccess, Version=2.112.1.0, Culture=neutral, PublicKeyToken=PUBLIC_KEY' at adrentech_previous_day.Program.processFile() at adrentech_previous_day.Program.Main() in DIRECTORY\program:line 25

=== Pre-bind state information === LOG: DisplayName = Oracle.DataAccess, Version=2.112.1.0, Culture=neutral, PublicKeyToken=PUBLIC_KEY (Fully-specified) LOG: Appbase = file:///DIRECTORY LOG: Initial PrivatePath = NULL Calling assembly : adrentech_previous_day, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null. === LOG: This bind starts in default load context. LOG: Using application configuration file: DIRECTORY\program.vshost.exe.Config LOG: Using host configuration file: LOG: Using machine configuration file from C:\Windows\Microsoft.NET\Framework64\v4.0.30319\config\machine.config. LOG: Post-policy reference: Oracle.DataAccess, Version=2.112.1.0, Culture=neutral, PublicKeyToken=PUBLIC_KEY LOG: Attempting download of new URL file:///DIRECTORY/Oracle.DataAccess.DLL. ERR: Failed to complete setup of assembly (hr = 0x8007000b). Probing terminated.

Any suggestions?

parrigin777
  • 178
  • 1
  • 1
  • 10
  • 2
    1) You can use the [Oracle Managed client](https://www.nuget.org/packages/Oracle.ManagedDataAccess/), which has no external dependencies and isn't X86/64 specific. 2) Make sure you wrap your [IDisposable](https://learn.microsoft.com/en-us/dotnet/api/system.idisposable?view=netframework-4.7.2) objects such as OracleConnection in a using statement. – mason Nov 01 '18 at 13:53
  • I installed the Oracle Managed client and wrapped the OracleConnection in a using statement, but I am still getting the same error. – parrigin777 Nov 01 '18 at 14:08
  • 1
    Did you make sure to remove the referenced to the non managed client? – mason Nov 01 '18 at 14:09
  • Okay yes I just did that, now I am getting a new error: Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-12154: TNS:could not resolve the connect identifier specified ---> OracleInternal.Network.NetworkException (0x00002F7A): ORA-12154: TNS:could not resolve the connect identifier specified – parrigin777 Nov 01 '18 at 14:14
  • 1
    That's good, that means you got further. I suggest you use a connection string such as this: `SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=MyOracleSID))); uid=myUsername;pwd=myPassword;` – mason Nov 01 '18 at 14:17
  • Is "MyHost" = the database? And how would I find "MyOracleSID"? Also does the port number have to change? – parrigin777 Nov 01 '18 at 14:28
  • MyHost is the database server DNS name or IP address. MyOracleSID is usually the name of your database. The default port for Oracle is 1521. If you're hosting it on a different port, then you'll need to change it to that port. – mason Nov 01 '18 at 15:15
  • Okay after a lot of digging I was able to get all the information, but now I'm getting this error: Oracle.ManagedDataAccess.Client.OracleException (0x80004005): Network Transport: TCP transport address connect failure ---> OracleInternal.Network.NetworkException (0x80004005): Network Transport: TCP transport address connect failure ---> System.Net.Sockets.SocketException (0x80004005): A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond. – parrigin777 Nov 01 '18 at 15:38
  • Try to use the TNS alias (see my answer below) rather than the full connection string. Also I think instead of `SERVER=...` it must be `Data Source=...` – Wernfried Domscheit Nov 01 '18 at 15:52
  • I have the TNSNAMES.ORA file, what directory should that go in inside my program directory? Also how to I reference the document which an alias connection string? – parrigin777 Nov 01 '18 at 16:24

2 Answers2

0

Most likely your compile target is .NET Framework 4.0 (or higher) but you installed the ODP.NET version 2.x - they don't work together.

One solution is to use the ODP.NET Managed Driver - which you did already. However, there is also anther solution, see The provider is not compatible with the version of Oracle client

Now the second problem regarding ORA-12154: TNS:could not resolve the connect identifier specified

ODP.NET Managed Driver uses a different method to find the sqlnet.ora and tnsnames.ora (and ldap.ora, if used) files than the unmanaged driver, see Determining location of relevant tnsnames.ora file

The unmanaged drivers searches folder %ORACLE_HOME%\network\admin and reads Registry key HKLM\SOFTWARE\ORACLE\KEY_OraClient11g_home1\TNS_ADMIN, the ODP.NET Managed Driver does not.

The ODP.NET Managed Driver retrieves location of tnsnames.ora from your .NET config file, i.e. machine.config. You can modify the file manually (see Oracle Data Provider for .NET, Managed Driver Configuration) or use a batch script as below (pick x64 or x86 lines which are relevant for you and change folder names according to your machine)

set Oracle_x64=c:\oracle\product\11.2\Client_x64\odp.net
set Oracle_x86=c:\oracle\product\11.2\Client_x86\odp.net

OraProvCfg_x64=%Oracle_x64%\bin\4\OraProvCfg.exe
OraProvCfg_x86=%Oracle_x86%\bin\4\OraProvCfg.exe

set TNS_ADMIN=C:\oracle\network\admin

"%OraProvCfg_x64%" /action:config /product:odpm /frameworkversion:v4.0.30319 /providerpath:%Oracle_x64%\managed\common\Oracle.ManagedDataAccess.dll /set:settings\TNS_ADMIN:%TNS_ADMIN%
"%OraProvCfg_x86%" /action:config /product:odpm /frameworkversion:v4.0.30319 /providerpath:%Oracle_x86%\managed\common\Oracle.ManagedDataAccess.dll /set:settings\TNS_ADMIN:%TNS_ADMIN%

Or set TNS_ADMIN as Environment variable which should work in any case.

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
0

I set the path to the directory with the files tnsnames.ora and sqlnet.ora in the TNS_ADMIN computer variable and it worked for me without rebooting.

Виктор Черняк
  • 11
  • 4