Transcription
Toad for Oracle Tips and Tricks Dell SoftwareForewordIntroductionI have used Toad for 10 years, since I made the leap fromAccess to Oracle . Most of my time is spent in the editor,writing new code or opening and running one of the manysnippets I have saved there over the years. Toad makes memore efficient, the latest version reminds me of errors beforeI even hit compile. Using code review, there is an instructorstanding over my shoulder every time I hit format.Toad for Oracle has been the IT community’s tool of choicefor more than a decade. With Toad, you are a member of anelite community of two million plus Oracle professionals.I work in a small IT group and wear many hats. Everything I needto do in Oracle, I do in Toad. From simple data or whole schemaimports, exports, and comparisons to building out completelynew projects it’s a click or two away. And even while Toad hasmade me more efficient and my job easier to do right, it is thecommunity around it that makes it more than just software.From forums and mailing lists to blogs, users from all over enjoysharing and discussing all that it can do. I’m sure there are sometips in here that I will be trying out real soon.– Jim Graham, Database DeveloperWhether you are new to Toad or have been using it for severalyears, there are several features that you should be familiar withfor achieving maximum productivity. This document will stepyou through some Toad fundamentals and break down thefeatures for the following Toad editions and modules: Toad for Oracle Base EditionToad for Oracle Professional EditionToad for Oracle Xpert Edition, which includes our proprietary SQL andindex-optimization technologyToad for Oracle DB Admin Module, which helps DBAs or developmentteams manage their Oracle environments
Most tools offer asingle display modelfor objects in thedatabase. Toadoffers three!If you are already a Toad customer anddo not have access to the featuresdiscussed below, please contact yourDell Software sales representative andrequest an evaluation key.Toad for Oracle Base EditionRegardless of your responsibility inyour organization, if you work withOracle, then you will need a quickand efficient way to access the datain your database. This section of thedocument will step you through howto browse the contents of tables, writeyour own custom queries, and view therelationships of your database objects.This section also discusses the powerfulfeatures of Toad that help you developand maintain PL/SQL stored procedures.Toad users generally spend most of theirtime in two areas: the Schema Browserand the Editor.Figure 1. Toad’s tree view2Schema browserThe Schema Browser is your gatewayto the database objects in your Oracleinstance. Simply select the user/schema,database object type, and databaseobject on your left, then immediatelygain access to all the pertinentinformation for that object on your right.Customizing the displayMost tools offer a single display modelfor objects in the database. Toad offersthree! Toad can display your objects in atree view, a dropdown selector, or a tab/page panel.Toad tip: Selecting “Dropdown” willgive you the most real estate for listingobjects and allow you to use thekeyboard to navigate the object typelist. Selecting “Treeview” will make Toadlook and feel more like SQL Navigator orOracle SQL Developer.
The Schema Browserallows you to creategroups of schemasfor each connecteddatabase.Figure 2. Toad builds a historical list of things you’ve viewed in the database.Basic navigationFind your object on the left side. Selectit and the meta-data or details for theselected object will appear on the rightside. As you click around the database,Toad builds a historical list.Quickly navigate to objects that you’vebrowsed to previously using the “Back”and “Forward” buttons on the rightside toolbar.FilteringBy default, Toad will display all objectsin the database that you have accessto. If you are working in a system withmany thousands of objects, this canquickly become overwhelming and hurtyour productivity. Therefore, Toad offersseveral levels of filters.3Hiding Schemas/UsersThe Schema Browser allows you to creategroups of schemas for each connecteddatabase. For example, you could createa group called “Oracle Test Data” thatcontained the “SCOTT,” “HR” and “SH”accounts. You can create custom groupsto manage your production and testaccounts or your different applicationschemas. Schemas you access on alimited basis would be “hidden” under the“Other Schemas” category.To get started, right- click on theschema selector (or a schema node inthe treeview) and select “Customize.”This will open the Customize SchemaDropdowns dialog. From here you canassign schemas to as many groups asyou like.
If you are workingon a project thatwill require frequentaccess to specificlist of objects acrossobject types andschemas, then youmay benefit fromthe “Favorites” panelin the SchemaBrowser.Figure 3. Use the Customize Schema Dropdowns dialog to assign schemas to groups(applies to the Object Palette).Toad tip: Tell Toad to load only schemasthat own objects: right-click on theschema selector or set in the View Toad Options dialog on the SchemaBrowser page.Filtering object listsEach object type has an independentlydefined filter. By default Toad will showall objects for the selected type. Quick filter – A basic pattern matchinginput box. You can input “C*; D*” forexample and have only objects that startwith the letter “C” or “D.” This control doesnot support regular expressions. In Toad forOracle v10.5 and higher, the filtering clauseis applied ONLY to the selected object list.So if you define a filter while the “Tables”object list is active, the filter will not applyto “Views.” Project filters – See below.Filter dialog – A much more powerfulcontrol.Data grid filtersYou can also filter the data displayed inany data grid, not just ones found in theSchema Browser, as explained in the“Data Grids” section below.Organizing objectsIf you are working on a project that willrequire frequent access to specific list ofobjects across object types and schemas,then you may benefit from the “Favorites”panel in the Schema Browser.Figure 4. You can load only schemas that own objects by right-clicking on the schemaselector or set.4
Figure 5. Favorites panelProjectsIf you want to extend the favoritesconcept beyond database objects, thenI recommend checking out the ProjectManager. To add database objects to aproject, right-click one or more objectsand select “Add to Project Manager.”You can also drag and drop the Objectsfrom Schema Browser into the ProjectManager panel.Having a project defined also allowsyou to filter your object lists in theSchema Browser by project. This canbe handy when you are logged into a30,000 object schema that containstables for 30 different applications andyou want to see only the “Payroll” tables,not everything.Are you feeling overwhelmed by theToad Interface? Read this blog onde-cluttering your Toad desktop.Figure 6. Adding database objects to the Project Manager5Having a projectdefined also allowsyou to filter yourobject lists in theSchema Browser byproject.
Many powerfulfeatures of Toadare exposed byusing your mouse.Right clicking on atable in the SchemaBrowser will exposemore than 35different operations.Figure 7. Additional details available in the right-click menuAdditional detail viewBy default, Toad displays all of the detailinformation for your objects on the rightside. You can manually add any of thesedetail items to the left side by rightclicking in the column header on the leftside. For example, you could add “NumRows” for Tables and sort by table sizestats for an estimated number of rows.Right-click functionsMany powerful features of Toad areexposed by using your mouse. Rightclicking on a table in the SchemaBrowser will expose more than 35different operations. If you rely onhaving a button available on the screen,you may be missing out.Overwhelmed by the Toad interface?Read this blog post on how tosimplify Toad.Toad tips:1. Multi-select objects and then right-click.2. Where you right-click determines whatyou see: grids, toolbars, menus, gridheaders, etc.Figure 9. Editor button on toolbarFigure 8. Schema Browser’s right-click menu6
EditorToad now has a single editor for workingwith SQL and PL/SQL objects. Olderversions of Toad had separate editors,but the former SQL Editor, PL/SQL Editor,and offline editors have been condensedto a single window for all of your editingrelated to SQL, PL/SQL, anonymousblock, SQL*Plus script, etc., as well as forad hoc querying on the database.The editor is for building and executingyour Oracle commands. Anything youcan execute via SQL*Plus can alsobe executed via the Toad Editor. Thisincludes: Anonymous blocksSQL, DDL & DMLPL/SQLRMAN commandsStored Java proceduresSQL*plus scriptsFigure 10. Code templates list box7Writing code from scratchAn empty editor might seem like more ofa “blank screen of panic” for those lesscomfortable with Oracle’s syntacticalrules and commands. Toad has many ofthese commands built-in and availableto invoke on demand.Code templatesCode templates are commonly writtenPL/SQL blocks of code that you canhave generated on demand. As anexample, try typing the following into ablank editor:anon Ctrl Spacebar This will activate the anonymous blockcode template:Each template has a name, description,and code component. To activate atemplate, type its name followed bythe Ctrl Spacebar key sequence. Ifno text is at the current cursor position,then a pop-up list of all the templateswill appear for you to select from.Toad now has asingle editor forworking with SQLand PL/SQL objects.
Figure 11. Pop-up list while scripting in the Toad editorToad can help youwrite your SQLand/ or PL/SQLstatements. As youtype, Toad can offerto complete thecommand or objectname for you.The default templates can be modifiedby right-clicking in the Editor andchoosing “Editing Options.” From thereyou will default into the behavior portionof the Editor options. Click the “CodeTemplates” button. Templates can beextended, modified, removed, and evenshared with other Toad users.Toad tip: Make your templates dynamicby using the & character.Code insightToad can help you write your SQL and/or PL/SQL statements. As you type, Toadcan offer to complete the command orobject name for you. The Code Insightfeature has been remarkably improvedover the past several releases. As you mayremember from older versions, CodeInsight ( ctrl Period from the editor)allows you to quickly browse and selectFigure 12. Toad options for code assist8tables in the editor. Code Insight has theability to see the following object types: TablesViewsAliasesFunctions, procedures and packages(with methods)Types (with attributes and methods)Java sourceSequencesUsersExpected tokens (keywords)Available variables and parametersCollection typesPublic and private synonymsToad tips:1. You can disable this feature in part or in full.2. You can disable support forexpected tokens.
For example, suppose I want toquery from a schema that starts with“Matthew.” Toad will recognize that textand pop up a complete list of accountsthat match that string:For performance reasons, not allobject types are enabled by default; inparticular, public synonyms are not. Soif you are looking for help referencinga DBMS package, you will need topreface it with the “SYS” notation orenable the public synonym support.All of the Code Insight options can befound on the Editor’s Code Assist pagein the Options dialog. To disable codeinsight, uncheck the “Enable CodeInsight pick list” option. You can stillinvoke the code assistance feature ondemand by using the “CTRL” “Period”key sequence.A less drastic approach would be toincrease the timer delay to somethinglike 5000. This would give you a goodfive seconds before Toad attempts toauto-complete your text.F4/DESCIf you have ever used SQL*Plus, youare probably familiar with the DESCFigure 13. Using the Toad DESC command9command, which gives you a table orview definition. Toad has its own DESCcommand that will work on any objectin the database.To use the Toad DESC, simply put yourcursor on an object name you havetyped in the editor and use the F4 key.This will open a pop-up dialog that givesyou all of the same information youwould see in the Schema Browser.Toad tips:1. You can DESC as many objects as you want.2. You can drag column names into the editor.3. You can modify the object from the DESC.Recall previous SQLEvery valid SQL statement you executevia F9 is automatically recorded by Toad.You can access previously executed SQLby using F8. The same panel is availableunder the View menu. By default, Toadstores the last 500 executed statements.Toad tip:You can cycle through your list ofpreviously written SQL statements using ALT Up Arrow or ALT DownArrow . You can do this on selected textto do a limited replacement.The DESCcommand givesyou a table or viewdefinition. Toadhas its own DESCcommand that willwork on any objectin the database.
Giving your SQLstatements a nameallows you to recallyour query bycontext versus someobscure SQL youmay have written sixmonths ago.Figure 14. Right-click to split the editorGiving your SQL statements a nameallows you to recall your query bycontext versus some obscure SQL youmay have written six months ago. Somegeneral advice: if you spend more than30 seconds writing a SQL statement,give it a name. You can now recall itwithout using the F8 dialog, giving youmore room to type in the editor. Use CNTRL N to pop up a list of namedSQL statements. Selecting one will putthe SQL in your editor.Split editorIf you are working on an extremely largeblock of code and want to view or editmultiple sections simultaneously, rightclick in the editor panel and choose”Split Editor Layout.” You can use either a“Top/Bottom” or a ”Left/Right” theme.Make code statementsToad supports six different programminglanguages (C , Delphi, Perl, and more),which can be selected from the Optionsmenu. From there you can create codeFigure 15. Toolbar button to format code10statements based on SQL, or strip nonSQL syntax from a code statement.For example, to take a SQL statement andautomatically format it to be embeddedinto a Java program, you can simplyselect “Make CODE Statement” from theEditor menu. This will copy the currentwindow’s statement to the clipboardin the language syntax you select. Thenext step is to paste the formatted SQLcall into the code. You can extend thisfeature to the programming language ofyour choice by adding it manually in theOptions dialog.Looking for help on how to executestatements and scripts? Read this blog.Format codeTo instantly transform chaotic, untidycode into easily managed, aestheticallypleasing code, right-click in the editorand select the icon with two yellowarrows, as shown below. Under the Viewmenu option, select “Formatting Options”to customize the code formatting.
Figure 16. Quickly determine which line of code is taking the most time to run.Working with PLSQLToad is a full featured IDE that allows aPL/SQL developer to accomplish thefollowing quickly and easily: Create PL/SQL stored proceduresExecute said proceduresIdentify syntax errorsDebug PL/SQL objectsPerform automated code reviewsDocument the PL/SQL objectsIdentify execution bottlenecksLoad test the PL/SQL objectsUnit test the PL/SQL objectsCapture REFCURSOR outputRefactor PL/SQLControl PL/SQL via source controlTo cover these topics in detail herewould make this paper many pageslonger and likely try your patience.Instead, we will cover a few of myfavorite and most-frequently overlookedfeatures. You can learn more about theother features on Toad World.Execution profilingToad allows you to profile your PL/SQLexecutions to determine where anyexecution bottlenecks are occurringand answer the question, “Why is myprogram taking so long to run?” To starta profiler run, toggle “on” the “stopwatch”button next to the “debug” toggle. Thenexecute your PL/SQL program from theEditor. After it is finished, pull up therecord from the Profiler tab. With Toadfor Oracle v9.6 and higher, you canautomatically pull up the visual chartrepresentations of your PL/SQL runs byclicking on the Details button.Figure 17. Toggle on the Details view to see a graphical representation.11Toad allows youto profile your PL/SQL executions todetermine whereany executionbottlenecksare occurringand answer thequestion, “Why ismy program takingso long to run?”
The debuggercan be your bestfriend to figure outwhy your programisn’t behaving asexpected. It runsyour program lineby line, steppingthrough the code.Figure 18. Watch panel in the Toad editorIf you are on Oracle 11g or higher,then Toad also supports Oracle’shierarchical profiler.DebuggerThe debugger can be your best friendto figure out why your program isn’tbehaving as expected. It runs yourprogram line by line, stepping throughthe code. Setting breakpoints: To set executionbreaks in the code, leave the cursor onthe line on which you want the code tobreak and hit F5. Set conditional breaks bydouble clicking the break in the Break andWatch View window” Make sure to onlyadd breakpoints that are executable; these are marked by little blue dots in the editorgutter for each line. Non-executable lineswill not have their breakpoints honoredby Oracle.Adding a watch: To add a watch on avariable, leave the cursor blinking on thevariable on which the watch is desired andclick the eyeglasses icon. Toad allowsyou to see the values of all your codevariables by using the “Enable SmartWatches” feature.Modifying variable values while debugging:To artificially change the value of one ofyour PL/SQL variables, highlight the variablein the watch window and hit the calculatoron the icon bar which, once moused over,will read “Evaluate/Modify Watch.”Figure 19. Auto-extract and execute embedded SQL statements in your PL/SQL code.12
Figure 20. Filter/Sort button on the Data Grid toolbarRunning SQL inside of PL/SQLWhile most of you know that youuse F9 to execute a single SQLstatement, did you know you can use CTRL ENTER on embedded SQLin your PL/SQL code? Toad will extractthe SQL and attempt to execute it. Anyvariables will need to be assigned, butToad will ask you for required values.Looking for an alternative to debugging?Read this blog on how to get Toadto generate your DBMS OUTPUTscenarios automatically (new in Toadfor Oracle v10.5).Data gridsYou may be asking why I took so long toget to the most important feature. Thedata grids are integrated into multipleToad windows. So while it is tied to boththe Editor and the Schema Browser, it isnot a stand-alone feature. The followingare a few tips and tricks for getting moreout of the data grids.Figure 21. Table Sort/Filter window13Sorting/filteringToad makes it easy to apply a sort or filteron your data via the grid. Note that thisfeature is not available for grids inthe Editor.If you want to apply multiple sortconditions, then use the Sort panel toassign a condition for each field. Youcan also decide how NULLs shouldbe treated.Filtering is available in severaldifferent mechanisms.Toad tip: The easiest way to apply a filteris to click into a cell that has the valueyou want filtered on, click on the filtercontrol, and choose “Add to filter current value.”Toad will extract theSQL and attemptto execute it. Anyvariables will needto be assigned, butToad will ask you forrequired values.
Figure 22. Applying a filter (the variable value is defined by the active cell)Figure 23. Filter condition copied to dialog14
Figure 24. Result set refreshed with active filterA few notes about the filter dialog: Filters can be named for easy recall.The WHERE clause can be input manually.You can use conditions, such as BETWEENand NOT IN.Filters are remembered between sessions. Ifthe funnel control is colored RED, then thefilter is active.Column ma
Editor Toad now has a single editor for working with SQL and PL/SQL objects. Older versions of Toad had separate editors, but the former SQL Editor, PL/SQL Editor, and offline editors have been condensed to a single window for all of your editing related to SQL, PL/SQL, anonymous block, SQL*Plus script, etc., as well as for
