Calling Oracle Packages

[Jeff]

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]

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" <ja*******@hotm ail.com> wrote in message
news:34******** *************** *****@phx.gbl.. .

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.

[Eidolon]

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 =============== =============== =============== ==============

[Eidolon]

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 =============== =============== =============== ==============

[Eidolon]

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 =============== =============== =============== ==============