PL/SQL 存储过程将游标输出到 VBA ADODB.RecordSet？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23554260/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PL/SQL stored procedure out cursor to VBA ADODB.RecordSet?
提问by DoranKatt
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:
作为这篇文章的序言，我想说我对 Excel 2007 vba 宏还是很陌生。我正在尝试调用具有游标作为输出参数的 Oracle PL/SQL 存储过程。程序规范如下所示：
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.
这显然不起作用，我收到运行时错误“-2147217900 (80040e14)：在处理命令期间发生一个或多个错误。” 那么好吧。
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?
我正在寻找有关如何将该光标带回 ADODB.RecordSet 的一些指导/建议。我认为我没有为“out_cur_data”正确设置输出光标，但到目前为止我在网上搜索任何帮助都没有成功。谁能给我一个基本的工作示例来帮助我理解我做错了什么？
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
谢谢，多兰
回答by Wernfried Domscheit
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.
注意：虽然 their_package.get_product() 需要三个参数，但只需要绑定两个，因为 Ref 游标参数是由 provider 自动绑定的。
For more information check Oracle documentation: Oracle Provider for OLE DB Developer's Guide - "Using OraOLEDB with Visual Basic"
有关更多信息，请查看 Oracle 文档：Oracle Provider for OLE DB Developer's Guide - “Using OraOLEDB with Visual Basic”
