Why an Oracle Ace thinks Toad® has the best SQL editor in the business

As an Oracle Ace, I’ve used many SQL editors. And, there are many SQL editors out there, but I believe the Quest® Toad® SQL editor is the best in the business because it has these features:

• Code Completion/Code Snippets.
• The best Code Templates complete with variable input.
• Color Coding key words.
• A useful Auto Replace for common typos and your own shortcuts.
• A couple of more nice features.

Watch this video about the Toad SQL Editor

What is a SQL editor?

Editors allow for the easy entry/update of text in files. There are many kinds of editors such as ‘vi’, the popular Unix/Linux editor, the SPF editor on IBM mainframes, Textpad, Notepad, and many more. Some of these editors are geared toward the code they were intended for. The ‘vi’ editor can easily edit any unix/linux file. The SPF editor is more geared for languages such as COBOL.

Why is a SQL editor important?

A SQL editor is geared toward the SQL query language. A good SQL editor will have code assistants and automatic display of useful columns and table names so the person working with the SQL does not have to do a lot of typing or entry of key information such as column names and table names.

The Toad SQL editor has these items and many additional features that aid in the creation of new SQL statements as well as maintaining existing SQL statements.

Examples of Toad SQL Editor Code Completion

Toad SQL Editor has a nice feature called Code Insights. This feature greatly assists the Toad user to show available tables and columns, allowing the Toad user to pick the columns they need from a popup list so that you do not have to manually type in long column names.

Toad Code Completion Example 1

The above example shows the basic syntax of a select statement. I filled in the table name and gave it a simple table alias as well. This plays nicely with good SQL code development.

Notice the red ! on line 2. Toad is an excellent SQL editor as it does syntax checking as you go. Hover the mouse over this item to see what it is trying to help you with.

Toad Code Insights

Toad Code Insights are signaled via a table name or table alias followed by a ‘.’ …then wait a few milliseconds. The Insights panel pops up. As the user starts typing the column name they desire, the list is quickly whittled down using the leading characters entered.

Microsoft Foundation Multi Select

The Toad SQL Editor user can double click on a column in this list or they can use Microsoft Foundation multi select. This is done by clicking on the first in a list, holding down the shift key and clicking the last of the desired list…this will highlight all the columns including and in between the columns selected.

I used the control button and made my selections. This feature allows you to select different columns throughout the list.

Code Completion Complete with Table Alias

Now, you hit the Enter or Return key and your selection is pasted into the SELECT clause or the WHERE clause…at the point of the cursor.

When you use the table alias, all the columns in the SELECT list now are qualified with the same table alias. This is a good coding practice and Toad Insights automatically does this style of coding.

Controlling the Code Assistant

The Toad Options (a button on the ribbon bar or menu items View àToad Options) allows for control over this feature. Notice in the above popup pick list that the columns appear in alphabetical order. This is controlled by the Sort alphabetically check box. I would prefer the columns appear in the order they are in the table, but Toad gives you the choice.

Examples of Toad SQL Editor Code Snippets

Toad Code Snippets are SQL syntax at your fingertips. A good SQL editor will have SQL syntax assistants and Toad’s is simply the best. The Toad team have added far more of these code snippets than I have seen in any other SQL editor tool.

Accessing Toad Code Snippets

Access Toad Code Snippets via the ribbon menu buttons View àCode Snippets. This panel will appear on the right hand side of Toad.

Toad Code Snippets auto hide panel

Toad Code Snippets has SQL syntax organized by category. As you select one, a description of the syntax and how to fill in the variables and options appears in the lower section. The push pin on the top bar allows for this panel to auto hide along the right hand side.

Toad Code Snippets Categories

Notice the plethora of various syntax options available. I use the SQL Optimizer hints when doing SQL performance tuning.

Using Toad Code Snippets

Just drag and drop the code snippet from the code snippet panel into the SQL where you would like it to be. This particular bit of syntax has 2 required options: a date field/data depicted by the ‘d’ and the number of months to apply to this date depicted by the ‘n’. This feature clearly makes finding and using various SQL functions and other options a snap with their options also at your fingertips.

Maintaining Toad Code Snippets

You can easily add your own code snippets. Using Toad Options –> Code Snippets, you can easily add or change any snippet in any category. You can even change your useful or commonly used snippets to include more of the syntax you would commonly use.

Examples of Toad SQL Editor Code Templates

Toad Code Templates are more useful for the PL/SQL developer, but you can add your own templates to store and easily reuse a common SQL pattern you use frequently.

Available Toad Code Templates

Toad Code Templates are signaled by holding the control button while pressing the space bar. You can enter the beginning parts of a template and just the pattern of your entry will appear in the list. No pattern or template name will signal the entire list to be displayed.

Using Toad Code Templates

Many of the Toad Code Templates has variables so that the code built in the Toad SQL Editor will contain more of the desired result. I don’t know if there is a limit to the number of variables you can have or even a limit to the size of the template.

This template has a single variable named ‘c1’.

Toad Code Template in Action

In this example, the text EMP was entered and Toad substituted this text where ever the &&c1 appeared in the template.

Coding Tips for Toad Code Templates

The cursor will be placed where the vertical bar appears (see line 3 before the INTO in this example).

Creating your own Toad Code Template

These templates are easy to setup and maintain. This illustration shows a code template being created with basic SQL syntax. This template will be signaled with the ‘sf’ entry (again using the control-space bar combination).

Another SQL Code Template Example

The above example shows some SQL syntax where the table name will be promoted and automatically filled in then the cursor will be placed on the SELECT line.

Your Own SQL Code Template in Action

In this example, when ‘sf’ is entered and followed by control-spacebar, the template starts up, prompts the Toad user for the TABLE_NAME variable then loads the Toad SQL Editor with the template and entered data.

You can put any code in here. I have always felt this would be a convenient place for common PL/SQL exception lists as well as commonly used FROM/WHERE clause statements. Templates like this would make for Toad users that are light with SQL experience to be able to use complex join conditions easily.

The SF Toad Code Template in Action

Examples of Toad SQL Editor Color Coding

This is a feature I find useful as the various parts of a SQL statement will appear in different colors. This is useful to quickly identify the SQL syntax from column and table names in your data objects.

Toad SQL Editor Color Coding Example

A PL/SQL Toad SQL Editor Color Coding Example

Color coding appears for the PL/SQL developer too. Again the Oracle syntax appears in blue, table names and columns in black and in this example, exception names appear in red.

Adjusting the Toad SQL Editor Color Coding

The Toad Options Font and Style allow for you to change or enhance the color coding.

Examples of Toad SQL Editor Auto Replace

Toad has a spell checker called Auto Replace.

This feature is signaled by the space bar. In the following examples, ‘the’ is misspelled and Toad auto corrects it to the correct spelling.

Toad Auto Replace Misspell Example

Toad Auto Replace Example in Action

Controlling the Toad Auto Replace

In Toad v13, Auto Replace has its own area in Toad Options. In older versions of Toad, Auto Replace appeared in the Code Assist area.

Enhancing Toad Auto Replace

Toad Auto Replace is a powerful feature of the Toad SQL Editor. You can literally add any of your own coding shortcuts. I enhance my own Toad with these short single line bits of syntax.

Notice you can Export and Import these. You can also reset these back to the original installation defaults. I call this the ‘Dan’ button. Long story short…years ago…I asked the Toad team for the ability to put Toad back to its original installation defaults easily. I am not sure if my input was a part of this button or not but I like to think so. When my training lab was in a client/server arrangement, Toad was installed on each attendee’s laptop that I provided. Now I use VMWare and each course and attendee gets a fresh install of Toad.

The SF Auto Replace Addition in Action

I entered ‘sf’ and hit the space bar, and voila: my code snippet appears.

When would I use a Code Snippet, a Code Template, or this Auto Replace?

I’d use a Code Template if the desired syntax was multiple lines long. I’d use this Auto Replace or a Code Snippet for short bits of commonly used SQL. This is more of the Toad users preference. Can you remember your little short cut names or do you like to drag and drop your options from the Code Snippet panel.

For example, I have my own preferred SQL Hints that I like to add to the Code Snippet category SQL Optimizer Hints. I also clean this category out of the hints I’ll never use so the ones I do use are easier to find.

Examples of Toad SQL Editor Other Useful Items

Since we are on the topic of the best SQL editor, I feel Toad is simply the best and Toad has so many features that make it the best, there just isn’t time to list them all here.

I did want to give Refactoring a plug though. If your shop or department only wants ANSI SQL and you are old school SQL like me (I like the traditionally coded join statements), Toad SQL Editor again is the best.

Right Click Assistance in Toad SQL Editor

You right click in the SQL editor and a variety of useful items appear. I have used many over the years and one I rather like is the ‘Formatting’ feature. This will clean up a lot of messy SQL and PL/SQL code.

Notice in the Refactoring option displayed above, you can convert SQL between non ANSI and ANSI coding standards, you can add or remove table aliases, convert DECODE (old school) to inline CASE statement, and so much more.

Toad Refactoring in Action

In this example, I asked Toad Refactoring to change my regular join syntax to ANSI join syntax. Toad Refactoring also added fully qualified table names (adding the schema name to the table name, another good coding practice) and formatting the SQL.

Closing

The Toad SQL Editor is probably the best SQL editor available because of its flexibility and its ability to assist the user with creating useful SQL code quickly and error free. Toad is rich with features that assist the developer or business analyst in creating and maintaining good SQL code quickly.

Related information

Video: Dan's Dozen Quick Toad Tips – Tip #1 – Toad Insights

Blog: Toad templates

Video: Dan's Dozen Quick Toad Tips – Tip #2 – Auto Replace

Blog: Toad 12.1 Offers Major SQL Code Refactoring Capabilities

Have questions or comments?

Head over to the Toad for Oracle forum on Toad World®!  Chat with Toad developers and lots of experienced users. 

Help your colleagues

If you think your colleagues would benefit from this blog, share it now on social media with the buttons located at the top of this blog post. Thanks!