连接到 Oracle 11g 的 VB.NET 问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12977790/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VB.NET issue connecting to Oracle 11g
提问by Jeremy F.
OS: Windows 7 64bit
VB: Visual Studio 2010
Oracle Client: 11g
I am developing an application that connects to an Oracle 11g server. I am able to connect to the Oracle server via Oracle SQL Developer, ODBC (in SYSWOW64), and in VB Server Explorer.
我正在开发一个连接到 Oracle 11g 服务器的应用程序。我可以通过 Oracle SQL Developer、ODBC（在 SYSWOW64 中）和 VB Server Explorer 连接到 Oracle 服务器。
I am using the .NET reference Oracle.DataAccess located at: C:\Oracle\product\11.2.0\client_32\ODP.NET\bin\2.x\Oracle.DataAccess.dll
我正在使用位于以下位置的 .NET 参考 Oracle.DataAccess：C:\Oracle\product\11.2.0\client_32\ODP.NET\bin\2.x\Oracle.DataAccess.dll
I have tried adding the .DLL files to my bin folder as suggested by other questions and that does not work.
我已经尝试按照其他问题的建议将 .DLL 文件添加到我的 bin 文件夹中，但这不起作用。
Does anyone have any other suggestions?
有人有其他建议吗？
When I run my application, I get the following:
当我运行我的应用程序时，我得到以下信息：
Here are the details:
以下是详细信息：
System.TypeInitializationException was unhandled
Message=The type initializer for 'Oracle.DataAccess.Client.OracleConnection' threw an exception.
Source=Oracle.DataAccess
TypeName=Oracle.DataAccess.Client.OracleConnection
StackTrace:
at Oracle.DataAccess.Client.OracleConnection..ctor(String connectionString)
at Orc_Test_1.Form1.Button1_Click(Object sender, EventArgs e) in c:\Visual Studio 2010\Projects\Orc_Test_1\Orc_Test_1\Form1.vb:line 9
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
at Orc_Test_1.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
InnerException: Oracle.DataAccess.Client.OracleException
DataSource=""
ErrorCode=-2147467259
Message=The provider is not compatible with the version of Oracle client
Number=-11
Procedure=""
Source=Oracle Data Provider for .NET
StackTrace:
at Oracle.DataAccess.Client.OracleInit.Initialize()
at Oracle.DataAccess.Client.OracleConnection..cctor()
InnerException:
VB.NET Code:
VB.NET 代码：
Imports System.Data
Imports Oracle.DataAccess.Client ' ODP.NET Oracle managed provider
Imports Oracle.DataAccess.Types
Public Class Form1
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
Dim oradb As String = "Data Source=SERVER1;Persist Security Info=True;User ID=username;Password=password;Unicode=True" 'From Server Explorer
'Dim oradb As String = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Server1.host.net)(PORT=1522))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=Server1))); USER ID = username;Password = password;" 'From TNSnames.ora
Dim conn As New OracleConnection(oradb) '<--- Error happens on this line
conn.Open()
Dim cmd As New OracleCommand
cmd.Connection = conn
cmd.CommandText = "select system_id from schema1.system_impacted where system_name = AWESOME"
cmd.CommandType = CommandType.Text
Dim dr As OracleDataReader = cmd.ExecuteReader()
dr.Read()
Label1.Text = dr.Item("system_id")
conn.Dispose()
End Sub
End Class
回答by tbone
I feel your pain, just went through something similar in a deployment situation. You probably have multiple clients installed, and your environment is pulling dlls for older releases (even if you have a latest oracle.dataaccess.dll correctly referenced in your project). Fixing this on your dev environment is one thing, a prod deployment server is another. Not sure what your deployment situation is, but here's what worked for me.
我感受到你的痛苦，刚刚在部署情况下经历了类似的事情。您可能安装了多个客户端，并且您的环境正在为旧版本提取 dll（即使您在项目中正确引用了最新的 oracle.dataaccess.dll）。在您的开发环境中修复此问题是一回事，生产部署服务器是另一回事。不确定您的部署情况是什么，但这对我有用。
After struggling with trying to upgrade odp.net in existing oracle home, adding new oracle home, etc., I found the easiest way to fix everything is to download the latest odac with xcopy deploymentfrom Oracle, and follow the readme (and see herefor an older article on this also). Basically you'll run an install.bat file to setup locally (in separate folder, mine was c:\oracle_odac), then change your project reference to point to the oracle.dataaccess.dll in this new folder (I used 4 instead of 2.x), and add the new folder's bin dirs to front of your path (c:\oracle_odac\bin and c:\oracle_odac\odp.net\bin\4). On your deployment server, you'll just need to copy the entire c:\oracle_odac folder over (via xcopy or however), and setup the path.
在尝试升级现有 oracle home 中的 odp.net、添加新的 oracle home 等之后，我发现解决所有问题的最简单方法是从 Oracle下载最新的odac 和 xcopy 部署，并按照自述文件进行操作（并参见此处也有一篇关于此的较旧文章）。基本上，您将运行一个 install.bat 文件以在本地进行设置（在单独的文件夹中，我的是 c:\oracle_odac），然后将您的项目引用更改为指向这个新文件夹中的 oracle.dataaccess.dll（我使用了 4 而不是2.x)，并将新文件夹的 bin 目录添加到路径的前面（c:\oracle_odac\bin 和 c:\oracle_odac\odp.net\bin\4）。在您的部署服务器上，您只需要复制整个 c:\oracle_odac 文件夹（通过 xcopy 或然而），并设置路径。
That said, I anxiously await the production release of the fully managed odp.netfrom Oracle (in beta now).
也就是说，我焦急地等待来自 Oracle的完全托管的 odp.net的生产版本（现在处于测试阶段）。
EDIT: Just to add that you can avoid messing with PATHs if you setup in your app or web config file the dllpath. For example:
编辑：补充一点，如果您在应用程序或 Web 配置文件中设置了 dllpath，则可以避免弄乱 PATH。例如：
<configuration>
...
<configSections>
<section name="oracle.dataaccess.client" type="System.Data.Common.DbProviderConfigurationHandler, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
...
<oracle.dataaccess.client>
<settings>
<add name="DllPath" value="c:\oracle_odac\bin"/>
</settings>
</oracle.dataaccess.client>
...
This will override other settings such as registry or machine.config. And it will allow multiple odp.net configurations to exists peacefully, and allow each app to point to the version it needs on the same server.
这将覆盖其他设置，例如 registry 或 machine.config。并且它将允许多个 odp.net 配置和平存在，并允许每个应用程序指向它在同一服务器上所需的版本。
回答by Jeremy F.
I removed all of the oracle clients that were available in the 'Programs' section of 'Control Panel'. Then I went into the registry searched for 'Oracle' and removed all instances/references of Oracle client(s). After 3 hours of registry removal and a reboot, I installed the 11g client and everything worked as expected.
我删除了“控制面板”的“程序”部分中可用的所有 oracle 客户端。然后我进入注册表搜索“Oracle”并删除 Oracle 客户端的所有实例/引用。删除注册表并重新启动 3 小时后，我安装了 11g 客户端，一切都按预期工作。
Note:when searching the registry for "Oracle", there may be references to the VB version of Oracle, or a reference to Crytsal Reports driver for 'Oracle'. I did not remove these. So pay attention to the file location and/or the syntax of the registry item you are deleting.
注意：在注册表中搜索“Oracle”时，可能会引用 VB 版本的 Oracle，或引用“Oracle”的 Crytsal Reports 驱动程序。我没有删除这些。因此，请注意您要删除的注册表项的文件位置和/或语法。
/Reading. It works.
/读。有用。
回答by b0rg
Usually happens when you're building x64 application which is trying to pick up x32 drivers that doesn't really work.
通常发生在您构建 x64 应用程序时，该应用程序试图获取实际上不起作用的 x32 驱动程序。
What you do in this case (and it's quite painful):
在这种情况下你会做什么（而且很痛苦）：
- Install x32 oracle client to oraclehome1
- Install x64 oracle client to oraclehome2
- 将 x32 oracle 客户端安装到 oraclehome1
- 将 x64 oracle 客户端安装到 oraclehome2
Make sure your application build (x64, x32) is pointed to correct oraclehome. I don't have that code handy, but I believe it is something along the lines of "Home=oraclehome2" in the connection string or similar.
确保您的应用程序构建 (x64, x32) 指向正确的 oraclehome。我手边没有那个代码，但我相信它是连接字符串中的“Home=oraclehome2”或类似的东西。
回答by Jayashree M Kudari
download ODAC 32/ ODAC 64 bit ,depending upon on the compatibility of OS, VS and oracle 11g . Use oracle 11g 32,os 32 bit,oracle 32 bit or oracle 64 bit,os 64 bit and oracle 11g 64 bit.
下载 ODAC 32/ ODAC 64 位，取决于 OS、VS 和 oracle 11g 的兼容性。使用oracle 11g 32,os 32 bit,oracle 32 bit or oracle 64 bit,os 64 bit and oracle 11g 64 bit.
回答by LEGARCIA
In my experience, after trying tbone's suggestion, playing with PATH env variable and other unsuccessful plays, the solution that worked immediately was to save a copy of the 8 DLLs files listed below RIGHT ON THE SAME FOLDER of the vb.net executable, and then add a reference to Oracle.DataAccess.dll in the same folder.
根据我的经验，在尝试了 tbone 的建议、使用 PATH env 变量和其他不成功的播放后，立即起作用的解决方案是将下面列出的 8 个 DLL 文件的副本保存在 vb.net 可执行文件的同一文件夹中，然后在同一文件夹中添加对 Oracle.DataAccess.dll 的引用。
C:\Users\Legarcia\Documents\Visual Studio 2010\Projects\LabRetention\LabRetention\bin\Debug>dir *.dll
Volume in drive C is OS
Volume Serial Number is 10EC-8C5D
Directory of C:\Users\Legarcia\Documents\Visual Studio 2010\Projects\LabRetention\LabRetention\bin\Debug
09/30/2008 07:57 PM 348,160 msvcr71.dll
10/01/2008 02:22 AM 520,192 oci.dll
10/01/2008 01:56 AM 352,256 ociw32.dll
08/12/2009 07:13 AM 1,011,712 Oracle.DataAccess.dll
09/18/2008 08:47 PM 1,130,496 orannzsbb11.dll
10/01/2008 01:48 AM 868,352 oraocci11.dll
10/01/2008 02:45 AM 117,604,352 oraociei11.dll
08/12/2009 07:21 AM 413,696 OraOps11w.dll
8 File(s) 122,249,216 bytes
0 Dir(s) 238,127,943,680 bytes free
When developing/testing/debuging, the set of DLLs is copied to ..\projects\{Your project}\bin\Debug
在开发/测试/调试时，DLL 集被复制到 ..\projects\{Your project}\bin\Debug
e.g.: [C:\Users\Legarcia\Documents\Visual Studio 2010\Projects\LabRetention\LabRetention\bin\Debug]
例如：[C:\Users\Legarcia\Documents\Visual Studio 2010\Projects\LabRetention\LabRetention\bin\Debug]
And when releasing the application for the users just copy the DLLs to the same dir of your EXE
当为用户发布应用程序时，只需将 DLL 复制到 EXE 的同一目录中
This seems to isolate all other Ora client installations.
这似乎隔离了所有其他 Ora 客户端安装。
The connection string used in the VB.net app is created from the info in TNSNAMES.ORA file found on some other client directory.
VB.net 应用程序中使用的连接字符串是根据在其他客户端目录中找到的 TNSNAMES.ORA 文件中的信息创建的。
Regards.
问候。
LE+
LE+
