TOAD Quick Guide
TOAD Quick Guide
Schema Browser Window
Procedure Editor and Debugger (Profiling)
SQL Editor Window
Data Subset Wizard
Using Auto Replace
Project Manager Tutorial
Action Palette or Application Designer or Automation Designer (Save repetitive actions like a macro)
Object and Schema Compare
Statspack and AWR Browser
Data Import / Export Browse
Trace File Browser
RMAN Script Templates
Rebuild Multiple Objects
SGA Trace Optimization
Tuning SQL (Optimize SQL)
Run TOAD on a USB Drive
DB Monitor (or Database Monitor)
Code Road Map
Other Good Tools
Toad for Oracle Editions
As you may already know, there are different editions of Toad for Oracle:
- Toad Base – maybe known as Standard (includes Knowledge Xperts + Debugging) - essential Toad features
- Toad Professional (Toad Base + extra features) - focussed on coding best practices
- Toad Xpert (Toad Professional + SQL Optimizer) - focussed on application performance
- Toad Development Suite (Toad Xpert + Quest Code Tester + Benchmark Factory for Oracle) – end-to-end Oracle development solution
- Toad DBA Suite (Toad Xpert + DB Admin Module+ Toad Data Modeler + Benchmark Factory for Oracle + Spotlight on Oracle) – complete DBAs toolkit for administration, diagnostics and tuning
Does Toad need OEM ?
Toad offers optional features in the DB Admin Module (under Database/Monitor) which WILL ACCESS the Oracle OEM Diagnostics Pack such as :
- ADDM/AWR Report Generator – enables snapshot management and shows both AWR and ASH reports
- AWR Browser – graphical representation of data collected from AWR
- ASM Manager – enables management of ASM disk groups and clients
Toad does offer alternate ways to determine performance bottlenecks without the use of ADDM/AWR such as the StatsPack Browser, TK Prof interface, Database Monitor, Session Browser and the Quest SQL Optimizer’s SQL Inspector to name a few. However, if you are already licensed to use the OEM Diagnostics Pack and you wish to get additional value from it, you can use the functionality we offer in Toad.
If you are on Oracle database 10g and above, you can use the Oracle Tuning Advisor feature in Toad Base (9.6 and higher) which WILL access both the Oracle OEM Tuning Pack and Diagnostics Pack.
Alternatively, you could use Toad Xpert, Development Suite or DBA Suite Editions and use the Quest SQL Optimizer which DOES NOT access any of the Oracle OEM Packs at all. In addition, Quest SQL Optimizer offers a unique way to rewrite your SQL code for maximum performance.
TOAD DB ModuleScreen Shots of its content:
Database - Administer - Compare and Import Options
Database - Optimize - Diagnose - Monitor Options
|Describe an Object (also CTRL+Rigth Click). Just put the cursor over the object ||F4 |
|Execute code as Script (from SQL Editor)||F5 |
|Jump to that Object (Browser Window)||SHIFT + F4|
|Toggle between SQL Editor and Results tabs||F6|
|Clear all text (from SQL Editor) or Trace Into on the Procedure Editor||F7|
|ReCall SQL History (opens the SQL Statement Recall panel) ||F8|
|Display Previous/Next Statement used|| |
ALT + Up or Dn
|Provides a Code Template (with Rigth Click - Editing Options - Code Templates I can edit them)||CRTL + Space|
|Run Statement (from SQL Editor) or Compile (on the Procedure Editor). Execute all statements in the Editor||F9|
|Verify statement without execution (parse)||CTRL + F9|
|Run Statement at Cursor||SHIFT + F9|
|Execute as script or Run (continue execution) in the Procedure Editor for PL/SQL debugging||F11|
|Run to cursor in the Procedure Editor for PL/SQL debugging.||F12|
|Convert Text to Lowercase / Uppercase ||CTRL + L / U |
|In the data grids, goes to the TOP/END of the recordset||CTRL + HOME or END|
|Clicking on a procedure/function name while holding the |
|Moves to the selected procedure in the body from the spec (up or down) ||CTRL + SHIFT + UP or DOWN |
|Completion dropdown. As you type the procedure name, the dropdown does an incremental search in the list.|| |
|If you begin a procedure call and pause after the first |
(, Toad parses the procedure spec and gives you parameter hints. You can cycle through parameters for overloaded functions.
|Execute Explain Plan on the Current Statement||CTRL + E|
|Recall Named SQL Statement||CTRL + N|
|Columns Dropdown||CTRL + T|
|Navigate to the next results panel tab||CTRL + ALT |
|In the data grids, goes to the top / End of the recordset||CTRL + HOME or END|
|Completely expand dependency tree views||CTRL + SPACE|
|Quick Describe anywhere in Toad to find objects and display their information in the Describe Objects window.||CTRL + D|
Schema Browser WindowThe Schema Browser is your gateway to the database objects in your Oracle instance. Simply select the user/schema, database object type, and database object, then immediately gain access to all the pertinent information for that object. For each object you can generate its DDL, see Used BY, check stats, indexes, constraints, etc
For Table Data you can:• Create Insert for All Rows • Create Insert statements replacing values (example use sysdate for dates, sequences, etc) • Save as HTML Report • Generate very nice reports • Detect Duplicate Rows • Record Count • Perform Multi-Select and then work with those rows only • Find Data • View/Edit Query • Create in Another Schema • Compare Data • Rebuild Objects • Master/Detail Report
For CodeCodeXpert to provide Tuning in your code
Customize Left Hand Side (LHS)TabsCustomize tabular view by Right clicking in the white area of the object icon bar and selecting the configure box. Double click on the box intersecting the Tab you would like to hide and the box that reads “VISABLE.” Captions can be modified to display a user-defined description in the tab. Or if English isn‟t your first language, translate “Tables” to “Les Tables” (yes, this is a joke.) Use the 'Up' & 'Down' arrow buttons to re-order the tabs, or use the 'Alphabetize' button to make items easier to find, versus 'Tables' and 'Views; appearing first and second respectively.
You can also customized. You can manually add any of these detail items to the left-hand-side (LHS) by mouse-right-clicking in the column header on the LHS. For example you could add „Num Rows‟ for Tables and sort by table size stats for estimated number of rows.
HistoryQuickly navigate to objects you've browsed to previously. Use the "Sundial" button.
DataView 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/Filterthe data by selecting the icon resembling a funnel
b) Select Columns- to limit the columns shown in the grid right click and choose SELECT COLUMNS
c) Save the datato 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.
d) Create Insert Statementsby right clicking the data grid and selecting create insert statement. It will save inserts on clipboard to be run from Editor.
e) Customiz Layoutsby dragging and dropping columns into select order. Fix a column or set of columns by right clicking and selecting fix current column. View only desired columns by right clicking and choosing select columns. To save a desired layout, select VIEW>OPTIONS>SCHEMA BROWSER> DATA and GRIDS>DATA GRIDS and check "Save Layouts"
f) Reporting- To access TOAD's Fast Report reporting tool, right click in the Data Grid and select "Report". You will find a report generation wizard to walk you through report creation. You can also access this via the "Grid" Toad menu item, e.g. GRID>Report.
Using Favorites as a Custom Schema BrowserOften in TOAD, you need to work with the same group or groups of objects. For example, you might only need to routinely work with the production tables for CUSTOMER and EMPLOYEE, their indexes and views. So you would like to have a customized Schema Browser for just those objects. TOAD offers the Favorites tab so that you can easily and quickly work in this manner. It can be a huge productivity enhancer. You can group your DB objects by different folders. Once you created your folders, you need to browse to your favorite DB object, rigth click on it and select "Add to SB Favorites List"
Procedure Editor Window and Debug Menu (Profiling, Debugger)First of all, some concepts:
Breakpointsare a pre-determined, unconditional stopping point. Code is executed and processing is stopped when this line of code is encountered. The rough equivalent of this would be to put a "pause" statement into your code. Using a symbolic debugger, you’re able to examine and change variable content as needed for your test.
Conditional breakpointsare similar to breakpoints, but they’re based on the content of a variable. When the condition is met, code processing is suspended at the point where this breakpoint is inserted.
Watchesdisplay the contents of variables while processing is occurring and when processing is stopped via a breakpoint or when the routine comes to an end.
Debugger Shortcut Keys
|Set or Delete a Breakpoint on the current line||SHIFT + F5|
|Add watch at cursor||CTRL + F5|
|Trace Into||SHIFT + F7|
|Step Over||SHIFT + F8|
|Display mouse right-click popup menu ||F10 |
|Trace Out||SHIFT + F10|
|Execute Current Source||SHIFT + F9|
|Set Parameters||SHIFT + CTRL + F9|
|Run (continue execution)||F11|
|Run to Cursor||F12|
|Display Breakpoints ||CTRL + ALT + B |
|Display DBMS Output ||CTRL + ALT + D |
|Evaluate/Modify ||CTRL + ALT + E |
|Display Watches ||CTRL + ALT + W |
- You can easily create the sentence for DBMS_OUTPUT with just a right click. Step over the variable to monitor, then
Right Clickand select
Output Statement - Make Output Statement, then go to the section that you want to show and press CTRL+V
- Find Closing Block
- Comment/Uncomment Code
The Toolbar for Debugger
From left to right:
•Thelightning boltis theRunbutton. This button will execute the procedure in debug mode.Execution will only stop if a breakpoint is encountered or if an exception is called. You can hit this button at any point during execution. The program will start up from where it left off and go until it reaches the end of your program, another breakpoint, or an exception.
(...) parameterallows you to set any
INvariables to the package and/or procedure.
• Next is the
Step Over button, which allows you to
walk through the PL/SQLcode one line at a time. This button will skip any procedures that are called. The first time you hit the button; the program will execute and stop on the first line of code.If you hit the button again, Toad will go to the next executable line of code.
fourth buttonis the
Trace Intobutton. Both this button and the Step Over button
walk through PL/SQLcode one line at a time, but Trace Into will enter any called procedures or functions and execute that code one line at a time. Right before your debug session starts, Toad will prompt you to compile ‘referenced objects’ with debug information.This will only come into play if you want to ‘Trace Into’ referenced objects inside your PL/SQL program.If you do not plan on doing this, then you can say ‘No.’ If you do decline this option, you will not be able to debug by using ‘Trace Into.’
• Next is the
Trace Out button; it
exits Trace Intocode.
last buttonis the
Run to Cursor button.
Place your keyboard cursor on the line of code you are interested in. Hit this button, and Toad will run to that line and stop. Be sure to have your cursor on an executable line, or this will not work.
hand buttonis the
Halt Execution key, and it will
stop executionof the PL/SQL routine retaining watch and breakpoint settings.
Example to Debug your codeLet's say that you have a procedure already created like this:
CREATE OR REPLACE PROCEDURE case2_proc (p1 IN OUT NUMBER) AS
n := p1;
p1 := n + 1;
Step 1:Load it into your editor.
If your program has an input parameter, then you need to supply the value, unless it has a program-supplied default value. If you are debugging a trigger, then you need to supply the column values necessary for building an INSERT/UPDATE/DELETE statement that will in turn cause your trigger to fire. If you do not specify the input values beforehand, Toad will open this window automatically when you attempt to debug or execute.
So (for this case) you will click the
button and you will get a window like thhe following.
Let’s take a few minutes to discuss each of the numbered areas below:
Procedure.The program to be executed.If you have a Package Body opened, you will see multiple functions and procedures listed here.Select the one you want executed/debugged.Ensure the code seen in area 3 executes the code you want to deal with.
Arguments.Your program may require several input parameters.Supply the values here.You’ll see the code in area 3 updated to reflect the values you supply in this area.If you are working with a DML trigger, you’ll see a list of columns for the affected table here.Supply values to help build a WHERE clause for area 3.
Code.Toad will build an anonymous block to cause your program to be called.If you are debugging or executing a DML trigger, you’ll see an UPDATE/INSERT/DELETE here instead of the PL/SQL call.
Transaction Control.At the end of the anonymous block, Toad supplies a COMMIT or ROLLBACK command.The default is COMMIT.This can be configured at View > Options > Debugger > Transaction Control.There is an option for ‘Prompt’.This will cause Toad to ask you each time you execute if you want to Rollback for Commit.
Toolbar.From here you can optionally enable the Profiler, or capture REFCURSOR and/or Collection values for display after execution.
The information you supply here will be saved for future executions. It should also be noted that the ‘Code’ section can be manually coded by the user, so you can add any additional code you want here. Just be sure to have a call to your program so the debugger will work.
Step 3:Then you can set Breakpoints (optional step)
A breakpoint is an instruction to Oracle to pause execution when it reaches a certain line of code. PL/SQL is interpreted at run-time. When Oracle sees a line of code with an attached breakpoint, it will stop execution to allow a debugger to take control. It should be noted that Oracle does not interpret every line of code as an ‘executable’ line. Only lines of code that are actually ‘executed’ will have their breakpoints honored. Toad will tell you ahead of time, what lines you can add breakpoints to by placing a blue dot in the gutter. Clicking in the gutter will add a breakpoint to the line. Clicking on it again will remove the break point. In the following example, I clicked on Line #7 to add a breakpoint.
The Breakpoints TabThe Editor has several output panels that assist with debugging. You will want to enable the ‘Breakpoints’ tab. You can do this by mouse-right-clicking on the editor output tabs and selecting ‘Desktop Panels > Breakpoints'.
The ‘Breakpoints’ tab will display the lines that have breakpoints attached.You can add new breakpoints here, disable existing ones, or remove them altogether.You can also optionally add breakpoint ‘conditions’.A conditional breakpoint means that a line will only stop execution if a particular condition is present.Let us take a moment to dive into this topic a bit deeper.
When you open the ‘Breakpoint Properties’ window using the ‘Edit Breakpoint’ button, you’ll be able to supply a condition and/or pass count. A condition can be any expression that Oracle can evaluate. Each time this line is executed, the expression will be evaluated first, and if it evaluates to ‘True’, then the program will break. For the pass count, Oracle will keep track of the number of times the line has been executed. It will only break on on a particular execution. This is extremely useful when working with LOOPs and when you want to see what happens on a particular iteration, e.g. the 340th time a particular line is executed. Setting the pass count will save the time it will take to manually step over the code 339 times.
Notes on Breakpoints:
- Breakpoints will be saved for future executions.
- You can manage breakpoint during execution.
Step 4:Using Watches to interact with PL/SQL Variables
While executing your code, you will probably want to see what a particular variable is set to, or you may even want to change its value artificially to step through some ‘what-if’ scenarios. You will want to activate the ‘Watches’ tab to do this kind of work.
The ‘Watches’ tab is broken up into 3 sections:
- ‘Smart Watches.’A newer feature available in version 9, this panel will automatically parse the code for all of the variables and show you their values.This is a ‘quick-n-dirty’ look at your variables.If you want to do anything other than ‘look’, you’ll need to manually add a watch.
- ‘Watches.’These are the variables or expressions you want to inspect during run-time.You can add these by dragging and dropping from the ‘Smart Watches’ area, or you can manually add by using the ‘Add Watch’ toolbar button.You’ll see that ‘x’ is showing as ‘NULL.’This is because line #7 has not actually executed yet.If you were to step over to the next line, ‘x’ would then show a value of ‘1234.’
- Toolbar.What if you want to see what happens in your code if ‘x’ were equal to something else?You can artificially alter the value of ‘x’ using the debugger.
‘Edit Watch.’ Clicking this button allows us to modify the properties of the watch. We’ll look at that in a second.
‘Evaluate/Modify Watch.’This button allows us to change the value of the variable inside the execution of the program.Let’s take a look at this in detail.
“Expression.” – this is the value that we are inspecting with the watch. “New Value.” – type in the new value you wan to assign to the expression selected in area #1. “Modify” – hit this button to send the value inputted in area #2. “Result” – you’ll see the updated value here. The new value will persist until the program is finished executing or until it changes based on a future line of code or until you modify it manually.
Using the properties of a watch will allow you to do some really cool things while debugging. Let’s finish our discussion by looking at these advanced features.
- ‘Expression.’This is the value that Toad will ‘watch’ for you.You can select any PL/SQL variable, or any expression of a PL/SQL variable.You could watch ‘X’ or ‘X+100.’
- ‘Break on value change.’Why are we debugging this code?Maybe you want to know why your program returns a value of 5 versus 15.Maybe you don’t know WHERE the program is misbehaving, but you do know that at some point, your variable gets set incorrectly.You can use the ‘Any value change’ watch break condition to tell Toad to stop execution.This will break the program based on a variable’s value changing, versus based on a particular line number.You could also choose a particular condition.So, instead of stepping through 4,000 lines of code waiting for ‘X’ to change, you can just set a breakpoint via the watch and have Toad stop execution automatically when your variable is updated.This can be a HUGE timesaver.
Notes on Watches:
- You can see the value of a variable without using the ‘Watches’ tab.Just place your mouse cursor over the variable in the editor, and Toad will respond by showing the value.
- Complex data types are generally not supported by Oracle’s debugger.For example, if you want to watch a REFCURSOR, you will need to declare a local variable in your code for each attribute of the cursor you want to watch.
- If you do not compile with debug, watches will not work.
Execution ProfilingToad allows you to record your PL/SQL execution to determine where any execution bottlenecks are occurring, e.g. Why is my program taking so long to run?‟
What do I need to do to set up the PL/SQL Profiler?You first need to run Toad’s Server Side Objects Wizard which is located under the Database – Administer menu group. This will install 3 tables into a schema of your choice then verify that you have access to Oracle’s SYS.DBMS_PROFILER Package. If this Package is not installed, you need to do the following:
- Login to Oracle through Toad as SYS.
- Load the $ORACLE_HOME\RDBMS\ADMIN\PROFLOAD.SQL script into the Editor.
- From the SQL Editor menu, select Execute as Script (or press F5).
- Make sure that GRANT EXECUTE on the DBMS_PROFILE package has been granted to PUBLIC or to the users that will use the profiling feature.
3- Then execute your PL/SQL program from the Editor or execute the Procedure/Package/Function from the Schema Browser or the Procedure Editor using the Execute (lightning bolt) button. You may be prompted to enter a value for your variables and a description of the procedure being executed ("TOAD, Diego Run 1 - 12/19/2010"). This will show up in the Profile Analysis window when you analyze the results. You could run the procedure several times to get some data into the profiling tables.
4- After it is finished, pull up the record from the "Profiler" tab (Database / Optimize / Profiler Analysis ).
How do I view the collected data and what do I do with it?The bottom half of the window lists the runs (called Anonymous Block, that is executed 3 times), Procedure, Timestamp, Comment, and Total Time to execute. You can sort on the columns by clicking on the column headers. The upper half is a graphical representation showing the percentage of time required to run each component of the Procedure. Between the top half of this window and the bottom half is the analysis toolbar which lets you alter the graph characteristics, navigate between the levels or open the Editor. With version 9.6 and higher, you can now automatically pull up the visual chart representations of your PL/SQL runs by clicking on the "Details" button and quickly determine which line of code is taking the most time to run. Toggle on the ‘Details’ view to see a graphical representation
Some notes on DebuggerIf you have OUT or IN OUT arguments in your procedure, you can elect to view their values during debugging in the Debug DBMS Output window. To accomplish this, check the “Output OUT Args” check box on the “Execute Procedure/Package/Trigger” dialog. TOAD will automatically add DBMS_OUTPUT. Put_Line statements at the end of the anonymous PL/SQL block used to invoke your procedure.
Turn on the Debug DBMS Output window from the “Debug” menu, or press CTRL + ALT +D
Execute a stored procedure or function and display the result in TOADNavigate to the sp or function in schema browser and highlight it. There is a thunder button "Execute Procedure" up there. Click on it to bring up the dialog where you can provide input parameters. To see output, you need to do things. - First, open a output window by View > DBMS Ouput. - Second, in the execute dialog, click "Output Options" link and check "Print OUT arguments/RETURN values to DBMS Output". Now the result will be disaplyed in the DBMS Ouput window after you click OK to execute the sp or function.
DebuggerA developer's best friend for figuring out why your program isn't behaving as expected. Run your program line-by-line, by "stepping" through the code.
Set Breakpoints: To set execution 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 “Break and Watch view window.” Make sure to only add breakpoints that are "executable". These are marked by little blue dots in the editor gutter for each line. Non-executable lines will not have their breakpoints honored by Oracle.
Add WatchTo add a watch on a variable, leave the cursor blinking on the variable on which the watch is desired and click the eyeglasses icon. New for v9.5, you can see the values of all of your code variables by using the "Enable Smart Watches" feature.
c) Modifying Variable Values While Debugging
To artificially change the value of one of your PL/SQL variables, highlight the variable in the watch window and hit the calculator on the icon bar which, once moused over will read “Evaluate/Modify Watch”
Setting breakpoints/visualizing variable contentsBreakpoints suspend the execution of the code. This allows you to view the contents of various variables. You could set the breakpoint by simply clicking to the left of the Procedure Window. Notice that the Break Points tab at the bottom shows the line location of the breakpoint. To work this code, click where the breakpoint is desired and then press the Run button. This will invoke the debugging routine and suspend code execution when the breakpoint is reached. After the code is suspended, you can check the contents of variables and implicit cursors. The line with the breakpoint set is now highlighted in blue. Simply place the mouse cursor over a variable or cursor and the contents will pop up after a second or so.
WatchesA watch in TOAD allows you to watch the changes to variables as the code is executed. You can also change the contents of a “watched” variable. Watches are easy to set. You can highlight the variable and press the Add Watch button on the Watch tab (the Ins key is the TOAD hot key).
You can also right-click on the highlighted variable and selectDebug | Add Watch at Cursor .Any of these options will add the variable to the Watch tab. You can also modify the value of a variable that is watched to force a special situation.
Conditional breakpointsTOAD allows for breakpoints to occur when the process has performed certain events (such as looping x number of times) or when a certain data condition exists.
This is easily accomplished by first setting the breakpoint. Click to the left of the Procedure Window, and then right-click and selectDebug | Set Breakpoint ). Then, go to the Break Points tab, highlight the breakpoint for which you want to define a condition, and press the Edit Breakpoint button (the leftmost button).
Debug ScriptsToad also offer you the option of Debugging Scripts. To perform this action, you just need to load your script on the editor, for example:
set pagesize 0
set feedback off
set term off
select 'alter table '||owner||'.'||table_name||' disable constraint
where constraint_type = 'R'
and status = 'ENABLED';
set term on
set feedback on
set echo on
Then from the Menu selectDebug / Script Debugger .After that you’ll see how the editor’s toolbar icons for debugging are now enabled. So now I can set a break on line 8, run the script to that point, and examine my variables under the Environment Section / User Variables (near the buttom).
Update on Toad v10.5
Now with a simple button click, Toad will insert the output commands for you throughout the program for all of your expressions. So debugging can now work more like this:
- Program isn’t doing what it should be
- Have Toad add the DBMS_OUTPUT lines
- Execute your program
- Review the DBMS_OUTPUT panel
- Double-click on the offending output value, Toad will navigate to the appropriate line of source code
- Repeat as necessary.
- When finished, have Toad remove the DBMS_OUTPUT lines
The code is inserted throughout your program for the following conditions:
- each program entry point,
- each assignment,
- calls to other PLSQL excluding calls to dbms_output, and
- SELECT INTO var.
You’ll notice a new tab is available, labeled ‘Auto Debugger XXXXXXXX’. Your native DBMS_OUTPUT code statements will appear in the ‘Default’ tab. The code that was added artificially via Toad will appear in the new ‘Auto Debugger’ tab. As you inspect your output, you can
auto-jump to the line of code that generated the value by double-clicking on the line.
A block is also inserted before all program exit points including the final END and any RETURN statements.
Enhanced Code Refactoring (All Toad Editions) Code refactoring is the process of improving the design of the code without affecting its overall behavior. There are a number of methods for achieving this, of which Extract Procedure is one (introduced in Toad 9.7).
In Toad v10.5, all refactoring methods are now presented from the Editorright-clickmenu and have their own group calledRefactor , and have two new additions:Find Unused VariablesandRename Identifier . Find Unused Variables locates variables which are no longer used in your code. Rename Identifier will rename all occurrences of a selected identifier within the same scope. So in summary these are all the options of Refactor:
Refactoring is the ability to alter code quickly and conveniently. To access Code Refactoring options, right-click code in the Editor and select Refactor. Toad 10.5 includes the following refactoring options:
- Extract Procedure—Create a new PL/SQL object from the selected text. The original code (where the selection was made) is modified to call the new procedure.
- Comment/Uncomment Block—Turn the selected text into a comment or removes the comment markers if they exist.
- Find Unused Variables—Find unused variables and identifiers in PL/SQL, and then jump to the occurrence in the Editor.
- Rename Identifiers—Rename identifiers (variables, parameters, or PL/SQL calls) for PL/SQL in the Editor.
SQL Editor Window
|Windows Help File||F1 |
|Toggle Output Window Figure||F2 |
|Toggle Data Grid Window||Shift + F2|
|Find Next Occurrence||F3|
|Find Previous Occurrence||Shift + F3|
|Describe Table, View, Procedure, Function. Just put the cursor over the object||F4|
|Execute SQL as a script||F5|
|Toggle between SQL Editor and Results Panel||F6|
|Clear All Text||F7 |
|Recall Previous SQL Statement||F8|
|Display Previous / Next Statement||Alt + Up / Down|
|Set Code Execution Parameters||Ctrl + F9|
|Execute Current SQL statement at Cursor||Shift + F9|
|Pop-up Menu||F10 or right-click|
|External Editor, Pass Contents||Ctrl + F12|
|Execute Explain Plan on the Current Statement||Ctrl + E|
|Goto Line||Ctrl + G|
|Convert Text to Lowercase||Ctrl + L|
|Make Code Statement||Ctrl + M|
|Recall Named SQL Statement||Ctrl+N|
|Open a Text File||Ctrl + O|
|Strip Code Statement||Ctrl + P|
|Columns Drop-down (show objects owner by user) ||Ctrl + T|
|Alias Replacement||Shift + Ctrl + R|
|Columns Drop-Down no alias||Shift + Ctrl + T|
|Code Templates||Ctrl + Spacebar|
|Converts Text to Uppercase / Lowercase ||Ctrl + U / L |
|Display Pop-up List of Matching Table Names||Ctrl + .|
|Redo Last Undo||Shift + Ctrl + Z|
|In the data grid: goes to the top / End of the record set||Ctrl + Home / End |
|Cycles through the Collection of MDI Child Windows||Ctrl + Tab|
|Quick Describe anywhere in Toad to find objects and display their information in the Describe Objects window||CTRL + D|
SQL Editor Shortcuts1. If you have long column/table names and do not like typing them out each time, then you can use our auto complete feature. Type in a portion of the table or column name, for example “ow_cu”, and then press Cntrl + . (period). Toad will automatically identify the name of the object and fill in the rest of the word to read “ow_customers”. If there is more than one object that satisfies the string “ow_cu”, then Toad will bring up a list of all the possible objects and you can select from there. 2. Everyone should be aware of SQL Recall Feature (F8) which brings up previously run select statements. If you want to quickly list old statements that you ran minutes ago, then in the editor press Alt + Up or Down arrow key and it will toggle through your SQL Recall list. This saves you from opening up the Recall list and scrolling down to find the SQL you want. You can keep the focus in the editor and toggle through with this hot key. 3. If you are constantly mistyping words in the Editor, then let Toad fix your spelling errors automatically. Right click in the editor and select “Editing Options”. On this new option screen click on the “Auto Replace” button in the bottom right hand corner. Here you will see a list of the common misspelled words our user’s experience. If there are words you constantly misspell, then you can add them to the list by using the “Add” button. So, now in the editor if you mistype the word “select” as “seelct” … it will automatically fix itself once you hit the spacebar. 4. If you keep the Ctrl key pressed and click over a procedure/package name, that code will be opened on another TAB. If you do it over a TABLE, it will desc that table.
SQL RecallEveryone should be using SQL Recall Feature (F8) which brings up previously run select statements. Access previously executed SQL by selecting F8. This will put a “Slide in slide out” SQL recall button on the left side of the Editor. The user can filter the list by adding to “Personal SQLs” or take it a step further by adding to “Named SQL” both of which are found under the SQL Editor menu option. To select Personal and Named SQL, right click the statement and select “Change to Personal” or “Change to Named.” a. Named SQL Giving your SQL Statements a name allows you to recall your query by context versus obscure SQL you may have wrote 6 months ago. If you spend more than 30 seconds writing a SQL statement, give it a name. You can now recall w/o using the F8 dialog giving you more room to type in the editor. Use
b. Quick Browse You can cycle through your list of previously written SQL statements using+
SQL Function TemplatesFrom the VIEW menu select the CODE SNIPPETS this will invoke a sliding/dockable window displaying code templates. 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 dockable window
Code TemplatesYou can customize the pre-written code templates by right-clicking in the Editor and choosing “Editing Options.” From there you will default into the behavior portion of the Editor options. From the Language area, choose edit, and you will see a number of templates to edit. Select Templates to see, add and edit you templates. Toad provides many templates but the user can also add their own templates from here. To access the Templates from the Editor simultaneously press
Code InsightToad can help you write your SQL and/or PL/SQL statements. Code Insight (
Also added in version 10 of Toad is the possibility of get variables!!! You need to enable this feature. In the main Toad Options window, located underEditor | Code Assist | Toad Insight Objects , checking the Expected Tokens or/and Available Variables/Parameters box will enable the automatic display of a list.Code Insight can now be initiated by
Explain PlanThis option is very helpful to identify the access path of a SQL Statement.
You can get the Explain Plan from theSQL Editor , theDatabase | Monitor | SGA Trace Menuoption (for Items in memory) or from theSession Browse r.Once the plan is generated you will get a menu like this:
Another nice feature in Version 10 is that now you can select any rectangular area within your data grid (instead of being forced to select the whole data grid, or only certain rows). This allows you to copy just a portion of your data results to the clipboard and then paste it into a spreadsheet or any other tool. You can also show and hide columns much more easily now using a quickselect drop-down menu at the top left of the data grid. When you click it, all of the columns in your data set are displayed in a menu, with a check box next to each column. Simply check or un-check a column in this menu to show or hide that column in your result set. This is a much quicker and easier method than in earlier versions of Toad.
Grids can be grouped by a column header by dragging the header into the Group By area at the top of the grid. This feature can be toggled on and of with aright-click menu / Appearance / Toggle Group Panel .
In addition to this information, there is also an online video for all these features.The right-click menu is also simpler and the ‘Save As’ feature has been renamed as ‘Export Dataset,’ which is more meaningful;
Split EditorNew for v10.6! If you are working on an extremely large block of code and want to view/edit multiple sections simultaneously, mouse-right-click in the editor panel and choose "Split Editor Layout.? You can use either a "Top/Bottom" or "Left/Right" theme.
Data Subset Wizard (Tools Menu)This window lets you copy a portion of data from one schema to another while maintaining referential integrity, so that you can work with a smaller set of data. The wizard creates a script that will copy a specified percentage of data beginning with all parent tables or from all tables with no constraints. You can specify a minimum number of rows. The wizard then continues with tables that have foreign key constraints, the rows copied are those whose parent rows have been copied into the parent tables. The wizard consists of four screens which allow you to set options as follows: Screen 1: Select source and target connections/schemas and specify where to save the script. Screen 2: Select Objects to create in the script. Screen 3: Set up the commands in and around the insert statements. Screen 4: Set up any extents or tablespaces when the wizard is set to create objects.
Using Auto ReplaceToad allows easily add your own commands or give simple keystroke access to your commonly-used coding techniques. Auto Replace is used to correct typo’s and to save you time. To set up this option go to Edit -> Editor Options, Click on Auto Replace and you will see the existing Auto Replace options. I entered the following options: Sf select * from Pl DBMS_OUTPUT.PUTLINE So if I type pl followed by the space bar, it will be changed by DBMS_OUTPUT.PUTLINE
Project Manager TutorialMost IT professionals do not live exclusively in the Oracle database. Instead, they are using the power of the Oracle database to provide information to their custom web pages, applications, batch jobs, and much more. So someone using Toad to access the database is most likely also using spreadsheet, web development, word processing, and other applications. Toad's Project Manager provides a centralized area for the user to manage ALL of the items for a given application, task, or a project. This could be a collection of tables, stored procedures, database jobs, ftp folders, web pages, documents, etc. For example, I have a project labeled TOADSOFT. I can use the PM to manage everything I need
Here you can see my project for managing QUEST content. The QUEST project contains a node for the remote server where the site is hosted, a link to my Desktop and some files I need to frequently edit and find easily, Oracle tables I need to update for posting the Release Notes, URL's I need to keep an eye on, and a link to the folder where I keep a backup of all the web content. I also have another project I use to manage the Beta release. I can zip up the files for the beta and DRAG-N-DROP the file to the Web Server to easily FTP the file.
How do I Add Something to a Project?If you have already found your database object using Toad, you can easily add it to your project. You can mouse-right-click on the object in the Schema Browser and choose 'Add to Project Manager.' You can also add objects from Toad's Object Search dialog. It's important to remember that you can select MULTIPLE database objects and add them to a project simultaneously. If you have more than one project open, Toad will prompt you to choose the project you want to add the object(s) to. To see just how powerful the Project Manager really is, bring up the options dialog by pushing the options button on the PM window. In the PM, we can configure what happens on Double-Clicks, R-Clicks|PopUps, and Drag-N-Drops. Project Manager works in exactly the same way as the Schema Browser. In particular, the right-click context menu is identical, and you get the same display on the right if you select an item. You can also create a REAL Directory, and if you drag and drop a table name over there you’ll find an actual file with the DDL to create that table and all of its supporting structures
Action Palette, Application Designer or Automation Designer (like a macro)The Automation Designer in Toad is a great way to build the equivalent of batch files for Toad.
Open the Automation Designer window from theUtilities | Automation Designer .A database developer or database business analyst might need to do something in Toad related to the data content, and then repeat or schedule that task, instead of doing over and over again, you can use the Automation Designer. The Automation Designer feature allows Toad users to create “mini-applications” within the Automation Designer, and schedule these tasks. It also enables you to run applications and scripts against multiple connections with one stroke. In Automation Designer, you can now right-click an application and choose “Run with connections…” to bring up a connection box where you can specify which connections you want the app to run against. These powerful features will provide huge time savings for data professionals who need to run the same executions in multiple databases at the same time. The Automation Designer is broken into three main areas:
A. The Detailed Navigation pane(Left, upper side). When you have selected Apps in Area C, this pane collects ToadApps and actions into categories. You can drag and drop apps between categories, and actions between apps.
B. The Display pane(Right Side) This area displays any item selected in the navigation pane. If an app is selected, you can drag and drop to change the order of actions. Drag and drop an applicable action(A) onto another action(B) and it will become a child of action(B).
C. Main Navigation pane(left lower side).Select the button that best describes what you want to do:
Apps- Organize, create and run actions and apps from this section. Apps relate directly to action sets from Toad for Oracle 9.6.
Execution Log- lists of actions that have been executed and the status of the execution.
Scheduled Items- view actions and apps that you have scheduled using Toad or the Windows Task Manager.
Search- Find an action or an app within the Automation Designer.
As an example:
I open Toad Editor, enter my query, and press
Executeto make sure it works as intended. Then I
right-clickanywhere on the data grid and choose
Save Asfrom the context menu. On the Save as screen, I choose my options and parameters and click
OKto verify that it works correctly. Then I reopen the
Save Asdialog; this time I click the
camera icon at the bottom. A window opens which asks you for the new name of the Toad App you wish to save your task to (or an existing one if you have one) and the name of the Action itself. I can now choose to run or schedule that application. If I choose to run it, Toad is simply executing the application within my currently running instance of Toad. That is, behind the scenes, it will execute the query and perform a Save As, but I won’t see anything flash by; I’ll just see a run status indicator such as the green Completed bar. I can schedule that task to be performed periodically. So where can you do this? Basically on any screen that has the camera icon at the bottom, like: Compare Schemas, Compare Databases, FTP, DB Health Check, email, TNS Ping and many others.
Another example:Most of the users love to use the “Save As” function in the right click menu for the data grid. But sometimes the user has to run the same query numerous times and repeat the steps to save the data off to the file format of their choice, that is a good example of using the Action Palette.
Run thequeryin theEditor . And then right click on the data results and select“Save As” .
Continue to select your
file format, and any options you want. Also remember to
set a destination location for the new file.
Once completed, on the
top right hand corner of this window, click the little yellow lightening bolt icon.
This will prompt you to give this action a name. Please
provide a meaningful nameand hit OK.
View Menu, select the
Action Paletteoption. This will bring up the Toad Action window. Located in this window will be the new Action you just created. On the top of this window will be a “Schedule” icon underneath the green triangle icon. Click on this and you can schedule the data extraction. Just keep in mind that this is using Windows Task Scheduling to setup the job and you must have Windows up and logged in. You do not need to have Toad running in order for this to work. Now sit back and enjoy, while Toad automatically exports data for you to your desired location and in your desired file format. Good information over here:
Automation Designer allows you to save tasks you would normally perform interactively in Toad, from saving data to a file, comparing 2 schemas to running a database health check and simply selecting them, when the time comes, and executing them on-the-fly. For more information on this feature, please refer to this article published on Toad World.In Toad 10, you now have the capability to executed these tasks (or what we call Actions) or groups of tasks saved as a sequential workflow (or what we call Action Sets) against multiple database connections simultaneously.
Here, you can see an Action Set in the right panel with a selection of tasks which can be executed sequentially, such as Export DDL, Schema reports, script execution, etc.
If I right-click an App (Action Set), I can select which database connections I want to run these tasks against. The Connections window opens and I can multi-select which ones to use. I can even call Toad from the command line and execute Action Sets remotely or programmatically.
Action ConsoleWhat is the action console? Simply put, anywhere you see or work with an Oracle object in Toad, simply do a mouse-right click on it and the Action Console will give you Toad's FULL arsenal of action items for that object. ALL the power of Toad in a simple RightClick: Add Column, Constraint, Indexes, Analyze Object, Truncate and many more!!!
Object and Schema ComparePerhaps one of the most popular Toad features for developers and DBAs is Schema Compare and Sync. With the DB Admin Module, you can also use schema definition files and perform synchronizations. Schema Compare & Sync have been made actionable, and are available from the Automation Designer, or from the Window Snapshot button. There are two ways to perform a Schema Compare using Toad: 1. Using information stored in the Oracle Data Dictionary 2. Using native Toad Schema Definition files. The first method uses “brute force” to obtain the DDL information pertaining to every object in each compared schema. The second method uses what are called Schema Definition (or schema snapshot) files. These files are encrypted, compressed files which contain all the information about every object in a schema at a particular point-in-time. The real beauty of using these files is that, not only are they an accurate record of what a schema looks like at a particular point-in-time, but when used to perform a schema compare using Toad, they take a fraction of the amount of time to process. What would take minutes to process using a data dictionary compare only takes seconds using this method. In addition, Schema Compare has been expanded for easier use. You can now add multiple target schemas to compare, while still being able to switch between two schemas. In addition, you can use either a live connection to a database which loads all DDL information as you go, or schema definition files. These files are encrypted, compressed files containing all the information about every object in a schema at a particular point in time. You may want to use a schema definition file to run a time-sensitive comparison, but something additional is that when they are used for a schema compare, they take much less time to process.
Statspack and AWR BrowserThe Toad StatsPack Browser (DBA module required) takes beginning and ending snapshots of database performance statistics and then offers graphical trending and time-series analyses based on those stats. The result is a gallery of useful charts for interpreting database performance.
It also contains advisories. When two snapshots are selected (not checked, but selected), the Advice tab will give some info about the top wait event during the selected interval. Another way to display the advice is to double-click blue underlined waits in the top waits grid.
Data Import and ExportOne of the most common development and test database tasks is to refresh data for users to run their code against. For this, many developers use the Oracle import and export utilities. But you really cannot see into a dump file, other than to ask for a table of contents.
Toad solves this problem with the Export File Browser, which is available by selectingDatabase | Export | Export File Browser.Once you oopen your dmp file, I can view the contents of the dump file via the navigator tree view on the left side. I can also see the DDL for that object and its data under the tabs on the right side as well, so I can cut and paste the DDL to make sure the objects exist before the import. Another handy tip: I can also see the data in a standard Toad data grid. This means I get all my right-click menu options such as Save As, Print, and Row Count. Note that the Export File Browser must access the dump file to perform its job, so the file must reside on your PC (remember, Toad has a built-in FTP tool).
Trace File BrowserOracle trace files have historically been difficult to examine, you needed to be an "expert" on TKPROF. The Trace File Browser provides a way to display this information in a manner that is easy to read and easy to navigate so that problem areas can be quickly isolated.
The new Trace File Browser (under Database | Diagnose | Trace File Browser ) provides far more information than was previously possible. In addition, after you have browsed a Trace File, a Benchmark Factory project file can be pushed to Benchmark Factory for workload replay.As of version 9.7, you can now visually inspect the contents of your Trace files. See immediately all of the queries captured with their binds, waits, and performance profiles.
Code ExpertWhat if you’re a DBA trying to track down a performance problem somewhere in the millions of lines of PL/SQL code in your database? Toad has a batch mode interface in Code Xpert designed for that very purpose. It scans the code and highlights line that needs attention—finding the proverbial needle in the haystack for you.
Just SelectDatabase | Diagnose | Code Expert, Load your objects, select the ones that you want to Review and you are ready to go
Click on a query to see the binds variables and the values passed for execution.
Toad will show the number of queries that run under specific amounts of time. Drill down to an individual statement to see the Execution vs Parse vs Fetch vs Wait times so you know EXACTLY how to approach a tuning scenario.
You can access this Mudule by selecting from the Menu:
Database > Monitor > Database BrowserThere have been some major enhancements to the Database Browser. You can now use it as a central point for your database administration. As before, you can navigate to every database simultaneously, and drill down into any of them to administer database and schema objects. Now you can right-click on a database node and perform a number of additional administration operations. You can go directly to the: * Top Session Browser for the selected database. * Session Browser for the selected database. * DB Health Check on the selected database. * AWR Browser * Generate RMAN Scripts * And many more...
RMAN Script TemplatesOracle RMAN scripts can be challenging and time consuming, especially for less experienced DBAs and they wanted an easier way to create and maintain scripts. Toad 9.7 supplies basic scripts that can be copied, edited and stored for re-use. Also, Toad variables can be embedded into the RMAN script.
You can edit existing RMAN scripts and add and delete scripts to and from the scripts list from theOptions | RMAN Templatespage.
You can then generate RMAN scripts from the
Database Browser. 1. From the Database Browser, select the database where you want to execute the script. 2. Right-click and select Generate RMAN Script and select the script you want to generate 3. Enter any required variables and click OK. The script will open in the Editor window, where you can then save or execute it via F9. Executing will open and run RMAN outside of Toad, and then close independently.
Since Toad 9.7 the Database Browser has an addition menu named “Generate RMAN Script”. From this menu you can call several predefined scripts and you can add your own scripts as necessary. The “Basic Script to setup RMAN for backups“ allows you to set the configuration parameters. Calling this script opens an additional parameter window to set or change some basic variables like log file location, backup location, etc.
Data GeneratorData generation is implemented in the following locations: * Schema Browser Left Hand Side (Object Panel) - right-click and select Generate Data. * ER Diagram - Click the Data Generation button: * Create Table window- Click the Generate Data tab. In particular, you can specify: * The number of records to create * How to handle constraints * How to handle Referential Integrity * How to commit your work You can use Toad to generate test data for your database. You can generate data for multiple tables at one time or for single tables. This feature is a Toad Action, which means that you can save it and schedule to run. Toad now has built-in generators that let you quickly add test data to common fields like business names, addresses and personal information like first and last names. You can also select from your own random data, through either a constant list or a custom Select statement. You can specify a foreign key column in a reference table to simulate data that’s linked to another table. Finally, Toad now gives you the option to select a custom procedure called TOAD_DATAGEN, in which you can create very specific instructions for generating your data.
You can access to this option with a simpleright clickover a table and select"Generate Data" . You can also use the Menu"Database | Import | Generate Data" .
There are several Options to consider here:
Random Data Engine
- Internal= Creates a script with "n" INSERT statements per table (depending on the individual table's settings). An option is also available to commit every "x" rows. This is the most basic data generation option, but it does not require you to install any packages on the database server. Note: This will create a very large SQL script, which will take longer to execute.
- DBMS_RANDOM = Uses Oracle's DBMS_RANDOM package to generate data. The package can generate Unicode data. It must be installed on the database server for this option to be available.
- TOAD_DATAGEN= Uses Toad's package to generate data on the server. This option generates a much smaller SQL script, performs faster, and supports real-life data sets.Note: You will need to install the TOAD_DATAGEN package through Server Side Object Wiza