Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

Not sure if this is ontopic so I figure I would ask here and if its not liked then we can close it.

Our hosting provider for a legacy application that I inherited is no longer supporting Oracle 10 and are requiring us to move to Oracle 11g. I didn't think this would be a problem until nearly half of the SQL queries started failing. Upon research I found that previous versions of Oracle had a bug that didn't throw an exception at malformed ANSI SQL queries, and that bug was fixed in 11g.

To make a long story short, the brilliant minds that came before me wrote some awful queries and only basic integration tests, and I now have a few weeks to fix about 1000 malformed queries or we are dead in the water.

I will literally set myself on fire if I have to manually fix all of these, and was wondering if you guys knew of some tools or techniques for hunting down these offending queries to make this slightly less painful. Perhaps a special code profiler, or even a brilliant regex that can help me locate column references without an alias?

share|improve this question
I'm guessing this is something that can't be handled by changing a compatibility mode then – Ryathal Jan 26 '12 at 20:56
@Ryathal Compatibility mode?! Hmmm... I clearly didn't think this through yet. I will have to look and see how I can set that universally and not just on the session. – maple_shaft Jan 26 '12 at 20:58
Are the queries available as text files? Or can you dump them all to text? A lexer/parser could go over them and rewrite them in an easier to read format, and let you know about any that don't fit necessary characteristics. – Spencer Rathbun Jan 26 '12 at 21:04
@maple_shaft The tools part of the question is probably more suitable for Stack Overflow, than here. – Yannis Jan 26 '12 at 21:17
@Spencer Splintered within Java files as literal Strings. I am screwed aren't I? If I had to break each query out into a text file then it might take me longer than just fixing each query manually. There is no getting out of this is there? :( – maple_shaft Jan 26 '12 at 23:00
show 1 more comment

migrated from programmers.stackexchange.com Jan 27 '12 at 7:38

1 Answer

up vote 1 down vote

Argh! I wanted to open a chat panel to ask you some more questions, but actually inviting doesn't work unless you are already chatting. Oh well.

You have indicated that these are java strings, which means that there are two problems.

  1. Parsing the SQL and making decisions (mark ambiguous items for user)
  2. Finding the SQL inside the java code

Hopefully the SQL should be fairly recognizable. It exists in a string, and sql follows a fairly ridged format. Also, your non-aliased items will follow a simple format too. They will be in columnname AS aliasname. Skip those, and only add aliases for columnnames not in that format. If you load in the db schema, you even get all valid columnnames to look for. Finally, if there is an ambiguous item, then write some context to stdout, and ask the user for input.

I would suggest using python and the PLY library. You get a full blown lex/yacc combo, with an easier language than C. Open a new question on SO if you want more help/or to scream at me. ;)

share|improve this answer
My python skills are poor and I don't have time to learn. I am having trouble discovering SQL statements as I am also getting any string starting with select. If you are still on I will jump into chat! I will be in programmers room. Thanks! – maple_shaft Jan 27 '12 at 16:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.