Calling Oracle Packages

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • Jeff

    #1

    Calling Oracle Packages

    Help!!

    I'm trying to convert a visual basic stand alone program
    into a Web Program using C#. There are a lot of Oracle
    packages already written that I am trying to use
    (unsuccessfully ). Can someone point me in the right
    direction on how to call an Oracle package into a
    datagrid??
    something like:

    cnn.Open();
    string sSelect = "{CALL DRM.PKG.LIST(?) }";
    OracleCommand selectCommand = new OracleCommand(s Select ,
    cnn);
    selectCommand.C ommandType = CommandType.Sto redProcedure;
    OracleParameter prm1 = new OracleParameter ();
    prm1 = selectCommand.P arameters.Add(" prm_1",
    OracleType.Int1 6);
    prm1.Direction = ParameterDirect ion.Output;
    prm1.Value = 0;
    OracleDataReade r dr = selectCommand.E xecuteReader();
    DataGrid1.DataS ource = dr;
    DataGrid1.DataB ind();

    Thanks Jeff.
  • Steve Jansen

    #2
    Re: Calling Oracle Packages

    Jeff,

    You most likely need to explictily add the REF CURSOR output parameter.
    ADO, but not ADO.NET, allowed you to specify the "PLSQLRSet= 1" connection
    string option to have the Command object automatically do this for you when
    populating an ADO Recordset.

    This code should work, assuming you have an OUTPUT parameter named
    p_ref_cursor that is your own package type that is a REF CURSOR.

    using System.Data.Ora cleClient;
    ....
    myOrclConnectio n = new System.Data.Ora cleClient.Oracl eConnection("Da ta
    Source=;User ID=scott;Passwo rd=tiger;");
    myOracleCommand = New OracleClient.Or acleCommand();
    myOracleCommand .CommandType = CommandType.Sto redProcedure;
    myOracleCommand .CommandText = "DRM.PKG.LI ST";
    myOracleCommand .Connection = myOrclConnectio n;
    myOracleCommand .Parameters.Add ("prm_1", OracleType.Int1 6);
    myOracleCommand .Parameters("pr m_1").Directio n = ParameterDirect ion.Output;
    myOracleCommand .Parameters.Add ("p_ref_cursor" , OracleType.Curs or);
    myOracleCommand .Parameters("p_ ref_cursor").Di rection =
    ParameterDirect ion.Output;
    OracleDataReade r dr =
    myOracleCommand .ExecuteReader( CommandBehavior .CloseConnectio n);
    DataGrid1.DataS ource = dr;
    DataGrid1.DataB ind();

    -Steve Jansen

    "Jeff" <jasmyth75@hotm ail.com> wrote in message
    news:347401c393 28$d89dc8e0$350 1280a@phx.gbl.. .[color=blue]
    > Help!!
    >
    > I'm trying to convert a visual basic stand alone program
    > into a Web Program using C#. There are a lot of Oracle
    > packages already written that I am trying to use
    > (unsuccessfully ). Can someone point me in the right
    > direction on how to call an Oracle package into a
    > datagrid??
    > something like:
    >
    > cnn.Open();
    > string sSelect = "{CALL DRM.PKG.LIST(?) }";
    > OracleCommand selectCommand = new OracleCommand(s Select ,
    > cnn);
    > selectCommand.C ommandType = CommandType.Sto redProcedure;
    > OracleParameter prm1 = new OracleParameter ();
    > prm1 = selectCommand.P arameters.Add(" prm_1",
    > OracleType.Int1 6);
    > prm1.Direction = ParameterDirect ion.Output;
    > prm1.Value = 0;
    > OracleDataReade r dr = selectCommand.E xecuteReader();
    > DataGrid1.DataS ource = dr;
    > DataGrid1.DataB ind();
    >
    > Thanks Jeff.[/color]


    Comment

    • Eidolon

      #3
      Re: Calling Oracle Packages

      Well, we are not using C# in house, we are working in VB right now. But
      this is the Sub we use for calling procedures in packages (hopefully you
      shouldnt have too much trouble converting it). It makes some assumptions
      about Procedure returns and such based on our business rules, so you may
      need to change it a bit, but weve had no problems with it.

      '// BEGIN CODE =============== =============== =============== ============
      #Region " Helper Class "
      Public Class clsParamDef
      Public Name As String
      Public Type As OracleType
      Public Direction As ParameterDirect ion
      Public Value As Object
      Public Size As Integer
      Public Sub New(ByVal Name As String, _
      ByVal Type As OracleType, _
      ByVal Direction As ParameterDirect ion, _
      Optional ByVal Value As Object = Nothing, _
      Optional ByVal Size As Integer = Nothing)
      Me.Name = Name
      Me.Type = Type
      Me.Direction = Direction
      Me.Value = Value
      Me.Size = Size
      End Sub
      End Class
      #End Region

      Public Module DB
      Public Function ExecuteProcedur e _
      (ByVal ProcName As String, ByRef Args() As clsParamDef) As Object

      Try
      '// Name of RETURN parameter if there is one
      Dim returnParamName As String = ""
      '// Remember if there are any OUTPUT params
      Dim wantCursors As Boolean = False
      Dim conConnection As OracleConnectio n = New OracleConnectio n
      Dim cmdCommand As OracleCommand = New OracleCommand
      cmdCommand.Conn ection = conConnection
      Dim da As OracleDataAdapt er = Nothing
      Dim ds As DataSet = Nothing

      '// ConnectionStrin g form:
      '// "Data Source=DEVELOP; User ID=User1;Passwo rd=PWD"
      conConnection.C onnectionString = ConnectionStrin g()

      cmdCommand.Comm andType = CommandType.Sto redProcedure
      cmdCommand.Comm andText = ProcName

      '// set up the parameters on the command object
      cmdCommand.Para meters.Clear()
      Dim cp As clsParamDef
      For Each cp In Args
      cmdCommand.Para meters.Add(cp.N ame, cp.Type).Direct ion _
      = cp.Direction
      cmdCommand.Para meters(cp.Name) .Value = cp.Value
      If Not IsNothing(cp.Si ze) AndAlso cp.Size > 0 Then _
      cmdCommand.Para meters(cp.Name) .Size = cp.Size

      If cp.Type = OracleType.Curs or Then wantCursors = True
      If cp.Direction = ParameterDirect ion.ReturnValue Then _
      returnParamName = cp.Name
      Next

      '// excute the command
      conConnection.O pen()
      If wantCursors Then
      da = New OracleDataAdapt er(cmdCommand)
      ds = New DataSet("result s")
      da.Fill(ds, "results")
      Else
      cmdCommand.Exec uteNonQuery()
      End If
      conConnection.C lose()

      'update any in/out parameters here
      For Each cp In Args
      If cp.Direction = ParameterDirect ion.InputOutput Or _
      cp.Direction = ParameterDirect ion.Output Then
      If wantCursors And cp.Type = OracleType.Curs or Then
      cp.Value = ds
      Else
      cp.Value = cmdCommand.Para meters(cp.Name) .Value
      End If
      End If
      Next

      mobjLastEx = Nothing
      '// return the return value if there is one
      If returnParamName <> "" Then _
      Return cmdCommand.Para meters(returnPa ramName).Value

      Catch e As Exception
      If conConnection.S tate <> ConnectionState .Closed Then _
      conConnection.C lose()
      Throw e
      Finally
      If conConnection.S tate <> ConnectionState .Closed Then _
      conConnection.C lose()
      End Try
      End Function
      End Module
      '// END CODE =============== =============== =============== ==============

      Comment

      • Eidolon

        #4
        Re: Calling Oracle Packages

        Well, we are not using C# in house, we are working in VB right now. But
        this is the Sub we use for calling procedures in packages (hopefully you
        shouldnt have too much trouble converting it). It makes some assumptions
        about Procedure returns and such based on our business rules, so you may
        need to change it a bit, but weve had no problems with it.

        '// BEGIN CODE =============== =============== =============== ============
        #Region " Helper Class "
        Public Class clsParamDef
        Public Name As String
        Public Type As OracleType
        Public Direction As ParameterDirect ion
        Public Value As Object
        Public Size As Integer
        Public Sub New(ByVal Name As String, _
        ByVal Type As OracleType, _
        ByVal Direction As ParameterDirect ion, _
        Optional ByVal Value As Object = Nothing, _
        Optional ByVal Size As Integer = Nothing)
        Me.Name = Name
        Me.Type = Type
        Me.Direction = Direction
        Me.Value = Value
        Me.Size = Size
        End Sub
        End Class
        #End Region

        Public Module DB
        Public Function ExecuteProcedur e _
        (ByVal ProcName As String, ByRef Args() As clsParamDef) As Object

        Try
        '// Name of RETURN parameter if there is one
        Dim returnParamName As String = ""
        '// Remember if there are any OUTPUT params
        Dim wantCursors As Boolean = False
        Dim conConnection As OracleConnectio n = New OracleConnectio n
        Dim cmdCommand As OracleCommand = New OracleCommand
        cmdCommand.Conn ection = conConnection
        Dim da As OracleDataAdapt er = Nothing
        Dim ds As DataSet = Nothing

        '// ConnectionStrin g form:
        '// "Data Source=DEVELOP; User ID=User1;Passwo rd=PWD"
        conConnection.C onnectionString = ConnectionStrin g()

        cmdCommand.Comm andType = CommandType.Sto redProcedure
        cmdCommand.Comm andText = ProcName

        '// set up the parameters on the command object
        cmdCommand.Para meters.Clear()
        Dim cp As clsParamDef
        For Each cp In Args
        cmdCommand.Para meters.Add(cp.N ame, cp.Type).Direct ion _
        = cp.Direction
        cmdCommand.Para meters(cp.Name) .Value = cp.Value
        If Not IsNothing(cp.Si ze) AndAlso cp.Size > 0 Then _
        cmdCommand.Para meters(cp.Name) .Size = cp.Size

        If cp.Type = OracleType.Curs or Then wantCursors = True
        If cp.Direction = ParameterDirect ion.ReturnValue Then _
        returnParamName = cp.Name
        Next

        '// excute the command
        conConnection.O pen()
        If wantCursors Then
        da = New OracleDataAdapt er(cmdCommand)
        ds = New DataSet("result s")
        da.Fill(ds, "results")
        Else
        cmdCommand.Exec uteNonQuery()
        End If
        conConnection.C lose()

        'update any in/out parameters here
        For Each cp In Args
        If cp.Direction = ParameterDirect ion.InputOutput Or _
        cp.Direction = ParameterDirect ion.Output Then
        If wantCursors And cp.Type = OracleType.Curs or Then
        cp.Value = ds
        Else
        cp.Value = cmdCommand.Para meters(cp.Name) .Value
        End If
        End If
        Next

        mobjLastEx = Nothing
        '// return the return value if there is one
        If returnParamName <> "" Then _
        Return cmdCommand.Para meters(returnPa ramName).Value

        Catch e As Exception
        If conConnection.S tate <> ConnectionState .Closed Then _
        conConnection.C lose()
        Throw e
        Finally
        If conConnection.S tate <> ConnectionState .Closed Then _
        conConnection.C lose()
        End Try
        End Function
        End Module
        '// END CODE =============== =============== =============== ==============



        Comment

        • Eidolon

          #5
          Re: Calling Oracle Packages

          Well, we are not using C# in house, we are working in VB right now. But
          this is the Sub we use for calling procedures in packages (hopefully you
          shouldnt have too much trouble converting it). It makes some assumptions
          about Procedure returns and such based on our business rules, so you may
          need to change it a bit, but weve had no problems with it.

          '// BEGIN CODE =============== =============== =============== ============
          #Region " Helper Class "
          Public Class clsParamDef
          Public Name As String
          Public Type As OracleType
          Public Direction As ParameterDirect ion
          Public Value As Object
          Public Size As Integer
          Public Sub New(ByVal Name As String, _
          ByVal Type As OracleType, _
          ByVal Direction As ParameterDirect ion, _
          Optional ByVal Value As Object = Nothing, _
          Optional ByVal Size As Integer = Nothing)
          Me.Name = Name
          Me.Type = Type
          Me.Direction = Direction
          Me.Value = Value
          Me.Size = Size
          End Sub
          End Class
          #End Region

          Public Module DB
          Public Function ExecuteProcedur e _
          (ByVal ProcName As String, ByRef Args() As clsParamDef) As Object

          Try
          '// Name of RETURN parameter if there is one
          Dim returnParamName As String = ""
          '// Remember if there are any OUTPUT params
          Dim wantCursors As Boolean = False
          Dim conConnection As OracleConnectio n = New OracleConnectio n
          Dim cmdCommand As OracleCommand = New OracleCommand
          cmdCommand.Conn ection = conConnection
          Dim da As OracleDataAdapt er = Nothing
          Dim ds As DataSet = Nothing

          '// ConnectionStrin g form:
          '// "Data Source=DEVELOP; User ID=User1;Passwo rd=PWD"
          conConnection.C onnectionString = ConnectionStrin g()

          cmdCommand.Comm andType = CommandType.Sto redProcedure
          cmdCommand.Comm andText = ProcName

          '// set up the parameters on the command object
          cmdCommand.Para meters.Clear()
          Dim cp As clsParamDef
          For Each cp In Args
          cmdCommand.Para meters.Add(cp.N ame, cp.Type).Direct ion _
          = cp.Direction
          cmdCommand.Para meters(cp.Name) .Value = cp.Value
          If Not IsNothing(cp.Si ze) AndAlso cp.Size > 0 Then _
          cmdCommand.Para meters(cp.Name) .Size = cp.Size

          If cp.Type = OracleType.Curs or Then wantCursors = True
          If cp.Direction = ParameterDirect ion.ReturnValue Then _
          returnParamName = cp.Name
          Next

          '// excute the command
          conConnection.O pen()
          If wantCursors Then
          da = New OracleDataAdapt er(cmdCommand)
          ds = New DataSet("result s")
          da.Fill(ds, "results")
          Else
          cmdCommand.Exec uteNonQuery()
          End If
          conConnection.C lose()

          'update any in/out parameters here
          For Each cp In Args
          If cp.Direction = ParameterDirect ion.InputOutput Or _
          cp.Direction = ParameterDirect ion.Output Then
          If wantCursors And cp.Type = OracleType.Curs or Then
          cp.Value = ds
          Else
          cp.Value = cmdCommand.Para meters(cp.Name) .Value
          End If
          End If
          Next

          mobjLastEx = Nothing
          '// return the return value if there is one
          If returnParamName <> "" Then _
          Return cmdCommand.Para meters(returnPa ramName).Value

          Catch e As Exception
          If conConnection.S tate <> ConnectionState .Closed Then _
          conConnection.C lose()
          Throw e
          Finally
          If conConnection.S tate <> ConnectionState .Closed Then _
          conConnection.C lose()
          End Try
          End Function
          End Module
          '// END CODE =============== =============== =============== ==============



          Comment

          Working...