通过VBA和ADO的VBA调用功能 [英] VBA calling function via VBA and ADO

问题描述

我在通过VBA在Oracle服务器上调用函数时遇到问题. 当我尝试调用不带参数的函数时.没关系. 但是，当我用参数调用函数时.我收到错误消息([Microsoft] [Oracle的ODBC驱动程序]参数类型无效) 有什么主意吗 这是vba代码和plsql(我为测试创建了基本函数)

I have a problem with calling my functions on Oracle server via VBA. When I try calling function without parameters. It´s ok. But, when I Calling functions with parameter. I get error ([Microsoft][ODBC driver for Oracle]Invalid parameter type) Have any idea? This is vba code and plsql (I make elementary function for test)

Vba

Private Sub test()
Dim Oracon As ADODB.Connection
Dim cmd As New ADODB.Command
Dim param1 As New ADODB.Parameter
Dim objErr As ADODB.Error


Set Oracon = CreateObject("ADODB.Connection")
mujuser = "xxxx"
mujPWD = "xxxxx"

  strConn = "UID=" & mujuser & ";PWD=" & mujPWD & ";driver={Microsoft ODBC for Oracle};" & _
              "SERVER=xx.xxx;"
Oracon.ConnectionString = strConn
Oracon.Open
cmd.ActiveConnection = Oracon
 cmd.CommandText = "el_test"
   cmd.CommandType = adCmdStoredProc

Set param1 = cmd.CreateParameter("P1", adLongVarChar, adParamInput, 256)
cmd.Parameters.Append param1
cmd.Parameters(0).Value = "ahoj1"

cmd.Execute

End Sub

和功能

CREATE OR REPLACE FUNCTION EL_TEST 
(
  P1 IN VARCHAR2 
) RETURN VARCHAR2 AS 
BEGIN
  RETURN 'Ahoj';
END EL_TEST;

谢谢.

推荐答案

我承认，我从未尝试通过代码来执行类似这样的功能，但是令我惊讶的是，在没有参数的情况下，它仍然有效，因为您拥有功能设置.我认为您希望获得价值的方式是:

I admit, I've never tried to execute a function like this through code, but I'm surprise this worked without the parameter because the way you have your function set up. I think the way you would want to get the value would be:

select el_test('ahoj1') from dual;

如果您从 function 更改为 procedure ，我认为它将按您期望的方式工作:

If you made this change from a function to a procedure, I think it will work the way you expect:

CREATE OR REPLACE procedure EL_TEST 
( P1 IN VARCHAR2,
  p2 out varchar2) is
BEGIN
  p2 := 'Ahoj';
END EL_TEST;

然后您的VBA将如下所示:

And then your VBA would look like this:

Private Sub test()
  Dim Oracon As ADODB.Connection
  Dim cmd As New ADODB.Command
  Dim param1 As New ADODB.Parameter
  Dim objErr As ADODB.Error

  Set Oracon = CreateObject("ADODB.Connection")
  mujuser = "xxxx"
  mujPWD = "xxxxxx"

  strConn = "UID=" & mujuser & ";PWD=" & mujPWD & _
        ";driver={Microsoft ODBC for Oracle};SERVER=xxxx-xx"

  Oracon.ConnectionString = strConn
  Oracon.Open

  cmd.ActiveConnection = Oracon
  cmd.CommandText = "el_test"
  cmd.CommandType = adCmdStoredProc
  cmd.NamedParameters = True

  cmd.Parameters.Append cmd.CreateParameter("P1", adVarChar, adParamInput, 256, "ahoj1")
  cmd.Parameters.Append cmd.CreateParameter("P2", adVarChar, adParamOutput, 256)

  cmd.Execute

  Dim result As String
  result = cmd.Parameters(1).Value

End Sub

这篇关于通过VBA和ADO的VBA调用功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！