Wednesday, June 18, 2014

FIX: ORA-00911: invalid character in c# but seemingly nowhere else

I am not normally a developer who targets Oracle, but something recently required me to go down this rabbit hole. I'm the first to admit that I hate Oracle primarily because I lack experience with their dialect of Sql and ... well, Java (Ask toolbars and security issues, grrr). But I digress.
The world's most useless error message
I write this at least partially as a joke because the specific solution I'm going to propose was a fix for my specific problem but because of the ambiguity of this error message, your problem could be something entirely different. Basically, what the Oracle database query parser is trying to tell us is that out of the hundreds of characters in your command text, at least one of them (maybe more) is not a character that it should be. It is, instead, an invalid one. Most likely, you've smashed two keys at the same time and put an errant underscore after a parenthesis. Maybe it's an obvious problem. Or maybe, like me, your unfamiliarity with Sql*Plus combined with this obtuse error led you down the wrong path a few times.
Bad semicolon
There's something about the Oracle provider in C# that causes it to crap all over your statement if the last character in the command text is a semicolon. Note that I've only tested this in the ODP.NET (DataAccess.dll not ManagedDataAccess.dll) 12.x client. In my case, I assumed it was that strange looking colon in front of the variable names. Nope, colon: Good. Semi-colon: Not so much.

No comments: