Stored Procedure Return w/RDO/Oracle/VB4

I am using VB4 with Oracle and RDO data access.  I need to make an Oracle stored procedure call from an RDO object and return either return codes or rows from a table.  This is not documented very well and I am more of a DBA than a VB programmer.  I tried using the rdoPreparedStatement and seem to get close but that's it.  The particular procedure I am calling has an OUT parameter with no IN parameters but I need something generic enough to call functions or procedures in Oracle with any number of parameters.  Thanks.
renglandAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

twardCommented:
I am using Oracles Objects for OLE which seems to work well.

You use objects and basically open Dynasets with a query by which you can movenext, moveprevious through the sets returned.

Below is some sample code that I use all the time to do database stuff with Oracle:

  Option Explicit

  ' Oracle setup '
  Public OraSession As Object
  Public oraDatabase As Object

  ' Oracle Connect String (Used in DatabaseOpen) '
  Public CONNECT$

  ' Oracle Database String (Used in DatabaseOpen) '
  Public DATABASE$

  ' Oracle SQL String (Used in OpenDynaset) '
  Public SQL$

  Public DynaSetOpenError As Boolean
'''''''''''''''''''''''''''''''''''''''''''''''''''
'    Function:  DatabaseOpen(CONNECT$,DATABASE$)  '
'                                                 '
' Description:  Opens the Database Passed to it   '
'               in DATABASE using the             '
'               NAME/PASSWORD in CONNECT.         '
'                                                 '
'       Input:   CONNECT$ - "NAME/PASSWORD"       '
'               DATABASE$ - "X:ORA_DB_SRV:ORCL"   '
'                           "PROD"                '
'                                                 '
'      Output:  Message Box on Error, otherwise   '
'               an Open Database Object.          '
'                                                 '
'     Returns:   True = Database was Opened.      '
'               False = Database Open Failed.     '
'                                                 '
'''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function DatabaseOpen(ByVal CONNECT$, ByVal DATABASE$) As Boolean
 
  DatabaseOpen = True
 
  ' Setup The Oracle Session '
  On Error Resume Next
  Set OraSession = CreateObject("oracleinprocserver.xorasession")
  If Err.Number <> 0 Then
   
    MsgBox "Oracle Error:  " & Err.Description, vbOKOnly + vbCritical, "Database Session Error..."
    DatabaseOpen = False
    On Error GoTo 0
    Exit Function
   
  End If
 
  ' Setup The Oracle Connection '
  On Error Resume Next
  Set oraDatabase = OraSession.dbopendatabase(DATABASE$, CONNECT$, 0&)
 
  If Err.Number <> 0 Then
   
    MsgBox "Oracle Error:  " & Err.Description, vbOKOnly + vbCritical, "Database Connection Error..."
    DatabaseOpen = False
   
  End If
 
  On Error GoTo 0
 
End Function

'''''''''''''''''''''''''''''''''''''''''''''''''''
'    Function:  OpenDynaset(SQL$)                 '
'                                                 '
' Description:  Opens a Dynaset using the SQL$    '
'               SQL Statement passed to it.       '
'                                                 '
'       Input:  SQL$ - Any valid SQL Statement.   '
'                                                 '
'      Output:  An Open Dynaset, Use CloseDynaset '
'               to Close Dynasets.                '
'                                                 '
'     Returns:  The OpenDynaset Object.           '
'                                                 '
'''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function OpenDynaSet(ByVal SQL$) As Object
 
  On Error Resume Next
 
  DynaSetOpenError = False
 
  Set OpenDynaSet = oraDatabase.DBCREATEDYNASET(SQL$, 0&)
 
  If Err.Number <> 0 Then
 
    Call MsgBox("Error #" & Err.Number & ":  " & Err.Description, vbCritical + vbOKOnly, "SQL Query Error.....")
    DynaSetOpenError = True
   
  End If
  On Error GoTo 0
 
End Function

'''''''''''''''''''''''''''''''''''''''''''''''''''
'    Function:  CloseDynaSet()                    '
'                                                 '
' Description:  Closes the Open Dynaset.          '
'                                                 '
'       Input:  None.                             '
'                                                 '
'      Output:  None.                             '
'                                                 '
'     Returns:  Nothing into the DynaSet Object.  '
'                                                 '
'''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function CloseDynaSet() As Object

  Set CloseDynaSet = Nothing

End Function

'''''''''''''''''''''''''''''''''''''''''''''''''''
'    Function:  DatabaseClose()                   '
'                                                 '
' Description:  Closes an Open Oracle Database.   '
'               Database should have been opened  '
'               using OpenDatabase.               '
'                                                 '
'       Input:  NONE.                             '
'                                                 '
'      Output:  Clears OraSession and oraDatabase.'
'                                                 '
'     Returns:  NONE.                             '
'                                                 '
'''''''''''''''''''''''''''''''''''''''''''''''''''
Public Sub DatabaseClose()
 
  Set OraSession = Nothing
  Set oraDatabase = Nothing

End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.