Toad Dynamic SQL Scripting

Bert Scalzo — Wed, 06 Feb 2008 15:36:00 GMT

Sometimes people will request that Toad have a certain special new feature, often special to the task at hand which they’re trying to accomplish. Keeping in mind my recent Toad blog about SQL*Plus compatibility, there are very often easy ways to use existing Toad features to do what they want. In fact, many times it’s simply using Toad’s SQL*Plus capabilities once again – namely, just using the Toad editor to write dynamic SQL scripts (i.e. scripts that write scripts). Here’s an example.

Let’s say we want to simply toggle off all our referential integrity in the current schema such that we can run an import or data load. The idea being two fold – we want to maximize the load speed and also forgo the error messages for now. One way to accomplish this task would be to open the schema browser; multi-select all those tables to process, and then press the disable toolbar button. That’s still a lot of manual effort, plus there just happens to be a toolbar button to do what I’m asking. But, what if the enable/disable buttons were not there – or better yet, that we just simply did not know about them? Now we might ask for a new feature to do this. Yet it’s easily done via SQL *Plus scripting – and Toad’s support for it:

Click image above to view full size.

The following SQL script executed the Toad editor as a script (i.e. F5), can be made to accomplish the exact same thing. It’s smart enough to both adjust to the changing nature of the database – and to require an absolute minimum amount of work (i.e. steps to do).

Click image above to view full size.

Look at that! The script creates the correct script of commands based upon the current status of the data dictionary data. So this script is always up to date – and requires no modifications as the database or its objects change. Now since I’m so totally lazy (i.e. like to press as few buttons as possible), I’d simply add this script to my Toad script manager such that I can run it anytime from anywhere by simply pressing one button.

Click image above to view full size.

Bert Scalzo's Blog

Toad Dynamic SQL Scripting