Leapfrogging TOAD

Stardate: 11011.2

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!


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 )

Google+ photo

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

Twitter picture

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

Facebook photo

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


Connecting to %s