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

How to detect HTML5 support for a browser?

HTML5 has introduced lots of new cool  tags . Not all the browsers support all tags and also the implementation of these tags may be different for each browser. HTML5 specification defines the functional aspects of these tags and not the implementation. Also the general concensus is that by 2022 all browsers will support all new features of HTML5. Of all the modern browsers, Chrome seems to have implemented most, if not all, featutes of HTML5. IE9 supports few. Firefox sits in between. So as a developer how do you make use of the cool HTML5 features without causing any compatibility issues with existing browsers? Traditionally developers have used User Agent to detect browser type and use the features accordingly. However these days, you can easily change a User Agent by using addons in your browser. So you need a more robust way to detect the features supported by the browser as the same engine of two different versions of a browser mig...
Post a Comment
Read more

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

System.Configuration in .Net Framework 2 onwards

Often application need custom configuration section. System.Configuration namespace includes classes for reading and writing configuration settings. There is a slight difference in how you use this namespace depending on the Framework version you are using Prior to .Net Framework 2.0, the .Net Framework included System.Configuration namespace, but that version of the namespace is now outdated. If you simply add the System.configuration namespace to your project (using in C#), your application references the outdated namespace. To refer to the updated namespace, follow these steps 1. In VS, open the project that requires System.Configuration namespace. 2. Click on the Project menu and then click Add Reference 3. On the .Net tab, Select System.Configuration as shown in following figure, and click OK 4. Now add the System.Configuration namespace to your project normally using Imports (in VB) or using (in C#) and your application will reference the correct version of the namespa...
Post a Comment
Read more