Returning Rows from a Stored Procedure in Oracle...can it be done?

From: Ryan Gallagher <ryang_at_quadrus.com>
Date: Tue, 27 Oct 1998 18:16:25 GMT
Message-ID: <Z5oZ1.3860$mP.4239842@news.rdc1.ab.wave.home.com>

In MS SQL server, I am able to have a store procedure that returns a result set and use it Via ADO recordset. How do I do this in Oracle?

Sample SQL server Code....

---

(For those of you who don't know MSSQL the last SQL Select statement in a
SQL statement will be returned as a dataset to ODBC)

CREATE PROCEDURE spGetUsers(@UID INTEGER) BEGIN     /* Heres some sort of condition statement*/

    IF @UID > 10
     BEGIN

        /*Return Users with UID < 10*/
        SELECT UID,UserName FROM tblUser WHERE UID < @UID

     END
    ELSE
     BEGIN

        /*Return Users with UID > 10*/
        SELECT UID,UserName FROM tblUser WHERE UID > @UID

     END

END Sample ADO Code (in VB5)....

---

    Dim dbCon As New ADODB.Connection
    Dim RS As New ADODB.Recordset

    'connect to the database
    dbCon.Open "DSN=MyDB", "sa", ""

    'Execute the SQL Statement
    RS.Open "EXEC spGetUsers 23", dbCon, adOpenForwardOnly

    *This should fill the RS with the records returned by spGetUsers*

Any help would be appreciated
(If possible, please Cc: me by email)

Thanks
Ryan Gallagher Received on Tue Oct 27 1998 - 12:16:25 CST