I know there have been many postings about this, but I tried the solutions
suggested and they didn't work. Lastly I found the following article in
MSDN: Q255043 - HOWTO: Retrieve ADO Recordset from Oracle (REF CURSOR)
Through ASP. Tried that and I keep getting: "Run-time error '-2147217900
(80040e14)': Syntax error in {call...} ODBC Escape."
I am running my test application on Windows 2000. I appreciate any help.

-Michal

Here is the code I am using:

Oracle code:

create or replace package experimentincursors
as
    cursor c1 is select constituent.firstname || ' ' || constituent.lastname
as ConstituentName from constituent;
    type cursorType is ref cursor return c1%ROWTYPE;
    Procedure spListEmp(theCursor in out cursorType);
end;
/

create or replace package body experimentincursors as
procedure spListEmp(theCursor in out cursorType) is
    v_cursor cursorType;
BEGIN
    OPEN v_cursor FOR
        SELECT constituent.firstname || ' ' || constituent.lastname as
ConstituentName from constituent;
    theCursor := v_cursor;
END spListEmp;
END experimentincursors;
/

VB Code:

Private Sub Command1_Click()
    Dim SOME_DA As SOME_DataAccess
    Dim RS As ADODB.Recordset

    Set SOME_DA = New SOME_DataAccess
    Set RS = SOME_DA.RunSPReturnRS("{call
experimentincursors.spListEmp({resultset 0, theCursor})}")
End Sub

Function RunSPReturnRS(ByVal strSP As String) As ADODB.Recordset
    On Error GoTo ErrorHandler

    ' Create the ADO objects
    Dim cmd As ADODB.Command

    ' GetContext.CreateObject does not work with Oracle driver

    'Set cmd = CtxCreateObject("ADODB.Command")

    Set cmd = New ADODB.Command

    ' Init the ADO objects & the stored proc parameters
    cmd.ActiveConnection = Conn
    cmd.CommandText = strSP
    cmd.CommandType = adCmdStoredProc
    Set RunSPReturnRS = cmd.Execute

    ' Cleanup and return
    Set cmd = Nothing
    Exit Function

ErrorHandler:
    Set cmd = Nothing
    RaiseError g_modName, "RunSPReturnRS(" & strSP & ", ...)"
End Function

Public Function GetConnectionString() As String
    GetConnectionString = "Provider=MSDAORA.1;Data Source=PC67ORACLE;User
ID=SomeUser;Password=SomePWD"
End Function