从 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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-18 23:08:37  来源:igfitidea点击:

Calling Oracle Stored procedure from VBA ADODB

oraclestored-proceduresadodbword-vba

提问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 用于第二个“用户”,它的作用就像一个魅力。