ADODB command object - syntax error on EXECUTE with a return value on stored procedure

[mekilah]

Hi,

I have been searching for a couple days for help on this issue. I am connecting to an Oracle database via VBScript to execute a stored procedure, and need to capture the return value of the procedure. When using the command line, I can see that the stored procedure works and returns the desired string.

Here is the code I am attempting:

Const adChar = 129 
Const adVarChar = 200 
Const adInteger = 3
Const adParamInput = 1
Const adParamOutput = 2
Const adParamReturnValue = 4

Set oADO_CMD = CreateObject("ADODB.Command")

sConnectionStr = "DRIVER={Oracle in OraClient11g_home1};SERVER=....."
' Activate the connection
oADO_CMD.ActiveConnection = sConnectionStr

' Set the command type to Stored Procedures
oADO_CMD.CommandType = 4 ' code for "stored procedure"
oADO_CMD.CommandText =  "chessstg.pkg_qtp.qtp_test"

'Set objparameter=objcommand.CreateParameter (name,type,direction,size,value)
set param = oADO_CMD.CreateParameter("result", adVarChar, adParamReturnValue, 400)
oADO_CMD.Parameters.Append param

' Execute the stored procedure
oADO_CMD.Execute 'this line gives me "[Oracle][ODBC]Syntax error or access violation."
res =oADO_CMD("result")
msgbox res
msgbox oADO_CMD.Parameters.Count

Set oADO_CMD = Nothing

The stored procedure has no inputs or output values, only the returned string "PL/SQL procedure successfully completed." No recordset is returned either.

If I comment out the line where I append the return value parameter, the Execute method runs fine.

Any help would be GREATLY appreciated - I am new to VBScript but getting these return values is essential to my application.

Thanks -