VBA calling function via VBA and ADO

Question

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

And function

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

Thanky you.

Answer 1

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;

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;

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