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(sSelect ,
cnn);
selectCommand.CommandType = CommandType.StoredProcedure;
OracleParameter prm1 = new OracleParameter();
prm1 = selectCommand.Parameters.Add("prm_1",
OracleType.Int16);
prm1.Direction = ParameterDirection.Output;
prm1.Value = 0;
OracleDataReader dr = selectCommand.ExecuteReader();
DataGrid1.DataSource = dr;
DataGrid1.DataBind();

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.OracleClient;
....
myOrclConnection = new System.Data.OracleClient.OracleConnection("Data
Source=;User ID=scott;Password=tiger;");
myOracleCommand = New OracleClient.OracleCommand();
myOracleCommand.CommandType = CommandType.StoredProcedure;
myOracleCommand.CommandText = "DRM.PKG.LIST";
myOracleCommand.Connection = myOrclConnection;
myOracleCommand.Parameters.Add("prm_1", OracleType.Int16);
myOracleCommand.Parameters("prm_1").Direction = ParameterDirection.Output;
myOracleCommand.Parameters.Add("p_ref_cursor", OracleType.Cursor);
myOracleCommand.Parameters("p_ref_cursor").Direction =
ParameterDirection.Output;
OracleDataReader dr =
myOracleCommand.ExecuteReader(CommandBehavior.CloseConnection);
DataGrid1.DataSource = dr;
DataGrid1.DataBind();

-Steve Jansen

 

[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 ParameterDirection
Public Value As Object
Public Size As Integer
Public Sub New(ByVal Name As String, _
ByVal Type As OracleType, _
ByVal Direction As ParameterDirection, _
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 ExecuteProcedure _
(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 OracleConnection = New OracleConnection
Dim cmdCommand As OracleCommand = New OracleCommand
cmdCommand.Connection = conConnection
Dim da As OracleDataAdapter = Nothing
Dim ds As DataSet = Nothing

'// ConnectionString form:
'// "Data Source=DEVELOP;User ID=User1;Password=PWD"
conConnection.ConnectionString = ConnectionString()

cmdCommand.CommandType = CommandType.StoredProcedure
cmdCommand.CommandText = ProcName

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

If cp.Type = OracleType.Cursor Then wantCursors = True
If cp.Direction = ParameterDirection.ReturnValue Then _
returnParamName = cp.Name
Next

'// excute the command
conConnection.Open()
If wantCursors Then
da = New OracleDataAdapter(cmdCommand)
ds = New DataSet("results")
da.Fill(ds, "results")
Else
cmdCommand.ExecuteNonQuery()
End If
conConnection.Close()

'update any in/out parameters here
For Each cp In Args
If cp.Direction = ParameterDirection.InputOutput Or _
cp.Direction = ParameterDirection.Output Then
If wantCursors And cp.Type = OracleType.Cursor Then
cp.Value = ds
Else
cp.Value = cmdCommand.Parameters(cp.Name).Value
End If
End If
Next

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

Catch e As Exception
If conConnection.State <> ConnectionState.Closed Then _
conConnection.Close()
Throw e
Finally
If conConnection.State <> ConnectionState.Closed Then _
conConnection.Close()
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 ParameterDirection
Public Value As Object
Public Size As Integer
Public Sub New(ByVal Name As String, _
ByVal Type As OracleType, _
ByVal Direction As ParameterDirection, _
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 ExecuteProcedure _
(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 OracleConnection = New OracleConnection
Dim cmdCommand As OracleCommand = New OracleCommand
cmdCommand.Connection = conConnection
Dim da As OracleDataAdapter = Nothing
Dim ds As DataSet = Nothing

'// ConnectionString form:
'// "Data Source=DEVELOP;User ID=User1;Password=PWD"
conConnection.ConnectionString = ConnectionString()

cmdCommand.CommandType = CommandType.StoredProcedure
cmdCommand.CommandText = ProcName

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

If cp.Type = OracleType.Cursor Then wantCursors = True
If cp.Direction = ParameterDirection.ReturnValue Then _
returnParamName = cp.Name
Next

'// excute the command
conConnection.Open()
If wantCursors Then
da = New OracleDataAdapter(cmdCommand)
ds = New DataSet("results")
da.Fill(ds, "results")
Else
cmdCommand.ExecuteNonQuery()
End If
conConnection.Close()

'update any in/out parameters here
For Each cp In Args
If cp.Direction = ParameterDirection.InputOutput Or _
cp.Direction = ParameterDirection.Output Then
If wantCursors And cp.Type = OracleType.Cursor Then
cp.Value = ds
Else
cp.Value = cmdCommand.Parameters(cp.Name).Value
End If
End If
Next

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

Catch e As Exception
If conConnection.State <> ConnectionState.Closed Then _
conConnection.Close()
Throw e
Finally
If conConnection.State <> ConnectionState.Closed Then _
conConnection.Close()
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 ParameterDirection
Public Value As Object
Public Size As Integer
Public Sub New(ByVal Name As String, _
ByVal Type As OracleType, _
ByVal Direction As ParameterDirection, _
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 ExecuteProcedure _
(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 OracleConnection = New OracleConnection
Dim cmdCommand As OracleCommand = New OracleCommand
cmdCommand.Connection = conConnection
Dim da As OracleDataAdapter = Nothing
Dim ds As DataSet = Nothing

'// ConnectionString form:
'// "Data Source=DEVELOP;User ID=User1;Password=PWD"
conConnection.ConnectionString = ConnectionString()

cmdCommand.CommandType = CommandType.StoredProcedure
cmdCommand.CommandText = ProcName

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

If cp.Type = OracleType.Cursor Then wantCursors = True
If cp.Direction = ParameterDirection.ReturnValue Then _
returnParamName = cp.Name
Next

'// excute the command
conConnection.Open()
If wantCursors Then
da = New OracleDataAdapter(cmdCommand)
ds = New DataSet("results")
da.Fill(ds, "results")
Else
cmdCommand.ExecuteNonQuery()
End If
conConnection.Close()

'update any in/out parameters here
For Each cp In Args
If cp.Direction = ParameterDirection.InputOutput Or _
cp.Direction = ParameterDirection.Output Then
If wantCursors And cp.Type = OracleType.Cursor Then
cp.Value = ds
Else
cp.Value = cmdCommand.Parameters(cp.Name).Value
End If
End If
Next

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

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