SQL Excel VBA 使用 InstantClient 连接到远程 Oracle DB
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22154288/
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
Excel VBA connect to remote Oracle DB with InstantClient
提问by agent provocateur
I am trying to use Excel (mainly 2003, for more user compatibility) to connect to a remote Oracle DB. I would like to run a .sql script and return the dataset to a worksheet.
I am on a Windows 7 64bit machine. I do not know the specs of the Oracle DB server.
I would like to keep this as lightweight as possible (no extra file installations on client machines, use shared network locations for required files as much as possible)
我正在尝试使用 Excel（主要是 2003，以获得更多用户兼容性）连接到远程 Oracle DB。我想运行一个 .sql 脚本并将数据集返回到工作表。
我在 Windows 7 64 位机器上。我不知道 Oracle DB 服务器的规格。
我想尽可能地保持轻量级（客户端机器上没有额外的文件安装，尽可能多地使用共享网络位置所需的文件）
So Far:
迄今为止：
I downloaded and "installed" the InstantClient from Oracle (versions 12.1 and 11.2 for both 32bit and 64bit) onto a remote network location.
I tried connecting to the Oracle DB using SQL Plus and it worked fine (I tried several of the installed InstantClient versions to see if there would be any compatibility issues).
As a test: using SQL Plus and the Shell function in VBA, I was able to successfully spool the data into a separate excel file.
我从 Oracle（32 位和 64 位版本 12.1 和 11.2）下载并“安装”到远程网络位置。
我尝试使用 SQL Plus 连接到 Oracle DB，它工作正常（我尝试了几个已安装的 InstantClient 版本，以查看是否存在任何兼容性问题）。
作为测试：在 VBA 中使用 SQL Plus 和 Shell 函数，我能够成功地将数据假脱机到一个单独的 excel 文件中。
I tried several different connection string formats using various drivers/providers:
我使用各种驱动程序/提供商尝试了几种不同的连接字符串格式：
- Driver={Oracle in instantclient_11_2}
- Driver={Microsoft ODBC for Oracle}
- Provider=MSDAORA
- Provider=MSDAORA.1
- Provider=OraOLEDB.Oracle
- 驱动程序={instantclient_11_2中的Oracle}
- 驱动程序={Microsoft ODBC for Oracle}
- 提供者=MSDAORA
- 提供者=MSDAORA.1
- 提供者=OraOLEDB.Oracle
Errors I Received:
我收到的错误：
"Run-time error '-2147467259 (80004005)':
[Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed
The Oracle(tm) client and networking components were not found. These components are supplied by Oracle Corporation..."
"Run-time error '-2147467259 (80004005)':
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"
"Run-time error '3706':
Provider cannot be found. It may not be properly installed"
And a few other similar errors.
以及其他一些类似的错误。
I have added the network location containing the instantclient files to my PATH environmental variable. Not sure what other environmental variables I require or even if my current one is correct.
我已将包含 Instantclient 文件的网络位置添加到我的 PATH 环境变量中。不确定我需要哪些其他环境变量，或者即使我当前的环境变量是正确的。
Do I need:
TNS_ADMIN?
ORACLE_HOME?
我需要：
TNS_ADMIN？ORACLE_HOME？
Question:
题：
How do I connect to the remote Oracle DBwith VBA, using the instantclientfiles that are located in a network (share) location?
What is the correct fullconnection string? (I used the EZConnect format with SQLPlus; are the actual connection details the same? and for clarification, could someone post an example of how the EZConnect format converts to the other format(s)?)
My EZConnect Format: username/[email protected]/mydbWhat "provider" or "driver" shouldI use for this purpose and are there any significant differences?
- What environmental variables do I requireto make this work?
如何使用位于网络（共享）位置的Instantclient文件通过 VBA连接到远程 Oracle DB？
什么是正确的完整连接字符串？（我在 SQLPlus 中使用了 EZConnect 格式；实际的连接细节是否相同？为了澄清起见，有人可以发布一个关于 EZConnect 格式如何转换为其他格式的示例吗？）
My EZConnect Format: username/[email protected]/mydb我应该为此目的使用什么“提供者”或“驱动程序” ，是否有任何显着差异？
- 我需要什么环境变量才能完成这项工作？
I've found a lot of questions that are similar or related, but none that directly answered my question or helped me enough to completely solve it.
我发现了很多相似或相关的问题，但没有一个直接回答我的问题或帮助我完全解决它。
采纳答案by agent provocateur
Ended up editing/using this function (which does not(?) use driver/provider: InstantClientbut still uses the files):
结束编辑/使用此功能（不（？）使用驱动程序/提供程序：InstantClient但仍使用文件）：
Function ORAQUERY(strHost As String, strDatabase As String, strSQL As String, strUser As String, strPassword As String)
Dim strConOracle, oConOracle, oRsOracle
Dim StrResult As String
StrResult = ""
strConOracle = "Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=" & strHost & ")(PORT=1521))" & _
"(CONNECT_DATA=(SERVICE_NAME=" & strDatabase & "))); uid=" & strUser & " ;pwd=" & strPassword & ";"
Set oConOracle = CreateObject("ADODB.Connection")
Set oRsOracle = CreateObject("ADODB.Recordset")
oConOracle.Open strConOracle
Set oRsOracle = oConOracle.Execute(strSQL)
MsgBox (oRsOracle.Fields(0).Value)
varResult = oRsOracle.GetRows
Do While Not oRsOracle.EOF
If StrResult <> "" Then
StrResult = StrResult & Chr(10) & oRsOracle.Fields(0).Value
Else
StrResult = oRsOracle.Fields(0).Value
End If
oRsOracle.MoveNext
Loop
oConOracle.Close
Set oRsOracle = Nothing
Set oConOracle = Nothing
ORAQUERY = StrResult
End Function
Correct full Connection String:
更正完整的连接字符串：
Driver={Microsoft ODBC for Oracle}; CONNECTSTRING=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=strHost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=strDatabase))); uid=strUser; pwd=strPassword;
Provider or Driver:
{Microsoft ODBC for Oracle}
提供程序或驱动程序：
{Microsoft ODBC for Oracle}
Needed to set PATH environmental variable to point to instantclient.
Didn't use any of the other environmental variables e.g. ORACLE_HOME, TNS_ADMIN, etc.
需要将 PATH 环境变量设置为指向 Instantclient。
没有使用任何其他环境变量，例如 ORACLE_HOME、TNS_ADMIN 等。
