vba 使用 ADO,如何调用 Oracle PL/SQL 块并指定输入/输出绑定变量(参数?)

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/2373401/
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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-11 11:23:34  来源:igfitidea点击:

With ADO, how do I call an Oracle PL/SQL block and specify input/output bind variables (parameters?)

oraclevbaado

提问by René Nyffenegger

I am trying to call a PL/SQL block with ADO and VBA, but I can't pass input and/or output bind variables (probably aka parameters).

我正在尝试使用 ADO 和 VBA 调用 PL/SQL 块,但我无法传递输入和/或输出绑定变量(可能也称为参数)。

dim cn as ADODB.connection
' ... open connection ...

dim plsql as string

plsql =         "declare"
plsql = plsql & "  num_in  number := ?;"
plsql = plsql & "  num_out number; "
plsql = plsql & "begin"
plsql = plsql & "  num_out := num_in * 5;"
plsql = plsql & "  ? := num_out;"
plsql = plsql & "end;"

dim cm as ADODB.command
set cm = new ADODB.command
set cm.activeConnection = cn
cm.commandText = plsql
cm.commandType = adCmdText

cm.parameters.append cm.createParameter(, adDouble, adParamInput,, 5)
cm.parameters.append cm.createParameter(, adDouble, adParamOutput   )

cm.execute  ' FAILS HERE

msgBox(cm.parameters(2))

The snippet above fails at the cm.executeline with an ORA-01008: not all variables bound

上面的代码片段在ORA-01008cm.execute行失败:并非所有变量都绑定

I'd appreciate any help towards a solution for my problem.

我很感激任何帮助解决我的问题。

回答by René Nyffenegger

It seems as though the statement cannot start with a declare. (Thanks to Thomas Jones-Low for his valuable comment).

似乎该语句不能以declare. (感谢 Thomas Jones-Low 的宝贵意见)。

So, the statement must be enclosed in another begin .. endblock:

因此,该语句必须包含在另一个begin .. end块中:

' additional begin so that the statement does not start with a declare:
plsql =         "begin "

plsql = plsql & "declare"
plsql = plsql & "  num_in  number := ?;"
plsql = plsql & "  num_out number; "
plsql = plsql & "begin"
plsql = plsql & "  num_out := num_in * 5;"
plsql = plsql & "  ? := num_out;"
plsql = plsql & "end;"

' closing the additional begin:
plsql = plsql & "end;"

Now, it works as expected.

现在,它按预期工作。