DBNull issue with Oracle 11g ODP.Net provider

Question

We are facing problem in checking output parameters for “DBNull”. “DBNull” value returned by Oracle stored procedure or function is treated as “null” string by oracle 11g client/ODP.Net provider. This works fine with oracle 10g client as it returns “DBNull”. Because of this all our “DBNull” check fails

Answer 1

Following the breaking change that Oracle released (see comment below). You need to add the additional bit below:

if (oraParam[7] == null || oraParam[7].Value == DBNull.Value || ((INullable)oraParam[7].Value).IsNull)

INullable is in the Oracle.DataAccess.Types namespace.

Answer 2

ODP.NET RETURNING "NULL" STRING WHEN THE VALUE IS NULL [ID 968857.1]

    Modified 04-JAN-2010 Type PROBLEM Status PUBLISHED               

In this Document Symptoms Changes Cause Solution

Applies to: Oracle Data Provider for .NET - Version: 10.2.0.2.20 to 11.1.0.7.10 Microsoft Windows (32-bit) Microsoft Windows x64 (64-bit)

Symptoms After migrating from a previous version of Oracle Data Provider for .NET, a change in behavior may occur with respect to null values being retrieved. Whereas the application previously returned an empty string, a string with the value "null" is now obtained. Changes Migration from 1.x ODP.NET to 2.x ODP.NET

Cause The behavior is due to a migration from the 1.x Oracle Data Provider for .NET to the 2.x provider. 2.x ADO.NET supports the ability of the provider to return provider specific types, and this is one of the potential "breaking changes" going from .NET 1.x to 2.x.

ODP.NET versions 9.2.x, 10.1.x, 10.2.0.1.0 were all 1.x framework providers. Typically this behavior is noticed when migrating from an early version ODP to a newer version of ODP, but at the same time swtiching from the 1.x provider to the 2.x provider. It is the change in .NET framework support rather than the change in Oracle client version that causes this behavior.

This behavior may also be noticed migrating the application to a 64 bit operating system, as there is no 1.x 64 bit framework.

Solution To resolve this behavior, the code will need to be modified.

As a workaround, using ODP.NET for 1.x (1.111.7.0 for example instead of 2.111.7.0) will result in the previous behavior, but note that the 1.x provider is not tested or supported on any version of framework other than 1.x, and that support for 1.x is not planned for any version later than 11.1.0.7.0

If the operating system is 64 bit, the application will need to be forced to run under the SYSWOW64 subsystem (ie, as 32 bit) in order to use 1.x ODP.NET.

To correct the code:

If the value is a DbType, you can check for param.Value==DbNull.Value

If the value is an OracleDbType, you can check for ((INullable)param.Value).IsNull since Oracle Types inherit INullable interface.