CODEWITHSUNDEEP
oracle-databasevbaplsqlado
Edák Edák
Edák Edák

Reputation: 33

VBA calling function via VBA and ADO

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.

Upvotes: 0

Views: 3237

Answers (2)

Howd
Howd

Reputation: 71

Get function return value with OLEDB

I searched high and low for this but finally solved the problem myself.
My solution is in VBScript but I have represented it in the (untested) code below.

The trick is that the first parameter is the return value.

Private Sub test()
Dim Oracon As ADODB.Connection
Dim cmd As New ADODB.Command
Dim param1 As New ADODB.Parameter
Dim param0 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 param0 = cmd.CreateParameter("P0", adLongVarChar, adParamReturnValue, 256)
Set param1 = cmd.CreateParameter("P1", adLongVarChar, adParamInput, 256)
cmd.Parameters.Append param0
cmd.Parameters.Append param1

cmd.Execute

Dim result As String
result = param0.Value  ' Use the variable you set. Same as cmd.Parameters(1).Value '

End Sub

Upvotes: 1

Hambone
Hambone

Reputation: 16397

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

Upvotes: 0

Related Questions