Ref Cursor failing in VBS
142970Mar 2 2004 — edited Apr 28 2004I have been unable to get a reference cursor back using OLDB and VB Script (or VBA). The stored proc is:
PROCEDURE REF_CURSOR_PROC(c_result in out my_ref)
IS
x_result my_ref ;
begin
OPEN x_result FOR
SELECT * FROM STVPREL
ORDER BY STVPREL_CODE;
c_result := x_result;
end;
The VB code is:
set Conn = CreateObject("ADODB.Connection")
conn.open "Provider=OraOLEDB.Oracle;Data Source=tester; User ID=humpty; Password=dumpty;PLSQLRSet=1"
set result_cmd = createObject("ADODB.Command")
SqlStatement = "{ CALL yokedxs.ref_cursor_proc() }"
result_cmd.CommandText = SqlStatement
result_cmd.ActiveConnection = conn
result_cmd.CommandType = 1
set rs = CreateObject("ADODB.Recordset")
rs = result_cmd.Execute
do until rs.eof
wscript.echo rs.fields(0)
rs.movenext
loop
Fails with:
PLS-00306: wrong_number or types of arguments in call to 'REF_CURSOR_PROC'
As far as I can tell this is about as simple as it can be. What am I doing wrong?
BTW- The ref cursor works as expected in SQL+PLUS and other return types work as expected in VBS. It seems as though the provider isn't handling ref cursors as advertised.
Thanks