TOAD SQL Editor | Overview (2023)

Learn how to use TOAD to create and execute SQL scripts, save the output, and examine the explain plan by looking at every option available in the SQL Editor.

This chapter is from the book 

This chapter is from the book

TOAD Handbook

Learn More Buy

This chapter is from the book

This chapter is from the book 

TOAD Handbook

Learn More Buy

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.

(Video) Getting started with the editor in Toad for SQL Server

Figure 3.1TOAD SQL Editor window.


This illustration shows the default SQL Editor. Notice the various buttons atthe 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 threeTOAD toolbars. Hover the mouse over a button and a balloon will appear with adescription 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 toolbarfor about every shortcut. The savvy TOAD user makes extensive use of theshortcuts.

F1 brings up the TOAD help facility.

Figure 3.2 illustrates the toolbars.

Figure 3.2TOAD SQL Editor window toolbars.


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

TOAD SQL Editor | Overview (3) Open a New SQL Window

TOAD SQL Editor | Overview (4) Open a New Schema Browser Window

TOAD SQL Editor | Overview (5) Open a New Procedure Edit Window

TOAD SQL Editor | Overview (6) Open a New SQL Modeler Window

TOAD SQL Editor | Overview (7) Explain Plan Window

TOAD SQL Editor | Overview (8) Open a New DBMS Output Window

TOAD SQL Editor | Overview (9) Find Object

TOAD SQL Editor | Overview (10) Save All Options

TOAD SQL Editor | Overview (11) Reports

TOAD SQL Editor | Overview (12) Open a New Text Editor Window

TOAD SQL Editor | Overview (13) Toggle PL/SQL Profiling

TOAD SQL Editor | Overview (14) Toggle Compiling with Debug

TOAD SQL Editor | Overview (15) Configure TOAD Options

TOAD SQL Editor | Overview (16) Execute a Knowledge Xpert Module or Formatter Plus

(Video) How to use the SQL editor tool in Toad Data Point

TOAD SQL Editor | Overview (17) Script Manager

TOAD SQL Editor | Overview (18) Configure/Execute External Tools

TOAD SQL Editor | Overview (19) Commit

TOAD SQL Editor | Overview (20) Rollback

TOAD SQL Editor | Overview (21) Show Windows by Connection

TOAD SQL Editor | Overview (22) Open a New Oracle Connection

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

TOAD SQL Editor | Overview (23) Execute Statement

TOAD SQL Editor | Overview (24) Execute Current Statement

TOAD SQL Editor | Overview (25) Execute as a Script

TOAD SQL Editor | Overview (26) Recall a Previous Statement

TOAD SQL Editor | Overview (27) Recall a Personal Statement

TOAD SQL Editor | Overview (28) Insert a Row

TOAD SQL Editor | Overview (29) Delete Current Row

TOAD SQL Editor | Overview (30) Post Data Changes

TOAD SQL Editor | Overview (31) Revert Data Changes

TOAD SQL Editor | Overview (32) Load a File into the Editor

TOAD SQL Editor | Overview (33) Save Editor to File

TOAD SQL Editor | Overview (34) Save Edits to File

TOAD SQL Editor | Overview (35) Create a Code Statement

TOAD SQL Editor | Overview (36) Strip All Non-SQL Syntax

(Video) Getting started With Toad for Oracle - in-depth tutorial

TOAD SQL Editor | Overview (37) Run Explain Plan for Current Statement

TOAD SQL Editor | Overview (38) Tune the Current Statement using SQLab Xpert tuner

TOAD SQL Editor | Overview (39) Change Session for this Window

TOAD SQL Editor | Overview (40) Cancel

The third and final toolbar contains shortcuts for the standard Windowsactions like cut and paste, clear, and so on. This toolbar also enables you toget information on specific objects as well. The third toolbar contains thefollowing icons:

TOAD SQL Editor | Overview (41) Cut

TOAD SQL Editor | Overview (42) Copy

TOAD SQL Editor | Overview (43) Paste

TOAD SQL Editor | Overview (44) Select All

TOAD SQL Editor | Overview (45) Clear All

TOAD SQL Editor | Overview (46) Find Text

TOAD SQL Editor | Overview (47) Find Next

TOAD SQL Editor | Overview (48) Replace Text

TOAD SQL Editor | Overview (49) Undo Edit

TOAD SQL Editor | Overview (50) Redo Last Undo

TOAD SQL Editor | Overview (51) Convert to Uppercase

TOAD SQL Editor | Overview (52) Convert to Lowercase

TOAD SQL Editor | Overview (53) Convert to Init Cap

TOAD SQL Editor | Overview (54) Indent Text

TOAD SQL Editor | Overview (55) Unindent Text

TOAD SQL Editor | Overview (56) Print Text

(Video) Overview of the user interface in Toad for SQL Server

TOAD SQL Editor | Overview (57) Show Table Select Window

TOAD SQL Editor | Overview (58) Show Column Select Window

TOAD SQL Editor | Overview (59) 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.

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

Notice that the SQL syntax appears (along with any other Oracle reservedwords) in blue where the supplied columns, table names, and other variablesyntax appear in black. Comments appear in green, and so on. These colorpatterns are controlled by the Editor Options. You can access these options byclicking Edit, Editor Options from the menu bar or by right-clicking andselecting Editing Options. Notice that TOAD lists the keyboard shortcutswhenever 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 theyare running. If you want this behavior, make sure you check the box ProcessStatements in Threads in the SQL Editor part of the TOAD Options screen. TheCancel button (far right button on the middle SQL Editor toolbar) will becomeavailable during the execution of a SQL statement being run in this fashion. Inthis same area, you can also increase or decrease the SQL statements TOAD willautomatically track. These SQL statements are stored in the file SQLS.DAT inyour TOAD home directory. You have control over default behavior such as whetheryou are prompted to save the current SQL (Prompt to Save Contents), code formatoptions, 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.

(Video) Editor Tricks and Tools in Toad for Developers

Figure 3.8Accessing SQL Formatter.


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

Figure 3.9TOAD formatted SQL.


Videos

1. How to send data to the SQL editor in Toad Data Point
(Quest)
2. Introduction to Toad for Oracle for Developers - in-depth tutorial
(Quest)
3. Basic code formatting in Toad for SQL Server
(Quest)
4. Short Tutorial - Toad Software Overview
(HandsonERP)
5. Create a simple PL/SQL unit test using Toad
(Quest)
6. Automation basics in Toad for SQL Server (Part 1)
(Quest)
Top Articles
Latest Posts
Article information

Author: Arline Emard IV

Last Updated: 11/07/2023

Views: 5790

Rating: 4.1 / 5 (52 voted)

Reviews: 91% of readers found this page helpful

Author information

Name: Arline Emard IV

Birthday: 1996-07-10

Address: 8912 Hintz Shore, West Louie, AZ 69363-0747

Phone: +13454700762376

Job: Administration Technician

Hobby: Paintball, Horseback riding, Cycling, Running, Macrame, Playing musical instruments, Soapmaking

Introduction: My name is Arline Emard IV, I am a cheerful, gorgeous, colorful, joyous, excited, super, inquisitive person who loves writing and wants to share my knowledge and understanding with you.