Excel VBA使用InstantClient连接到远程Oracle DB [英] Excel VBA connect to remote Oracle DB with InstantClient

问题描述

我正在尝试使用Excel(主要是2003，以提高用户兼容性)连接到远程Oracle DB.我想运行.sql脚本并将数据集返回到工作表.
我在Windows 7 64位计算机上.我不知道Oracle DB服务器的规格.
我想使它尽可能轻巧(在客户端计算机上没有额外的文件安装，请尽可能将共享的网络位置用于所需的文件)

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)

我从Oracle下载了InstantClient并将其安装"(32位和64位的版本分别为12.1和11.2)到远程网络位置.
我尝试使用SQL Plus连接到Oracle DB，并且运行良好(我尝试了几个已安装的InstantClient版本，以查看是否存在任何兼容性问题).
作为测试:在VBA中使用SQL Plus和Shell函数，我能够成功地将数据假脱机到单独的excel文件中.

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.

我使用各种驱动程序/提供程序尝试了几种不同的连接字符串格式:

I tried several different connection string formats using various drivers/providers:

• Driver = {instantclient_11_2中的Oracle}
• Driver = {Microsoft ODBC for Oracle}
• Provider = MSDAORA
• Provider = MSDAORA.1
• Provider = OraOLEDB.Oracle
  

• Driver={Oracle in instantclient_11_2}
• Driver={Microsoft ODBC for Oracle}
• Provider=MSDAORA
• Provider=MSDAORA.1
• Provider=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.

我已经将包含InstantClient文件的网络位置添加到PATH环境变量中.不知道我还需要其他哪些环境变量，即使我当前的环境变量是正确的也是如此.

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.

我需要吗?
TNS_ADMIN? ORACLE_HOME?

Do I need:
TNS_ADMIN? ORACLE_HOME?

• 如何使用位于网络(共享)位置的 instantclient 文件通过VBA连接到远程Oracle DB /em>?
  

• How do I connect to the remote Oracle DB with VBA, using the instantclient files that are located in a network (share) location?
  

• 正确的 full 连接字符串是什么? (我将EZConnect格式与SQLPlus一起使用；实际的连接详细信息是否相同?为澄清起见，有人可以举例说明EZConnect格式如何转换为其他格式吗?)
  

• What is the correct full connection 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/[e-mail]/mydb

为此目的我应该使用什么提供者"或驱动程序" ，并且有什么显着差异吗?

What "provider" or "driver" should I use for this purpose and are there any significant differences?

我发现了很多相似或相关的问题，但是没有一个直接回答我的问题或对我有足够的帮助来完全解决问题.

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.

推荐答案

最终编辑/使用此功能(不使用驱动程序/提供程序: InstantClient ，但仍使用文件) :

Ended up editing/using this function (which does not(?) use driver/provider: InstantClient but still uses the files):

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;

提供者或驱动程序:
{Microsoft ODBC for Oracle}

Provider or Driver:
{Microsoft ODBC for Oracle}

需要将PATH环境变量设置为指向InstantClient.
没有使用任何其他环境变量，例如ORACLE_HOME，TNS_ADMIN等

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.

这篇关于Excel VBA使用InstantClient连接到远程Oracle DB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！