Toad for Oracle Unleashed: Working with SQL
Dell Software has a single editor now for all types of code, including SQL and PL/SQL. This editor is the main interface to Toad and enables the execution of SQL and SQL scripts, viewing data, saving data into various formats, and more. The SQL is saved in a history. Toad saves on keystrokes in a variety of ways; you can develop working SQL with a minimal amount of key entry. Toad also makes it easy for you to rerun recent work (using the Rerun menu button; see Figure 3.1).
FIGURE 3.1 Toad SQL Editor panel
The SQL Editor is the main interface to Toad. Simply type in a SQL statement and click the green triangle execute button . You can also press F9. This sends the SQL statement that the cursor is on (or the highlighted SQL) to the Oracle database. If the query is successful, then the data or success of the statement will be returned and displayed.
The Editor button makes opening additional Editor windows possible. This version of Toad enables you to open additional tabs in the Editor window. Simply right-click the tab just above the selected statement and select SQL or PL/SQL tab type. This opens another Editor window using the same database connection. Use the Editor button in the top-left row of buttons to open another Editor window, perhaps using a different connect string.
The top toolbar (refer to Figure 3.1), called the standard toolbar, provides access to various Toad browsers, features, commit/rollback, and initiating additional connections. Right-click on any of these top toolbars and you can select the ones you wish to have visible. By closing the ones you don’t use, Toad can perhaps have a larger Editor window. The window toolbar shows the active Toad windows. There are also toolbars for team coding, desktops, connection, jump search, and workspaces. You can change the color border per your needs. Dan uses red borders for SYS and SYSTEM accounts, and then a different color for each different Oracle database to which he connects. The next toolbar enables you to open code from the file system or from the database and also offers access to other features. You can right-click on these toolbars to show or hide the ones you don’t work with. The bottom toolbar has the Execute, Execute as Script, Clear, and Halt buttons. The Execute button will run all code in the Editor window. You can also just put the cursor on a SQL statement (if there are multiple SQL statements in the Editor window) and click this button. Toad will just execute the one SQL statement. Some people highlight the SQL they wish to execute. After execution, the Data Grid output tab will be populated. This technique works fine as well. The Execute as a script button runs the contents of the Editor window as if it were using a SQL*Plus character-mode interface. There is a separate Script Output tab for the result of this type of execution.
The Halt button stops the current SQL execution. It will un-gray as SQL is executing in Toad. This feature is also dependent upon Toad Options → Oracle → Transactions Run in Threads selected.
The Clear button (see Figure 3.2 at the cursor position) clears the contents of the SQL Editor window. One of the authors likes to simply open a new tab (right-click the tab, and select New Tab) and put each individual unit of work in its own tab. Use the + next to the existing open tab to open others. These tabs are associated with a single connection to the Oracle database. This approach makes it easy to rerun prior SQL statements, particularly when testing. This technique is also useful for PL/SQL code such as packages. Putting the package specifications in one tab and the package body in another tab keeps the two separate. The package body won’t compile correctly if any issue exists with the specifications, as shown in Figure 3.3. Dan likes to work with package specifications and the body in separate tabs for this reason. This is a good technique to follow. Toad automatically puts the package spec and body in separate tabs when opening a package either from a file or from the database. This feature is controlled by Toad Options → Editor → Open/Save Packages/Types settings. You can choose this behavior or turn this feature off.
FIGURE 3.2 Clear button
FIGURE 3.3 Toad with separate tabs for the package specification and body
The toolbars are easy to configure. Right-click one to see the pop-up menu shown in Figure 3.4.
FIGURE 3.4 Configuring the toolbars
Notice the menu enables you to turn on and off the various toolbars. You can also configure the individual toolbars from here. You can delete unused buttons you find cluttering the toolbars, move buttons around, and add additional buttons. Figure 3.5 shows the toolbar configuration menu. The Toolbars tab enables you to turn toolbars on or off. The Commands tab enables you to add or delete buttons from each particular area of Toad. Drag and drop to move the buttons, remove the buttons, or add additional buttons. The Options tab enables you to turn on or off the mouse hover tips, change the size of the icons, and adjust how Toad stores your executed commands.
FIGURE 3.5 Configuring the toolbar buttons
Notice in Figure 3.4 that you can easily reset these options back to the Toad defaults.