vba ORA-01019 从 Excel 连接到 Oracle
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3306116/
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
ORA-01019 connecting to Oracle from Excel
提问by ramesh
I have installed Oracle 10g Express Edition. When try to test the connection I am getting the error "Error while trying to retrieve text for error ORA-01019".
我已经安装了 Oracle 10g 快捷版。当尝试测试连接时，我收到错误“尝试检索错误 ORA-01019 的文本时出错”。
Below is my code.
下面是我的代码。
strConnection = "Driver={Microsoft ODBC for
Oracle};Server=Servername;Uid=username;Pwd=password;"
Set conn = CreateObject("ADODB.Connection")
conn.Open strConnection
conn.Close
Set conn = Nothing
Thanks in advance
提前致谢
回答by DevRanger
I had the following error occur recently.
我最近发生了以下错误。
System.Runtime.InteropServices.COMException (0x80004005): ORA-01019: unable to allocate memory in the user side
at ADODB.ConnectionClass.Open(String ConnectionString, String UserID, String Password, Int32 Options)
I managed to resolve the problem by simply modifying my connection string.
我通过简单地修改我的连接字符串设法解决了这个问题。
from:
从：
"Provider=MSDAORA.1;Data Source=tprss;Persist Security Info=True;User ID=myUser;Password=myPassword"
To:
到：
"Provider=MSDASQL;Data Source=tprss;Persist Security Info=True;User ID=myUser;Password=myPassword"
someone modified/updated the components on the box.
有人修改/更新了盒子上的组件。
回答by Sjuul Janssen
"ORA-01019 unable to allocate memory in the user side
“ORA-01019 无法在用户端分配内存
Cause: The user side memory allocator returned an error.
原因：用户端内存分配器返回错误。
Action: Increase the size of the process heap or switch to the old set of calls."
行动：增加进程堆的大小或切换到旧的调用集。”
Followup from the comments:
来自评论的跟进：
Could you try this code?
你能试试这个代码吗？
Dim Cn As ADODB.Connection
Dim CP As ADODB.Command
Dim Rs As ADODB.Recordset
Dim Conn As String
Dim QSQL As String
'Connect to Oracele server begin
Conn = "DRIVER={ORACLE ODBC DRIVER};SERVER=Service name;UID=username;PWD=password;DBQ=Service name;DBA=W;APA=T;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;FRL=F;MTS=F;CSR=F;PFC=10;TLO=O;"
Set Cn = New ADODB.Connection
With Cn
.ConnectionString = Conn
.CursorLocation = adUseClient
.Open
End With
If Cn.State = adStateOpen Then
MsgBox "Connection successful."
End If
'Connect to Oracle server end
'close connection begin
Cn.Close
Set Cn = Nothing
Set CP = Nothing
'close connection end
'关闭连接端
回答by Sjuul Janssen
(This didnt fit in a Comment box)
（这不适合在评论框中）
You need at least one driver. The oracle driver is best but Microsoft Driver will work too.
您至少需要一名司机。oracle 驱动程序是最好的，但 Microsoft 驱动程序也可以工作。
Lets first try to make a connection string. Right click on your desktop and the create a new .txt file. Now rename your textfile to something.udl Double click on the udl file. Go to "Provider" and select Microsoft OLEDB Provider for Oracle. Then click on next. In the server name field you fill in your TNS name. Then username and password and put a V inside "Allow saving password" (we will need this) And click on test connection. Make sure this works.
让我们首先尝试创建一个连接字符串。右键单击您的桌面并创建一个新的 .txt 文件。现在将您的文本文件重命名为 something.udl 双击 udl 文件。转到“Provider”并选择 Microsoft OLEDB Provider for Oracle。然后点击下一步。在服务器名称字段中填写您的 TNS 名称。然后用户名和密码并在“允许保存密码”中放置一个 V（我们将需要这个）并单击测试连接。确保这有效。
If it works then click on OK. Now open the UDL file with a text editor. You will see something similar to:
如果有效，则单击“确定”。现在使用文本编辑器打开 UDL 文件。你会看到类似的东西：
[oledb]
; Everything after this line is an OLE DB initstring
Provider=MSDAORA.1;Password=yourpw;User ID=youruser;Data Source=yourTNS;Persist Security Info=True
Copy this part into your connection string:
将此部分复制到您的连接字符串中：
Provider=MSDAORA.1;Password=yourpw;User ID=youruser;Data Source=yourTNS
Now your connection string should look like:
现在您的连接字符串应如下所示：
Conn = "Provider=MSDAORA.1;Password=yourpw;User ID=youruser;Data Source=yourTNS"
I hope this works.
我希望这有效。
回答by CactusPCHyman
We had the same problem, specifically on Windows 7 when using the Microsoft OleDb driver from VB6. Following the instructions in this post fixed out problem: http://prasanth4microsoft.blogspot.com/2010/11/windows7-excel-vba-ora-01019-unable-to.html
我们遇到了同样的问题，特别是在 Windows 7 上使用来自 VB6 的 Microsoft OleDb 驱动程序时。按照这篇文章中的说明解决了问题：http: //prasanth4microsoft.blogspot.com/2010/11/windows7-excel-vba-ora-01019-unable-to.html
回答by user6269646
I had this problem also but it is on win10.. After I have tried a lots of different solution from web .. Finally.. it worked to change connection string to fix this problem.. But I changed "Provider=MSDAORA.1"to "Provider=OraOLEDB.Oracle"
我也有这个问题，但它是在win10 ..我已经尝试了很多从网络上不同的解决方案后..最后..它努力改变连接字符串来解决这个问题。但我改变了"Provider=MSDAORA.1"对"Provider=OraOLEDB.Oracle"
