Upgrading projects from ODP.Net 9.2 to ODP.Net 10.2

I've recently been upgrading a system from ASP.Net 1.1 to 2.0. At the same time we are upgrading the Oracle client from 9.2 to 10.2, and thus to ODP.Net (vs10.2.0.2.20 to be precise).

I've now completed the first pass of the conversion and all main parts of the system appear to be working fine - note we have left the Oracle back-end on 9.2 for now, but this is also going to be upgraded to 10.2 too... let's do it in stages ;-)

Here's a couple of issues I've come across so far regarding the upgrade:

a) -1 Result from ExecuteNonQuery

It is expected behavior that ExecuteNonQuery returns -1 when executing a stored procedure (regardless of what the stored proc does), but there was a bug in earlier 9.2.x versions where it returned 1 instead of -1. If you have any code that checks this result, it's worth a scan to see if this affects any of your logic. I was caught out in one of my calls to a stored proc that updated a number of rows, and I had incorrectly assumed it would return "number of rows UPDATEd" like a standard UPDATE statement would (and in this case I was expecting an update count of 1 for 'success' so it all worked fine thanks to the 9.2 bug!). Digging deep into the Oracle ODP.Net docs come up with this:

-------
ExecuteNonQuery returns the number of rows affected, for the following:
If the command is UPDATE, INSERT, or DELETE and the XmlCommandType property is set to OracleXmlCommandType.None.
If the XmlCommandType property is set to OracleXmlCommandType.Insert, OracleXmlCommandType.Update, OracleXmlCommandType.Delete.
For all other types of statements, the return value is -1.
-------

So I read this as meaning all stored procs return -1 for success. If you want the number of recs affected, you'll need to do this yourself by adding an output param instead. If anyone has found out something different please shout ;-)


b) Runtime data types of output parameters:

Ever had problems mapping .Net types to native database field types (and vice versa)? Umm me too. DBNULLS vs nulls, casting issues, loss of accuracy, I'm sure you've been there. Anyway, here's a couple of general issues to watch out for when moving to ODP.Net 10.2:

Example 1:

OracleCommand cmd = new OracleCommand();
cmd.CommandText = "EPR_Month_PKG.Get_Month_YTD";
cmd.Parameters.Add("p_isd_id", OracleDbType.Int32,ParameterDirection.Input).Value = isdID;
cmd.Parameters.Add("p_mp_id", OracleDbType.Int32,ParameterDirection.Input).Value = mpID;
cmd.Parameters.Add("p_month_ytd", OracleDbType.Double).Direction = ParameterDirection.Output;
DBTool.ExecuteStoredProc(cmd);
// if (cmd.Parameters["p_month_ytd"].Value != System.DBNull.Value) // fine in 9.2 but condition never fires in 10.2
if (!((Oracle.DataAccess.Types.OracleDecimal)(cmd.Parameters["p_month_ytd"].Value)).IsNull) // works fine in 10.2
{
// not null... rest of code here
}
cmd.Dispose();

(Note DBTool is simply a DAL helper class that sits as part of a larger DAL library we have).

The commented out if statement worked fine in ODP.Net 9.2. but in 10.2 the output param (specified as OracleDbType.Double) now comes back as an OracleDecimal (hence my cast in the if statement). In fact I've also got cases where the output param type is specified as OracleDbType.Int32 and this still comes back as OracleDecimal. Oracle really needs to be consistent on all this, especially across versions.

Example 2:

Old working code in .Net 1.1/ODP.net 9.2

OracleCommand cmd = new OracleCommand();
cmd.CommandText = "EPR_Month_PKG.Get_Max_MP_ID";
cmd.Parameters.Add("p_max_mp_id", OracleDbType.Int32).Direction = ParameterDirection.Output;
DBTool.ExecuteStoredProc(cmd);
MaxMPID = (int) cmd.Parameters["p_max_mp_id"].Value; // this bit no longer works, you get System.InvalidCastException
cmd.Dispose();

Again, this now fails in 10.2 due to output param data type being returned as OracleDecimal (!).

This new code works fine though in 10.2:

OracleCommand cmd = new OracleCommand();
cmd.CommandText = "EPR_Month_PKG.Get_Max_MP_ID";
OracleParameter ParMaxMPID = new OracleParameter();
ParMaxMPID.Direction = ParameterDirection.Output;
ParMaxMPID.DbType = DbType.Int32;
ParMaxMPID.ParameterName = "p_max_mp_id";
cmd.Parameters.Add(ParMaxMPID);
DBTool.ExecuteStoredProc(cmd);
MaxMPID = (int) cmd.Parameters["p_max_mp_id"].Value;
cmd.Dispose();

The key bit is the specifying of DbType (instead of using OracleDbType). Doing this infers an OracleDbType (you can see a table of mappings in ODP.Net docs) and also ensures a correct mapping to the .Net int type. We could use this "DbType approach" to solve the issue in example 1 above too.

Strings as output params can be handled this way too. If you don't do it this way, I found nulls and empty strings were handled inconsistently in 10.2.


LATEST UPDATE (16 Mar 2007)

Having converted a number of calls to cater for the issues above I have added a static member to DAL class, e.g. DBTool.GetOracleParameterByDbType(...) so typical code can now look as follows:

cmd.CommandText = "EPR_MonthlyReport_PKG.Get_Target";
cmd.Parameters.Add("p_year", OracleDbType.Int32,ParameterDirection.Input).Value = year;
cmd.Parameters.Add("p_year_offset", OracleDbType.Int32,ParameterDirection.Input).Value = (int) Target.YearOffSet.TargetPlus1;
cmd.Parameters.Add("p_mp_id", OracleDbType.Int32,ParameterDirection.Input).Value = mpID;
cmd.Parameters.Add("p_person_id", OracleDbType.Int32,ParameterDirection.Input).Value = personId;
cmd.Parameters.Add(DBTool.GetOracleParameterByDbType("p_target", DbType.Double, ParameterDirection.Output));
DBTool.ExecuteStoredProc(cmd);
if (cmd.Parameters["p_target"].Value != System.DBNull.Value)
{
target = Convert.ToDouble(cmd.Parameters["p_target"].Value);
}

This is nice and tidy. Whether we should also be passing Input params by DbType is another matter but not wanting to interfere with existing code too much maybe go with this for now. Note passing a DbType also ensures that System.DBNull.Value works as expected too.

The helper method by the way is simply:

public static OracleParameter GetOracleParameterByDbType(string paramName, DbType dbType, ParameterDirection paramDirection)
{
OracleParameter NewParam = new OracleParameter();
NewParam.Direction = paramDirection;
NewParam.DbType = dbType;
NewParam.ParameterName = paramName;
return NewParam;
}