running oracle procedure from vb6

hi

how can i run

Code:

---

begin
dbms_stats.gather_table_stats(ownname=> 'usr', tabname=> 'TBL', partname=> NULL , estimate percent=> 30 );
end;
/

---

through vb . I am using adodb connection with oracle 9i

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

Code:

---

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

thanks for your help