Returning recordsets from stored procs with ADO

From: <paulsherman_at_my-dejanews.com>
Date: Fri, 02 Apr 1999 23:55:18 GMT
Message-ID: <7e3ld3$bfr$1_at_nnrp1.dejanews.com>

This is my package:

Package VBTEST

IS
  TYPE ret_type is TABLE of VARCHAR2(25)   INDEX BY BINARY_INTEGER;  PROCEDURE RetArray

(RetValArray OUT ret_type);

END; -- Package specThis is package body:

---

Package Body VBTEST
is
 PROCEDURE RetArray

(RetValArray OUT ret_type)

 is

    CURSOR arr_cursor is

        select last_name
        from s_emp;

    arr_count NUMBER default 1;
 BEGIN
    for item in arr_cursor
    loop

        RetValArray(arr_count) := item.last_name;
        arr_count := arr_count + 1;

    end loop;
 end;
END;

---

And here I call that procedure from Visual Basic:

SQL = "{call VBTEST.RETARRAY({resultset 100,RetValArray} )}"

Set Qry.ActiveConnection = cnADO
Qry.CommandText = SQL
Qry.CommandType = adCmdText

Param.Type = adLongVarBinary
Param.Size = 100
Param.Direction = adParamOutput

Qry.Parameters.Append Param

rs.CursorType = adOpenStatic
rs.CursorLocation = adUseClient
rs.LockType = adLockReadOnly

Set rs.Source = Qry
rs.Open

Do While Not rs.EOF

    Combo1.AddItem rs(0)
    rs.MoveNext
Loop

It works OK with Latest Microsoft Oracle ODBC driver, but with latest Oracle ODBC driver (2.05.0301) gives an "Syntax error" message.

I would use the MICROSOFT driver, but some other stuff doesn't work with it.

Do you have any idea, what the difference is?

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Sat Apr 03 1999 - 01:55:18 CEST