ORA-06502: PL/SQL: numeric or value error: character string buffer too small

With the annoouncement from Microsoft on deprecating System.Data.OracleClient, i started to use Oracle client, ORACLE.DATAAccess and it has many uncovered challenges. i am posting one of the challange i faced in utilizing ORACLE.DATAAccess and the resolution.

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Finally got it resolved. The problem is the proc returns two parms as OUT - RAW and VARCHAR2.

From C#, we can pass input with VARCHAR2 and the size of VARCHAR2 is adjusted based on the input string length. But for the Output, C# allocates 0 bytes - so the step in the PROC that assigns value to the output parm is throwing this error.

To Fix this error i assigned the MAX length of VARCHAR2 for the OUT parm - eventhough i dont like it :) and it started to work.

So if you know the size of the output parameter, you can pass the size appropriately. but for arbitraty length output we may have to pass the max of datatypes's length.

Maximizing Revenue - Web2.0 & Business Process Management - Part 1

"Maximize Revenue", the golden term of maximum usage among corporates from food chains to retail to technology to financial sectors to energy industries and everywhere. Everybody wants to run a profitable business.
At the end of the day, the corporates want to provide maximum value to their investors, employees and to their customers.

To provide the maximum values, the companies have to stay ahead in competition and offer products and services faster and easier than their competitors. Needless to say the quality of service plays an important role as well.

Gone are the days where we had been to banks, fill the form and waited for days to week to get an account set up for us. It was once upon a time, long long ago where we had been telephone companies, fill the application form and waited for weeks to get the telephone line installed at home. Gone are the days where we had filled the application for insurance, waited for months to get the policy mailed to us.

The continous invention in the technology and integration of business process had led us to where we are rite now. Within few mouse clicks or a call we can have a new bank account set up, telephone lines established, have our insurance policy created and emailed to us. The integration of business process have also resulted in decreased number of manual intervention, decreased errors and increased quality of service. It reduces the labour requirements for corporations and corporations started to effectively hire/allocate human resources. There by reducing the cost of manual labour significantly.

The companies have to keep up with these innovations on par with their competitors otherwise they would lose their potential customers. Lets take a banking industry for example, the banks typically come up with different products and market the new products to their customers.Unless they have a good mechanism to market the new products, the customer might not know about the product and will not utilize/buy it. Assume the bank has a good marketing strategy in place to sell their product, unless the bank has a very good business/technology process they might not be able to offer the product on time with a good quality. If the bank dont have the infrastructure/resources in place to market, provision and provide updates to the customers they will not be successful in realizing the Return on Investment for the new product.

Over the past years we have seen the that organizations are investing in providing the information about the products that the customer bought, history of bill payment and invoices through the web sites. In the recent years the trend had shifted to market and sell the products and services online. We are just few clicks away from buying the products. We can also feel the product, customize for our needs and get a price quote for our customizations within a fraction of minutes.

It is very important for corporations to keep the information available to the customers upto date 24X7. If there is any problem with the way we provide informations - either through web sites or emails or mail, it will affect the trust the customer has on businesses and decreases customer satisfaction.

So with that said, just want provide the summary of what i had talked above and close this discussion for today (its 11.30 PM CST). To maximize the revenue, the corporations had invested and will invest in integrating business processes, provide accurate information about products and service offerings online with 100% availability of information, effectively market and sell products online, innovate/invest in new technologies to provide faster services efficiently and cheaply.

I will talk about the online service offerings, marketing/selling products in Part 2 of this series. I am planning to cover in detail about the business process integration at the end of this series.

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.

Cloud Computing

I have been researching about Cloud computing for one of the projects i am working on rite now. It was amazing to see the services offered through cloud computing.

In the traditional application development model, if there is a need to develop and host a internet facing applications, we need to do the capacity, infrastructure, security planning and purchase hardwares (windows, unix, websphere servers) and softwares. The process of setting up the infrastructure takes few weeks to couple months since it has to go through neccessary approvals and budgetary constraints of the organization. Lets say we crossed the first bottleneck of acquiring hardwares/softwares, the next hurdle is having the infrastructure laid down by the network group and setting up the secure environment with the Militarized zones for security purpose. Also a point to note is, if the application is mission critical and have heavy volume online transactions the capacity planning will also include the details for load balancing of the servers and the infrastructure design and environemnt set up has to account for the installation of load balancers.

Also cost wise in the traditional model, its very expensive to maintain and manage the infrastructure. The project will incur an annual maintanance cost for the servers, licenses and support to install patches and upgrades.

I think i am throwing things that is pretty normal in IT organizations. Well, the cloud computing provides a relief to all these hurdles.

The cloud computing provides the infrastructure and technology facilities over the internet. So the project team doesn't need to worry about establishing the infrastructure. The technology infrastructure is abstracted from the application development. We just need to know the type of infrastructure required for the application (For eg: windows 2008 , unix with ORACLE 10g etc.,). Once the type of technology infrasture has been determined, we can install/set up the hardwares/softwares through couple of mouse clicks and the instances will be ready to use. The infrastructure set up takes around 10-20mins and you are all set to begin development/deployment.

The cloud computing alleviates the effort/cost required to set up the infrastructure and also saves the maintanance cost. One more good thing about cloud computing is the cloud service providers automatically patche the instance/server and its free of cost.

One more point to note in cloud is, if for any reason the application requires additional servers/processing power, adding few more instance to the existing instance is pretty easy. For ex; lets say if the application requires few additioanl processing power from 8AM-5PM and can run with the normal processing power during the rest of the day, the cloud computing provides elastic facility to expand/contract the computing capacity of the server.

From cost perspective, we pay for what we use in terms of computing facility.

I was playing around with Amazon Elastic cloud and was suprised to see that within few mouse clicks i was able to mount the Unix with the ORACLE 10g database instance.

There are several providers in the cloud space but i just looked at services provided by Amazon and its extra-ordinary.

Web 2.0 & Business Process Management

Web2.0 has revolutionized the internet world and offered a seamless transition from paper forms/document to online web pages. The industries across the globe invests in IT projects to cut costs on postal and paper charges by offering the solutions online through the websites.

The telecom industry has eliminated the paper submission of application forms for new telephone/wireless connection to online submission and paper bills to electronic bills. The form submitted online is integrated with the Ordering system whereby the orders are keyed in for a customer. The created order flows through the provisioning system where the order is assigned to a network technician for physically establishing the connection to the home. Once the technician completes the order, the call charges are calculated in the Rating system and supplied to a billing system where the bills are generated and posted online for customers review and payments.

The financial services [banking] industries has integrated online submission of mortgage forms with the business process of credit approval. When the customer submit the mortgage form online, the process flow through validating credit history & residential address. Upon credit approval, the business process is coupled with the quote generation for customers review. Once the customer approves the quote online, the customer will be recieve the credit and the bills for mortgage payments are generated online for customers review and payment.

The other major financial services industry is insurance and the business process is tightly integrated with the technology. The insurance companies are promoting the sales online by selling the products through websites. When the customer requests for quote for a policy, the customers primary medical details are collected online. The medical history is verfied with medical information board and the underwriting engine generates the quotes for underwriters review based on mortality/medical history. The quotes are released by underwriters for online review of customers/policy owners. When the customer agrees for the insurance quote, the quote is converted to a policy and the policy is available online for the customers.

We can speak a lot about Web 2.0 and integration of business process management. But the ultimate benefit to the businesses are:

> Reduction in expenses incurred for paper mail
> Quickers sales
> Faster realization of cash/payments from the customers
> Reduction in manual intervention in the business process & hence decreased errors.
> Effective collection of data for generating potential leads
> Environmental friendly business