WIXLIB

April 2006 Volume II Issue 2TOAD: Tips and TechniquesBy Dan HotkaEditor’s Note: Every Oracle Developer and Database Administrator knows the power of TOAD. It isboth freeware and a licensed product, and is an excellent alternativeto SQL*Plus for developing Oraclescripts and reviewing databaseschema information. But unlike thecliché, “you cannot teach an old dognew tricks”, first time ORAtips contributor and TOAD instructor DanHotka has put together a few of hisfavorite things about TOAD for boththe novice and experienced TOADuser. The result is a tip-packed article of new tricks to save keystrokes,reduce time spent searching for syntax errors, and make more efficientuse of the symbolic debugger.IntroductionORAtipsJournalPage ORAtips4On DevelopmentTOAD is a Windowsbased graphical toolthat allows forOracle SQL andscript execution.This article will illustrate some ofthe more popular uses of TOAD. Itwill detail some of the keystroke saving features such as auto replace andcode templates. There are severalways to obtain information and atleast three ways to do most things.For example, menu selection, hotkey sequence, right-mouse click, andassigned function key all have similar purposes. This article will focuson the three main TOAD utilities:the SQL Editor, the Schema Browser,and the Procedure Editor.SQL EditorFigure 1 illustrates the main TOADinterface, the SQL Editor. The SQLEditor allows you to easily enter andexecute Oracle SQL or unnamed PL/SQL blocks. The results appear inthe lower half of the screen.F9 or the green arrow-shaped button allows you to run a single SQLstatement or the SQL statement thatthe mouse cursor is currently on. Thisbutton also executes SQL that is high-TOAD is undoubtedly the defacto standarddevelopment tool for theOracle database. TOADcame from the sharewaredays and is sold by QuestSoftware. A free trial version is available at www.TOADsoft.com and www.Quest.com.What is TOAD? TOADisaWindows-basedgraphical tool that allowsfor Oracle SQL and scriptexecution, and viewingobject information, relationships, scripts, anddata. Data can also beeasily viewed, saved invarious formats (including MS Excel, XML, andinsert scripts), manipulated, and more. TOADis very powerful in theOracle PL/SQL arena, itsFigure 1: TOAD SQL Editortrue strength.ORAtips.comORAtips 2006 Klee Associates, Inc.

April 2006 Volume II Issue 2ORAtipsJournalPage ORAtips4On Developmentlighted from inside a PL/SQL routine. The outputfrom this button appearsin the DATA GRID tab inthe lower area. There willalso be an Explain Plangenerated for this SQL,which you can view byclicking the EXPLAINPLAN tab.The button next to thegreen arrow-shaped button runs all of the SQLthat is in the SQL area.The output from thisexecution appears in theSCRIPT OUTPUT tab.Multiple connectionsto the same or differentdatabases can be maintained. And, multipleTOAD windows can beopen; the only limit isthe amount of memoryon your workstation! Itis common practice tohave connections to thetest, development, andeven production environments open at the sametime. Under the Windowmenu item, it is easy to Figure 2: TOAD SQL Recalltile or cascade all openwindows.SQL into the SQL Editor. This is alsoa dockable window; notice the pushThe clear button, encased in the pin button. This provides an autoblue circle in Figure 1, clears all the hide type function but still allowsSQL in the window.easy access to the SQL history.Pressing F8 brings up the TOADSQL History or SQL Recall, asshown in Figure 2. This is one ofthe most popular features of TOADbecause it makes it easy to bring upSQL worked on previously or, whentuning SQL 5 iterations ago. Thereare options to control how many SQLstatements get saved. TOAD defaultsto the last 100 SQL statements executed. Simply highlight the SQL andpress the SQL button (encased in theblue circle in Figure 2) to load theALT-Up Arrow and ALT-DownArrow also access SQL. Recall fromthe SQL Editor window, and it willwalk you up or down through recently submitted SQL.There are a lot of TOAD featuresthat can eliminate extra keystrokeswhen coding. One of the more popular features is the embedded aliasprocessing. Using table aliases isa good practice to get into, and inORAtips.comTOAD, it greatly aids in the quickdevelopment of SQL statements.As an example, starting with theOracle SQL illustrated in Figure 3,add a SELECT and FROM clauseand give each table in the FROMclause an alias.Now, navigate to other parts ofthe SQL statement and enter thealias followed by a“.” and wait onesecond.A column popup, as shown in Figure 4, will appear. Double click onany of the columns, and they will bepasted into your SQL session.ORAtips 2006 Klee Associates, Inc.

April 2006 Volume II Issue 2these tabs. The INDEXES tab is useful when doing SQL tuning work.Being an old DBA, I am used toworking with scripts. I like to seethe script that can be used to recreate this object in the SCRIPTS tab.Oracle data can be viewed, filteredand sorted, and saved in a numberof formats, as will be discussed inthe Schema Browser section of thisarticle.Figure 3: TOAD Enabling Alias ProcessingThe Schema Browsershows all databaseFigure 4: TOAD Alias Pop-UpYou can highlight several columnsat once and hit return, and they willall be pasted into your SQL session asillustrated in Figure 5, complete withaliases! This saves a lot of typing andresearch, and the impending syntaxerrors because you did not spell thecolumn name correctly.ORAtipsJournalPage ORAtips4On DevelopmentThis feature works anywhere inTOAD where you can work with SQLincluding SQL Editor and ProcedureEditor.objects the user ownsThe DESCRIBE window is anothervery popular feature. Highlight orjust place the cursor on the objectname in the FROM clause and pressF4. This brings up a window shownin Figure 6, which shows the columns, data, scripts, indexes, andrelationships. This window can beresized and moved around.Column names can also be draggedand dropped from this window. Datacan easily be viewed along with allthe other information you see onor has access to.Note the buttons along the top ofFigure 6. You can easily start a wizard to edit an object, view key relationships, run statistics, or even truncate all of the data! But be carefulout there!Auto replace is typically used fortypo corrections. Auto replace is activated in TOAD every time you hit thespace bar. You can easily add yourown items for replacement. Typicallyitems for long table names, putlinefor PL/SQL coding, etc., are added.Using the menu item EditorOptions (Edit Editor Options), asshown in Figure 7, you can see whatis in AUTO REPLACE as shown inFigure 8.Figure 5: Results of Alias ProcessingORAtips.comORAtips 2006 Klee Associates, Inc.

April 2006 Volume II Issue 2Figure 6: F4 Describe WindowORAtipsJournalPage ORAtips4On DevelopmentFigure 7: Accessing Auto ReplaceOptionsFigure 8: Auto Replace OptionsORAtips.comORAtips 2006 Klee Associates, Inc.

April 2006 Volume II Issue 2In this example, there are a varietyof common typos that are addressed.You can also see the “pl” that I addedfor my dbms output.put line template. Every time I enter “pl” followed by the space bar, this code willbe substituted in its place, as shownin Figure 9.That was a brief overview of some ofthe top tips and techniques forusing TOAD’s SQL Editor. But itwas barely the tip of the iceberg!Topics not covered in-clude generating template SQL code, saving data,code formatting, opening existingSQL, and saving SQL, which will beaddressed in future articles.Schema BrowserPressing the Schema Browser button, as shown in Figure 10, accessesthe Schema Browser. This interfaceshows all database objects the userowns or has access to.Figure 9: Using Auto Replaceobject type. Tabs can be tiled with anoption and right-mouse clicking onthe tabs can hide tabs and uncheckthe ones you do not want displayed.The right side of the SchemaBrowser is remarkably similar to theF4 DESCRIBE screen in the SQLEditor. In fact, all of the same fea-tures discussed here also apply to theF4 DESCRIBE screen.You can have a Schema Browserwindow open and drag and drop columns from this window back into theSQL Editor or the Procedure Editor.On the left side,the logged in userappears. Using thedropdown menu canchange this. TOADcan access any information on the database that the user haspermissions to see.ORAtipsJournalPage ORAtips4On DevelopmentThe box just underthe user box is a filter. You can haveonly certain objectsappear in the tabbednavigatorwindow(left side). This ishandy when theapplication you areworking with hashundreds of objects.Notice again onthe left side that theTABLES tab is displayed. There is a tabfor every database Figure 10: Schema Browser InterfaceORAtips.comORAtips 2006 Klee Associates, Inc.

April 2006 Volume II Issue 2Figure 11: Data TabORAtipsJournalPage ORAtips4On DevelopmentThe DATA tab, as shown in Figure11, has several options. You can add,delete, or edit the data using the buttons along the top, (encased in a bluecircle on Figure 11). If you click ona row, then click the little book button (encased in a red circle on Figure11), this will bring the whole row upfor easy editing.displayed data because you canuse filters and sorts to get thedata the way you want and theprint and Save As options willonly save the data displayed.In the SQL Editor, again, usethe SQL itself to filter the data(WHERE clauses) and sort thedata (ORDER BY clause).TOAD has two different reportingoptions.1. You can print the displayeddata.These options are all available byright-mouse clicking on the dataitself. Select Save As from the popupmenu and you will see the screen inFigure 12.2. You can save the displayed datain a number of formats. I sayData can be saved in a varietyof formats, as shown in Figure 12.ORAtips.comCode templates aresimilar to the autoreplace except thatthey are signaledwith the CTRL-spacebar keycombination.ORAtips 2006 Klee Associates, Inc.

April 2006 Volume II Issue 2brings up a procedure, function, package developmentenvironment, as shown in Figure 13, complete with symbolic debugger. Existing procedures, functions, triggers,and packages can be openedfrom a file or loaded into theProcedure Editor from theSchema Browser (PROCStab, and either double clicking on the code or rightmouse clicking and clicking on load into procedureeditor).All the coding shortcutsdescribed in the SQL Editoralso work here.Code templates are similar to the auto replace exceptthat they are signaled with theCTRL-space bar key combination. TOAD comes with avariety of existing templatesFigure 12: Save As Options ScreenThere are several different options depending onwhat you selected in theupper radio group.ORAtipsJournalPage ORAtips4On DevelopmentThe INSERT statementselection allows you to put“COMMIT;” into the created text after a selectednumber of rows. Outputoptions here include theclipboard; use it to pasteback into the SQL Editor window, or a file. Thisfeature along with DDLgeneration helps quicklyand easily generate a testenvironmentcompletewith test data.Procedure EditorThe Procedure Editoris accessed via the Procedure Editor button. This Figure 13: Procedure EditorORAtips.comORAtips 2006 Klee Associates, Inc.