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

Searching Unicode characters in Oracle table

Oracle implementation of Regular expression has no support for using hexadecimal code to search for Unicode characters. The only way to search for Unicode character is it use the character itself. Normally with Regular expression, you can use \x or \u followed by hexadecimal code to search for any character. E.g. \x20 will match space. But REGEXP_LIKE in Oracle does not support \x. You need to use unistr function to convert the code to equivalent character and then use it with REGEXP_LIKE. E.g. REGEXP_LIKE(source,'[' ||unistr('\0020')|| ']');
Post a Comment
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

How to get started with .Net Core and Lambda functions?

 Recently I started experimenting with .Net Core and AWS Lambda functions, I thought I will share the steps I took to get started Tools to install .Net Core 3.1 -  https://dotnet.microsoft.com/download/dotnet-core/3.1 AWS CLI -  https://aws.amazon.com/cli/   AWS Account - 12 months Free Tier -  https://aws.amazon.com/free Terminal - on iOS I use iTerm2 -  https://www.iterm2.com   Once you installed the above tools, head to AWS console and login with your root account to create an account which will be used by AWS CLI. Please see steps here   Now you can configure AWS CLI as detailed here . Verify that you able to connect to AWS service using CLI. To make life little easy with dotnet core and Lambda functions, you need to install AWS Extensions nuget pack for .Net CLI dotnet tool install -g Amazon.Lambda.Tools You can also install templates to get started quickly  dotnet new -i Amazon.Lambda.Templates That's it. Now the fun part!!!  Creat...
Post a Comment
Read more