I am working on a an application which uses Oralce as database. There are quite a few PL/SQL stored procedures which take Oracle Array as Input parameter.
Example:
The user needs to update more than 1 role for an user from UI. The stored proc is written such that it accepts User Id and User Roles as Input Parameters. The parameter - User Roles is an array of VARCHAR2.
I have used BusinessListBase<UserRoles, UserRole>. The DataPortal_Update in my UserRole.cs loops through each User Role and checks if the User Role being updated is IsNew or not. If it is not IsNew it calls Update method on UserRole.cs which in turn has Database calls.
The problem in the above approach is I can pass only one User Role Code to Oracle stored proc at a time. But in my case the stored proc takes User Role Code as an Olacle Array.
How do we acheive such batch updates or delete using CLSA business objects and Oralce stored procs which take Oralce Array as Input Parameter?
I can't quite find the reference quickly but I think you can do something like
param1.CollectionType = OracleCollectionType.PLSQLArray
where param1 is of type OracleParameter
Sorry - that wasn't correct
Here's a bit of sample code from Oracle documentation:
int[] myArrayDeptNo = new int[3] { 10, 20, 30 };
OracleCommand cmd = new OracleCommand();
// Set the command text on an OracleCommand object
cmd.CommandText = "insert into dept(deptno) values (:deptno)";
cmd.Connection = con;
// Set the ArrayBindCount to indicate the number of values
cmd.ArrayBindCount = 3;
// Create a parameter for the array operations
OracleParameter prm = new OracleParameter("deptno", OracleDbType.Int32);
prm.Direction = ParameterDirection.Input;
prm.Value = myArrayDeptNo;
// Add the parameter to the parameter collection
cmd.Parameters.Add(prm);
// Execute the command
cmd.ExecuteNonQuery();
Console.WriteLine("Insert Completed Successfully");
Copyright (c) Marimer LLC