Handle NULL values when reading through OracleDataReader?

Question

I'm working on my first ASP.Net application, and seem to be hitting a lot of hurdles (background primarily in WinForms with a recent MVC5 project under my belt).

I am successfully making my DB connection using OracleCommand and executing my query, but when I try reading through the rows I am getting a Column contains NULL value on the second row for odr.GetDecimal(1). Anyone know how to handle null values when reading through an OracleDataReader?

Below is my code:

        List<YearsOfService> yearsOfService = new List<YearsOfService>();
        string SQL = "SELECT SCHOOL_YEAR as YEAR, " +
                            "TOTAL_SERVICE_CREDIT as ServiceCredited, " +
                            "RETIREMENT_SALARY as Salary, " +
                            "SOURCE_VALUE as CoveredEmployer " +
                     "FROM " + Schema + ".RANDOM_ORACLE_TABLE a " +
                     "WHERE MEMBER_ACCOUNT_ID = :memberAccountId";

        DbConnection dbc = new DbConnection();
        OracleCommand cmd = dbc.GetCommand(SQL);
        cmd.Parameters.Add(new OracleParameter("memberAccountId", memberAccountId));
        OracleDataReader odr = cmd.ExecuteReader();

        int counter = 0;
        if (odr.HasRows)
        {
            while (odr.Read())
            {
                YearsOfService yos = new YearsOfService();
                yos.Year = odr.GetInt16(0);
                yos.ServiceCredited = odr.GetDecimal(1); // Error on Second Pass

                yos.Salary = odr.GetDecimal(2);

                yos.CoveredEmployer = odr.GetString(3);

                yearsOfService.Add(yos);
                counter++;
            }
        }

        return yearsOfService;
    }

I had thought a simple check for NULL and if so replace with 0 (since expecting a Decimal value) would work with the following, but no luck. Same error: yos.ServiceCredited = Convert.IsDBNull(odr.GetDecimal(1)) ? 0 : odr.GetDecimal(1);.

Full error is:

An exception of type 'System.InvalidCastException' occurred in Oracle.DataAccess.dll but was not handled in user code

Additional information: Column contains NULL data

I have confirmed that my 2 rows being returned are in the following format:

Year|CreditedService|Salary  |CoveredEmployer
2013|0.70128        |34949.66|ER
2014|NULL           | 2213.99|NULL

Anyone have advice on how best to proceed? How should I handle receiving the NULL value when reading through my OracleDataReader?

just curious on this particular line `yos.ServiceCredited = odr.GetDecimal(1);` how come you are not referencing the field by name and the checking it value prior to just assigning the value..? `Look up Null Coalesce` — MethodMan, Sep 24 '14 at 19:20
I haven't used OracleDataReader, but I'd guess you need to check if `odr.GetValue(1)` is NULL instead of `odr.GetDecimal(1)`. — juharr, Sep 24 '14 at 19:20
@DJKRAZE, not sure I follow what you mean. Will look up Coalesce though. @juharr: I tried that, but then I get an error either way as `yos.ServiceCredited` is expecting a `Decimal` value, not just `object` from `GetValue()`. @Syed: Tried that one, no luck, same error. — Analytic Lunatic, Sep 24 '14 at 19:29
oh, actually I used that with SqlDataReader worked fine so I thought it may work with OracleDataReader too :P — Syed Farjad Zia Zaidi, Sep 24 '14 at 19:37

score 35 · Accepted Answer · answered Sep 24 '14 at 19:22

35

yos.ServiceCredited = odr.IsDBNull(1) ? 0 : odr.GetDecimal(1);

OracleDataReader provides a IsDBNull() method.

And the docs on GetDecimal() ask us to do this

Call IsDBNull to check for null values before calling this method.

answered Sep 24 '14 at 19:22

Sourav 'Abhi' Mitra

2,390
16
15

Thanks Abhi, this did the trick! I had tried using the `IsDBNull()` method earlier, but I was implementing it incorrectly. Previously I think I tried something like `yos.ServiceCredited = Convert.IsDBNull(odr.GetDecimal(1))`. This is my first time using `OracleDataReader` and I'm inexperienced with how it does the "column" references using the different `Get()`'s. Thanks for your help! – Analytic Lunatic Sep 24 '14 at 19:32
4

thanks for this. Anyone else think it's absolute madness that a Client has to make a pre-check call like this to find out if the value is Null before you read it? Why doesn't `GetDecimal()` (etc) just return a Null value like every other DB driver? Seems crazy – Black Jan 13 '17 at 12:45
1

@Black I guess that's what we get for using Oracle? – EKW Apr 19 '18 at 14:27
@Black Because a Decimal value cannot be null. If the method were able to return null as you describe, you would have to always check the result wasn't null anyway in order to assign it to a Decimal. – Andy Hames Jan 03 '19 at 15:36
@AndyHames They should use `default` like anyone sane. – EKW Mar 09 '23 at 04:07

Handle NULL values when reading through OracleDataReader?

1 Answers1

Linked

Related