Toad for Oracle Macro Recording

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!