Building SQL and PL/SQL Using TOAD

TOAD enables you to create and modify SQL and PL/SQL. This code can be entered in a blank SQL Edit window, load SQL in from a file (menu bar File, Open File, see Figure 16.8), recall a previously edited SQL statement, or be recalled from a previously created work session. A SQL edit window can also be launched from the object browser, the schema browser, or the SGA trace window.

Working with SQL

TOAD allows you to view available columns while working on a SQL query.

1.	After you type the table name (or view name) and the period, press CTRL+T or wait a few seconds.
2.	A list of columns displays, see Figure 16.9. Figure 16.9. Column Display/Column Insert while working on SQL.
3.	Click the column you want to select. (Hold the CTRL key to select multiple columns.)
4.	Press Enter and TOAD places the selected column(s) into the SQL Editor in your query.

After a query populates the SQL Results Grid, you can press CTRL+T to display a list of the columns from the SQL Results Grid.

The View, Options displays a series of tabs that control virtually every aspect of TOAD. The SQL Editor tab contains numerous options for the SQL Editor; see Figure 16.10.

Working with PL/SQL

The Procedure Editor lets you create or modify procedures, functions, packages, triggers, types, and type bodies. If you have the optional PL/SQL Debugger, the debugger buttons will display enabled to the right of the Procedure Editor toolbar.

The Procedure Edit window has two panels. The left panel contains the Package Navigator List, which is a list of objects or package contents. The right panel contains the Procedure Editor, which shows the code for the selected object.

Packages are composed of a SPEC and a BODY. Functions in the navigator list are indicated with an F( ) and procedures are indicated with a P( ). A sort button lets you sort the navigator list in the source code order or in an alphabetized Spec and Body list. You can synchronize the navigator list to your new code edits. Each object being edited will have its own tab in the right window.

With TOAD you can create stored procedures (such as functions and procedures), test them, grant privileges on them, and create public synonyms to them.

You run a selected procedure by clicking the Execute button in the Schema Browser, or right-click the “Execute without debugging” menu item in the Procedure Editor.

There are two main ways to get code into the Procedure Editor: read from a file or load from the database (as illustrated in Figure 16.12).

To Load Source from an existing object:

1.	Click the Load Source from an existing object button.
2.	The Select Database Object window will display, see Figure 16.12.
3.	Select the desired schema from the schema drop-down list.
4.	You can filter using the object types drop-down list, which defaults to All.
5.	You can further filter by clicking the filter button (funnel icon next to object types), which shows/hides widgets that let you set up a starts with, includes, or ends with filter.
6.	To preview the source for an object, select the object from the object list and click the right arrow. (You can double-click an object name to load it directly into the Procedure Edit window.)
7.	The selected object's SQL script is displayed in the right panel of the Select Database Object window.
8.	Click OK. The code is opened in the Procedure Editor, and the Select Database Object window closes automatically.

Creating a New Procedure

The Create a New Procedure button invokes the New Procedure Create Options window (see Figure 16.13). You select the Object Type from the drop-down list and enter the New Object Name. You also select the Object Template from a drop-down list. If the object type is a trigger, the trigger options panel is enabled. A template script (appropriate to the object type you selected) with your object name is pulled into the editor. TOAD automatically substitutes values for keywords in the template such as object name, user name, and time.