I created this function to handle the calling of procedures in packages
and it works just fine for procedures, but it doesn't seem to work for
calling functions (IsGenerator) in a package so I want to create another
function called ExecFunction. I'm not quite sure what I have to change.
The command type? The SQL passed in?
Private Const _PackageNameAdd As String = "GENERATORS.ADD_REC"
Calling function that developers code
Private Sub Add(ByVal R As GeneratorsDataset.C_GENERATORSRow)
Dim Ps As New Utilities.DBManager.Parameters
Ps.Add("n_cca_num", R.CCA_NUM, Int32, Input)
Ps.Add("v_ccs", R.CCS, Varchar2, Input)
Ps.Add("v_substation_name", R.SUBSTATION_NAME, Varchar2, Input)
Ps.Add("n_eqp_type_id", R.EQP_TYPE_ID, Int32, Input)
Ps.Add("v_short_name", R.EQP_SHORT_NAME, Varchar2, Input)
Ps.Add("v_long_name", R.EQP_LONG_NAME, Varchar2, Input)
Ps.Add("n_capacity", R.CAPACITY, Double, Input)
Ps.Add("v_gen_type", R.GEN_TYPE, Varchar2, Input)
Ps.Add("v_plant_name", R.PLANT_NAME, Varchar2, Input)
Ps.Add("v_fuel_type", R.FUEL_TYPE, Char, Input)
Dim OutputPs As New Utilities.DBManager.Parameters
OutputPs = _DB.ExecProcedure(_PackageNameAdd, Ps)
End Sub
Utility function
Public Function ExecProcedure(ByVal SQL As String, ByVal Parms As
DBManager.Parameters) As Parameters
' Author: Franklin Gray
' Created Date: 8/23/2005
' Purpose: Call an Oracle package and return output parms
'
' Process: Create parm collection
' Call package
' Load output parms into collection
'
' Input: package name and collection of parameters
' Output: collection
'
' Changes: (who, what, where, and when)
'
If _Conn.State = ConnectionState.Closed Or _Conn.State =
ConnectionState.Broken Then _Conn.Open()
Dim cmd As New Oracle.DataAccess.Client.OracleCommand(SQL, _Conn)
cmd.CommandType = CommandType.StoredProcedure
Dim P As DBManager.Parameters.Parameter
'add parms to ADO parm collection
If Not (Parms Is Nothing) Then
For Each P In Parms
Debug.Write(P.Name & " = " & P.Value & ", ")
cmd.Parameters.Add(P.Name, P.type, P.Value, P.Direction)
Next
End If
'run package
cmd.ExecuteNonQuery()
Dim OutputDataTables As New Parameters
'loop through all parms and find all output and add to collection
If Not (Parms Is Nothing) Then
For Each P In Parms
If P.Direction = ParameterDirection.Output Then
P.Value = cmd.Parameters(P.Name)
OutputDataTables.Add(P)
End If
Next
End If
_Conn.Close()
'return collection of output
Return OutputDataTables
End Function
===================================
This list is hosted by DevelopMentor® http://www.develop.com
View archives and manage your subscription(s) at http://discuss.develop.com
