Sometimes time-saving tools can be fairly time-consuming. TOAD claims this dubious distinction for its auto reformat feature. Our DBAs often use TOAD to apply packages and other PL/SQL code on Oracle databases. Now, TOAD has this feature where it can beautify your code. What that translates into is that PL/SQL on the database cannot be directly matched against (for parity checks) the PL/SQL in the version control system! So, our challenge was to find some way to quickly but accurately compare these two versions. Here is how our engineers leapfrogged TOAD:
Run the following 4 regular expression find-and-replace operations on both files (we used Visual Studio 2008 but the same can be accomplished by sed or any other utility)
- Remove all newlines in the files so that we end up with one very long line of code. Step 1: Replace ‘\n’ with ‘ ‘
- Replace all tabs and other whitespace with a simple space. Step 2: Replace ‘:b’ with ‘ ‘
- Replace multiple consecutive spaces with a single space. Step 3: Replace ‘:b+’ with ‘ ‘
- Replace all semi-colons (PL/SQL statement terminator) with a semi-colon and newline. Step 4: Replace ‘;’ with ‘;\n’
Voila! Now we have two files that (although not very easily readable) can be compared for parity!