TOAD Tips and Tricks – SQL Editor

Date: Monday, October 4, 1999

Author: Steve C. Chapman, TOAD Development Team, Quest Software, Inc.
More Info: www.toadsoft.com or www.quest.com

TABLE OF CONTENTS

Is there a way to have TOAD look up table columns as I construct a query? ....................................................................................2
How can I copy table names into the SQL editor? .............................................................................................................................3
How can I copy table column names into the SQL editor? ................................................................................................................3
Is there a shortcut key to present the context right mouse click menu? .............................................................................................4
How can I get a description of an object when typing in the SQL editor? .........................................................................................5
How can I use TOAD to dump some Oracle table data to another Windows application? ................................................................6
Can you list all of the ways to get code into the SQL editor? ............................................................................................................9

“TOAD Tips and Tricks SQL Editor” copyright © 1999 by Quest Software, Inc. www.toadsoft.com and www.quest.com 1
Is there a way to have TOAD look up table columns as I construct a query?

Yes. When typing a table name or view name and the period, pause (or press <CTRL>T) to get a list of columns in a dropdown list.
This applies to ANY table or view that the user has access to, including objects from other schemas, SYS, SYSTEM, etc.

Once the list appears, click <CTRL> click to multi select items, then press <ENTER>. TOAD will place those selected columns into
the SQL editor to build your query.

select table_privileges.TABLE_NAME,
TABLE_PRIVILEGES.SELECT_PRIV,
TABLE_PRIVILEGES.INSERT_PRIV

After a query has populated the SQL Results grid, pressing <CTRL>T will present a dropdown list of the columns from the SQL
Results grid.

“TOAD Tips and Tricks SQL Editor” copyright © 1999 by Quest Software, Inc. www.toadsoft.com and www.quest.com 2
How can I copy table names into the SQL editor?

You can copy the Table, View, or Synonym names from the Table Select dialog to the SQL Editor via drag and drop or multi select
list copy <CTRL>C and paste <CTRL>V.

How can I copy table column names into the SQL editor?

You can copy column names from the Column Name Select dialog via drag and drop or multi select list copy <CTRL>C and paste
<CTRL>V.

“TOAD Tips and Tricks SQL Editor” copyright © 1999 by Quest Software, Inc. www.toadsoft.com and www.quest.com 3
Is there a shortcut key to present the context right mouse click menu?

Yes. Pressing <F10> displays the right click mouse menu for either the SQL editor or the SQL results grid.

“TOAD Tips and Tricks SQL Editor” copyright © 1999 by Quest Software, Inc. www.toadsoft.com and www.quest.com 4
How can I get a description of an object when typing in the SQL editor?

Placing the cursor on an object and pressing <F4> will display a popup window that completely describes the object. This applies to
Tables, Views, Procedures, Functions, and Packages.

“TOAD Tips and Tricks SQL Editor” copyright © 1999 by Quest Software, Inc. www.toadsoft.com and www.quest.com 5
How can I use TOAD to dump some Oracle table data to another Windows application?

You can query some data and process it further in another Windows application, e.g. Excel, by executing a query, e.g., [select * from
employee], narrow down the columns to just those you want via right click over the SQL results grid and selecting the “Select
Columns…” menu item, unchecking the undesired columns. Then select the “Save As…” menu item from the right click menu, check
the “Include Column Headers” checkbox. Switch to Excel and paste in the results.

[a]. Execute The Query:

select * from employee

[b]. Narrow down the columns you want:

“TOAD Tips and Tricks SQL Editor” copyright © 1999 by Quest Software, Inc. www.toadsoft.com and www.quest.com 6
[c]. Save the result set to the Windows Clipboard:

“TOAD Tips and Tricks SQL Editor” copyright © 1999 by Quest Software, Inc. www.toadsoft.com and www.quest.com 7
[d]. Include the column names in the clipboard copy:

[e]. Paste the results into Excel and continue processing or printing the data:

“TOAD Tips and Tricks SQL Editor” copyright © 1999 by Quest Software, Inc. www.toadsoft.com and www.quest.com 8
Can you list all of the ways to get code into the SQL editor?

(1). Start with a blank window, type in some SQL code, execute it, refine it, and save it to disk.

(2). Open a SQL script file into the editor by clicking the “Load a file into the editor” button on the SQL Editor toolbar.

The small black triangle to the right of the button is a dropdown list that will recall the previous 10 files you have opened while using
this particular SQL Edit window. Once the window is closed the list is reset.

(3). Recall previous SQL by selecting the “SQL-Window|SQL Command Recall” menu item, or by pressing <F8>. This presents the
“SQL Statement Recall” window and lists the past 100 queries you previously executed in the SQL Edit window. Select a query from
the list and it will be copied to the SQL editor. There is also a SQL Editor toolbar button for the SQL Command Recall function.

On the TOAD Options dialog (see “View|Options” menu item), SQL Editor node, you can elect to save only those SQL commands
that executed without errors by checking the “Save only statements that are valid” checkbox. This is a useful filter because otherwise
the recall list will be filled with queries until you get it right.

select foo from dummy (invalid)

select items from dept (invalid)
select item from dept (invalid)
select item from department (valid)

There is also an option for you to enter the number of previous SQL statements you want to save and recall. The default is 100. The
list of previous SQL statements is stored to and retrieved from SQLS.DAT.

(4). Recall a Personal SQL that you created previously. Enter a query, select menu item “SQL-Window|Add to Personal SQLs”.
Then select menu item “SQL-Window|Recall Personal SQL” which presents the “SQL Statement Recall” dialog similar as above.
The list of personal SQL statements is stored to and retrieved from PERSSQLS.DAT.

There is also a SQL Editor toolbar button for the Recall Personal SQL function.

(5). Recall a Named SQL statement that you created previously. Enter a query, select menu item “SQL-Window|Add to Named
SQLs”, enter a name, e.g., “My Favorite Query”. Then select menu item “SQL-Window|Recall Named SQL” (or press <CTRL>N)
and select the query to return to the SQL editor. This presents the “SQL Statement Recall” window similar to above, but the queries
are organized by the names that you select.

(6). Recall a Code Completion template that you set up previously. Edit TOAD.DCI in the TOAD\Temps folder and enter the shortcut
keys for the code snippet, the complete name, and the code, in the same format as the samples already in the TOAD.DCI file. In the
SQL editor, type in the shortcut keys, e.g., “crbl” press <CTRL><SPACE>, and TOAD will look up the code completion template and
plug it into the SQL editor.

“TOAD Tips and Tricks SQL Editor” copyright © 1999 by Quest Software, Inc. www.toadsoft.com and www.quest.com 9