I would like to know how it's possible to send a list of a complex type as an input parameter to an Oracle Stored Procedure.. eg.
Code: Select all
var employees = new List<Employee>();
employees.add (new Employee {id=1, name="joe", HireDate=xxx});
employees.add (new Employee {id=2, name="mike", HireDate=xxx});
employees.add (new Employee {id=3, name="scott", HireDate=xxx});
Now I would like to send this list as a parameter on a command object:
Code: Select all
OracleParameter op = new OracleParameter("InputParamNameInProc", OracleDbType.Table);
op.Direction = System.Data.ParameterDirection.Input;
OracleTable table = new OracleTable(OracleType.GetObjectType("tableTypeName", _connection));
table.Add(employees);
op.Value = table;
cmd.Parameters.Add(op);
cmd.ExecuteNonQuery();
My question is, am I on the right track here? Also, how would I create the type ("tableTypeName") in pl/sql, so I can do an insert statement for each employee? That's my end goal - to execute all employees in the employees table. I tried doing a single insert per employee from .NET, using mutiple command parameters, but the performance was really bad as there are thousands of employees. I was told trying something like this would significantly improve performance.
Thanks for any guidance. This forum has been very helpful!