Skip to main content

ODP.Net issue for 10.2.0.4 driver

Please note that Windows 7 has a new Oracle Client (10.2.0.4). In most cases this should be simply the case of removing the existing reference to Oracle.DataAccess from your application and adding a new one from new client folder.

However in one case, we came across rather an unusal problem.

We call stored functions from a windows application which return Integer type value. Please note we are using RETURN type values not OUT type parameters as this a Function.

Here is the C# code.
/****************************/
OracleCommand cmd = new OracleCommand("DoesDataItemExist", oraCn);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("RETURN_VALUE", OracleDbType.Int16, ParameterDirection.ReturnValue);

cmd.Parameters.Add("p_ID", id);

cmd.Parameters.Add("p_YEAR", year);

oraCn.Open();

cmd.ExecuteNonQuery();


Int16 retVal = (Int16)cmd.Parameters["RETURN_VALUE"].Value;

oraCn.Close();

return retVal > 0;

/*******************/

This calls a stored procedure

/***********************************/

FUNCTION DoesDataItemExist(p_ID IN CHAR, p_YEAR IN CHAR) RETURN INTEGER IS

v_RecCount NUMBER;

BEGIN


SELECT Count(*) INTO v_RecCount FROM t_dataitems t

WHERE t.f_id = p_ID AND t.f_year = p_YEAR;

RETURN v_RecCount;

END DoesDataItemExist;

/************************************/

The above code worked fine with VS2008, Oracle driver 10.2.0.1 and .Net Framework 3.5. However when we migrated the code to VS2010, Windows 7, Oracle driver 10.2.0.4 and compiled it against .Net Framework 3.5, we got a run time InvalidCast operation in line

Int16 retVal = (Int16)cmd.Parameters["RETURN_VALUE"].Value;



It seems, the RETURN_VALUE parameter was now of type OracleDbType.Decimal and clearly .Net runtime could not cast it into Int16. Even if we change the return type on stored function to Number or Integer or Number(1), we were still getting the parameter as Decimal.

It seems this is a known Oracle bug for which no fix has been applied. Please see http://forums.oracle.com/forums/thread.jspa?threadID=613812.

The workaround is to explicitly convert the RETURN_VALUE to Int16 by one of following ways

Int16 retVal = Int16.Parse(cmd.Parameters["RETURN_VALUE"].Value.ToString());

or

Int16 retVal = Convert.ToInt16(cmd.Parameters["RETURN_VALUE"].Value.ToString());

This seems to fix the problem.

So please look out for cast operations in your code specially when reading data from Oracle.
Labels:

Comments

Post a Comment

Popular posts from this blog

Why there is semicolon at the start of a JavaScript function?

Very often while reviewing the code for my team, I will come across a semicolon at the start of JavaScript function as show below ; (function () { 'use strict'; ...and I often wondered what purpose it served. Guess what. It is an insurance to make sure your script works fine when all other scripts are merged together;  The leading ; in front of immediately-invoked function expressions (iffe) is there to prevent errors when appending the file during concatenation to a file containing an expression not properly terminated with a ;. So there you go. Now you know what that little semicolon is doing there in your code.
3 comments
Read more

C# Performance Improvement - The Power of StringBuilder

 Often when we are wring code we don't think about performance and go with the default options available to achieve a task. String concatenation is one such scenario. If you are doing simple and few string catenations, then you can use the following result = string1 + string2; string1+= string2; result = String.Concat(string1,string2); String.Format and string interpolation are few other options.  However when you are performing large and repetitive  operation, string catenation can be expensive. Here is an example to prove the point.  As you can see it took 41 seconds to perform 100k string catenation. Now lets replace this with StringBuilder and see.  8 ms!!!!!! That is a massive performance difference. Hope you get the point. More info on StringBuilder can be found here https://learn.microsoft.com/en-us/dotnet/api/system.text.stringbuilder?view=net-7.0
Post a Comment
Read more