Automatic Bulk Formatting W/Toad

We write code once, but read it many times. PL/SQL is a functional language which is very readable. We should make every effort to make the code cleaner and maintainable.

We have Oracle code in the source control repository that does not meet standard formatting practice. Also in many cases developers do not have access to Toad, or choose not to take advantage of Toad for-matter.

How do we automate the formatting of Code in Bulk to meet the standards ?

The Solution : Toad for Oracle’s Automation Designer.

The idea is to create an App/program in Toad with instructions to format files in a specific folder. We can then run this App in Toad or through command-line.

I have detailed steps below on how to create an app using Toad, that takes files in a folder and automatically format them, then call this action from the command-line so it can be built into your existing build automation scripts.The process to import the TOAD settings are provided on page 4.

Step 1: Create a new App

I have created a new folder called ‘GWP Coding Standard’ in the AppsDesigner GUI. This is an optional step, by default your new apps will appear in the ‘Default’ folder.

Step 2: Formatting files

(Just read through Step 2, no need to take any action)

It is very easy to tell Toad to format a specific file. I can simply choose the ‘Format Files’ action from the ‘Utilities’ page and supply the file name(s).

When you double-click on the ‘Format Files’ action in my app action list, you will see the required inputs and properties for the format option.

Unfortunately this method requires that you know the file names in advance. If you are receiving a directory of files to be formatted, you do not want to explicitly input each file name every time you want to run the app. So, the brilliant minds behind Toad have given us the ‘Folder Iterator’ action.

Step 3: Create a Folder Iterator Action and point it to our input directory.

Go the “Control” Tab and click on the “Folder Iterator”.

Once you have that action in your action list, open it’s properties dialog.

Pay attention to the ‘recursive’ option. If your input directory contains sub-folders, you will want to enable this. That will cause Toad to open each folder and recursively work with each file. I have chosen the ‘*.sql’ file type, but you could have just as easily gone with just ‘*.prc’ files or all file type “.”.

Step 4: Add the Format Files Action

Instead of using the Format Files action by itself, we are going to add this as a sub-action( see red circle below) to the above ‘Folder Iterator’ action.

Click on “Add Variable “ to get the select variable dialog. Select the variable “Folder Iterator1” created in Step 3.

Step 5: Test the app

Simply hit the ‘Run selected apps’ button from the AppDesigner toolbar. Any error messages will get logged to the bottom ‘Single Run’ panel.

Step 6: Run via command-line

To execute our app from the command-line, we simply need to know the name of our app.

Import App from File

Simply download the App (txt file) to a known location on your desktop. Open Toad and launch Automation Designer. Click the “Import App from File” button to import the App with its Actions into your window. You will have to change some of the properties (right click the Action and select Properties) so that this App works in your environment or you can simply read it so you get the idea.

Source Control and formatted code

When we check-in the formatted code, that must be the ONLY change checked-in into the source control. We must remember that formatting the code eliminates (or at least greatly reduces) the ability to track previous changes.

Process to import TOAD format settings

  1. Go to View → Toad Options
  2. Go to left panel and click on “Formatter”. You can load a new format option file (.opt) using the button.