running oracle procedure from vb6

[leinad31]

Sample for a function (note adParamReturnValue for return value of function). For a procedure you will simply omit parameter for retval. More important is reference on data type mapping for the in/out parameters http://www.carlprothman.net/Default.aspx?tabid=97, you will be using variant a lot

Public Function CountRaffleEntries(ByVal valDate As Date) As Variant
Dim cmd As ADODB.Command

   If CheckConn = False Then
      MsgBox "An error was encountered. Please logout then reconnect.", vbOKOnly, "Connection Check"
      Exit Function
   End If
   
   Set cmd = New ADODB.Command
   Set cmd.ActiveConnection = conn
   
   cmd.CommandType = adCmdStoredProc
   cmd.CommandText = "dbadm.sales_drive_raffle.count_raffle_entries"
   
   cmd.Parameters.Append cmd.CreateParameter("retnum", adNumeric, adParamReturnValue)
   
   cmd.Parameters.Append cmd.CreateParameter("p_raffle_dt", adDBTimeStamp, adParamInput)
   cmd.Parameters("p_raffle_dt").Value = valDate
   
   cmd.Parameters.Append cmd.CreateParameter("p_category", adVariant, adParamInput)
   cmd.Parameters("p_category").Value = SALES_DRIVE_CATEGORY
   
   cmd.Parameters.Append cmd.CreateParameter("p_year", adNumeric, adParamInput)
   cmd.Parameters("p_year").Value = Year(valDate)
   
   cmd.Execute
   
   CountRaffleEntries = cmd.Parameters.Item(0).Value
   
   Set cmd = Nothing
End Function

Lastly, don't save sys credential in code and for other users don't forget to check your privileges