Probably b/c you're doing a cmd.ExecuteNonQuery. That SELECT is a query--you want the rset it's going to return.
-----Original Message----- From: Unmoderated discussion of advanced .NET topics. [mailto:[EMAIL PROTECTED] On Behalf Of Franklin Gray Sent: Thursday, September 29, 2005 7:31 AM To: [email protected] Subject: Re: [ADVANCED-DOTNET] Oracle Package.Function call from VB.Net? No....that doesn't do it. Message from Davy J <[EMAIL PROTECTED]>@DISCUSS.DEVELOP.COM received on 09/29/2005 02:53 AM 09/29/2005 02:53 AM Davy J <[EMAIL PROTECTED]>@DISCUSS.DEVELOP.COM Please respond to "Unmoderated discussion of advanced .NET topics." <[email protected]> Sent by "Unmoderated discussion of advanced .NET topics." <[email protected]> To: [email protected] cc: Subject: Re: [ADVANCED-DOTNET] Oracle Package.Function call from VB.Net? Select fn() from dual; No? Davy On 28/09/05, Franklin Gray <[EMAIL PROTECTED]> wrote: > 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(r) http://www.develop.com > > View archives and manage your subscription(s) at > http://discuss.develop.com > -- Dave Jones [EMAIL PROTECTED] [EMAIL PROTECTED] =================================== This list is hosted by DevelopMentor(r) http://www.develop.com View archives and manage your subscription(s) at http://discuss.develop.com =================================== This list is hosted by DevelopMentor(r) http://www.develop.com View archives and manage your subscription(s) at http://discuss.develop.com =================================== This list is hosted by DevelopMentor® http://www.develop.com View archives and manage your subscription(s) at http://discuss.develop.com
