How do I get an output paramater back from an oracle stored proced

[Larry]

The following code is ruinning from an Access 2003 application:

Function callStoredProcLogin() As Boolean
Dim db As DAO.Database
Dim qdf As DAO.QueryDef



On Error GoTo Err_Execute

Set db = CurrentDb()
Set qdf = db.CreateQueryDef("")
'test
x = 1000
qdf.Connect = "ODBC;DSN=Oracle_Poc;UID=" & userid & ";PWD=" & passwrd & _
";SERVER=" & CONNSERVER & ""

qdf.SQL = "BEGIN USERLOGIN(" & "'" & userid & "'" & "," & "'" & passwrd &
"'" & ",0)" & "; END;"


qdf.ReturnsRecords = False
qdf.Execute 'dbFailOnError

Set qdf = Nothing
callStoredProcLogin = True
Exit Function

Err_Execute:
Select Case Err
Case 3146 To 3299
For Each objErr In DBEngine.Errors
MsgBox objErr.Description
Next
Case Else
MsgBox Err.Description
End Select
callStoredProcLogin = False
End Function

I am passing a 0 as my output paramater, but I only care about the return
value.


The stored procedure returns the value correctly when testing from oracle.

How to I get this to run correctly and how do I test the return value from
Access.



Thanks,
Larry

 

[Ofer]

This Code sample will give you an idea hoe to do that, basically you should
create a pass through query, empty, assign the code line to run the SP, and
then open the query as recordset

Create a passthrough query , empty

run this code:

Dim db As Database , MySet As Recordset , myqs As QueryDef

Set db = CodeDb()
Set myqs = db.QueryDefs("QueryName")
myqs.ReturnsRecords = True
' Define the execute line for the SP
myqs.sql = "DECLARE @R int EXEC SPName select @R "
Set MySet = db.OpenRecordset("QueryName")
If not MySet.Eof then
your code...
end if

 

[Larry]

This will work for an orale Stored Procedure?
I don't see a begin or end.

 

[Ofer]

Insert that code into a function, about the oracle, please try, in the pass
through query, specify the ODBC

Function FunctionName()
Dim db As Database , MySet As Recordset , myqs As QueryDef

Set db = CodeDb()
Set myqs = db.QueryDefs("QueryName")
myqs.ReturnsRecords = True
' Define the execute line for the SP
myqs.sql = "DECLARE @R int EXEC SPName select @R "
Set MySet = db.OpenRecordset("QueryName")
If not MySet.Eof then
your code...
end if


End Function