Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Package.Function call from VB.Net?
Oracle Package.Function call from VB.Net? [message #139787] Thu, 29 September 2005 10:12 Go to next message
FranklinGray
Messages: 15
Registered: September 2005
Location: Houston
Junior Member
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? I've tried a sql of "begin :RETURN := EQUIPMENT.IsGenerator( :n_CCA_NUM ); end;" with a command type of text and that didn't work. It didn't error but the return value was 0 instead of the hard coded value of 100 in the function. Please help.


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)
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
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 Or P.Direction = ParameterDirection.ReturnValue 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

[Updated on: Thu, 29 September 2005 10:25]

Report message to a moderator

Re: Oracle Package.Function call from VB.Net? [message #139801 is a reply to message #139787] Thu, 29 September 2005 11:32 Go to previous messageGo to next message
FranklinGray
Messages: 15
Registered: September 2005
Location: Houston
Junior Member
I figured it out. Here is how I did it.

Caller

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim ps As New Parameters
Dim Outs As New Parameters
Try
ps.Add("n_CCA_NUM", 5, Oracle.DataAccess.Client.OracleDbType.Int32, ParameterDirection.Input)
Me.TextBox2.Text = CType(ExecFunction(Me.TextBox1.Text, ps), String)
Catch ee As Exception
MsgBox(ee.ToString)
End Try
End Sub

Utility

Public Function ExecFunction(ByVal PackageName As String, ByVal Parms As Parameters) As Object
If _Conn.State = ConnectionState.Closed Or _Conn.State = ConnectionState.Broken Then _Conn.Open()
PackageName = "SELECT " & PackageName & "("

Dim P As Parameters.Parameter
'add parms to ADO parm collection
If Not (Parms Is Nothing) Then
For Each P In Parms
PackageName = PackageName & ":" & P.Name & ","
Next
End If

PackageName = PackageName.Substring(0, PackageName.Length - 2) & ") FROM DUAL"

Dim cmd As New Oracle.DataAccess.Client.OracleCommand(PackageName, _Conn)
cmd.CommandType = CommandType.Text
'add parms to ADO parm collection
If Not (Parms Is Nothing) Then
For Each P In Parms
cmd.Parameters.Add(P.Name, P.type, P.Value, P.Direction)
Next
End If
'run package
ExecFunction = cmd.ExecuteScalar
_Conn.Close()
End Function
Re: Oracle Package.Function call from VB.Net? [message #155922 is a reply to message #139801] Thu, 19 January 2006 12:52 Go to previous messageGo to next message
FranklinGray
Messages: 15
Registered: September 2005
Location: Houston
Junior Member
Found a bug in this solution. It fails if there is an output parameter. Can anybody help me with the proper way to call a function that is inside an oracle package from VB.net using Oracle's provider?
Re: Oracle Package.Function call from VB.Net? [message #155938 is a reply to message #155922] Thu, 19 January 2006 14:46 Go to previous messageGo to next message
FranklinGray
Messages: 15
Registered: September 2005
Location: Houston
Junior Member
Ok...I'm going to answer my own question again Smile

This is the new utility function that works....yahooooooo.
   
Public Function ExecPackageFunction(ByVal PackageAndFunctionName As String, ByVal Parms As Parameters) As Parameters
        '   Author:         Franklin Gray
        '   Created Date:   9/29/2005
        '   Purpose:        Call an Oracle package function and return the return value
        '
        '   Process:        Create parm collection
        '                   Call package
        '                   Load output parms into collection
        '
        '   Input:          package name and collection of parameters
        '   Output:         return value
        '
        '   Changes:        (who, what, where, and when)
        '
        If _Conn.State = ConnectionState.Closed Or _Conn.State = ConnectionState.Broken Then _Conn.Open()
        PackageAndFunctionName = "declare RET INT; BEGIN :RET := " & PackageAndFunctionName & "("
        
        Dim P As DBManager.Parameters.Parameter, Outputs As New DBManager.Parameters
        'add parms to ADO parm collection
        If Not (Parms Is Nothing) Then
            For Each P In Parms
                If Not P.Direction = ParameterDirection.ReturnValue Then PackageAndFunctionName = PackageAndFunctionName & ":" & P.Name & ","
            Next
        End If

        PackageAndFunctionName = PackageAndFunctionName.Substring(0, PackageAndFunctionName.Length - 2) & "); END;"

        Dim cmd As New Oracle.DataAccess.Client.OracleCommand(PackageAndFunctionName, _Conn)
        cmd.CommandType = CommandType.Text
        'add parms to ADO parm collection

        If Not (Parms Is Nothing) Then
            For Each P In Parms
                'Debug.Write(P.Name & " = " & P.Value & ", ")
                Dim L As Int32
                If P.Size > 0 Then
                    L = P.Size
                Else
                    If P.Value Is System.DBNull.Value Then
                        L = 1
                    Else
                        L = Len(P.Value)
                    End If
                End If
                cmd.Parameters.Add(P.Name, P.type, L, P.Value, P.Direction)
                If P.Direction = ParameterDirection.Output Or P.Direction = ParameterDirection.ReturnValue Then Outputs.Add(P)
            Next
        End If
        'run package
        cmd.ExecuteScalar()
        Dim OutputDataTables As New Parameters
        'loop through all parms and find all output and add to collection
        If Not (Outputs Is Nothing) Then
            For Each P In Outputs
                If P.Direction = ParameterDirection.Output Or P.Direction = ParameterDirection.ReturnValue Then
                    P.Value = cmd.Parameters(P.Name).Value
                    OutputDataTables.Add(P)
                End If
            Next
        End If
        ExecPackageFunction = OutputDataTables
        cmd.Dispose()
    End Function
Re: Oracle Package.Function call from VB.Net? [message #207260 is a reply to message #155938] Mon, 04 December 2006 21:13 Go to previous message
hurley1
Messages: 1
Registered: December 2006
Location: Boston
Junior Member
I have been searching for a solution to this problem... so thanks for posting... I'm new to VB.NET but very familiar with VB 6 so this should not be a stretch (I'm building this in .NET)
Thus I am a little unclear about a couple of things:

1)You code contains: DBManager.Parameters but I do not know where to reference this object/class
2)Which parameters object should I reference for: Dim ps As New Parameters
I've searched through the various classes and there seem to be many versions of 'Parameters' classes

Thanks
Previous Topic: Datatype for Null
Next Topic: DENSE RANK / FIRST_VALUE help
Goto Forum:
  


Current Time: Mon Dec 05 02:40:59 CST 2016

Total time taken to generate the page: 0.23586 seconds