Adventures in the Fog

Disclaimer: I am not positive of what guidelines, etiquette, etc. I should be following here so I am going to write this like I would tell someone that I bumped into at the water cooler. It may be bitchy at times. Some stuff is really aggravating and I've heard tell that Blogs can be cathartic. Take everything with a grain of salt and don't be such a bitch.

I had an interesting ulcer inducing morning this morning. I received a request for a report that was timing out in the Admin, which is Standard Operating Procedure for us because we report off of the transaction system because why would you want to do things the right way. That is so boring and it has totally been done before.

Anyway, I get the request and it is for data from the old system that was cobbled together by steam punk artists and Katrina survivors. The queries for the reports are built in the java code, are pulled together by loading temp tables and massaging data in the application. It is horseshit. We had a project a few months back that allows for pulling old system data from the new system so I checked and we had converted the requested report to a stored procedure. One would think this would be a fantastic thing. Turns out, not so much.

I convert the parameters to bind variables so I can just run the query and export the data. I get an Invalid Number error on the service code field...which is alpha-numeric. WTF, right? How can Oracle throw a number error on a character column with no conversion functions on it? I know! It shouldn't! Does not compute!

For those of you who don't know...a good way to figure out what is wrong with an extensive query is to strip things out by commenting column lists, joins, etc until you get to something you know works right. I begin the process by dropping some tables from the query (NOTE: I did NOT drop tables from the schema - that's just a phrase for commenting things out) until I get something that works. Problem is, I never got something that worked.

Another good method for fixing a broken query is rewriting it from scratch - adding in more columns and tables until you get the desired result or recreate the issue. Hopefully by this time we are getting zeroed in on the problem and can formulate a plan of attack to fix the goddamn thing.

I start out with a single table and the query runs as expected. I add in a few more columns and joins and stuff and everything seems to be working. I get to the last little bit and boom. No dice. It doesn't make any sense. Service Code can contain numbers but should not be restricted to numeric only. And why the sometimes issue? Not sure.

I take another tack and create a few temp tables to simulate the joins in the query. It is basically the idea that if there is a problem when you join table a, table b and table c all at once, how about create table ab and then joining that to c. It may shine light on whatever the ultimate issue is.

So, after a few hours of investigation, false starts and curses hurled at a world that would birth an abomination like this, I discover the true issue. The start and end date parameters are passed as timestamp and in the query, a to-char wraps the end date but not the start date. When you convert a parameter in a query to a bind variable (by adding a ':' in front) and are passing date or timestamp, you can write the value in the default format to short circuit conversion. In this case the varchar2 value'01-AUG-2012' will implicitly convert to timestamp when the query runs. Since we are doing an inclusive range, we are converting the end date to character and adding in the time component of ' 23:59:59' and since the incoming parameter is timestamp when the procedure runs as normal we need to convert that timestamp to varchar2 to add in the time component. There was a superfluous to_char() on end date. Oracle somehow flagged service_code as the problem column. Wild goose chases ensues. Fun is had by all.

I blame: Oracle for misleading errors, the system for not working appropriately, all of the developers and managers that are no longer here to defend themselves, users that have the gall to use the system and expect results, the liberal media and right-wing bible-thumping fascists. Oh, and Apple. Steve Jobs was a dick.

So, the ultimate lesson to learn here is that the error you see may not be the real error. You could have a ninja error - pops in, fucks shit up. poof - disappears. You could also have a Fox News error. This is an error that points to other things that are fine as the issue. Fox News errors are totally oblivious of their own culpability in the matter and will argue against all evidence to the contrary. The bible says God exists. Problem, Athiests?

And so concludes my virginal foray into blogging. I hope to continue blogging but let's be realistic. All of that girl on girl action is not going to watch itself, now is it. We all have our priorities.