With ADO, how can I call an Oracle PL / SQL block and specify I / O binding variables (parameters?)

Question

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

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

Count of fragment above in line cm.executewith ORA-01008: not all variables are connected

I would be grateful for any help in solving my problem.

Answer 1

It seems that the instruction cannot begin with declare. (Thanks to Thomas Jones-Low for his valuable comment).

So, the operator should be enclosed in another block 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.