5000+ lines of SQL Query

Why you should not use online code formatting tools? Coz most of them suck at their job.

A friend wrote a query for an Oracle Report which had 52 unions and spanned over 5000 lines. Tested and all working fine, before saving the report, she decided to format and indent the code using an online code formatter one of many many available all across the internet.

And it broke it just enough to not run anymore and just not enough to notice the difference immediately.

The error message was pretty clear that one of the unions had less/more number of selected columns but no message on which union, thanks to the bad code analyzer of Oracle BI EE.

As always, ruby magic always comes to rescue when you are in a tight pinch.

def analyse file_path
file = File.open(file_path, "r")
counter = 0 # counter for having a 2D Array
writefile = false
array = Array.new
file.readlines.each do |line|
if line.start_with? "SELECT"
writefile = true;
array[counter] = Array.new
if line.start_with? "FROM"
writefile = false;
counter = counter + 1
array[counter].push line.strip if writefile == true
require 'csv'
CSV.open('.\working_output_analysis.csv', 'w+') do |csv|
array.each { |ar| csv << ar }

The code is pretty straight forward to read. It starts with the line starting with “SELECT” and ends before the line starting with “FROM”. We had a total of 52 unions, so I just needed to store that list of selected columns from all these 52 unions in an Array. We cannot really define a 2-D array in Ruby however we can always define an Array within an Array and that solves our purpose of Multi dimensional Array.

The script provided us a nice sheet with all the column names and helped in figuring out the incorrect SELECTED list of columns from a specific union list.



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.