Thursday, December 24, 2009

ORACLE Bulk Update from C#

In the traditional programming model, if there is a requirement to update bunch of data to database, the programmers typically will end up writing a loop in C# to establish connection and insert/update data for each record that needs to be updated. This is an expensive, inefficient operation and can cause database connection exception, memory leakage on the application servers.

The alternative way of handling bulk updates is through utilizing ORACLE custom packages & C# ORACLE ODP.NET provider. The idea is to group all the data necessary for updates into an Array, hand it over to ORACLE in a single connection from C# utilizing ODP.NET and let ORACLE loop through the bunch of data and update it accordingly.

ORACLE Custom Package

In oracle we can create a custom package of CHAR array or INT array.


CREATE OR REPLACE PACKAGE ARRAY_PACKAGE AS
TYPE REF_CUR IS REF CURSOR;
TYPE CHARARRAY IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
TYPE INTARRAY IS TABLE OF INTEGER(38) INDEX BY BINARY_INTEGER;
TYPE DATEARRAY IS TABLE OF DATE INDEX BY BINARY_INTEGER;
END ARRAY_PACKAGE;
/


The package can be utilized as an input parameter for the stored proc as shown below.



CREATE OR REPLACE PROCEDURE BULK_UPDATE_DATA (
p_Input_Value_Emp_Id Array_Package.Chararray,
p_Input_Value_Emp_Salary Array_Package.Chararray,
)
IS
BEGIN
DECLARE
DECLARE
BEGIN
FOR i IN p_Input_Value_Emp_Id.FIRST .. p_Input_Value_Emp_Id.LAST
LOOP
UPDATE EMP_TABLE
SET EMP_SAL_COL= p_Input_Value_Emp_Salary(i)
WHERE EMP_ID_COL= p_Input_Value_Emp_Id(i);
END LOOP;
COMMIT;
END BULK_UPDATE_DATA;
END;
/



ODP.NET for calling the proc

Well, we have created the ORACLE package/procedure above and we need a mechanism to invoke the procedure from C#. The default Microsoft Oracle provider does not have the necessary interfaces to pass the array to a ORACLE procedure.

The ODP.NET interface provides a collection type called PLSQLAssociativeArray which enables the C# app to pass the Array of value to the ORACLE CHAR ARRAY package.

Through ODP.NET provider, we can set the data type & Collection type of the input paramter as below.
OracleParameter parm1 = new OracleParameter();
parm1.OracleDbType = OracleDbType.Varchar2;
parm1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;

So in the above snippet, the input parameter parm1 is of type varchar (string) and the collection is associative array. In simple term, the parm1 is a string array.

Once the parameters are set to match the stored proc we have defined above (BULK_UPDATE_DATA), we just need to add the parameters to the connections command object and invoke the connection.


The complete C# program to invoke the oracle procedure "BULK_UPDATE_DATA" will look like the one given in the image below.


With the next .NET/VS upgrade (.NET4.0/2008) microsoft is getting away with the support for Microsoft's default ORACLE provider and will become deprecated (i have a posting on June that details about microsoft's oracle provider support. please click here to read more ) . So it is good practice to build the new applications that are interfacing with ORACLE using ODP.NET provider.

No comments: