I have following Employee class in my web API.
public class Employee
{
public string Name { get; set; }
public string City { get; set; }
}
my web API receives the list of 50 employee records on each request and it needs to update those details in Oracle database.
I followed the same approach of passing array of Integers to Oracle stored procedure and tried the below approach.
Created following type and stored procedure in Oracle.
--base type
create or replace type EMPTEST AS OBJECT
(
Name varchar2(50),
City varchar2(50)
);
--package
create or replace PACKAGE pkgUDFArray_Test
AS
type "T_EMPUDF_ARRAY" is table of EMPTEST;
PROCEDURE ProcEmpUDFArray_Test(
Param1 IN T_EMPUDF_ARRAY);
END pkgUDFArray_Test;
--package body
create or replace PACKAGE BODY pkgUDFArray_Test
AS
PROCEDURE ProcEmpUDFArray_Test(
Param1 IN T_EMPUDF_ARRAY)
AS
BEGIN
FORALL indx IN 1..Param1.COUNT
INSERT INTO Employee(Name,City)
VALUES(Param1(indx).Name,Param1(indx).City);
END ProcEmpUDFArray_Test;
END pkgUDFArray_Test;
Following is the code from web API.
List<Employee> EmpList = new List<Employee>()
{
new Employee() { City = "City1", Name="Emp1" },
new Employee() {City = "City2", Name="Emp2" }
};
OracleConnection con = new OracleConnection(connectionString);
con.Open();
var cmd = con.CreateCommand();
cmd.CommandText = "pkgUDFArray_Test.ProcEmpUDFArray_Test";
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter param1 = new OracleParameter();
param1.Direction = ParameterDirection.Input;
param1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
param1.Value = EmpList.ToArray();
param1.Size = 2;
param1.DbType = DbType.Object;
try
{
cmd.ExecuteNonQuery();
con.Close();
con.Dispose();
}
catch (Exception ex)
{
throw ex;
}
When i tried this I get the error "value does not fall within the expected range." at the following line. param1.Value = EmpList.ToArray();
I see that I need to do some more here to get it worked.
I searched for similar topics and I could not find any topics on this for managed client. I know there is a way for this using old Oracle.DataAccess.dll, but wanted to know if there is a way using latest managed client.