How do you Send More that 20 Parameters to a Stored Procedure Using ODP.Net?
- by discwiz
Switching from Microsofts Oracle Driver to ODP.NET version 10.2.0.100. After changing the data types to OracleDBTypes in a procedure, that worked perficetly using System.Data.OracleClient, the procedure fails if we try and pass in more that 20 parameters. The error returned is:
ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'ADD_TARP_EVENT' ORA-06550: line 1, column 7: PL/SQL: Statement ignorede
If we reduce the number of parameters to less than 20 it works. Is this a known issue?
Thanks,
Dave
Here the code for creating the parameters:
Shared Function CreateTarpEventCommand(ByVal aTarpEvent As TARPEventType) As OracleCommand
Dim cmd As New OracleCommand
With aTarpEvent
cmd.Parameters.Add(New OracleParameter("I_facID_C", OracleDbType.Char)).Value = .FacilityShortName
cmd.Parameters.Add(New OracleParameter("I_facName_VC", OracleDbType.Varchar2)).Value = .FacilityLongName
cmd.Parameters.Add(New OracleParameter("I_client_VC", OracleDbType.Varchar2)).Value = .ComputerNameTarpIsRunningOn
cmd.Parameters.Add(New OracleParameter("I_TARP_Version_VC", OracleDbType.Varchar2)).Value = .TarpVersionNumber
cmd.Parameters.Add(New OracleParameter("I_NAS_Type_VC", OracleDbType.Varchar2)).Value = .FacilityNASSystemType
cmd.Parameters.Add(New OracleParameter("I_Aircraft1_Callsign_VC", OracleDbType.Varchar2)).Value = .Aircraft1Callsign
If .Aircraft1Type Is Nothing Then
cmd.Parameters.Add(New OracleParameter("I_Aircraft1_Type_VC", OracleDbType.Varchar2)).Value = .Aircraft1Type
End If
If .Aircraft1Category Is Nothing Then
cmd.Parameters.Add(New OracleParameter("I_Aircraft1_Cat_VC", OracleDbType.Varchar2)).Value = .Aircraft1Category
End If
cmd.Parameters.Add(New OracleParameter("I_Aircraft2_Callsign_VC", OracleDbType.Varchar2)).Value = .Aircraft2Callsign
If .Aircraft2Type Is Nothing Then
cmd.Parameters.Add(New OracleParameter("I_Aircraft2_Type_VC", OracleDbType.Varchar2)).Value = .Aircraft2Type
End If
If .Aircraft2Category Is Nothing Then
cmd.Parameters.Add(New OracleParameter("I_Aircraft2_Cat_VC", OracleDbType.Varchar2)).Value = .Aircraft2Category
End If
If .SensorShortName Is Nothing Then
cmd.Parameters.Add(New OracleParameter("I_Sensor_Name_VC", OracleDbType.Varchar2)).Value = .SensorShortName
End If
If .TarpConfigurationName Is Nothing Then
cmd.Parameters.Add(New OracleParameter("I_TARP_Config_Name_VC", OracleDbType.Varchar2)).Value = .TarpConfigurationName
End If
If .EntryCreatorID Is Nothing Then
cmd.Parameters.Add(New OracleParameter("I_Create_VC", OracleDbType.Varchar2)).Value = .EntryCreatorID
End If
If .LogAction Is Nothing Then
cmd.Parameters.Add(New OracleParameter("I_Log_Action_VC", OracleDbType.Varchar2)).Value = .LogAction
End If
cmd.Parameters.Add(New OracleParameter("I_TARP_Mode_VC", OracleDbType.Varchar2)).Value = .TarpOperatingMode
cmd.Parameters.Add(New OracleParameter("I_Min_Loss_N", OracleDbType.Decimal)).Value = .ClosestMeasureOfLoSS
If .MapName Is Nothing Then
cmd.Parameters.Add(New OracleParameter("I_MAP_NAME_VC", OracleDbType.Varchar2)).Value = .MapName
End If
If .TarpConfigurationFileHash Is Nothing Then
cmd.Parameters.Add(New OracleParameter("I_CONFIG_HASH_VC", OracleDbType.Varchar2)).Value = .TarpConfigurationFileHash
End If
Dim aDate As OracleDate = CType(.LossEventsMessages(0).LossEventTime, System.DateTime)
cmd.Parameters.Add(New OracleParameter("I_FIRST_LOSS_EVENT_DATE", OracleDbType.Date)).Value = aDate
cmd.Parameters.Add(New OracleParameter("I_FIRST_LOSS_EVENT_MS_N", OracleDbType.Int32)).Value = .LossEventsMessages(0).LossEventMilliSeconds
If .ZippedMapFiles Is Nothing Then
cmd.Parameters.Add(New OracleParameter("I_Map_File_BL", OracleDbType.Blob)).Value = .ZippedMapFiles
End If
cmd.Parameters.Add(New OracleParameter("I_TARP_Package_BL", OracleDbType.Blob)).Value = .ZippedTarpPackageWithoutMaps
cmd.Parameters.Add(New OracleParameter("rs_RESULTS", OracleDbType.RefCursor)).Direction = ParameterDirection.Output
End With
Return cmd
End Function
And here is the code for executing the procedure:
Dim workingDataSet As New DataSet
Dim oracleConnection As New OracleConnection
Dim cmd As New OracleCommand
Dim oracleDataAdapter As New OracleDataAdapter
Try
Using oracleConnection
oracleConnection.ConnectionString = System.Configuration.ConfigurationManager.AppSettings("MasterConnectionODT")
cmd = HelperDB.CreateTarpEventCommand(TarpEvent)
cmd.Connection = oracleConnection
cmd.CommandText = "LOADER.ADD_TARP_EVENT"
cmd.CommandType = CommandType.StoredProcedure
Using oracleConnection
oracleConnection.Open()
Dim aTransation As OracleTransaction = oracleConnection.BeginTransaction(IsolationLevel.ReadCommitted)
Try
Using oracleDataAdapter
oracleDataAdapter = New OracleDataAdapter(cmd)
oracleDataAdapter.TableMappings.Add("Results", "rs_Max")
oracleDataAdapter.Fill(workingDataSet)
....
