In
my previous blog, I covered things that you should know about Toad for Oracle’s editor. I thought for today we could spend a few minutes on what users may benefit the most from Toad’s cousin, Toad for Data Analysis. This blog will concentrate on working with SQL.
1. SQL Recall
The time you’ve spent engineering the SQL that will one day show up on your resume will continue to pay dividends for as long as you use Toad. Every statement you’ve ever written is remembered by Toad for Data Analysis and is ready for instant recall.
The basics:
- Every statement you execute gets recorded
- By default we remember the last 100 executed (configurable with no upper-limit)
- SHIFT+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
2. Code Snippets
When you need to make a call to an database provided function, you can waste a lot of time going over your existing code looking to copy/paste or even more time trying to find examples on the Internet. In Toad for Data Analysis, I suggest you start using CTRL+SPACEBAR. This keystroke will activate Toad’s Code Snippets.
Toad will allow you to progressively drill down into a category of database function, complete with documentation on how to use them!
The real power here lies in the ‘Favorites.’ We can take a SQL query we’ve stored to the SQL Recall and convert it to a ‘Favorite’ code snippet. Or, you can choose to hand code your collection of frequently used snippets of Code. Just use CTRL+N to pull up your list of favorite snippets of code.
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 Database 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.
- schema.[A-Z]+CTRL. – ok, this sounds more difficult than necessary
. If you start typing your object name and hit CTRL. (CTRL key with a period), Toad will pop up a list of objects that match that pattern.
- Toad waits .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 also configure Toad to list the column names in the order they appear in the table, or alphabetize them
- 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. Toad will even show you the column comments so you know exactly what you’re looking at!
4. F4 DESC
DESC, or Describe, is a Oracle command that will display the table or view structure, e.g. a list of columns and their datatypes with size and precision. Toad for Data Analysis has its own DESC command available for all of the supported database vendors (Oracle, SQL Server, MySQL, DB2, and for v2.0 – Sybase!) 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’. You can type away in the editor with the DESC dialogs available to you. Ever find yourself working on a query only to find out you need to consult the existing columns or indexes? Or maybe you just need to see the existing data or even update a row to get your report query to work properly? Don’t go to the Database Browser anymore, just use ‘F4’ for instant gratification.
Get instant access to any database object with the power of the
Database Browser immediately available.
5. Make/Strip Code
Have you ever asked a developer for help on a query to only get a jumbled mess of Java of Perl script? Sure, it has the SQL in it that you need, but you spend as much time trimming off the quotes and other junk characters than you would have just writing the query from scratch, right? From now on, just let Toad for Data Analysis do this work for you! 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.
To activate the feature, just use the ‘Editor’ toolbar menu:
Presto-change-O! Your SQL is now ready to be put into your custom report or spreadsheet.
Configure Toad to use the programming language of your choice!
6. Reverse Engineer your SQL to a Query Builder Model
Ok, I’m cheating a bit here. This feature has been available in Toad for Oracle for awhile now, and has been frequently requested as a feature in Toad for Data Analysis. Well, the waiting is almost over! Toad for Data Analysis users will be able to take advantage of this feature in v2.0 of the product which is due in a few weeks. Can’t wait? Then go
here to download the beta and give it a whirl!
Summary
Writing SQL by hand can be a pain, whether you’re using Notepad or a visual query tool application like Toad for Data Analysis if you’re not taking advantage of all the features. In a future blog I’ll discuss how to develop your SQL more efficiently by using the visual Query Builder.