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!

Quality of Design

This weekend I had an opportunity to look at an Access 2003 platform database/application, and it reminded me that quality of design can exceed platform capabilities. As any developer who has ever dealt with Access, especially ancient versions, knows, the product is a kludge upon a kludge. It tends to encourage bad design at the outset because of its lack of data/presentation layer separation, and it's crimes against productivity (in terms of user interface choices) are myriad. But the application I looked at was atypical of Access-based design in two distinct ways: it was designed with a mostly correct data model, making it migration-friendly even after all these years; and it was developed with a degree of restraint that avoided at least many of the traps of Access applications. That isn't to say it was the perfect product model, because Access just doesn't manage to engender perfection, but it was one of those pleasant review experiences where it so easy to see how to move it ahead without damaging its quality of design, it's functionality, or sacrificing future opportunities to the gods of necessity.

The takeaway from this is simple: platform can be overcome by good design to a great extent.