CT Witter
MrExcel MVP
- Joined
- Jul 7, 2002
- Messages
- 1,212
I'm looking for some help on calling an oracle stored procedure from access. The code below seems to call the sp, but doesn't return any results.
It always seems to have a closed recordset (or more accurately a recordset is never created)
Any thoughts?
It always seems to have a closed recordset (or more accurately a recordset is never created)
Any thoughts?
Code:
Function AccAPILoad(strTransactionID As String, strRegionID As String, strTransactionType As String, _
strRequestType As String, strUserID As String, strSubID As String)
Dim conOracle As New ADODB.Connection
Dim adoParm As ADODB.Parameter
Dim adoQuery As ADODB.Command
Dim strRTransactionID As String
Dim strRRegionID As String
Dim strRTransactionType As String
Dim strRRequestType As String
Dim strRUserID As String
Dim strRSubID As String
Dim strRPersonNumber As String
Dim strRDetailSvcDate As String
Dim strRRuleID As String
Dim strRBaseAmount As String
Dim strRBaseQty As String
Dim strRReason As String
Dim strRIDNumber As String
Dim strRLineNumber As String
Dim strRSubLineCode As String
Dim strRCType As String
Dim strRErrorString As String
Dim strRReturnCode As String
Dim strRResponseString As String
With conOracle
.ConnectionString = "Provider=MSDAORA;" & _
"Data Source= ****;" & _
"User ID= *****;" & _
"Password=****"
.ConnectionTimeout = 10
.CursorLocation = adUseClient
.Open
End With
Set adoQuery = New ADODB.Command
With adoQuery
Set .ActiveConnection = conOracle
.CommandType = adCmdStoredProc
.CommandText = "sp_acool_api"
'
'Ins
Set adoParm = .CreateParameter("pi_s_transaction_id", adVarChar, adParamInput, Len(strTransactionID & ""), strTransactionID)
.Parameters.Append adoParm
Set adoParm = .CreateParameter("pi_s_region_id", adVarChar, adParamInput, Len(strRegionID & ""), strRegionID)
.Parameters.Append adoParm
Set adoParm = .CreateParameter("pi_s_transaction_type", adVarChar, adParamInput, Len(strTransactionType & ""), strTransactionType)
.Parameters.Append adoParm
Set adoParm = .CreateParameter("pi_s_request_type", adVarChar, adParamInput, Len(strRequestType & ""), strRequestType)
.Parameters.Append adoParm
Set adoParm = .CreateParameter("pi_s_user_id", adVarChar, adParamInput, Len(strUserID & ""), strUserID)
.Parameters.Append adoParm
Set adoParm = .CreateParameter("pi_s_sub_id", adVarChar, adParamInput, Len(strSubID & ""), strSubID)
.Parameters.Append adoParm
'
'Outs
Set adoParm = .CreateParameter("po_s_transaction_id", adVarChar, adParamOutput, 255, strRTransactionID)
.Parameters.Append adoParm
Set adoParm = .CreateParameter("po_s_region_id", adVarChar, adParamOutput, 255, strRRegionID)
.Parameters.Append adoParm
Set adoParm = .CreateParameter("po_s_transaction_type", adVarChar, adParamOutput, 255, strRTransactionType)
.Parameters.Append adoParm
Set adoParm = .CreateParameter("po_s_request_type", adVarChar, adParamOutput, 255, strRRequestType)
.Parameters.Append adoParm
Set adoParm = .CreateParameter("po_s_user_id", adVarChar, adParamOutput, 255, strRUserID)
.Parameters.Append adoParm
Set adoParm = .CreateParameter("po_s_sub_id", adVarChar, adParamOutput, 255, strRSubID)
.Parameters.Append adoParm
Set adoParm = .CreateParameter("po_s_error_string", adVarChar, adParamOutput, 255, strRErrorString)
.Parameters.Append adoParm
Set adoParm = .CreateParameter("po_s_return_code", adVarChar, adParamOutput, 255, strRReturnCode)
.Parameters.Append adoParm
Set adoParm = .CreateParameter("po_s_response_string", adVarChar, adParamOutput, 255, strRResponseString)
.Parameters.Append adoParm
' .Execute
Dim rst As ADODB.RecordSet
Set rst = .Execute
' Process results from recordset, then close it.
'http://support.microsoft.com/default.aspx?scid=kb;en-us;194792
Dim i As Integer
Dim rStr As String
Do While (Not rst Is Nothing)
If rst.State = adStateClosed Then Exit Do
While Not rst.EOF
For i = 0 To rst.Fields.Count - 1
rStr = rStr & " : " & rst(i)
Next i
Debug.Print Mid(rStr, 3, Len(rStr))
rst.MoveNext
rStr = ""
Wend
Debug.Print "----------------------"
Set rst = rst.NextRecordset
Loop
rst.Close
Set rst = Nothing
Set .ActiveConnection = Nothing
End With
Set adoQuery = Nothing
End Function