Transcription
Toad for Oracle 12.8Guide to Using Toad
Copyright 2015 Dell Inc. All rights reserved.This guide contains proprietary information protected by copyright. The software described inthis guide is furnished under a software license or nondisclosure agreement. This software may beused or copied only in accordance with the terms of the applicable agreement. No part of thisguide may be reproduced or transmitted in any form or by any means, electronic or mechanical,including photocopying and recording for any purpose other than the purchaser’s personal usewithout the written permission of Dell Software Inc.The information in this document is provided in connection with Dell Software products. Nolicense, express or implied, by estoppel or otherwise, to any intellectual property right isgranted by this document or in connection with the sale of Dell Software products. EXCEPTAS SET FORTH IN DELL SOFTWARE’S TERMS AND CONDITIONS AS SPECIFIED INTHE LICENSE AGREEMENT FOR THIS PRODUCT, DELL SOFTWARE ASSUMES NOLIABILITY WHATSOEVER AND DISCLAIMS ANY EXPRESS, IMPLIED OR STATUTORYWARRANTY RELATING TO ITS PRODUCTS INCLUDING, BUT NOT LIMITED TO, THEIMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULARPURPOSE, OR NON-INFRINGEMENT. IN NO EVENT SHALL DELL BE LIABLE FORANY DIRECT, INDIRECT, CONSEQUENTIAL, PUNITIVE, SPECIAL OR INCIDENTALDAMAGES (INCLUDING, WITHOUT LIMITATION, DAMAGES FOR LOSS OF PROFITS,BUSINESS INTERRUPTION OR LOSS OF INFORMATION) ARISING OUT OF THE USEOR INABILITY TO USE THIS DOCUMENT, EVEN IF DELL SOFTWARE HAS BEENADVISED OF THE POSSIBILITY OF SUCH DAMAGES. Dell Software makes norepresentations or warranties with respect to the accuracy or completeness of the contents of thisdocument and reserves the right to make changes to specifications and product descriptions atany time without notice. Dell Software does not make any commitment to update theinformation contained in this document.If you have any questions regarding your potential use of this material, contact:Dell Software Inc.Attn: LEGAL Dept5 Polaris WayAliso Viejo, CA 92656Refer to our web site (www.software.dell.com) for regional and international office information.Dell, the Dell logo, and Dell Knowledge Xpert, Dell vWorkSpace, Dell Toad, T.O.A.D., ToadWorld, Toad for Oracle, SQL Optimizer for Oracle, Code Tester for Oracle, Spotlight on Oracle,Benchmark Factory, and Dell Backup Reporter for Oracle are trademarks of Dell Inc. and/or itsaffiliates. Other trademarks and trade names may be used in this document to refer to either theentities claiming the marks and names or their products. Dell disclaims any proprietary interest inthe marks and names of others.
Toad for OracleInstallation GuideUpdated - August 2015Version - 12.8
ContentsChapter 1: Getting Started14Welcome to Toad14About This Guide15Help and Resources15Toad Advisor16Jump Search16Additional Toad Resources18Customize Toad19Use Workspaces19Customize Schema Drop-Downs20Customize Toolbars and Menus20Reset Default Toolbars and Menus23Customize Shortcut Keys23Popular Shortcut Keys24Toad Insight Pick List Shortcuts26Print List of Shortcut Keys27Create and Manage Connections28About Database Connections28Create New Connections29Import/Export Connection Settings32Automatically Connect on Startup33Use Previous Connections33Change Active Connection in Window34Save Connection Passwords34
Beginner's Guide to Using Toad5Table of ContentsCommit or Rollback Changes35End Connections35Test Connections36Display Connection and Window Bars36Color Code the User Interface per Connection37Manage Oracle Homes38Work with the Editor40About the Editor40Important Editor Settings41Customize the Editor Layout42Split the Editor Layout43Work with Code44Execute Single Statements44Execute Scripts in the Editor44Execute Scripts with Toad Script Runner45Change Current Schema46Format Code47Auto-complete Names47Extract Procedures48Comment Out Code Block48Find Unused Variables49Rename Identifiers49Debug PL/SQL49Compile with Debug Information50Start Debugging51
Beginner's Guide to Using Toad6Table of ContentsView DBMS Output52Debug External Sessions52Work with Database Objects54About the Schema Browser54Customize the Schema Browser55Helpful Features58Automatically Refresh the Schema Browser Data Grid60Filter Schema Browser Content60Object Search61Use Query Templates63Work with Data Grids64Work with Editable Resultsets64Sort and Group Data65View a Single Record65Filter Data66Edit Data in Popup Editor66Use an External Editor67Access the Calculator67Perform Calculations on Grid Cells67Anchor Column in Data Grid68Preview Selected Column68Hide Columns68BLOB Editor69Date Editor69View BFILE data70
Beginner's Guide to Using Toad7Table of ContentsView Nested Table Data70View VARRAY Data70View Object Data70View CURSORs71Online Options71MyToad71Toad World Repository72Sync User Files76Chapter 2: Toad Without CodeManage Objects7777Create Objects77Copy Objects to Another Schema78Create new objects from existing ones79Drop (Delete) Objects79Flashback Tables (Recover Dropped Tables)79Rebuild Multiple Indexes, Tables, or LOB Segments80Rebuild an Index82Rebuild a Table82Copy Data to Another Schema83View and Modify Object Privileges84Configure Grantees84Use the Master Detail Browser84Master/Detail Browser85Generating XML Output85Selecting the Master Object86
Beginner's Guide to Using Toad8Table of ContentsAdding Detail Datasets86Defining a Master/Detail Relationship87Import and Export Data88Export Dataset88Export Data to Flat File89Export with Data Pump90Import with Data Pump92Data Pump Job Manager93Export DDL93Viewing an Export File94Finding Information in an Export File94Open Export File Window95DB Compare Mode Overview96Reading the Compare Grid97Freeze the Compare Grid97Copy DDL to Clipboard98Saving DDL as a File98Extracting DDL from Multiple Nodes99Export Utility Wizard99Data Subset Wizard100Import Table Data103Import Utility Wizard104Compare Data, Objects, and More105Data Duplicates105Compare Single Objects106
Beginner's Guide to Using Toad9Table of ContentsCompare Databases107Compare Schemas109Compare Multiple Schemas110Compare Data113Reviewing Differences116Compare Files and Objects116Viewing File Differences117File Comparison Rules117Difference Viewer Options119Automate Processes and Manage Projects119Automation Designer Overview119Add Apps/Actions to Toolbar120Search the Automation Designer121Create New Actions121Using Action Recall122Action Parameter Files123Sharing Actions125Run Actions from the Automation Designer126Schedule a Task126Schedule Actions and Apps127Execute Actions from the Command Line128Project Manager Overview130Configuring the Project Manager131Adding Objects to Project Manager132Working with Server Directories and Files132
Beginner's Guide to Using Toad10Table of ContentsWorking with Local Files and Directories133Format Files133Checking Files for Syntax134File to FTP134Chapter 3: Toad and Your Code135Navigator135Navigator Options136Navigator options136Bookmarks137Moving Between Editor Tabs138Work with Statements and Scripts138Create and Manage Code Snippets139Code Templates Tab140Code Completion Templates141Auto Replace Substitutions143Code Folding144Wrap Utility145Results Grids145Describe (Parse) Selected Query146Strip Code Statement and Make Code Statement Functions146Creating Make Code Templates148Change Text to Upper or Lower Case149View Code Statistics149Swap This/Prev Lines150Find Closing Block150
Beginner's Guide to Using Toad11Table of ContentsVariables Window150Aliases150ALIASES.TXT file151Skip Aliases152Track Aliases152Save and Reuse SQL Statements152Save and Reuse SQL Statements152View Recently Executed SQL Statements153Import and Export Saved SQL154Work with Results156Auto Trace156Optimization157Row Numbers157Saving Toad Query Results158Time Values159Code Analysis159About Code Analysis159Analyze Code in the Editor160Perform Detailed Code Analysis161Create or Edit Rule Sets163Create, Clone, or Edit Code Analysis Rules163Archive Code Analysis Results for Reporting165Ignore Code Analysis Rules165Code Analysis Action166Work with PL/SQL167
Beginner's Guide to Using Toad12Table of ContentsCreate New PL/SQL Object167Using Templates169Load Database Object170Reload Object171SQL Tuning171About the Profilers172Additional DBMS Profiler Requirements173Additional Hierarchical Profiler Requirements173Profile PL/SQL173View Profiler Results174Editor Profiler Tab175Query Builder176Query Builder Overview177Query Builder Options178Removing columns from the Tree178Use the Query Builder178Model Area179Viewing Joins180Populating the Where Clause180Populating the Having Clause182Creating a Subquery183Reverse Engineering a Query184Generated Query185Query Results186
Beginner's Guide to Using Toad13Table of ContentsAbout Dell187Contacting Dell187Technical support resources187
Chapter 1: Getting StartedThis chapter introduces you to the basics of the Toad interface. Once you know your wayaround, you can more easily move on to use the many advanced features of Toad.Welcome to Toad14About This Guide15Help and Resources15Customize Toad19Create and Manage Connections28Work with the Editor40Work with Code44Debug PL/SQL49Work with Database Objects54Work with Data Grids64Online Options71Welcome to ToadToad for Oracle provides an intuitive and efficient way for database professionals of all skill andexperience levels to perform their jobs with an overall improvement in workflow effectivenessand productivity. With Toad for Oracle you can:lUnderstand your database environment through visual representationslMeet deadlines easily through automation and smooth workflowslPerform essential development and administration tasks from a single tool
Beginner's Guide to Using Toad15Chapter 1: Getting StartedlDeploy high-quality applications that meet user requirements; perform predictably andreliably in productionlValidate database code to ensure the best-possible performance and adherence to bestpractice standardslManage and share projects, templates, scripts, and more with easeThe Toad for Oracle solutions are built for you, by you. Over 10 years of development andfeedback from various communities like Toad World have made it the most powerful andfunctional tool available. With an installed-base of over two million, Toad for Oracle continuesto be the “de-facto” standard tool for database development and administration.About This GuideThis guide will help you to quickly start using Toad by learning basic features and tasks. Toad isa very diverse and powerful tool, hence there are features not covered. Refer to the online helpfor additional information about Toad, which you can access at any time by pressing F1.Many Toad for Oracle users access data via Toad to work with it in another environment. Othersextensively use the deeply robust features of Toad more fully. Both groups will find their needsmet in the logical organization of this guide:oGetting StartedoToad Without CodeoToad and Your Code.The following topics are not included in the guide. They will be covered in other content:oUsing the Toad Report ManageroSQL Optimizer.Meanwhile, please refer to online help and to our award-winning Support team’s knowledge baseand to Toad World for instructions on using those features.Help and ResourcesToad provides tools to help you find what you are looking for and get answers toyour questions.
Beginner's Guide to Using Toad16Chapter 1: Getting StartedToad AdvisorToad is self-diagnosing. If you are having difficulties with Toad that you cannot understand norfix, the Toad Advisor may be able to help you. It offers warnings, alerts, and hints concerningthe current state of your Toad installation. If you are in a managed environment, it specifieswhich features in Toad are managed, and to what extent.To use Toad Advisor1. Select Help Toad Advisor.2. Review the results, which are divided into the following categories:WarningsDescribe things that should be fixed immediatelyAlertsDescribe things that may have an impact upon Toad's functionalityHintsProvide information about your Toad installation that may affecthow Toad worksPerformanceDescribe settings that could be changed to improve speed ofsuggestionsperformanceTip: Select a result for additional information in the bottom pane. You can doubleclick the performance suggestions to navigate direction to the relevant Toad option.Jump SearchThe Jump Search feature enables you to quickly find features and functionality within the Toadinterface. Jump Search also gives you quick access to a wealth of information online, so that youcan learn from other users and get your questions answered quickly.Note: Toad also jumps from error dialogs to the Jump search where the error message is thencopied so you can quickly find answers.To use Jump SearchType a search term in the Jump to . search field on the main toolbar of Toad. You are presentedwith a list of categories from which to choose a jump-to link. The number of links is displayednext to the category heading. Double-click the heading to show all of the available links, if notinitially shown.
Beginner's Guide to Using Toad17Chapter 1: Getting StartedJump SearchcategoryWhat it doesWindowsGo directly to the feature in a window in the Toad interface.Try it: Jump Search for Code Snippets then go directly to the CodeSnippets window or the Code Snippets category menu.Menus &Go directly to the feature in the Toad menu or toolbar.ToolbarsTry it: Jump Search for Code Snippets then go directly to View CodeSnippets.OptionsGo directly to the Toad Options settings (View Toad Options) for thefeature.Try it: Jump Search for Code Snippets then go directly to that section onthe Code Assist page of the Editor options.If options do not exist in the Toad Options, this category is not shown inthe search results.Support PortalGo to Dell information sites.Try it: Jump Search for Code Snippets, then click Knowledge Articles,then go directly to articles about Code Snippets in the Dell SupportKnowledge Base.Toad WorldGo to related articles on the Toad World website.Try it: Jump Search for Code Snippets, then click the Toad Worldheader to expand the list of results. Choose from user forumconversations, knowledge articles, blog posts, and how-to videos.Note: Also searched (and listed if your search-term is found there) areyour files, Code Analysis rules, SQL Recall history, and Code Snippets.Toad HelpGo to search results for the feature in the Toad online help.DocumentationTry it: Jump Search for Code Snippets then select a topic from the searchresults.Search WebGo to internet search results in a web browser.Try it: Jump Search for Code Snippets then click Search Web to viewthe search results in your default web browser.To change the browser that Toad uses, View Toad Options Online Jump Search.
Beginner's Guide to Using Toad18Chapter 1: Getting StartedAdditional Toad ResourcesThere are many resources for you to learn more about Toad.ResourceDescriptionHelpfileProvides step-by-step directions on how to use Toad. Press F1 in anyToad window to open the help system to the relevant topic.KnowledgeXpert forOracleAn extensive Oracle technical resource with thousands of insightfulOracledocumentationOracle's database documentation. Since Toad is a tool to help youtopics and working examples.manage Oracle databases, the more you understand Oracle the moreintuitive Toad becomes.toadworld.comThe main website for all Toad products. Click Explore a Product Toadfor Oracle to go to resources that relate specifically to Toad for Oracle.Alternatively, go to Help Toad World from the Toad menu.Here are just a few of those resources:lForums and Blogs—Connect with thousands of other Toad usersto get help and learn from the experts.Tip: Customers often use common Toad acronyms in the forums.lIdea Pond—Submit ideas to improve Toad and vote on ideassubmitted by other customers.lToad for Oracle Wiki—An open wiki where you can share yourknowledge of Toad.lMedia—Free learning videos and apps.lDownloads—Download trial versions, beta trials, screen savers,ringtones, and more.Follow forum threadslTo receive emails for all posts, Forum Email Subscribe toForum.lTo follow a thread, select Email Subscribe to Replies.lTo unfollow, select Email Unsubscribe to Replies.
Beginner's Guide to Using Toad19Chapter 1: Getting StartedCustomize ToadToad provides many ways for you to arrange and customize the interface to help you work fasterUse WorkspacesYou can save your current Toad windows and connections as a Workspace. This allows you toquickly resume work after restarting Toad. It also allows you to save different Toadconfigurations and then switch easily back and forth between them.If you don’t see the Workspace toolbar, right-click in the main toolbar to select Workspaces, orselect Restore Defaults.To create a WorkspacelSet up your current working layout in Toad.lIn the Workspace toolbar, clickto name and save your Workspace.Your open windows and connections are saved and will be reestablished the next time you openthis Workspace.These components are saved:Schema Browser - The currently active database objects (type and name)Editor - The tab contents, the number of tabs, the last active tab, each tab's lineand caret position, and the split mode of the EditorOther windows - All other MDI (multiple document interface) style windows, forexample the Automation Designer, are managed with Workspaces. Non-MDIwindows, that is, docked windows such as the Project Manager and Team Coding,are retained across Workspaces since they likely represent an overall workingdesktop state you may need across Workspaces.To make changes to your Workspace configuration1. Make the change in the workspace.2. Clickin the Workspace toolbar.3. To keep the current name, reply Yes to the confirmation prompt. To save as a newworkspace, enter a name for the new workspace. Newly opened windows and connectionsare saved.
Beginner's Guide to Using Toad20Chapter 1: Getting StartedCustomize Schema Drop-DownsYou can customize schema drop-downs by creating a list of favorites, hiding schemas, setting thedefault schema for connections, and other options. Changes apply to allow windows with theschema drop-down, such as the Editor and Schema Browser.To set a default schema»Right-click the schema in the schema drop-down and select Set schema name toDefault Schema.To customize schema drop-downs1. Right-click the schema drop-down and select Customize.2. Select schemas to categorize and click the button.3. To hide schemas, select Hidden Schemas in the Category field for the schema.4. To create a new category, enter the category name in the Category field for the schema.The new name becomes available in the Category drop-down.5. To change when the schema is categorized, select the When to Categorize field for theschema and click.Customize Toolbars and MenusYou can configure Toad menu bars to adjust automatically to the way you work with Toad. Youcan also customize menus and toolbars yourself. This lets you arrange Toad to best reflect howyou want to work.Enable self-adjusting menusWhen you enable self-adjusting menus, Toad collects data on your work habits and then adjustsits menus accordingly. Toad moves your most used commands to the top of the menu and hidesthe commands that you rarely use.To have Toad menus configure themselves1. Right click in the toolbar and select Customize.2. On the Options tab of the Customize dialog, select Menus show recently usedcommands first.
Beginner's Guide to Using Toad21Chapter 1: Getting StartedConfigure toolbars and menusIn Toad, you can hide, add, change, delete, move, and rename toolbars and menus. Any changescan be undone later as needed.To show or hide toolbars1. Right-click the toolbar area.2. Select the toolbars you want to display, and clear the toolbars you want to hide.To customize a toolbar or menu1. Right click the toolbar o
ProfilePL/SQL 173 ViewProfilerResults 174 EditorProfilerTab 175 QueryBuilder 176 QueryBuilderOverview 177 . Toad provides tools to help you find what you are looking for and get answers to . Editor-Thet
