This chapter is from the book

This chapter is from the book

The SQL Editor is the original development area of TOAD. This window enables
you to type, save, run, and tune SQL statements. In addition, you will learn
how to use TOAD to create and execute SQL scripts, save the output, and examine
the explain plan. This chapter will discuss and illustrate every option available
in the SQL Editor.

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.

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.

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.

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).

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.

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.

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.