Another Oracle Caveat.

[abhijit]

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

[deja]

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

[abhijit]

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