TOAD SQL Editor
- Overview
- Predefined Shortcuts
- User-Defined Shortcuts
- Using Variables
- Table and Column Name Select Lists
- Using Aliases and Autoreplacement Substitutions
- SQL Templates
- Code Completion Templates
- Executing SQL Statements
- Executing SQL Scripts
- Editing Result-Set Data
- Saving Result-Set Data
- Printing Result-Set Data
- Examining Explain Plans
- Examining Basic Performance Info
- Using Auto Trace
- SQL*Plus Compatibility
- Scripts That Write Scripts
- Summary
This chapter is from the book
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.
)
Figure
3.1TOAD 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.
){kind=small}
Figure
3.2TOAD 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.
F2 toggles on/off the full-screen editor.
Shift+F2 toggles on/off the full-screen data grid.
){kind=small}
Figure
3.3TOAD SQL Editor window, full-screen 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.4TOAD 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.5Defining external editors in TOAD.
)
Figure
3.6Using 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.7Selecting 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.8Accessing SQL Formatter.
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.
){kind=small}
Figure
3.9TOAD formatted SQL.