通过Excel连接到Oracle数据库 [英] Connecting to Oracle Database through Excel
问题描述
我正在尝试从Excel工作表连接到服务器上的Oracle数据库，但无法理解原因.
I'm trying to connect to an Oracle Database on our server from an Excel sheet, but am having trouble understanding why.
我目前在不同的ORACLE_HOME中分别安装了 32位和64位Oracle 12c，并且在我的64位计算机上安装了32位Excel.
I currently have both 32-bit and 64-bit Oracle 12c installed in different ORACLE_HOMEs and have a 32-bit installation of Excel on my 64-bit computer.
我正在尝试使用 New Query 向导连接到Excel中的Oracle数据库.
I'm trying to connect to an Oracle Database in Excel using the New Query wizard.
单击从Oracle数据库时，出现以下错误/消息.
When I click From Oracle Database I get the following error/message.
我可以继续使用当前的提供程序，但是当我实际上尝试连接到数据库时，我得到了:
I can continue with my current provider, but when I actually try to connect to a database I get this:
我不理解的是为什么在计算机上同时安装32位和64位版本的Oracle 12c时出现此错误的原因.两者都包含在我的PATH变量中(第一个32位)，并且我还包括一个特定的ORACLE_HOME和TNS_ADMIN来指向我的32位安装，因为我的excel是32位的.
What I don't understand is why I'm getting this error when I have both 32-bit and 64-bit versions of Oracle 12c installed on my computer. Both are included on my PATH variable (32-bit first), and I also included a specific ORACLE_HOME and TNS_ADMIN to point to my 32-bit installation since my excel is 32-bit.
我真的很希望能够通过Excel从数据库中进行查询，但是这个问题使我迷惑不解.
I really want the ability to query from the database through Excel, but this problem is confusing me to death.
编辑
我还刚刚将Oracle主目录中的ODBC驱动程序添加到了ODBC数据源管理员工具中.
I've also just added the ODBC Driver from my Oracle home's into the ODBC Data Source Administrator Tool.
当前可以通过此工具进行连接.
It's currently able to connect from this tool.
但是当我尝试通过 ODBC Connection 向导进行连接时，我知道得到以下信息:
But when I try to connect to going through the ODBC Connection wizard, I know get this:
有什么想法吗?
推荐答案
如何安装32位和64位Oracle Client?
How did you install 32bit and 64bit Oracle Client?
Your excel is 32bit and you try to use the 32bit Oracle. I assume you launch the 64bit version of "ODBC Administrator" - there might be the mismatch. Or you have a problem in your PATH regarding %ORACLE_HOME% and/or %ORACLE_HOME%\bin folder
您的Excel是32位的，因此通常您将32位客户端放入PATH中进行了正确的选择，对于ORACLE_HOME，您不能在一个进程中混合使用32位和64位程序集.顺便说一句，当您按照上面的说明进行操作时，Windows将自动对其进行管理.
Your Excel is 32bit, so in general you did the right approach by putting the 32bit Client into PATH and for ORACLE_HOME, you cannot mix 32bit and 64bit assemblies in one process. Btw, when you follow the instructions above your Windows will manage this automatically.
我假设您安装了Oracle Instant Client.默认的Instant Client不包含任何ODBC驱动程序或Oracle Data Provider(ODP.NET，Oracle.DataAccess.Client)
I assume you installed the Oracle Instant Client. The default Instant Client does not include neither any ODBC driver nor Oracle Data Provider (ODP.NET, Oracle.DataAccess.Client)
您可能有2个ODBC驱动程序，一个来自Oracle，通常称为 OraClient12_home1中的Oracle ，另一个来自Microsoft，称为 Microsoft ODBC for Oracle (应默认安装Windows)安装，但是还需要一个Oracle Client.)
You may have 2 ODBC drivers, one from Oracle typically called like Oracle in OraClient12_home1 and one from Microsoft called Microsoft ODBC for Oracle (which should be installed by default Windows installation, however it also requires an Oracle Client).
Oracle的ODBC驱动程序适用于32位和64位，而Microsoft驱动程序仅适用于32位.您有2位ODBC管理员:32位(运行c:\Windows\SysWOW64\odbcad32.exe)和64位(运行c:\Windows\System32\odbcad32.exe).在那里，您应该看到已安装的32位驱动程序. 64位.
The ODBC driver from Oracle is available for 32bit and 64bit, the Microsoft driver exits only for 32bit. You have 2 ODBC Administrators, 32bit (run c:\Windows\SysWOW64\odbcad32.exe) and 64bit (run c:\Windows\System32\odbcad32.exe). There you should see installed drivers for 32 resp. 64bit.
对于数据提供者，您有类似的情况.您有一个来自Microsoft(针对Oracle的 Microsoft .NET Framework数据提供程序，System.Data.OracleClient)和来自Oracle(针对.NET的 Oracle数据提供程序，Oracle.DataAccess.Client)之一，有多个版本).两者都可用于32位和64位.
For the Data Provider you have similar situation. You have one from Microsoft (Microsoft .NET Framework Data Provider for Oracle, System.Data.OracleClient) and from Oracle (Oracle Data Provider for .NET, Oracle.DataAccess.Client, several versions). Both are available for 32bit and 64bit.
原则上，使用哪个驱动程序/提供程序连接到Oracle都没有关系-只是架构(即32位和64位)必须匹配.每个驱动程序/提供程序都需要根据Oracle Client安装. Microsoft的所有驱动程序/提供程序都已弃用，您应该首选Oracle驱动程序/驱动程序(如警告消息中所述)
In principle it does not matter which driver/provider you use to connect to Oracle - just the architecture (i.e. 32 vs. 64 bit) has to match. Each driver/provider requires according Oracle Client installation. All drivers/providers from Microsoft have been deprecated, you should prefer the Oracle ones (as stated in the warning message)
Oracle还提供了 ODP.NET托管驱动程序，它不需要任何其他Oracle Client安装，并且可以在32位和64位上运行.但是，我不知道您是否可以在Excel中使用它.
Oracle provides also the ODP.NET, Managed Driver which does not require any further Oracle Client installation and runs on both, 32bit and 64bit. However, I don't know whether you can use this in Excel.
最后但并非最不重要的是，您还具有OLE DB提供程序.再次来自微软( Microsoft OLE DB Provider for Oracle )，另一个来自Oracle( Oracle Provider DB for OLE DB ). Microsoft提供程序仅适用于32位，并且已弃用.
Last but not least, you also have OLE DB provider. Again one from Microsoft (Microsoft OLE DB Provider for Oracle) and one from Oracle (Oracle Provider for OLE DB). The Microsoft provider exist only for 32bit and has been deprecated.
这篇关于通过Excel连接到Oracle数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
