Calling a Oracle Stored Procedure

[maboo]

does anyone know the syntax for calling a stored procedure.
the one i need to call will be taking one paramter and returning one value to signify success or failure.
any help would be appreciated.
thanks

 

[B827]

I think you do it via a pass through query, I seem to remember doing something like this, but am still racking my brain trying to remember where it was. Sandy

 

[B827]

I knew I had it somewhere! You can do this in code and here is what you need.

Function RunProcedure(Userid As String, Pwd As String, ProcName As String)
Dim ErrMsg As String, ConnectStr As String, ProcStr As String
ConnectStr = "ODBC;DSN=Sandy;UID=" & Chr(34) & Userid & Chr(34) & ";PWD=" & Chr(34) & Pwd & Chr(34) & ";SERVER=TNS:SANDY;"
On Error GoTo Err_RunProcedure
Dim wrkODBC As Workspace, qdf As QueryDef
Dim conODBC As Connection, Rst As Recordset, SQLStr As String
Set wrkODBC = CreateWorkspace("ODBCWorkspace", "sandy", "sandy", dbUseODBC)
Set conODBC = wrkODBC.OpenConnection("Publishers", dbDriverNoPrompt + dbRunAsync, False, ConnectStr)
Do While conODBC.StillExecuting
If MsgBox("No connection yet--keep waiting?", vbYesNo) = vbNo Then ' If the connection has not been made, ask the user
conODBC.Cancel ' if he/she wants to keep waiting. If the user does not, cancel
MsgBox "Connection cancelled!" ' the connection and exit the procedure.
wrkODBC.Close
Exit Function
End If
Loop
ProcStr = "{CALL " & ProcName & "}"
Set qdf = conODBC.CreateQueryDef("qry", ProcStr)
qdf.Execute
qdf.Close
conODBC.Close
wrkODBC.Close
MsgBox "Procedure Run Complete", vbInformation + vbOKOnly, "System Update"
Exit_RunProcedure:
Exit Function

Err_RunProcedure:
ErrMsg = "Error Running Oracle Procedure" & vbCrLf & vbCrLf & "Error Code =" & Err.Number & vbCrLf & "Error Description =" & Err.Description
MsgBox ErrMsg, vbCritical + vbOKOnly, "Run Procedure Failure"
Resume Exit_RunProcedure
End Function
Sandy

 

[maboo]

thankx sandy.
i'll give it a try

 

[B827]

maboo,
let me know how you get on. Sandy

 

[maboo]

sandy,
haven 't tried it yet.
waiting for coworker to finish the procedure.
maboo