从 VBA ADODB 调用 Oracle 存储过程
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/5409160/
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
Calling Oracle Stored procedure from VBA ADODB
提问by Willy Elliker
Hi
I am trying to use a stored procedure in WORD VBA to retrieve some addresses using a stored procedure to populate a list field.
嗨,
我正在尝试使用 WORD VBA 中的存储过程来检索一些地址,使用存储过程填充列表字段。
Private Sub txtCpny_AfterUpdate()
Dim rst As ADODB.Recordset
Dim cmd As ADODB.Command
Dim param1 As ADODB.Parameter
Dim param2 As ADODB.Parameter
Dim strCpny As String
strCpny = GetSearchString(Me.txtCpny) 'ie %Name%
Set cmd = CreateObject("ADODB.Command")
With cmd
.ActiveConnection = mcn
.CommandText = "LISTPARTNER_NAME"
.CommandType = adCmdStoredProc
Set param1 = .CreateParameter("RCT1", adInteger, adParamInputOutput, , Null)
Set param2 = .CreateParameter("firmaName", adVarChar, adParamInput, 50, strCpny)
.Parameters.Append param1
.Parameters.Append param2
Set rst = .Execute
End With
...Using the recordset here
rst.Close
Set param1 = Nothing
Set param2 = Nothing
Set cmd = Nothing
End Sub
The Stored Procedure looks as follows: The SQL should result in a recordset holding the matching Companies.
存储过程如下所示: SQL 应该产生一个包含匹配公司的记录集。
PROCEDURE LISTPARTNER_NAME (
firmaName IN VARCHAR2 DEFAULT NULL,
RCT1 IN OUT GLOBALPKG.RCT1
)
AS
BEGIN
OPEN RCT1 FOR
SELECT
...
FROM
...
WHERE
KNAG.NAME_ORG LIKE LISTPARTNER_NAME.firmaName
...
END LISTPARTNER_NAME
When the command is executed VB throws a RunTime Error
执行命令时 VB 抛出运行时错误
ORA-06550: Line 1, column 13:
PLS-00306: wrong number or types of arguments in call to
'LISTPARTNER_NAME'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
In my opinion the I am doing something wrong wit the parameters. Ihave been trying various versions of setting the parameters with no luck
Any Clues?
Thanks
在我看来,我在参数上做错了。我一直在尝试各种版本的参数设置,但没有
任何线索有任何线索吗?谢谢
回答by Harrison
have a look at this thread, it may be of assistance http://forums.oracle.com/forums/thread.jspa?threadID=360922
看看这个线程,它可能有帮助 http://forums.oracle.com/forums/thread.jspa?threadID=360922
The only other thing I could suggest would be switching the order of the parameters (so they are in the same order -- I know ODP defaults to order but I am unsure of ADODB)
我唯一可以建议的另一件事是切换参数的顺序(因此它们的顺序相同——我知道 ODP 默认为顺序,但我不确定 ADODB)
回答by Patrick Honorez
I have been successfully using this code in Access:
我在 Access 中成功使用了此代码:
Function runAdo(sql As String, usr As String, pwd As String)
'by Patrick Honorez - www.idevlop.com ----- 09-nov-2012
'Purpose : run Oracle proc using ADO connection
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
cn.Open GetPersonalizedConnectStringADO(usr, pwd)
Set rs = New ADODB.Recordset
rs.Open sql, cn, adOpenStatic, adLockReadOnly
cn.Close
End Function
I rarely use ADO from Access, since I find DAO simpler to use, but in this case I had to execute some Oracle procs requiring a different UID, and creating a new DAO Querydef specifying different UID and PWD, did not work, perhaps due to the fact that Access keeps a cache of connections.
So I decided to use ADO for the second "user" and it works like a charm.
我很少使用 Access 中的 ADO,因为我发现 DAO 更易于使用,但在这种情况下,我不得不执行一些需要不同 UID 的 Oracle proc,并创建一个指定不同 UID 和 PWD 的新 DAO Querydef,但不起作用,可能是由于Access 保留连接缓存的事实。
所以我决定将 ADO 用于第二个“用户”,它的作用就像一个魅力。