Oracle Stored Procedure Returning Recordset

Oracle Stored Procedure Returning Recordset

Post by Eric Krausk » Tue, 14 Mar 2000 04:00:00



I have created an Oracle stored procedure that is *capable* of returning a
recordset to the caller by using examples from Microsoft's site.  I have
been successful in getting this to work by using ADO directly:

    Dim oCon                        As ADODB.Connection
    Dim oCommand               As ADODB.Command
    Dim oRS                          As ADODB.Recordset

    Set oCon = New ADODB.Connection
    oCon.CursorLocation = adUseClient
    Call oCon.Open("DSN=MyDB;UID=me;PWD=pass")
    Set oCommand = New ADODB.Command
    oCommand.CommandText = "{call
PackageName.StoredProcedureName(?,{resultset 20, Col0, Col1, Col2, Col3})}"
    oCommand.CommandType = adCmdText

    oCommand.Parameters.Append oCommand.CreateParameter(, adInteger,
adParamInput)
    oCommand(0) = 20
    Set oCommand.ActiveConnection = oCon
    Set oRS = New ADODB.Recordset
    oRS.CursorType = adOpenStatic
    Set oRS.Source = oCommand
    Call oRS.Open

As you can see, the code to call the stored procedure has to specify the
columns that it expects back (Col0..Col3).  This can become ugly and
cumbersome - especially since the entire purpose for me calling a stored
procedure to get data back is so the business object is completely ignorant
of the database schema.  What I would really like to do is call a command
from the data environment and have it set the recordset automatically for
me:

    Set oDataEnv = New denvTest

    Call oDataEnv.CommandName(20)
    Set oRS = oDataEnv.rsCommandName

For some reason, this does not work against Oracle as it does SQL Server.
Does anyone know how to make this work?  I would much prefer to call a
stored procedure this way, allowing the business layer to be dumb when it
calls the db besides for method (stored procedure) calls.

Thanks in advance,

Eric Krauska