Notes on moving from System.Data.OracleClient to ODP.NET

Have just completed migration of a project from System.Data.OracleClient to ODP.NET version 11g. There were many reasons why to do this, including MS giving up further OracleClient development (http://www.infoq.com/news/2009/06/oracleclient_deprecated).

Here are my notes from the field.

1. Order of parameters in the command In ODP.NET, by default, you have to pass parameters in the order as they are in the stored procedure, otherwise you are lucky to get a conversion error, if you are unlucky you can get to the real mess without knowing. The easiest choice is then to use BindByName bool property to handle parameters by their names:

 cmd.CommandType = CommandType.StoredProcedure;

 cmd.BindByName = true;

2. Checking for null parameter values

With System.Data.OracleClient it was enough to check if value is not equal to DBNull.Value. It is not like this with ODP.NET. Actually every odp.net data type implements the INullable interface:

And thus, the check for the null value is quite different as shown in the bellow method where I try to handle both odp and OracleClient scenarios.

        public static bool ParameterIsNull(OracleParameter parameter)

        {

            if (parameter == null) throw new ArgumentNullException("parameter");

            #region Covers Oracle.DataAccess nullability of a parameter

            INullable nullable = parameter.Value as INullable;

            if (nullable != null) return nullable.IsNull; 

            #endregion

            // For System.Data.OracleClient check for DBNull.Value

            return parameter.Value == DBNull.Value;

        }

3. Conversion of parameter values

Here is very rough way one would convert parameter with System.Data.OracleClient:

 (Decimal)cmd.Parameters["p_TRACE_ID"].Value // Let’s assume check for null is somewhere above :)

Now with odp.net, it is a bit different:

((OracleDecimal)parameter.Value).Value; // Note this is an OracleDecimal, not decimal

The fact that Oracle has its own data types creates a need for extra mapping should you wish to convert the values in the generic way. I employ something like this for my needs:

        public static T GetDefaultValue<T>(OracleParameter parameter, bool mandatory, T defaultValue, 
ConvertParameter<T> convert)

        {

            try

            {

                bool isNull = ParameterIsNull(parameter);

                if (!ErrorTrap.AddAssertion(!(mandatory && isNull),

                    String.Format("Parameter: {0}, DBNull values are not allowed!", parameter.ParameterName)))

                {

                    return default(T);

                }

                if (!mandatory && isNull) return defaultValue;

                return convert(parameter);

            }

            catch (Exception ex)

            {

                ErrorTrap.AddAssertion(ex == null, String.Format("Parameter: {0}. Exception when converting value {1} to type {2}. \r\n {3}",

                    parameter.ParameterName, parameter.Value, typeof(T), ex));

                return default(T);

            }

        }

        public static string ConvertOracleStringParameter(OracleParameter parameter)

        {

            return ((OracleString)parameter.Value).Value;

        }

        public static decimal ConvertOracleDecimalParameter(OracleParameter parameter)

        {

            return ((OracleDecimal)parameter.Value).Value; // Note this is an OracleDecimal, not decimal

        }

With the following usage as:

return OracleHelper.GetDefaultValue(cmd.Parameters["p_TRACE_ID"], true, 0M,
OracleHelper.ConvertOracleDecimalParameter);

4. Usage of OracleDbType instead of OracleType. This is really a no-brainer.

This was it for me. And as you convert, don’t forget to check if you really close all IDisposable objects like RefCursors. And it really helps to have unit/integration tests :).