Transcription
04 4865 ch031/31/032:17 PMPage 413TOAD SQL EditorThe SQL Editor is the original development area ofTOAD. This window enables you to type, save, run, andtune SQL statements. In addition, you will learn how touse TOAD to create and execute SQL scripts, save theoutput, and examine the explain plan. This chapter willdiscuss and illustrate every option available in the SQLEditor.OverviewTOAD 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-setdata Compatibility with SQL*PlusThe 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.
04 4865 ch03421/31/032:17 PMCHAPTER 3FIGURE 3.1Page 42TOAD SQL EditorTOAD SQL Editor window.This illustration shows the default SQL Editor. Notice the various buttons at the topthat perform about any function (including executing the current SQL, saving thecurrent 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 itsuse.A shortcut is a keystroke or keystrokes that perform a certain function. F1, forexample, brings up the TOAD help facility. There is a button on the toolbar forabout 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.FIGURE 3.2TOAD SQL Editor window toolbars.The first toolbar provides easy access to the main TOAD browsers and editors as wellas the save functions. Some additional TOAD features also appear on this toolbar.The first toolbar (left to right) contains the following icons:
04 4865 ch031/31/032:17 PMPage 43OverviewOpen a New SQL WindowOpen a New Schema Browser WindowOpen a New Procedure Edit WindowOpen a New SQL Modeler WindowExplain Plan WindowOpen a New DBMS Output WindowFind ObjectSave All OptionsReportsOpen a New Text Editor WindowToggle PL/SQL ProfilingToggle Compiling with DebugConfigure TOAD OptionsExecute a Knowledge Xpert Module or Formatter PlusScript ManagerConfigure/Execute External ToolsCommitRollbackShow Windows by ConnectionOpen a New Oracle ConnectionThe second toolbar focuses on execution. This toolbar enables you to execute codeand scripts, and allows code to be loaded into the environment by a number ofmethods. The second (middle) toolbar contains the following icons:Execute StatementExecute Current Statement43
04 4865 ch03441/31/032:17 PMCHAPTER 3Page 44TOAD SQL EditorExecute as a ScriptRecall a Previous StatementRecall a Personal StatementInsert a RowDelete Current RowPost Data ChangesRevert Data ChangesLoad a File into the EditorSave Editor to FileSave Edits to FileCreate a Code StatementStrip All Non-SQL SyntaxRun Explain Plan for Current StatementTune the Current Statement using SQLab Xpert tunerChange Session for this WindowCancelThe third and final toolbar contains shortcuts for the standard Windows actions likecut and paste, clear, and so on. This toolbar also enables you to get information onspecific objects as well. The third toolbar contains the following icons:CutCopyPasteSelect AllClear AllFind TextFind Next
04 4865 ch031/31/032:17 PMPage 45OverviewReplace TextUndo EditRedo Last UndoConvert to UppercaseConvert to LowercaseConvert to Init CapIndent TextUnindent TextPrint TextShow Table Select WindowShow Column Select WindowShow SQL Template WindowThe 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 gridoutput (on the bottom) to full screen. Figure 3.3 shows the SQL Editor with theoutput toggled off, or the full-screen grid. This is helpful when working on longerSQL statements or SQL scripts. You can easily toggle on the output tabs when youwant 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.45
04 4865 ch03461/31/032:17 PMCHAPTER 3Page 46TOAD SQL EditorThe lower section, or data grid, contains the result-set data from the query, theexplain plan used to retrieve the data, code statistics, Auto Trace output, DBMSoutput, 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) inblue where the supplied columns, table names, and other variable syntax appear inblack. Comments appear in green, and so on. These color patterns are controlled bythe Editor Options. You can access these options by clicking Edit, Editor Optionsfrom the menu bar or by right-clicking and selecting Editing Options. Notice thatTOAD lists the keyboard shortcuts whenever possible.Figure 3.4 illustrates the Highlighting options in the SQL window. You can see thatyou have complete control over the editor environment (such as autoreplacement ofwords, general layout and text wrapping in the edit window, key assignments, andcode templates).TOAD has three editors: theSQL Editor, the ProcedureEditor (covered in Chapter 4),and a text editor of yourchoice. The editor environment applies its options toboth TOAD editors.Additional editors can easilybe added to TOAD. ChooseView, Options from themenu bar, and then selectEditors (or use the ConfigureTOAD Options button) toadd your editor of choice.Figure 3.5 illustrates how toFIGURE 3.4 TOAD SQL Editor Options.add the Notepad editor, forexample. Be sure to use thevariable %s to pass this editorthe SQL that you are currently working on. If your current session has not beensaved, you will be prompted to save it. Also, upon exiting your external editor, youwill be prompted to reload your work from the saved file. Make sure the optionReload Files When Activating TOAD is checked on in the Procedure Editor section ofthe TOAD Options screen. You then use this external editor by choosing Edit, Loadin External Editor from the menu bar or by using the shortcut Ctrl F12. Figure 3.6shows some work in the Notepad editor.Ctrl F12 accesses a previously defined external editor.
04 4865 ch031/31/032:17 PMPage 47OverviewTOAD supports threads,which allows SQL statements to be canceled whilethey are running. If youwant this behavior, makesure you check the boxProcess Statements inThreads in the SQL Editorpart of the TOAD Optionsscreen. The Cancel button(far right button on themiddle SQL Editor toolbar)will become availableduring the execution of aSQL statement being run inthis fashion. In this sameFIGURE 3.5area, you can also increaseor decrease the SQL statements TOAD will automatically track. These SQLstatements are stored in thefile SQLS.DAT in your TOADhome directory. You havecontrol over default behaviorsuch as whether you areprompted to save the currentSQL (Prompt to SaveContents), code formatoptions, showing executiontime, and so on.Defining external editors in TOAD.There are several ways to getSQL into the SQL Editor. YouFIGURE 3.6 Using Notepad as editor in TOAD.can simply type in a new SQLstatement. You can use theSQL Statement Recall button (fourth button on the middle SQL Editor toolbar) andselect a SQL statement from the stored TOAD SQL history (see Figure 3.7). PressingAlt 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 pasteSQL code into the Editor from other applications. The Load option is also useful forloading in SQL from files from the pop-up menu that appears when you right-click.Alt Up arrow gets the previous SQL statement from the TOAD history.47
04 4865 ch03481/31/032:17 PMCHAPTER 3Page 48TOAD SQL EditorAlt Down arrow gets the next SQL statement from the TOAD history.TOAD will also easily formatyour SQL into an easy-to-readformat. Figure 3.8 shows howto access the formatter byright-clicking and selectingFormatting Tools, Format Codefrom the context menu. Figure3.9 shows how TOAD formatsthe SQL.This overview covered some ofthe basic concepts and featuresof the SQL Editor. The remainder of this chapter will coverspecific topics in the SQLEditor.FIGURE 3.8FIGURE 3.7Accessing SQL Formatter.Selecting SQL from TOAD history.
04 4865 ch031/31/032:17 PMPage 49Predefined ShortcutsFIGURE 3.9TOAD formatted SQL.Predefined ShortcutsKeyboard shortcuts are one of the features that make TOAD so powerful and easy touse. TOAD comes with a host of predefined shortcuts. These shortcuts savekeystrokes and mouse actions and perform a variety of tasks such as issuing adescribe on the current highlighted object, or find (find next, find previous, and soon). The shortcuts differ slightly between the SQL Editor and the PL/SQL Editor(covered in Chapter 4). This section focuses on the shortcuts for the SQL Editor only.Table 3.1 shows all the shortcuts in keystroke order and Table 3.2 shows all theshortcuts in description order. The third column refers you to the figure in thischapter where the shortcut is illustrated.TABLE 3.1SQL Editor Shortcuts by KeystrokeShortcutDescriptionF1F2Shift F2F3Shift F3F4F5F6F7F8F9Ctrl F9Shift F9F10 or right-clickWindows Help FileToggle Output WindowToggle Data Grid WindowFind Next OccurrenceFind Previous OccurrenceDescribe Table, View, Procedure, FunctionExecute SQL as a scriptToggle between SQL Editor and Results PanelClear All TextRecall Previous SQL StatementExecute StatementSet Code Execution ParametersExecute Current SQL statement at CursorPop-up MenuIllustrationFigure 3.2Figure 3.26Figure 3.34Figure 3.1Figure 3.33Figure 3.33Figures 3.4, 3.1049
04 4865 ch03501/31/032:17 PMPage 50CHAPTER 3TOAD SQL EditorTABLE 3.1ContinuedShortcutDescriptionIllustrationCtrl F12Ctrl ACtrl CCtrl ECtrl FCtrl GCtrl LCtrl MCtrl NCtrl OCtrl PCtrl RCtrl SShift Ctrl SCtrl TShift Ctrl RShift Ctrl TCtrl SpacebarCtrl UCtrl VCtrl XCtrl ZCtrl .Shift Ctrl ZAlt Up ArrowAlt Down ArrowCtrl HomeCtrl EndCtrl TabExternal Editor, Pass ContentsSelect All TextCopyExecute Explain Plan on the Current StatementFind TextGoto LineConvert Text to LowercaseMake Code StatementRecall Named SQL StatementOpen a Text FileStrip Code StatementFind and ReplaceSave FileSave File AsColumns Drop-downAlias ReplacementColumns Drop-Down no aliasCode TemplatesConverts Text to UppercasePasteCutUndo Last ChangeDisplay Pop-up List of Matching Table NamesRedo Last UndoDisplay Previous StatementDisplay Next Statement (After Alt Up Arrow)In the data grid: goes to the top of the record setIn the data grid: goes to the end of the record setCycles through the Collection of MDI Child WindowsFigures 3.5, 3.6Figure 3.41Figure 3.18Figure 3.19Figure 3.30Figure 3.16Figure 3.7Figure 3.7The competent TOAD user makes use of Shift F9 to execute SQL statements one at a timeout of a script, and F8 to recall the previous SQL statement. The TOAD user also makes useof cut and paste to move code between TOAD windows.Shift F9 executes single SQL statements.F8 recalls the previous SQL statement.
04 4865 ch031/31/032:17 PMPage 51Predefined ShortcutsTABLE 3.2SQL Editor Shortcuts by DescriptionDescriptionShortcutAlias ReplacementClear All TextCode TemplatesColumns Drop-downColumns Drop-down no aliasConvert Text to LowercaseConvert Text to UppercaseCopyCutCycles through the Collection of MDI Child WindowsDescribe Table, View, Procedure, Function, or PackageDisplay Next Statement (After Alt Up Arrow)Display Previous StatementDisplay Pop-up List of Matching Table NamesExecute Current SQL Statement at CursorExecute SQL as a ScriptExecute Explain Plan on the Current SQL StatementExecute SQL StatementExternal Editor, Pass ContentsFind and ReplaceFind Next OccurrenceFind Previous OccurrenceFind TextGoto LineIn the data grid: goes to the end of the record setIn the data grid: goes to the top of the record setMake Code StatementOpen a Text FilePastePop-up MenuRecall Named SQLRecall Previous SQL StatementRedo Last UndoSave FileSave File AsSelect All TextStrip Code StatementToggle between SQL Editor and Results PanelToggle Full Screen EditorUndo Last ChangeVerify Statement Without Execution (Parse)Windows Help FileShift Ctrl RF7Ctrl SpacebarCtrl TShift Ctrl TCtrl LCtrl UCtrl CCtrl XCtrl TabF4Alt Down ArrowAlt Up ArrowCtrl .Shift F9F5Ctrl EF9Ctrl F12Ctrl RF3Shift F3Ctrl FCtrl GCtrl EndCtrl HomeCtrl MCtrl OCtrl VF10 or RT-MouseCtrl NF8Shift Ctrl ZCtrl SShift Ctrl SCtrl ACtrl PF6F2Ctrl ZCtrl F9F1IllustrationFigure 3.30Figure 3.18Figure 3.19Figure 3.7Figure 3.7Figure 3.16FigureFigureFigureFigure3.353.413.343.5Figures 3.4, 3.10Figure 3.1Figure 3.251
04 4865 ch03521/31/032:17 PMCHAPTER 3Page 52TOAD SQL EditorUser-Defined ShortcutsTOAD is completely configurable. You can easily add your own shortcuts to TOAD. Itis easy to change the shortcut keystrokes for existing shortcuts, and it is just as easyto add your own shortcuts.Access the Editor Options menu with a right-click or by pressing the F10 key andselecting Editing Options.To change an existingkeystroke assignment,select KeyAssignments, locatethe particular assignment to change, andclick on the EditSequence button asillustrated inFigure 3.10.To add additionalkeystroke assignments, select KeyAssignments, locatethe particular assignFIGURE 3.10ment to change, andclick on the Add NewSequence button. Type thesequence desired to performthe key assignment task. InFigure 3.11, notice thatShift Ctrl D was added bypressing and holding downthe Control key while pressing Shift and d.Changing shortcut keystroke assignments inTOAD.FIGURE 3.11Adding shortcut keystroke assignmentsin TOAD.
04 4865 ch031/31/032:17 PMPage 53Using VariablesUsing VariablesTOAD supports all kinds of SQL, from all kinds of applications. If you were to bringin SQL, for example, from a SQL*Forms application, it will contain bind variables.Bind variables are used to supply SQL with data at execution time. This allows applications to use the same SQL statement to select and manipulate different data,depending on the data supplied to the bind variables.Using the same SQL statement makes efficient use of the Oracle RDBMS SQL pool as theSQL will not be reparsed when using bind variables. The text of the SQL remains the same,so Oracle will reuse the same execution plan, making for a better-performing databaseenvironment.When TOAD encounters bind variables, it will prompt you for their value as inFigure 3.12. This illustration shows two bind variables. The Scan SQL button willcheck for any missing bind variables, which is particularly useful if you are addingand changing bind variables in this interface.Substitutionvariables workthe same wayas bind variables. Doremember thatsubstitutionvariables areresolved intoSQL text atparse time,but bind variables won’tFIGURE 3.12 Resolving SQL bind variables.change theactual SQLtext (thisgreatly enables SQL reuse in the Oracle SQL pool). TOAD will prompt for the data foreach substitution variable as shown in Figure 3.13.53
04 4865 ch03541/31/032:17 PMCHAPTER 3Page 54TOAD SQL EditorFIGURE 3.13Resolving SQL substitution variables.Table and Column Name Select ListsTOAD makes it easy to find and work with tables and columns. Figure 3.14 illustratesusing the Show Table Select Window button (mouse cursor is pointing to thisbutton). This brings up the Table Name Select window, with a list of available tablesfor this particular user. If the user has privileges to other schemas, the drop-down listin this window can be changed to the schema owner and those objects will thenappear in the select list. Double-clicking on the selected object adds the selectedtable to the SQL Editor as illustrated in Figure 3.14.Similarly, columns can be added to the SQL Editor by using the Show Column SelectWindow button. Figure 3.15 illustrates using the Column Name Select window toadd the three columns EMPNO, ENAME, and JOB to the SQL being built in the SQLEditor.
04 4865 ch031/31/032:17 PMPage 55Table and Column Name Select ListsFIGURE 3.14Table Name Select list.FIGURE 3.15Column Name Select list.TOAD will automatically give a column select list if you type or select a valid schema tablename followed by a ‘.’. Wait a second and the column select list will appear as illustratedin Figure 3.16!55
04 4865 ch03561/31/032:17 PMCHAPTER 3FIGURE 3.16Page 56TOAD SQL EditorAutomatic column select lists.Using Aliases and Autoreplacement SubstitutionsTOAD supports the use of its own alias names. Aliases are convenient to shortenkeystrokes, using short names instead of rather long table names. TOAD also allowsthe automatic replacement of text, or in this case, these aliases. The purpose of thealias is to shorten keystrokes. The purpose of the automatic replacement is to automatically resolve the TOAD alias to the full table or column name.TOAD aliases are used to shorten the number of keystrokes to develop SQL. TOAD aliasesare not to be confused with Oracle RDBMS table aliases, which are used in qualifyingcolumns in a multitable SQL statement.TOAD aliases enable you to Access the Column Name Select drop-down list Type the alias as a shortcut rather than typing the full table nameAliases are easy to set up with any text editor. Make sure TOAD is not running (onnetwork installations, make sure all users are off TOAD) and edit the ALIASES.TXTfile found in the TOAD installation directory under the TEMPS subdirectory.DO NOT edit this file with TOAD running. When TOAD exits, it rewrites this file and anychanges you make will be lost!Figure 3.17 illustrates the format of this file. The format is table name aliasname .
04 4865 ch031/31/032:17 PMPage 57Using Aliases and Autoreplacement SubstitutionsYou will learn a method of quickly creating this file in the “Scripts That Write Scripts”section later in this chapter.TOAD aliases are easy to use.Figure 3.18 shows a simpleSQL statement using an aliasto get to the Column NameSelect drop-down. Notice thatthe user typed in ‘inv.’. The ‘.’signaled the Column NameSelect drop-down and the aliaswas resolved to the INVENTORY table.[ic:Keyboard]The ‘.’ signalsTOAD to see if this is an alias.FIGURE 3.17TOAD alias setup.Notice in Figure 3.19 thatthere is both an INVENTORYtable and an INV table. Thealias INV was resolved in Figure 3.18to the INVENTORY table, not the INVtable. Notice the column names inFigure 3.19 compared to those inFigure 3.18. Granted, this is a poornaming convention, but bear withthe example. To get the INV tabledisplayed, use Shift Ctrl T (or Edit,Columns drop-down no alias) toignore the alias and get the correctlist of columns. See Figure 3.20.Shift Ctrl T ignores the aliasrequest.FIGURE 3.18TOAD alias usage.57
04 4865 ch03581/31/032:17 PMCHAPTER 3Page 58TOAD SQL EditorIf an alias is identified in the SQLstatement, and a Column Selectis activated, the alias is automatically added to ALIASES.TXT.TOAD scans only the first FROMclause in any SQL statement, soany TOAD aliases in complex SQLstatements that have subqueries,for example, will not be foundand resolved.FIGURE 3.19Available tables in the SCOTTschema.Autoreplace substitution replacesa short string with the full name.This differs from TOAD aliasesbecause aliases make reference toa different name but do notchange the text of the SQL statement. When autoreplace substituFIGURE 3.20 Ignore the alias in action.tion is defined, it happensautomatically when you press thespacebar. Autoreplace substitutionis activated by typing the short sequence (illustrated in Figure 3.21) and pressing thespacebar (the autoreplace delimiter key defaults to the spacebar). This will then automatically substitute the predefined string in place of the short key sequence. SeeFigure 3.22.Replacement happens after pressing the spacebar.FIGURE 3.21Autoreplace substitution keysequence.
04
TOAD SQL Editor T he 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 .
