Oracle 11g odp.net driver issues with Null value

Question

I am having issues comparing null values with oracle 11g odp.net driver. It works fine with oracle 10g odp.net driver. If a column is null in the database, then in datarow it has a string value of null.

This code fails:

int parentId =  row[PARENTID] != DBNull.Value ? int.Parse(row[PARENTID].ToString()) : 0;

Thanks

Hows does the code fail? (Failing and not working expected are slightly different cases...) — , Feb 02 '12 at 23:21
What I mean is the value of row[PARENTID].ToString = "null" instead of null. So when it tries to Parse it as an integer. I get an exception. "Input string is not in correct format" — Naveen Chakravarthy, Feb 03 '12 at 16:51
What is the type of `row[PARENTID]`? If it is `string` or `object` and if the value *is really null* then that would throw a NullReferenceException (*unless* someone snuck in an extension method!). Perhaps the data stored *is* the string "null"? (Or there is something broken, or a flag, in the adapter...) — , Feb 04 '12 at 00:07
@pst what I learn from other users is Oracle is now spending "null" value if the column is null in database. — Naveen Chakravarthy, Feb 06 '12 at 22:44

maridob · Answer 1 · 2012-02-15T22:18:10.793

This has something to do with the ORacle11g. Like what you said, instead of real null value, it is a null string. If you are checking from OracleDbType, the null in OracleDbType and CLR Datatype both inherits from INullable so below was my solution:

((INullable) rowParamDBType.Value).IsNull

To have a cleaner solution, you can place this in extension.

If your row is a DbType, you can check for param.Value==DbNull.Value.

While the above will work too, the real solution to this problem is use the OracleDbTypeEx instead of OracleDbType in your OracleParameter declaration. OracleDbTypeEx will return the value to DBType and because of this it will recognize the DBNull. See example code below.

command.Parameters.Add(new OracleParameter
                            {
                                ParameterName = "param_out",
                                OracleDbTypeEx = OracleDbType.Decimal,
                                Direction = ParameterDirection.Output
                            });

if (command.Parameters["param_out"].Value != Convert.DBNull)
{
  //your code here
}

score 4 · Accepted Answer · edited Oct 05 '12 at 20:51

4

Here :

var para6 = new OracleParameter("pOrganizationParentId", OracleDbType.Long){
    IsNullable = true,
};

if (string.IsNullOrEmpty(organizationParentId)) {
    para6.Value = null; 
} else {
    para6.Value = long.Parse(organizationParentId);
}

edited Oct 05 '12 at 20:51

Chris Laplante

29,338
17
103
134

answered Aug 23 '12 at 10:08

DuND

56
1

score 3 · Answer 3 · answered Feb 08 '12 at 20:53

3

Fromm the Oracle documentation:

f 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.

answered Feb 08 '12 at 20:53

Adilson de Almeida Jr

2,761
21
37

Oracle 11g odp.net driver issues with Null value

3 Answers3