PL/SQL stored procedure out cursor to VBA ADODB.RecordSet?

Question

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:

PROCEDURE get_product
(
    out_cur_data    OUT SYS_REFCURSOR,
    rptid           IN  NUMBER,
    scenario        IN  VARCHAR2
);

And I have written my macro as:

Sub GetProduct()
    Const StartRow As Integer = 4
    Dim conn As ADODB.Connection
    Set conn = New ADODB.Connection
    With conn
        .ConnectionString = "<my connection string>"
        .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

This does not work obviously, I get a run-time error '-2147217900 (80040e14): One or more errors occurred during processing of command.' So, OK.

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?

BTW... I do not have control of the stored procedure at all, it is from an external package.

Any help is really appreciated.

Thanks, Doran

Answer 1

I think it should be this one:

With cmd
    .Properties("PLSQLRSet") = TRUE
    .ActiveConnection = conn
    .CommandType = adCmdText
    .CommandText = "{call their_package.get_product(?,?)}"
    .NamedParameters = True
    .Parameters.Append cmd.CreateParameter("rptid", adNumeric, adParamInput, 0, 98)
    .Parameters.Append cmd.CreateParameter("scenario", adVarChar, adParamInput, 4, "decline001")
End With
...

Set rs = cmd.Execute
cmd.Properties("PLSQLRSet") = FALSE

Note: Although their_package.get_product() takes three parameters, only two need to be bound because Ref cursor parameters are automatically bound by the provider.

For more information check Oracle documentation: Oracle Provider for OLE DB Developer's Guide - "Using OraOLEDB with Visual Basic"