title: Call Oracle stored procedure with VBA using a refcursor
body: <p>I have stored procedure in Oracle:</p>
  
  <pre><code>create or replace procedure testproc (articlenr in number, storenr in number, cweek in varchar, prc out sys_refcursor)
  is begin
  open prc for 
  select * from weekly_revenues
  where
    article = articlenr
    and period = cweek
    and store = storenr
    ;
  end;
  </code></pre>
  
  <p>I can call that function perfectly with the following SQL code:</p>
  
  <pre><code>variable rc refcursor;
  exec testproc(123,345,'201705',:rc);
  print rc;
  </code></pre>
  
  <p>That code gives me all the sales data for article 123, store 345 in week 5 of 2017.</p>
  
  <p>Now, I want to call that stored procedure from VBA and I've tried the following code:</p>
  
  <pre><code>Sub ConnectToOracle()
  
  Dim cn As ADODB.Connection
  Dim rs As ADODB.Recordset
  Dim cmd As New ADODB.Command
  Dim param1 As New ADODB.Parameter
  Dim param2 As New ADODB.Parameter
  Dim arr As Variant
  
  connstr = "Provider=msdaora;Data Source=###;User Id=###;Password=###;"
  
  Set cn = New ADODB.Connection
  Set rs = New ADODB.Recordset
  
  cn.Open connstr
  
  cmd.CommandText = "testproc"
  cmd.ActiveConnection = cn
  cmd.CommandType = adCmdStoredProc
  Set param1 = cmd.CreateParameter("articlenr", adInteger, adParamInput, , 47)
  Set param2 = cmd.CreateParameter("storenr", adInteger, adParamInput, , 281)
  Set param3 = cmd.CreateParameter("cweek", adVarChar, adParamInput, 10, "201705")
  Set param4 = cmd.CreateParameter("prc", adVariant, adParamOutput, , Null)
  cmd.Parameters.Append param1
  cmd.Parameters.Append param2
  cmd.Parameters.Append param3
  cmd.Parameters.Append param4
  
  Set rs = cmd.Execute
  
  arr = rs.GetRows
  'Work with array...
  
  End Sub
  </code></pre>
  
  <p>However, I get an arrow for the</p>
  
  <pre><code>Set rs = cmd.Execute
  </code></pre>
  
  <p>line ("ORA-0136: illegal variable name/number"). Does anyone know how I can get my code to run? I'm not sure whether the variable for the refcursor needs to be declared in a different way (as in the second code snippet), but I'm not sure whether that is the mistake or not.</p>
  
  <p>Thanks!</p>