oracle - 对象关闭时不允许执行 ADO Oracle 操作

Question

我有一个 Excel 2013 VBA 宏，它需要在 Oracle 12c 数据库上调用 SQL 过程。执行 Oracle 过程(它将结果写入表中)，但在 Excel 中，我在 Set rs = cmd.Execute 处收到错误。 :

Operation is not allowed when the object is closed

代码下方:

Dim v_userpw As String

Dim cnn As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim l_userpw, l_reqid, l_pwhash, l_sighash As New ADODB.Parameter
Dim objErr As ADODB.Error

v_userpw = Cells(7, 1).Value

On Error GoTo err_test
'Set cnn = CreateObject("ADODB.Connection")
Set cnn = New ADODB.Connection
cnn.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=devdb;User ID=db1;Password=db1;"
cnn.Open
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cnn
Set l_userpw = cmd.CreateParameter("l_userpw", adVarChar, adParamInput, 1024, v_userpw)
cmd.Parameters.Append l_userpw
Set l_reqid = cmd.CreateParameter("l_reqid", adVarChar, adParamOutput, 1024)
cmd.Parameters.Append l_reqid
Set l_pwhash = cmd.CreateParameter("l_pwhash", adVarChar, adParamOutput, 1024)
cmd.Parameters.Append l_pwhash
Set l_sighash = cmd.CreateParameter("l_sighash", adVarChar, adParamOutput, 1024)
cmd.Parameters.Append l_sighash

'cmd.Properties("PLSQLRSet") = True
cmd.CommandText = "{CALL db1.genheader(?, ?, ?, ?)}"
Set rs = cmd.Execute
'cmd.Properties("PLSQLRSet") = False

Cells(8, 1) = rs.Fields("reqid").Value
Cells(9, 1) = rs.Fields("pwhash").Value
Cells(10, 1) = rs.Fields("sighash").Value

cnn.Close

err_test:
MsgBox Error$
For Each objErr In cnn.Errors
    MsgBox objErr.Description
Next
cnn.Errors.Clear
Resume Next

Oracle 过程如下所示:

create or replace procedure genheader (
l_userpw in varchar2,
l_reqid out varchar2,
--l_pwhash out raw,
--l_sighash out raw
l_vpwhash out varchar2,
l_vsighash out varchar2
)

我需要返回预定义单元格中的值。

Answer 1

该过程实际上是否返回结果集？看起来它只是使用输出参数返回数据，因此您不会从记录集中获取结果，而是在命令执行后从命令参数中获取它们。

cmd.Execute

Cells(8, 1) = cmd("l_reqid")
Cells(9, 1) = cmd("l_pwhash")
Cells(10, 1) = cmd("l_sighash")