ODP.NET OracleDecimal Conversion Fun

This is one of those self-supportive posts I love to discover.

I have used ODP.NET and Oracle for ages (the latter for more than 20 years), and generally I don't run into anything too odd, but in a very recent project I encountered something that threw me. In fact, it took a few kicks at the can to even be sure it was what I thought.

Since the project was in VB.NET, I'll show a similar line:

Dim intVar as Integer = CInt(OracleCmd.Parameters("ParamName").Value)

The Oracle command parameter in this case was a NUMBER type being returned from a stored procedure, which in ODP.NET returns an OracleDBType.OracleDecimal.

As soon as this line was encountered (and a Long conversion did the same) the code would fault with a conversion type error. Now, at first glance it threw me because I wasn't thinking as clearly as I probably should have been. And I asked myself, "How the hell can a cast from a decimal to an integer fault?" Worse was that I was misled by the actual value, which was the number 1; and my ignorance of the obvious was reinforced by a slew of documentation that noted the OracleDecimal type was equivalent to the .NET Framework's System.Decimal type.

In reality, of course, OracleDecimal is a boxed value. It may contain a decimal, but it is stored in a way that prevents an explicit cast. (I understand you can CDec it, or do a decimal cast from the ORacleDecimal, though I didn't test this.) That means that you can't expect the line shown above to work. That said, you can expect this to work reliably:

Dim intVar as Integer = CInt(OracleCmd.Parameters("ParamName").Value.ToString())

This works because the ToString() append returns a new string to memory that is a representation of the string equivalent of the primary stored number in the OracleDecimal box. It doesn't technically un-box the object, but it serves the same essential purpose and by casting a new string the CInt (Or CLng) then acts on the string returned. So, in the case of my quirky situation, ToString() returned "1" and that was then equivalent to writing CInt("1") to return a 1.

As inelegant as that all is (I'm sure there is a more pleasant way of working this magic) it works, and when you are supporting legacy code (in this case a conversion from the old MS Oracle data services provider to ODP.NET) it represents a workable solution. It is much faster than fiddling about trying to un-box the actual value, or whatnot, and you can search and replace the damn thing fairly efficiently.

The conclusion is that I was reminded of how boxed and unboxed objects behave, and how badly misleading documentation can be (even when it is correct). What hasn't been explained to me by this, of course, is why Oracle chose to not handle the conversion internal in the provider and return a System.Decimal. It strikes me as likely this would have been a much less jarring when having to manage legacy code conversions to the new ODP.NET model. But, hey, I guess I can admit it reminded me of the fabulous complexity of simple things!