Maximizing Revenue - Web2.0 & Business Process Management

I had a great holidays and had great time with family & freinds. There were great deals in the internet for electronics, clothes, furniture etc., etc., Companies were desparate to unwind the winter stuffs like cashmere, heaters, christmas lights to refill the rackspace with the goods needed for Spring. It was so insane to see the products priced less than 60% of actual retail price and we browsed through few websites to pick some household products.

While browsing through the internet i started to think about Web 2.0, Business process, System Integration, System availability, Business Continuity, Customer Satisfaction, faster time to market, more and more!!!..

To a corporate person all these different terms means the single concept, "Maximize Revenue". I am planning to post a series of article on this business concept "Maximize Revenue" linking it to a IT process.

I talked about a web2.0 & business process management in one of my earlier blogs posted here.

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.

Music in CLOUD - Apple's strategic move

There is no question about the revolution created by Apple in the Music industry. Until now the users of iPod or desktop has to download the iTunes software on their devices(ipod, iphone & desktops) to get the music from Apple iTunes.

With the recent purchase of LALA media, an online music provider, there will be a big shift in the way the Apple delivers content. That's correct, Music from CLOUD.

May be the entire technology industry is witnessing a move towards WEB3.0 - CLOUD services.