Overview

TOAD provides a number of features that make SQL development easy:

• Keyboard shortcuts

• Table and column select lists

• SQL templates

• Options for creating and executing SQL scripts

• Options for reviewing, editing, and saving result-set data

• Compatibility with SQL*Plus

The Editor window is the basis of the entire TOAD tool, giving you the ability to create and edit SQL: both individual SQL statements (possibly to be inserted into applications) and scripts that contain multiple SQL statements. Figure 3.1 shows the basic SQL Editor window.

Figure 3.1. TOAD SQL Editor window.

This illustration shows the default SQL Editor. Notice the various buttons at the top that perform about any function (including executing the current SQL, saving the current SQL, and so on). There are three rows of buttons , or three TOAD toolbars . Hover the mouse over a button and a balloon will appear with a description of its use.

A shortcut is a keystroke or keystrokes that perform a certain function. F1, for example, brings up the TOAD help facility. There is a button on the toolbar for about every shortcut. The savvy TOAD user makes extensive use of the shortcuts.

F1 brings up the TOAD help facility.

Figure 3.2 illustrates the toolbars.

Figure 3.2. TOAD SQL Editor window toolbars.

The first toolbar provides easy access to the main TOAD browsers and editors as well as the save functions. Some additional TOAD features also appear on this toolbar. The first toolbar (left to right) contains the following icons:

Open a New SQL Window

Open a New Schema Browser Window

Open a New Procedure Edit Window

Open a New SQL Modeler Window

Explain Plan Window

Open a New DBMS Output Window

Find Object

Save All Options

Reports

Open a New Text Editor Window

Toggle PL/SQL Profiling

Toggle Compiling with Debug

Configure TOAD Options

Execute a Knowledge Xpert Module or Formatter Plus

Script Manager

Configure/Execute External Tools

Commit

Rollback

Show Windows by Connection

Open a New Oracle Connection

The second toolbar focuses on execution. This toolbar enables you to execute code and scripts, and allows code to be loaded into the environment by a number of methods . The second (middle) toolbar contains the following icons:

Execute Statement

Execute Current Statement

Execute as a Script

Recall a Previous Statement

Recall a Personal Statement

Insert a Row

Delete Current Row

Post Data Changes

Revert Data Changes

Load a File into the Editor

Save Editor to File

Save Edits to File

Create a Code Statement

Strip All Non-SQL Syntax

Run Explain Plan for Current Statement

Tune the Current Statement using SQLab Xpert tuner

Change Session for this Window

Cancel

The third and final toolbar contains shortcuts for the standard Windows actions like cut and paste, clear, and so on. This toolbar also enables you to get information on specific objects as well. The third toolbar contains the following icons:

Cut

Copy

Paste

Select All

Clear All

Find Text

Find Next

Replace Text

Undo Edit

Redo Last Undo

Convert to Uppercase

Convert to Lowercase

Convert to Init Cap

Indent Text

Unindent Text

Print Text

Show Table Select Window

Show Column Select Window

Show SQL Template Window

The first shortcut is F2. This toggles the bottom output window, or a better description might be: toggles the SQL Editor window to full screen. Shift+F2 toggles the grid output (on the bottom) to full screen. Figure 3.3 shows the SQL Editor with the output toggled off, or the full-screen grid. This is helpful when working on longer SQL statements or SQL scripts. You can easily toggle on the output tabs when you want to see the output.

Figure 3.3. TOAD SQL Editor window, full-screen grid.

F2 toggles on/off the full-screen editor.

Shift+F2 toggles on/off the full-screen data grid.

The lower section, or data grid, contains the result-set data from the query, the explain plan used to retrieve the data, code statistics, Auto Trace output, DBMS output, and Script output. Each of these will be covered in this chapter.

Notice that the SQL syntax appears (along with any other Oracle reserved words) in blue where the supplied columns , table names , and other variable syntax appear in black. Comments appear in green, and so on. These color patterns are controlled by the Editor Options. You can access these options by clicking Edit, Editor Options from the menu bar or by right-clicking and selecting Editing Options. Notice that TOAD lists the keyboard shortcuts whenever possible.

Figure 3.4 illustrates the Highlighting options in the SQL window. You can see that you have complete control over the editor environment (such as autoreplacement of words, general layout and text wrapping in the edit window, key assignments, and code templates).

Figure 3.4. TOAD SQL Editor Options.

TOAD has three editors: the SQL Editor, the Procedure Editor (covered in Chapter 4), and a text editor of your choice. The editor environment applies its options to both TOAD editors. Additional editors can easily be added to TOAD. Choose View, Options from the menu bar, and then select Editors (or use the Configure TOAD Options button) to add your editor of choice. Figure 3.5 illustrates how to add the Notepad editor, for example. Be sure to use the variable %s to pass this editor the SQL that you are currently working on. If your current session has not been saved, you will be prompted to save it. Also, upon exiting your external editor, you will be prompted to reload your work from the saved file. Make sure the option Reload Files When Activating TOAD is checked on in the Procedure Editor section of the TOAD Options screen. You then use this external editor by choosing Edit, Load in External Editor from the menu bar or by using the shortcut Ctrl+F12. Figure 3.6 shows some work in the Notepad editor.

Figure 3.5. Defining external editors in TOAD.

Figure 3.6. Using Notepad as editor in TOAD.

Ctrl+F12 accesses a previously defined external editor.

TOAD supports threads, which allows SQL statements to be canceled while they are running. If you want this behavior, make sure you check the box Process Statements in Threads in the SQL Editor part of the TOAD Options screen. The Cancel button (far right button on the middle SQL Editor toolbar) will become available during the execution of a SQL statement being run in this fashion. In this same area, you can also increase or decrease the SQL statements TOAD will automatically track. These SQL statements are stored in the file SQLS.DAT in your TOAD home directory. You have control over default behavior such as whether you are prompted to save the current SQL (Prompt to Save Contents), code format options, showing execution time, and so on.

There are several ways to get SQL into the SQL Editor. You can simply type in a new SQL statement. You can use the SQL Statement Recall button (fourth button on the middle SQL Editor toolbar) and select a SQL statement from the stored TOAD SQL history (see Figure 3.7). Pressing Alt+Up arrow and Alt+Down arrow also walks you thru the SQL statement history. You can also choose File, Open from the menu bar (or Ctrl+O), and cut and paste SQL code into the Editor from other applications. The Load option is also useful for loading in SQL from files from the pop-up menu that appears when you right-click.

Figure 3.7. Selecting SQL from TOAD history.

Alt+Up arrow gets the previous SQL statement from the TOAD history.

Alt+Down arrow gets the next SQL statement from the TOAD history.

TOAD will also easily format your SQL into an easy-to-read format. Figure 3.8 shows how to access the formatter by right-clicking and selecting Formatting Tools, Format Code from the context menu. Figure 3.9 shows how TOAD formats the SQL.

Figure 3.8. Accessing SQL Formatter.

Figure 3.9. TOAD formatted SQL.

This overview covered some of the basic concepts and features of the SQL Editor. The remainder of this chapter will cover specific topics in the SQL Editor.