The Find and Replace feature is handy for replacing text you know already exists in a file. However, sometimes you need to modify text without existing keywords. If this modification is repetitive, it’s likely you can automate some of it.

Let’s say I’ve got this series of select statements in a SQL script loaded in Toad’s Editor window:

select * from employees
select * from departments
select * from locations
select * from regions
select * from jobs

Toad can run the statement at your cursor by using the F9 key, but if you want to run the whole script at once with the F5 key it will error because there needs to be a semicolon at the end of each statement.

This is not a problem when there’s just a few lines, but what if there are hundreds? That’s very common in long scripts that insert data. This is one instance where using Toad Macro Recordings can be a huge time saver. Not to mention, save my sanity from mind-numbingly boring tasks like this. Since we’re modifying this script anyway, let’s also clean it up a bit by putting a white space in between.

First, find your Macro toolbar on the Toad Editor. You can right click on the toolbars, disable, then re-enable the Macro toolbar to help you locate it. The buttons highlighted below from left to right are: Play Macro, Record Macro, Stop Recording, Cancel Recording, and Edit Macros. The Play Macro button has a drop-down menu next to it so you can select what Macro to play.

Now, in the example select script above, put your cursor at the end of the first line. Click on the Record Macro button. Then, press the following on your keyboard:

1)      Semicolon key

2)      Return key

3)      Arrow Down key

4)      End Key

Now click on the Stop Recording button in the toolbar. We put in the semicolon for the current line, create a new line, and then arrowed over to the end of the next line of SQL. You’ll be asked to name this key stroke and your key stroke will be saved as Macro.

Now you can select your Macro from the drop-down menu next to Play Macro. Then click Play Macro and it will put in the semicolon, new line, and move the cursor to the next end of line.

This is helpful for other text modifications where Find and Replace doesn’t work out, like adding a Schema name in front of each table. Let’s say these tables don’t exist in my Schema, but exist in another HR Schema. Assuming the same number of characters start each line, put the cursor at the beginning of the first line:

Start Recording

1)      Arrow Right key 14 times

2)      HR.

3)      Arrow Down key 2 times

4)      Home key

Stop Recording. Select your new macro from the drop-down menu, then press the Play button to insert the “HR.” prefix to each line.

Now I can press F5 on my keyboard if I want to run all these statements in one go. This is even more helpful for something with insert statements. As always – work smarter, not harder!

About the Author

Mathew Phan

Mathew Phan began his career as a technical support engineer for Toad for Oracle soon after graduating from the University of California, Irvine, where he obtained a bachelor's degree in computer and information science. Mat has devoted his entire 14-year career with the Toad family of database solutions and held various positions helping to shape the direction and growth of Toad. Early in his tenure, his focus was on Oracle databases from a developer point of view. Now as a systems consultant, he continues to work with customers to build effective solutions that meet their needs.

Start the discussion at forums.toadworld.com