Printable View
hi all,
I am facing a problem with Oracle Stored Procedures.
I am using ADO to get the results out of Oracle Stored Procedures.
These procedures have been written by someone else. Presently I want to know two things.
[BOLD]
1) How do I execute a procedure at the SQL PROMPT and output the results?
2) How do I get the result of a stored procedure in my VB Client?
[/BOLD]
I am using Oracle 8i database.
Regards,
Abhijit
Public Function RunSPReturnRs(ByVal strConnectionString As String, ByVal strProcedureName As String, Optional Params As Variant) As ADODB.Recordset
On Error GoTo ErrorHandle
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim lngIndex As Long
Dim varArray As Variant
Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset
With cmd
.ActiveConnection = strConnectionString
.CommandText = strProcedureName
.CommandType = adCmdStoredProc
End With
BuildParams cmd, Params
With rs
.CursorLocation = adUseClient
.Open cmd, , adOpenStatic, adLockBatchOptimistic
End With
With cmd
If Not IsMissing(Params) Then
ReDim varArray(.Parameters.Count - 1)
For lngIndex = 0 To .Parameters.Count - 1
varArray(lngIndex) = .Parameters.Item(lngIndex).Value
Next
Params = varArray
End If
End With
Set RunSPReturnRs = rs
Set rs.ActiveConnection = Nothing
Set cmd.ActiveConnection = Nothing
Set rs = Nothing
Set cmd = Nothing
Exit Function
ErrorHandle:
Set rs = Nothing
Set cmd = Nothing
RaiseError MODULE_NAME, "RunSPReturnRs", Err.Number, Err.Description, , True
End Function
Private Sub BuildParams(ByRef cmd As ADODB.Command, ByVal Arg_Params As Variant)
On Error GoTo Err_Handler
Dim var_Params As Variant
Dim var_v As Variant
Dim int_i As Integer
Dim int_LBound As Integer
Dim int_UBound As Integer
If Not IsArray(Arg_Params) Then Exit Sub
var_Params = Arg_Params
For int_i = LBound(var_Params) To UBound(var_Params)
int_LBound = LBound(var_Params(int_i))
int_UBound = UBound(var_Params(int_i))
If int_UBound - int_LBound = 4 Then ' Check for nulls.
If VarType(var_Params(int_i)(4)) = vbString Then
var_v = IIf(var_Params(int_i)(4) = "", Null, var_Params(int_i)(4))
Else
var_v = var_Params(int_i)(4)
End If
With cmd
.Parameters.Append .CreateParameter( _
var_Params(int_i)(0), _
var_Params(int_i)(1), _
var_Params(int_i)(2), _
var_Params(int_i)(3), _
var_v _
)
End With
Else
'RaiseError m_modName, "BuildParams(...): incorrect # of parameters", bWriteToLog:=True
End If
Next
Exit_Proc:
Exit Sub
Err_Handler:
Resume Exit_Proc
End Sub
Public Function CreateParam(ByVal PName As String, ByVal PType As ADODB.DataTypeEnum, ByVal PDirection As ADODB.ParameterDirectionEnum, Optional ByVal PSize As Long, Optional ByVal PValue As Variant) As Variant
On Error GoTo ErrorHandle
CreateParam = Array(PName, PType, PDirection, PSize, PValue)
Exit Function
ErrorHandle:
RaiseError MODULE_NAME, "CreateParam", Err.Number, Err.Description, , True
End Function
example calling this function:
Public Function FetchContract(byval lngID as Long) As ADODB.Recordset
Set FetchContract = RunSPReturnRs(CONNECTION_STRING, _
"scott.fetch_contract", _
Array(CreateParam("a_id", adNumeric, adParamInput,,lngID)))
End Function
Hi
Thanks for your reply. I found out this way I can get a refcursor too.
Thanks a lot,
Abhijit
Dim adoCn As New ADODB.Connection
Dim adoCmd As New ADODB.Command
Dim rs As New ADODB.Recordset
adoCn.Open "Provider=MSDAORA;Password=scott;User ID=scott;Data Source=ALPHA"
adoCmd.ActiveConnection = adoCn
adoCmd.CommandType = adCmdText
adoCmd.CommandText = "{call CF_SP_QRIGHTS(?,?,{resultset 0, RC1})}"
With adoCmd
.Parameters.Append .CreateParameter("P0", adVarChar, adParamInput, 50, "abhi")
.Parameters.Append .CreateParameter("P1", adInteger, adParamInput, , 105)
End With
With rs
.CursorLocation = adUseClient
End With
Set rs = adoCmd.Execute
Do While Not rs.EOF
rs.MoveNext
Loop
End Sub