TOAD Tips and Tricks Quick Reference
Brian_O’Donnell@Quest.com Schema Browser - Tabular view of visual objects with add/delete/modify ability 1) Customize Tabs Customize tabular view by Right clicking in the gray area of the object icon bar 2) Data View and modify a table’s data by selecting table from the object tabular view, and the DATA from the right side of the screen a) Sort/Filter the data by selecting the icon resembling a funnel b) Save the data to a file format of your choice by right clicking in the data grid and selecting Save As. This gives the choice of format and saving the data to a file or copying to the clipboard. c) Create Insert Statements by right clicking the data grid and selecting create insert statement. It will save inserts on clip board to be run from SQL Editor. SQL Modeler - allows for building queries visually 1) Select Tables Select one, all or any subset in between of the tables or views necessary for the query and drag them into the workspace on the left. 2) View Joins Double click the links to view the joins. 3) Choose Columns Double click in the check boxes of the columns desired for the query 4) Add Criteria Set up any added criteria such as sorts or where conditions, by double clicking in the empty cell that intersects the action required and the column to which that action pertains 5) View Generated Query View the SQL by clicking on the Generated Query tab. To modify this statement click the icon that says SQL and has a blue database symbol which is located on the second of the top two icon bars next to the ambulance 6) Run Generated Query Run the query with the F9 key or the green play button to return a data set. 7) Explain Plan View the explain plan by clicking on the ambulance icon on the second of the top two icon bars SQL Editor – SQL Plus on steroids 1. SQL Recall Access previously run SQL by selecting F8 or SQL Window from the top menu bar. The user can narrow the selections by using “Personal SQLs” or take it a step further by using “Named SQL” both of which are found under the SQL Window menu option as well. 2. Table and Column Windows Floating windows containing a schema’s tables and a table’s columns can be displayed by clicking the “Show Tables Window” and “Show Columns Window” icons respectfully. They are found on the third of three icon bars second and third from the right. Once these windows are floated, drag and drop functionality can be fully utilized. Another manner for retrieving
3.
4.
5.
6.
7.
8.
table names is by selecting the first letter or letters of the table and hitting SQL Function Templates A large number of SQL function templates can be accessed through the icon on the far right of the third icon bar which has a “?” and reads “Show SQL Help Window.” A drop down let’s us see all of the different Function Types from which we can select templates. Drag and drop can also be utilized from this floating window. Make Code Statements TOAD supports 5 different languages, chosen through the Options menu, into which it can make code statements to be imbedded in source code. To take a SQL statement and automatically format it to be imbedded into a Java program, for example, we need to simply click the icon on the right side of the second icon bar, which has a database symbol pointing to “VB.” If moused over it reads “Make CODE Statement.” This will copy the current window’s statement to the clipboard in whichever language’s syntax the user has chosen. The next step is to paste the formatted SQL call into the code. Format Code To instantly transform chaotic, untidy code into easily managed aesthetically pleasing code by right clicking in the editor and selecting “Formatting tools” then choose “Format code.” Under the menu option “View” select Formatting Options to customize how the code will be formatted. Auto Replace To access the auto replace functionality right click from the editor and select “Editing Options”. From there choose “Auto Replace” to set what the user wants replaced and with what to replace it. Code Templates Right click from the SQL Editor and choose “Editing Options.” From there select Code Templates. TOAD provides many templates but the user can also add their own templates from here. To access the Templates from the SQL Editor simultaneously press CTRL and the Space Bar. The user can also memorize the shortcuts, type one and hit CRTL/Space to retrieve the Template. Tuning SQL To make sure the user’s code most efficient highlight the statement and launch the Xpert tuning piece by double clicking the marble like icon which is second from the right on the second icon bar. a. Advise To get advice from the experts on how to tune this statement click Advise b. Build Scenarios To have the tool build different scenarios to which you can compare the original and decipher what is the best course of action, select “Auto Tune”
Procedure Editor – Allows you to create and debug functions, procedures, & packages. 1) Customizing Procedure Template TOAD has templates for Procedures, Functions, Triggers and Packages which are fully customizable. They are stored in the TOAD\Temps directory and can be modified as files accessed from said directory. 2) Debugger – TOAD’s tool to help set breaks and view variables’ values.
a) Set Breaks To set breaks in the code leave the cursor on the line on which you wish the code to break and hit F5. Set conditional breaks by double clicking the break in the floating “Break and Watch view window.” b) Add Watch To add a watch on a variable, leave the cursor blinking on the variable on which the watch is desired and click the eyeglasses icon. 1) Deposit values To explicate deposit a value into a variable, highlight the variable in the watch window and hit the calculator on the icon bar which, once moused over will read “Evaluate/Modify Watch” c) Input Parameters To pass in parameters, click the “(…)” icon d) Run the code by hitting the yellow lightening bolt icon