代码之家  ›  专栏  ›  技术社区  ›  René Nyffenegger

使用ado,如何调用oracle pl/sql块并指定输入/输出绑定变量(参数?)

  •  5
  • René Nyffenegger  · 技术社区  · 15 年前

    我试图用ado和vba调用pl/sql块,但是我不能传递输入和/或输出绑定变量(可能是aka参数)。

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

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

    如果有人帮我解决问题,我将不胜感激。

    1 回复  |  直到 9 年前
        1
  •  6
  •   René Nyffenegger    11 年前

    似乎这句话不能以 declare . (感谢托马斯·琼斯·洛的宝贵评论)。

    所以,声明必须包含在另一个 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;"
    

    现在,一切如期而至。