The editor window is probably where most users spend a majority of their time. In this blog, I’ll try to highlight the 10 features that will save you the most time when it comes to writing your SQL queries, SQL*Plus scripts, anonymous blocks, and PL/SQL stored procedures. Please note that all of these features are available in the standard edition of Toad for Oracle, version 9.6.
1. SQL Recall
By far one of the most popular features in Toad, but do you know how to take FULL advantage of this feature?
The basics:
- Every statement you execute gets recorded
- By default we remember the last 100 executed (configurable up to 999)
- F8 opens the SQL Statement Recall panel (also available under the View menu)
- ALT+PgUp/Dn will cycle through the most/least recently executed statements
- You can name your SQL for instant recall using the CTRL+N keystroke
Give your queries a name so you remember why you wrote them in the first place!
2. Code Templates
Ever experience déjà vu while typing out your code? This is your cue to save it as a template for use later. Toad gives you access to several dozen of these out of the box. You can also add your own or customize the existing ones. Use the CTRL+Spacebar keystroke to see the code you no longer have to type out manually.
Remember the code snippet names for instant recall, try typing ‘anon CTRL+Spacebar’ right now!
3. Code Insight (Dot Lookups)
When you want to write a SQL query or make a PL/SQL call, you probably spend a bit of time manually looking up the column names or command arguments required in the Schema Browser. Stop doing this right now! From now on, let Toad figure this out for you automatically in the editor.
The basics:
- Schema. – this will pop up a list of all objects in the current schema you may want to type
- Schema.Table. – this will pop up a list of columns for a given table. You don’t need to prefix the schema name if the table is in your current schema. Multi-select columns to get more than one, and we’ll comma separate the list for you automatically
- Package. – this will pop up a list of procedures, functions, variables, etc for that package.
- Stored Procedure( - this will pop up the list of input parameters to make the call. You can type while you reference the list. Entering the ‘)’ character will close the hint.
- Toad waits 1.5 seconds after seeing the ‘.’ or ‘(‘ character before popping up the list of items available for that object. This is configurable down to the millisecond for optimal usage.
- You can use aliases for your view/table names and the code insight feature will automatically include the alias when bringing back a list of columns.
4. F4 DESC
DESC, or Describe, is a SQL*Plus command that will display the table or view structure, e.g. a list of columns and their datatypes with size and precision. Toad has its own DESC command. You can invoke it immediately for ANY object in the database by typing that object in the editor, putting you cursor on it, and hitting ‘F4’. This is one of the most ‘borrowed’ features in an Oracle IDE. Do you find it curious that it’s always activated with ‘F4’? Imitation is surely flattering, even if it is poorly executed J
You can have multiple F4 dialogs open at a time. You can type away in the editor with the DESC dialogs available to you. Ever working on a query only to find out you need to add a column, update a row, or change the permissions on a table? Don’t go to the Schema Browser anymore, just use ‘F4’ for instant gratification.
Get instant access to any database object with the power of the Schema Browser immediately available.
5. Make/Strip Code
Turn any SQL statement into a Java, C++, Delphi, Perl, etc command in a single button click. Even better, take any embedded SQL statement and strip away the non-SQL syntax for instant execution in the Editor. This is one of the most over-looked features in Toad. No more REGEX search and replace to get your well-crafted SQL statement ready for your Eclipse or Visual Studio application.
Configure Toad to use the programming language of your choice!
Summary
Ok, I know we only covered five things, but my fingers are tired and I need to get back to my ‘real’ job. Look for the next five things you MUST know about Toad’s editor in my next blog. And as always, feel free to share your own tips and questions using the blog comments feedback section.