title: PL/SQL stored procedure out cursor to VBA ADODB.RecordSet?
body: <p>To preface this post, I want to say that I am fairly new to Excel 2007 vba macros. I am trying to call an Oracle PL/SQL stored procedure that has a cursor as an output parameter. The procedure spec looks like this:</p>
  
  <pre><code>PROCEDURE get_product
  (
      out_cur_data    OUT SYS_REFCURSOR,
      rptid           IN  NUMBER,
      scenario        IN  VARCHAR2
  );
  </code></pre>
  
  <p>And I have written my macro as:</p>
  
  <pre><code>Sub GetProduct()
      Const StartRow As Integer = 4
      Dim conn As ADODB.Connection
      Set conn = New ADODB.Connection
      With conn
          .ConnectionString = "&lt;my connection string&gt;"
          .Open
      End With
  
      Dim cmd As ADODB.Command
      Set cmd = New ADODB.Command
      With cmd
          .ActiveConnection = conn
          .CommandType = adCmdText
          .CommandText = "{call their_package.get_product({out_cur_data 100},?,?)}"
          .NamedParameters = True
          .Parameters.Append cmd.CreateParameter("rptid", adNumeric, adParamInput, 0, 98)
          .Parameters.Append cmd.CreateParameter("scenario", adVarChar, adParamInput, 4, "decline001")
      End With
  
      Dim rs As ADODB.Recordset
      Set rs = New ADODB.Recordset
      With rs
          .CursorType = adOpenStatic
          .CursorLocation = adUseClient
          .LockType = adLockOptimistic
      End With
  
      Set rs = cmd.Execute
  
      Cells(StartRow + 1, 1).CopyFromRecordset rs
  
      rs.Close
      conn.Close
  End Sub
  </code></pre>
  
  <p>This does not work obviously, I get a run-time error '-2147217900 (80040e14): One or more errors occurred during processing of command.' So, OK. </p>
  
  <p>I am looking for some guidance/advice on how to bring back that cursor into an ADODB.RecordSet. I don't think I have set up the output cursor correctly for "out_cur_data", but my searches online for any help have come up dry so far. Can any give me a basic working example to help me understand what I am doing wrong?</p>
  
  <p>BTW... I do not have control of the stored procedure at all, it is from an external package.</p>
  
  <p>Any help is really appreciated.</p>
  
  <p>Thanks,
  Doran</p>