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:
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
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.
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: