how to call Oracle Stored Procedure from MS ACCESS 2007 VB

[csidb]

We have a new Oracle DB with stored procedure:

xxcus_test.product (p_number1 in NUMBER,
p_number2 in NUMBER,
x_result out number)

I wrote the following VB code to call it, but it fails on "Execute". Can
anyone see what is wrong?
-----------------------------------------------------------
Option Compare Database

Private Sub Command0_Click()
Me.Text5 = GetTotal()
End Sub

Function GetTotal() As Long

Dim db As Database
Dim LSProc As QueryDef
Dim LSQL As String
Dim p_number1 As Integer
Dim p_number2 As Integer
Dim x_result As Integer

p_number1 = 1
p_number2 = 2
x_result = 0

On Error GoTo Err_Execute

Set db = CodeDb()
Set LSProc = db.CreateQueryDef("")

LSProc.Connect = "ODBC;DRIVER={Oracle in
XE};SERVER=CONV;UID=apps;PWD=apps;DBQ=CONV;DBA=W;APA=T;EXC=F;XSM=Default;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=Me;CSR=F;FWC=F;FBS=60000;TLO=O;"

LSProc.SQL = "BEGIN xxcus_test.product(" & p_number1 & "," & p_number2 &
"," & x_result & "); END;"

LSProc.ReturnsRecords = False
LSProc.ODBCTimeout = 100

LSProc.Execute
Set LSProc = Nothing

GetTotal = x_result
Exit Function

Err_Execute:
MsgBox "The call to the Oracle stored procedure failed."
GetTotal = -1
End Function

 

[Douglas J. Steele]

Try

LSProc.Execute, dbFailOnError

and change your error routine to

MsgBox Err.Number & ": " & Err.Description

The error might tell you something.

 

[csidb]

Thanks for the tip. I get error "3146:ODBC -- call failed".

I'm confident that my CONNECT string is correct because I can link to the
Oracle tables. I chgd the SQL to a simple "SELECT COUNT(*) FROM
ABM.ABM_APPL_WINS" (which is an ORACLE table) and it went through with no
errors.

I have a bad feeling about the "stored procedure". I'll question the
Oracle developers again, but do you know of another way of executing a stored
procedure?

Thanks again -

 

[Douglas J. Steele]

Another way to execute the stored procedure would be to use ADO.

Since your SP isn't returning a recordset, you'd typically use the ADO
Command object to run it.

(Sorry, I don't have sample code handy)

 

[csidb]

I rewrote the code using ADO to call the Stored Procedure. The SP just adds
the first 2 params and puts the result in the 3rd param. When I run this
code I get the error code "3065: Cannot execute a select query". I don't
know where this is coming from. Any ideas??? Thank you!
--
Option Compare Database

Private Sub Command0_Click()
'MsgBox "In Sub Command0_Click."
Me.Text5 = GetTotal()

End Sub
Function GetTotal() As Long

Dim objConn As ADODB.Connection
Dim objCmd As New ADODB.Command

Dim prm1 As ADODB.Parameter
Dim prm2 As ADODB.Parameter
Dim prm3 As ADODB.Parameter


Dim px_number1 As Integer
Dim px_number2 As Integer
Dim xx_result As Integer

MsgBox "In GetTotal."
px_number1 = 1
px_number2 = 2
x_result = 0

On Error GoTo Err_Execute

Set objConn = CreateObject("ADODB.Connection")
objConn.ConnectionString = "DRIVER={Oracle in
XE};SERVER=CONV;UID=apps;PWD=apps;DBQ=CONV;DBA=W;APA=T;EXC=F;XSM=Default;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=Me;CSR=F;FWC=F;FBS=60000;TLO=O;"
objConn.Open

'Define the three parameters for the Stored procedure call
Set prm1 = New ADODB.Parameter
prm1.Type = adInteger
prm1.Direction = adParamInput
prm1.Value = px_number1
Set prm2 = New ADODB.Parameter
prm2.Type = adInteger
prm2.Direction = adParamInput
prm2.Value = px_number2
Set prm3 = New ADODB.Parameter
prm3.Type = adInteger
prm3.Direction = adParamInputOutput
prm3.Value = x_result

' Build the objCmd Command Object
With objCmd
.ActiveConnection = CurrentProject.Connection
'Set other properties
.CommandText = "XXCUS_TEST.PRODUCT"
.CommandType = adCmdStoredProc 'Type for stored
procedure

'.Parameters.Refresh
.Parameters.Append prm1
.Parameters.Append prm2
.Parameters.Append prm3
End With

objCmd.Execute

GetTotal = 1
Exit Function

Err_Execute:
MsgBox Errors.Count
For Each MyError In DBEngine.Errors
With MyError
MsgBox .Number & " " & .Description
End With
Next MyError
GetTotal = -1
End Function

Private Sub Command8_Click()

End Sub
------------------------------------------------------------------------

 

[Douglas J. Steele]

What's the SP? From the error message, it sounds as though it's supposed to
be returning a recordset.