We have a oracle sql query we want to run, with one of the parameters given to it being a table parameter with multiple columns. The type definitions used are as follows:
Code: Select all
create type calculateServiceInputRecord as object (employeeId varchar2(8 byte),
startDate date,
endDate date);
create type calculateServiceInputList is table of calculatServiceInputRecord;
The code being used is the below:
Code: Select all
public async Task<IEnumerable<CalculatedService>> GetEmployeeSummedServiceAsync(IEnumerable<EmployeeDateRange> employees)
{
using (var connection = (OracleConnection)_connection())
{
try
{
connection.Open();
var query = _scriptSource.GetScript("calculateService");
var command = new OracleCommand(query, connection);
var parameter = new OracleParameter("InputData", OracleDbType.Table, "calculateServiceInputList"); // fails here with 'no data found...'
var table = new OracleTable(OracleType.GetObjectType("calculateServiceInputList", connection));
var itemType = OracleType.GetObjectType("calculateServiceInputRecord", connection);
foreach (var employee in employees)
{
var item = new OracleObject(itemType) { IsNull = false };
item["employeeId"] = employee.EmployeeId; // Fails here with 'not valid for current state...;'
item["startDate"] = employee.StartDate;
item["endDate"] = employee.EndDate;
table.Add(item);
}
parameter.Value = table;
command.Parameters.Add(parameter);
var reader = await command.ExecuteReaderAsync();
return reader.Parse<CalculatedService>().AsList();
}
catch(Exception ex)
{
System.Diagnostics.Debugger.Break();
throw ex;
}
}
}