Your Problem: You receive code from developers who do not have access to Toad, or choose not to take advantage of Toad’s SQL & PL/SQL Formatter. Before checking their code into your source control repository, you need to format them so they meet your standards.

The Solution: Toad for Oracle’s Automation Designer.

Toad’s Automation Designer originally debuted as the ‘AppDesigner’ in version 9.7 (tutorial.)

The feature allows you to take an activity in Toad and repeat it on demand. You can daisy-chain activities to build your own little Toad programs, or ‘apps.’ The activities or ‘apps’ can be executed via:

  • AppDesigner GUI
  • Toad’s Command-Line interface
  • Windows Scheduler.

    • The Scheduling feature is automatically integrated into the AppDesigner GUI, you just need to right-click on your app and say ‘Schedule.’

    For this blog, we will concentrate on how to create an app 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.

    Let’s Do It!
    Step 1: Create a new App

    I have created a new folder called ‘Toad World Examples’ in the AppsDesigner GUI so I can organize the apps I build for my blog. This is an optional step, by default your new apps will appear in the ‘Default’ folder. They will also default to being named something like ‘AppX’ where ‘X’ is the number of the app created. I suggest you give them logical names so that they are self-documenting.

    Step 2: Format our files

    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.

    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 ‘*.*’ file type, but you could have just as easily gone with just your ‘*.SQL’ or ‘*.prc’ files.

    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 to the above ‘Folder Iterator’ action. For the input name of the file, we can now supply a variable that references the filename as it iterates through the FOR LOOP.

    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.

    You can consult the ‘Execute Actions from the Command Line’ page in the Toad Help for complete instructions. In this example I can simply do this:

    Potpourri

  • When folks ask me why they should pay $ for Toad, when other stuff is ‘free’, this is one of those features I like to use as an example. The ability to define Toad macros and automate your work is a huge time-saver. Toad is all about making your job easier and saving you time.
  • Formatter options: you may want to disable or customize the header line that the Formatter adds to each file.
  • You could create an additional action that moves the folder to a new ‘Formatted’ folder. Otherwise the files will continue to be formatted each time you run the app until you clear out the folder.
  • You can also add an e-mail action to notify you when the formatting app has been executed.