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 | |
| end | |
| if line.start_with? "FROM" | |
| writefile = false; | |
| counter = counter + 1 | |
| end | |
| array[counter].push line.strip if writefile == true | |
| end | |
| require 'csv' | |
| CSV.open('.\working_output_analysis.csv', 'w+') do |csv| | |
| array.each { |ar| csv << ar } | |
| end | |
| end |
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.
Phew!!
Analects of an Otaku 