PL/SQL存储过程将光标移到VBA ADODB.RecordSet吗? [英] PL/SQL stored procedure out cursor to VBA ADODB.RecordSet?

问题描述

在开始本文之前，我想说我对Excel 2007 vba宏还很陌生.我正在尝试调用一个具有游标作为输出参数的Oracle PL/SQL存储过程.该过程规范如下所示:

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
);

我将宏写为:

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

这显然不起作用，我收到运行时错误"-2147217900(80040e14):在处理命令期间发生一个或多个错误."那么好吧.

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

我正在寻找有关如何将光标带回ADODB.RecordSet的一些指导/建议.我认为我没有为"out_cur_data"正确设置输出游标，但是到目前为止，我对任何帮助的在线搜索都干dry了.任何人都可以给我一个基本的工作示例，以帮助我了解我在做什么错吗?

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.

谢谢， 多兰(Doran)

Thanks, Doran

推荐答案

我认为应该是这样的:

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

注意: 尽管them_package.get_product()带有三个参数，但是由于Ref游标参数由提供程序自动绑定，因此仅需要绑定两个.

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.

有关更多信息，请查看Oracle文档: Oracle Provider for OLE DB开发人员指南-在Visual Basic中使用OraOLEDB"

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

这篇关于PL/SQL存储过程将光标移到VBA ADODB.RecordSet吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！