TOAD

GETTING STARTED GUIDE

This guide contains proprietary information, which is protected by copyright. The information in this guide is subject to change without notice and does not represent a commitment on the part of Quest Software. The software described in this guide is furnished under a license or nondisclosure agreement. This software may be used or copied only in accordance with the terms of this agreement. No part of this guide 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 purchasers personal use without the written permission of Quest Software, Inc.

2000 Quest Software, Inc. All Rights Reserved. Quest is a trademark of Quest Software, Inc. Quest Software, Inc. 8001 Irvine Center Drive Irvine, CA 92618 USA Tel. 949-754-8000 / Fax 949-754-8999

[email protected] [email protected] www.quest.com www.toadsoft.com

TOAD is a trademark of Quest Software, Inc. Other trademarks and registered trademarks used in this guide are the property of their respective owners.

Contents

C O N T E N T S

TOAD

Getting Started
About this Guide 5 For More Information......................................................................................................5 Introduction 7

TOAD Features 9 SQL Editor......................................................................................................................9 Schema Browser ..........................................................................................................11 Procedure Editor ..........................................................................................................12 Options Menu...............................................................................................................13 TOAD Help ...................................................................................................................14 Install/Uninstall and TOAD Files 15 System Requirements ..................................................................................................15 Install............................................................................................................................17 License Key..................................................................................................................22 TOAD files ....................................................................................................................23 Uninstall........................................................................................................................29 Troubleshooting 31 SQL*Net/Net8 Installation Problems ............................................................................31 Cannot connect to Personal Oracle .............................................................................32 Frequently Asked Questions ........................................................................................33 Registration and Upgrades 35 Trial Version Registration .............................................................................................35 Commercial Version Purchase and Registration..........................................................37 Updating TOAD Commercial Version...........................................................................38 Product authorization errors.........................................................................................39 TOAD add-on modules 41 TOAD PL/SQL Debugger .............................................................................................42 Quest Software SQLab Tuner/Xpert ............................................................................44 Quest Software SQL Impact.........................................................................................45

TOAD

Getting Started

RevealNet PL/Formatter ..............................................................................................46 RevealNet Knowledge Base ........................................................................................47 About Quest Software 48 Contacting Quest Software ..........................................................................................50 Technical Support ........................................................................................................50

1
About this Guide
This guide introduces TOAD. It helps you install and run TOAD. It also introduces you to some of the TOAD functions and optional modules. The guide covers the following major topics: system requirements install/uninstall registration troubleshooting TOAD files optional add-on modules

For More Information

This is an introductory guide. Refer to TOAD Help for more detailed information on most of the topics in this guide. You can easily access TOAD Help while in TOAD by pressing the F1 key.

2
Introduction
TOAD Tool for Oracle Application Developers TOAD is a powerful low-overhead tool built around an advanced SQL and PL/SQL editor. It was designed from the developers perspective, and the result is an easy to use, fast, and effective interface. The GUI object browsers provide quick access to database objects. You dont have to be a PL/SQL expert to access database objects when youre using TOAD. You can view the Oracle Dictionary, tables, indexes, stored procedures, and more-- all through a multi-tabbed browser. PL/SQL script writers can use the advanced editing features to save time and increase productivity. Code can be created from shortcuts and code templates. Even create your own code templates. Use TOAD to Create, browse, or alter objects (tables, views, indexes, etc.) including Oracle8 TYPE objects Graphically build, execute, and tune queries Edit and Debug PL/SQL and profile stored procedures including functions, packages, and triggers Search for objects Find and fix database problems with constraints, triggers, extents, indexes, and grants

3
TOAD Features
This chapter introduces you to a few of TOADs many features. To learn more about these or any other feature refer to TOAD Help.

SQL Editor
The SQL Editor lets you type, execute, and tune SQL statements. The SQL window contains an SQL Editor and a results grid for viewing fetched data. The window supports standard windows editing commands but includes other functions such as a gutter for quick multi-line text selection, programmer style double-click highlighting, and syntax highlighting of categories, keywords, table names, and more. The data grid displays the results of SELECT statements. It is user configurable with resizable and moveable columns, ability to export data to the clipboard or disk, support for LONG and LONG RAW columns, and more. If the dataset is editable, additional buttons on the toolbar are enabled (insert, delete, post updates, and more). Developers spend a lot of time in SQL Edit windows. As you learn TOAD, youll find yourself using Statement Recall, Personal Statement Recall, and Copy/Paste from other TOAD windows.

3 ways to invoke the SQL Edit Window Click the first button in TOADs main toolbar. Select menu item Database SQL Editor. Options dialog.

Set the SQL Editor to StartUp on new connection in the View

10

The SQL Edit window displays the query in the upper portion of the screen, and the corresponding select statements results in the lower data grid. Typing a tablename or alias and . pops up a list of column names for easy selection.

Chapter 3

TOAD Features

11

Schema Browser
The Schema Browser separates database objects by type, using tabs. So, you can quickly view all objects of one type with a single click of the mouse. Youll find it a nice change from other database navigation tools which are often slow, cumbersome, burdened with excessive graphics, and hierarchical in approach. It also lets you compare details between objects of the same type with one click. Keyboard users can easily use the scroll keys to perform the same tasks.

3 ways to invoke the Schema Browser window Click the second button in TOADs main toolbar. Select menu item Database Schema Browser.

Set the Schema Browser to StartUp on new connection in the View Options dialog.

Here, a click on the CUSTOMER table on the tables tab in the left panel displays the details about the customer table on the tabs in the right panel.

12

Procedure Editor
The Procedure Editor lets you edit, compile, test, and debug stored procedures and triggers. By clicking on the right mouse button or pressing F10 you access a context menu with numerous options such as Set Bookmark, Uncomment Block, Format Statement (using PL/Formatter from RevealNet which is discussed in the TOAD add-on modules chapter of this guide), and Show Edit Toolbar. A unique feature of the Procedure Editor is its source check in and source check out buttons. The source you are typing can be directly checked in and out of SourceSafe.

3 ways to invoke the Procedure Editor window Click the third button on TOADs main toolbar. Select menu item Database Stored Procedure Edit. Options

Set the Procedure Editor to StartUp on new connection in the View dialog.

The Procedure Editor

Chapter 3

TOAD Features

13

Options Menu
To access the Options Menu select View Options. Click a topic from the left panel and your options for that topic appear in the right panel. Simply click in the boxes or on the item text to check or uncheck the options. Keyboard users can press the Tab key to move from the left panel to the right panel and press <SPACE> to check or uncheck boxes. The Options are: General, Oracle, SQL Editor, Data Grids, Procedure Editor, Editors, StartUp, Schema Browser, Files, Debugging, Object Browser, Source Control, Printing, and SQL Modeler.

Here, the Editors topic is selected. Refer to TOAD Help for details about options.

14

TOAD Help
To access TOAD Help while in TOAD, simply press the F1 key. TOAD Help includes contents, a searchable index, and details on TOADs features. It also contains details for troubleshooting problems.

4
Install/Uninstall and TOAD Files
This chapter covers the install process for TOAD including an overview of the TOAD files that are installed. It explains the different types of installs. It also shows the uninstall procedure. You can install TOAD from a CD-ROM or installation files you download from the Quest Software web site, www.quest.com. Before installing TOAD, make sure that your system meets the minimum hardware and software requirements. Also, check your SQL*Net or Net8 setup with another application, such as Oracle's tnsping utility, to verify your connection with the database.

System Requirements
TOAD requires either Windows 95, 98, NT 3.51, NT 4.0, or Windows 2000.

If you have SQL*Net (32 bit) installed (or Net8 for Oracle 8 users) and can connect to Oracle 7 using any of Oracles tools (with the exception of SQL*Plus), TOAD should operate properly.

16

Minimum Hardware Requirements

IBM style PC 16 MB of RAM for Windows 95, 98, 2000 or 32 MB of RAM for Windows NT 3.51, 4.0 5 MB disk space required for installation CD-ROM drive (required for commercial version installation from CD-ROM) VGA monitor Mouse pointing device

Minimum Software Requirements

The current release of TOAD requires SQL*Net (32 bit) or Net8 for Oracle 8 users.

TOAD will not run on Linux, Unix, OS2, or Macintosh.

Optional Requirements
This option Requires

TOAD Debugger Tuning modules

Oracle 7 or 8 including Oracle Probe v2.0 or higher (SYS.DBMS_DEBUG package) 32 MB of RAM and an additional 25 MB of disk space

Chapter 4

Install/Uninstall and TOAD Files

17

Install
To Install TOAD
1 Run the TOAD Installation program from the CD or downloaded file. 2 The first install dialog box is a Welcome screen. This ensures that you want to install TOAD. If you want to cancel, click Cancel. As you progress through the following dialog boxes, you will have a Back button to go back to the previous dialog box and a Cancel button to cancel the installation. To progress through the installation, choose your option for each dialog box and click Next.

3 The next dialog asks you to select the type of installation you want. The choices are: TOAD and Personal Configuration files to PC Personal Config Files to PC, read TOAD from Network Server TOAD to Network Server TOAD to Network Server and Personal Config Files to PC

18

You can only choose one. TOAD always installs its files in a TOAD folder. No files will be placed in Windows folders or other miscellaneous folders. TOAD and Personal Configuration Files to PC This is the default choice. This installation is a full client install. This installs all TOAD files to your local hard drive. A TOAD folder is created on your local hard drive and it puts Toad.exe, Toad.hlp, and other TOAD files in your TOAD folder. TOAD\TEMPS contains most of your personal configuration files and the majority of the TOAD files. The last 3 choices involve installing some or all of TOAD on a network server. Is Network Installation the right choice for you? An advantage of installing TOAD to a network server is that as TOAD is updated, the network administrator will have to refresh only Toad.exe and Toad.hlp on the network server. So, only 2 files have to be overwritten. All users are updated when you update TOAD on the server, as opposed to having to update each workstation individually. This makes periodic TOAD updates easier. The disadvantage of installing TOAD to a server is whenever someone starts TOAD at their workstation they have to read the Toad.Exe application file (currently approximately 5 megabytes) across the network. If you have a slow network or a network with a lot of traffic, this type of installation might not be your best choice.

Personal Config Files to PC, read TOAD from Network Server This option installs the personal configuration files to the PC, and creates shortcuts to Toad.exe and Toad.hlp on the server, which already has TOAD installed. All other TOAD files, such as files that you can customize, are installed on your workstation in your hard drive in a TOAD folder. So, if you choose this option you need to know where on the server you want your Toad.exe and Toad.hlp files to be read from. This option is for individual workstations in situations where Toad.hlp and Toad.exe are already installed on the server. TOAD to Network Server This installs Toad.exe and Toad.hlp on the network server so other users can run the Personal Config Files to PC, read TOAD from Network Server installation to

Chapter 4

Install/Uninstall and TOAD Files

19

create shortcuts to the served TOAD. If TOAD is installed to run on a server, the network administrator should be the only one with write access to TOAD. TOAD to Network Server and Personal Config Files to PC This option installs Toad.exe and Toad.hlp files on the server and other TOAD files on the workstation. The files that affect the workstations personal configuration are installed on the workstation. This option is a combination of Option 2 Personal Config File to PC, read TOAD from Network Server and Option 3 TOAD to Network Server. Only the network administrator should have write access to TOAD.

4 The next dialog asks you to select your destination directory. The default directory is C:\Program Files\Quest Software\TOAD, but you can browse through your directories and select another one if you want TOAD installed in another directory or another hard drive letter (D, E, F, etc.). If in the previous step you chose to install some or all of TOAD on a server you will also be asked for the destination directory for the server install.

5 The next dialog box asks if you want to backup replaced files. If you choose Yes, then all copies of configuration files will be put in a backup folder. Then, when TOAD updates are performed, your configuration files wont be overwritten. This only backs up TOAD install files. The default is Yes.

6 If you chose Yes in the previous dialog, it will ask you to select a backup directory. The default is the TOAD\BACKUP folder. You can browse through your directories if you want to choose another location.

7 TOAD starts its installation process. It checks your system to see if either SQL*Net or Net8 are installed properly, such as ORACLE_HOME, PATH environment variable, and prerequisite SQL*Net/Net8 files. It asks you if you would like a TOAD desktop shortcut icon. If you choose Yes it creates a TOAD shortcut and an icon on your desktop.

20

After TOAD makes these checks, a progress bar displays which shows the install program uncompressing the files and copying the files to their destinations. At the same time, 2 log files are created in the TOAD folder: Install.log and Install.txt. Install.log and Install.txt Install.log is created by the install script. It contains verbose commands of what the installation did. Do not edit Install.log, because it is needed by the uninstall engine. It is the file the uninstall engine reads when removing files, icons, and other parts of TOAD. This file is also helpful when trying to diagnose an installation problem such as low disk space, inadequate NT privileges, etc. Here is a section from an Install.log file.
File Copy: d:\Program Files\Quest Software\TOAD\readonly.lic File Copy: d:\Program Files\Quest Software\TOAD\temp.key Made Dir: d:\Program Files\Quest Software\TOAD\BACKUP File Overwrite: d:\Program Files\Quest Software\TOAD\Toad.exe.

Install.txt is also created by the installation, but it has a different format. Its userfriendly approach is easier to follow. Its basically an outline of how the installation script went. If there is an installation problem, such as a problem creating icons, the file can be helpful in determining why that problem is occurring. Here is a section from an Install.txt file. Notice how different its format is from the Install.log.
Beginning TOAD version 6.2.7.19 install... Look in d:\Program Files\Quest Software\TOAD\install.log for more details... Copying down files... File install complete Creating icons... Create a shortcut to TOAD on the desktop... Done creating icons!

So, TOAD writes the files to the appropriate places. 8 Then TOAD creates 4 icons/shortcuts in the Start/Programs/Quest Software/Toad menu or Program Manager Group. TOAD Application

Chapter 4

Install/Uninstall and TOAD Files

21

TOAD Help TOAD ReadMe General Uninstall TOAD 9 If the SQLab Tuner/Xpert module installation (Toaddev.exe) is found in the same folder as TOADs install program, itll ask you if you want to run SQLab Tuner/Xpert installation at the same time. 10 The finish dialog will display indicating installation has been completed. TOAD will ask you if you want to read the readme.txt. The default choice is Yes. If you choose Yes, TOAD will put the readme.txt into notepad for you to read. The License Key is placed into another instance of notepad. The License Key is discussed in the next section.

22

License Key
After the installation is completed, your registration key number displays on notepad. Write down the number that displays. Or, you can drag your mouse over the number to select it, select Copy from the Edit menu (or type <CTRL> + C), and then when you run TOAD you can paste your number when you are asked for your registration key number, by right clicking in the Enter authorization key text box and clicking on Paste from the pop up menu, or typing <CTRL> + V. You will only be asked for the number the first time you run TOAD. You will also need the number to get upgrades from Quest Software. For more information about License Keys refer to the Registration and Upgrades chapter.

The Product Authorization string (also known as the authorization key) must be copied and pasted or typed into the Product Authorization window. The window will appear the first time you run TOAD.

Chapter 4

Install/Uninstall and TOAD Files

23

TOAD files
When TOAD is installed, files are copied into the appropriate directories. As you work with TOAD more files are created by you. This section gives you an overview of TOAD files that are in your TOAD folder. For more details, refer to TOAD Help by pressing the F1 key while you are in TOAD.

TOAD.EXE This is the TOAD application file.

TOAD.HLP This is the TOAD help file. You can always access Help in TOAD by pressing F1.

UNINSTAL.EXE This is the uninstall engine. You select uninstall from the start menu, or control panel add/remove programs applet. The uninstall engine reads and undoes everything that it finds in the Install.log.

TOAD.LIC This file must be in the TOAD folder. FULLTOAD.LIC READONLY.LIC TOAD.EXE only reads TOAD.LIC to determine if it is full TOAD or read-only. So, the network administrator can copy READONLY.LIC over the TOAD.LIC on an individual workstation, to make TOAD read-only at that workstation.

24

HINTS.TXT ASCII file for hints for the toolbar buttons and status panel descriptions.
SqlEdit=Open a new SQL Window|Open a new SQL Window This is a section from the Hints.txt file. Browser=Open a new Schema Browser Window ProcEdit=Open a new Procedure Edit Window SqlBuilder=Open a new SQL Builder SqlModeler=Open a new SQL Modeler ExPlanResults=Explain Plan Window|Show previous Explain Plan Results DBMS=Open a new DBMS Output Window Execute=Execute statement|Execute the complete or highlighted statement ( F9 ) ExecutePartial=Run current statement|Run the statement at the cursor ( Shft-F9 ) ExecuteScript=Execute all of current window as script RecallSql=Recall Previous SQL ( F8 ) RecallPers=Recall Personal SQL

NAMEDSQL .DAT A list of user named SQLs PERSSQLS.DAT A list of user personal SQLs SQLS.DAT A list of last x SQLs executed by the user README.TXT A legal reminder, install notes, license files, and other TOAD information REVBASE.DLL Interface from TOAD to RevealNets Oracle Knowledge Base TEMP.KEY ASCII text of temporary software registration key TOAD2.INI Initialization parameters file TOADLOAD.WAV Croak sound, plays when TOAD.EXE is invoked SCHEMA.FLT Schema filters files. If you use browser filters, TOAD creates SCHEMA.FLT files.

Chapter 4

Install/Uninstall and TOAD Files

25

The TEMPS folder contains 12 text files associated with SQL functions Help. To see the SQL Help Templates, go to the toolbar in the SQL Edit Window and select Show SQL Help.

FILE NAME STRFUNCS.TXT NUMFUNCS.TXT GRPFUNCS.TXT DATFUNC.TXT DATEFMTS.TXT CNVFUNCS.TXT MSCFUNCS.TXT NMBRFMTS.TXT PSEUDO.TXT OPTHINTS.TXT PREDFXCP.TXT USRFUNCS.TXT

ASSOCIATED TEMPLATE Single Row Character Functions Single Row Number Functions Group Functions Date Functions Date Format Options Data Conversion Functions Other Misc. Functions Number Format Options Oracle Pseudo Columns SQL Optimizer Hints Defined Exceptions User Provided Function List

These templates are user configurable by editing the text file. This is especially useful for configuring PREDFXCP.TXT and USRFUNCS.TXT.

26 4 templates are for the Procedure Editor in the Procedure Editor Window. NEWFUNC.SQL NEWPACK.SQL NEWPROC.SQL NEWTRIG.SQL

3 files are associated with syntax highlighting in the SQL Editor and Procedure Editor. PLSQLSCR.TXT Main file SHORTSCR.TXT The default PLSQLSCR.TXT=SHORTSCR.TXT LONGSCR.TXT Can copy LONGSCR.TXT over PLSQLSCR.TXT for more functionality Syntax highlighting support also includes HTML, INI, JAVA, and TEXT files. HTMLSCR.TXT INISCR.TXT JAVASCR.TXT TEXTSCR.TXT

2 SQL scripts are for setting up TOAD user schema tables: TOADPREP.SQL SQL script to create the TOAD schema, with temp tables and grants to all users to setup functionality such as Explain Plan. This is useful for multi-user situations where all the developers can reference one schema.

NOTOAD.SQL Creates temp tables without an external TOAD user schema, replaces the TOADPREP.SQL This creates temp tables in your current schema.

Chapter 4

Install/Uninstall and TOAD Files

27

EXPLAIN.SQL User customizable SQL for showing previous explain plan SQL results. You can preview your SQL results before you execute them.

ALIASES.TXT List of user-defined table aliases For example, Employee table can be set up as alias EMP.

PLSQL.DCI Code completion templates activated in the SQL Edit or Procedure Edit window by typing a couple of letters and <CTRL> + <SPACE> or to access the whole list press <CTRL> + <SPACE> The user can customize the file by manually editing PLSQL.DCI with notepad or From the edit menu Editor Options dialog tab for editing code templates. Code completion templates support also includes HTML, INI, JAVA, and TEXT files. HTML.DCI INI.DCI JAVA.DCI TEXT.DCI

TOADPROFILER.SQL Script available in Oracle 8i. Sets up profiler analysis. As TOAD runs a procedure, the profiler keeps statistics. It can see where the bottlenecks are so that you can improve them.

TOADSECURITY.SQL A script that sets up TOAD security features. It enables or disables certain buttons or functions. For example, it can prevent certain TOAD users from dropping tables. It does not affect Oracle security, such as object grants or system privileges.

28

PLSQLSUB.TXT List of AutoCorrect substitutions. If you mistype and press <SPACE>, the word you incorrectly typed is autoreplaced if its in PLSQLSUB.TXT. Example; teh would be replaced with the You can add words by accessing the editor options dialog or Notepad - PLSQLSUB.TXT. Other SUB.TXT files are supported for HTML, INI, JAVA, and TEXT files. HTMLSUB.TXT INISUB.TXT JAVASUB.TXT TEXTSUB.TXT

SCHEMA_DATABASE.lst Lists of Oracle users for the selected schema/database. You can use the View Oracle Users List dialog to select which users schemas you want to see. This saves query time. TOADSTATS.INI Warning levels for server statistics window. You can set thresholds for when a warning or error will display. warning pink error red

If you select server installation, TOAD installs the following files on the server: TOAD.EXE TOAD.HLP REVBASE.DLL and SQL Help Templates New Procedure Templates Code Completion Templates in \TEMPS folder

Chapter 4

Install/Uninstall and TOAD Files

29

Uninstall
If you uninstall TOAD, it might not totally uninstall. When you use TOAD, you create new TOAD files such as SQL scripts, data output, or temp files. When you run uninstall, TOAD has no way of knowing what those files are. So it wont remove new TOAD files that youve created. Youll have to manually delete them. Two methods allow you to uninstall TOAD. 1 Using the Start Menu Click Start to display the Start Menu Point to Programs or 2 Go to add/remove programs in Windows and uninstall TOAD. If a network administrator installs server components they will not be uninstalled. The network TOAD components will have to be manually deleted. Quest Software Toad Uninstall TOAD

30

How Uninstalling TOAD works for each of the install types. TOAD and Personal Configuration files to PC Uninstall uninstalls everything from your PC. Personal Config Files to PC, read TOAD from Network Server Uninstall only deletes client files. With this type of install the Toad.exe and Toad.hlp files were installed on a server. The files on the server will have to be manually deleted. TOAD to Network Server This is run by a network administrator. Toad.exe and Toad.hlp files were installed on the server. An install log was not created and it did not create an uninstall. This was intentional. If a network administrator decided to uninstall TOAD, this would affect all the workstations. So, network TOAD files must be manually deleted. TOAD to Network Server and Personal Config Files to PC This installed Toad.exe and Toad.hlp to the server and the other files to the client. Uninstall only deletes client files. If a network administrator decided to uninstall TOAD, this would affect all the workstations. So, network TOAD files must be manually deleted.

So, when network installation is involved, remember that any TOAD components that are installed on the server cannot be uninstalled. They will have to be manually deleted. When TOAD files are installed on the server they always include Toad.exe and Toad.hlp. TOAD files on the client (workstation) can be uninstalled.

5
Troubleshooting
SQL*Net/Net8 Installation Problems
For SQL*Net/Net8 installation problems, follow these steps: 1 Determine your ORACLE_HOME value. Check the Registry under HKEY_LOCAL_MACHINE\Software\Oracle 2 Check to see if you have access to ORACLE_HOME\bin. The most common cause of problems involves the PATH environment variable. ORACLE_HOME\bin must be in your PATH environment variable. Confirm you have access to the drive and make sure the Oracle Home folder exists. Your PATH should look similar to this:
PATH=C:\PROGRA~1\MSOFFICE\OFFICE;C:\WINDOWS;C:\ORAWIN95\BIN;

3 Confirm that the Oracle interface DLL is in the Bin folder. TOAD must have an Oracle interface DLL to connect. It would be ORAxy.DLL, where xy is the database version you are connecting to. So, for 7.1 it is ORA71.DLL. For 8.0.5 it is ORA805.DLL. For Net8/Oracle8i, the DLL is called ORACLIENT8.DLL. Remember, if this DLL file is not in the bin folder, then TOAD cannot connect. 4 Look for TNS Admin value. The TNS Admin value would be a folder name in the Windows registry. This is an optional folder. If it is not present the default is Oracle_Home\Network\Admin Open up the Tnsnames.ora file. It should be in the Admin folder. Make sure the database alias name you are using is in the Tnsnames.ora file.

32

Cannot connect to Personal Oracle

If you are having trouble connecting to Personal Oracle or creating a SQL*Net alias for PO7 or PO8, then for the database name on the TOAD login window try one of the following 3 entries: 2: BEQ-LOCAL LOCAL

Chapter 5

Troubleshooting

33

Frequently Asked Questions

QUESTION How do I edit the results of a query? ANSWER In the SQL Edit window, the data grid that displays the results of the SQL queries is fully editable providing that the query itself returns an updateable resultset. Query statements MUST return the ROWID to be updateable. For example: select * from employee would not be updateable whereas: select employee.*, rowid from employee would be updateable. To reduce this obvious nuisance, you can substitute EDIT EMPLOYEE which TOAD will translate into the updateable version of the statement. For example: edit employee If the resultset should be editable but remains read-only, make sure Read-Only on the Options Window is not enabled. Why can't I see all users in the users dropdown lists? Check View Options, in the Schema Browser node. Is option "Only Show Users That Own Objects in DropDown Lists" checked? If checked, it will only show users that own objects. Also check View Oracle User List which selects a subset of all users. Start TOAD, see menu item View Options. On the General node, check the "Allow Multiple Copies of TOAD to be loaded" check box. Purchase the TOAD PL/SQL Debugger option from www.quest.com. Plug in your new License Key into the Help Register TOAD dialog. Close and reopen TOAD to reinitialize the License Key option settings. Yes. See Tuning menu Spool SQL to Screen or to File.

How can I run multiple copies of TOAD at the same time? How do I get the PL/SQL Debugger to work?

Can you see the SQL that TOAD executes?

34

QUESTION How can I set TOAD to commit automatically after every SQL statement I execute?

ANSWER See menu item View Options. On the Oracle node, check the "Commit Automatically After Every Statement" check box. This is a session level setting which means it affects not only the current SQL Edit window but all other windows sharing the same session. When checked, this option forces an automatic COMMIT to be sent following every statement. When not checked, the user must manually perform commits when desired. If you attempt to close TOAD without auto commits, TOAD will ask you if you want to perform a commit. See menu item View Options. On the SQL Editor node, check the "Process Statements in Threads/Allow Queries to be Cancelled" check box. If checked, TOAD will issue each query in a separate thread. This will show the <Cancel> button on the SQL Edit window toolbar to the right of the Change Active Session button, and will permit the user to cancel a long running query. If you have the DBA role, you can check the "Check for Access to DBA Views" check box in View Options, StartUp node. TOAD will then query the Oracle Dictionary using the DBA_ views, e.g., select * from dba_tab_columns which is a lot faster than select * from all_tab_columns because the DBA_ views do not contain any security features. You can either make TOAD read-only by copying readonly.lic over toad.lic, or revoking system privs, e.g., "create table" from those user accounts, or install TOAD Features Security by running script TOAD\temps\TOADSecurity.sql, then administer the lists of who can execute which function in the Database TOAD Security dialog. See the View Options dialog, Data Grids node. Check the Clone SQL cursor when exporting grid contents check box. TOAD will then use a read ahead only cursor, which uses less resources, and does not have to interact with the grid as it traverses the records.

How can I cancel long running queries?

How can I improve the Oracle Dictionary query performance?

How can I limit TOAD's object altering capabilities?

How do you improve the performance of saving the SQL results grid to a file?

You can get help from the Quest Software Web site at http://www.quest.com/html/support/index.html.

6
Registration and Upgrades
This chapter discusses how to register trial and commercial versions of TOAD. This chapter also covers the purchase procedure for TOAD and how to obtain upgrades.

Trial Version Registration

Before you register the Trial Version of TOAD you need your registration Key (TOAD\temp.key). The PL/SQL Debugger option is activated in the trial key.

To Start Trial TOAD

1 Start TOAD. 2 The Product Authorization window displays. 3 Click in the Product Authorization text box. 4 Enter the software registration key number that is included in your TOAD files (TOAD\temp.key).

Time limit
The trial version of TOAD is the same as the commercial version, except that the trial times out in 30 days. You basically have a free sample of TOAD for 30 days. After 30 days you will get a message telling you that TOAD has expired.

36

When the clock starts

The 30 day evaluation period is 30 days from the day you start using TOAD. So, if you download TOAD on Jan. 1 and dont use it until March 1, your 30 day clock starts on March 1, and TOAD expires 30 days later.

Registration Key good for 3 months

Your key can be activated during a 3 month window. After 3 months, if your registration key has not been activated, it expires. This is to ensure that you are not evaluating an old copy of TOAD. We want users to have the latest and greatest copy to evaluate. So, if your unactivated key expires, download the latest trial copy and get your new registration key, or call Quest Sales at 949-754-8000 for a trial extension.

If you are satisfied with the trial version and want to purchase the commercial license key, refer to the next section. Remember, youll need to update your license key through Help Register TOAD.

Chapter 6

Registration and Upgrades

37

Commercial Version Purchase and Registration

To purchase a commercial version or place a large order you can either contact Quest Sales at 949-754-8000 to discuss payment options or visit the quest site at http://www.quest.com

To purchase TOAD
1 Go to: http://www.quest.com 2 Click Products. 3 Click TOAD from the Products List. 4 Click the Purchase Now button.

An Order Form displays with price information. Fill in the form and purchase TOAD using your credit card number. Then download the commercial version. Youll receive a 30 day temporary key in the download. After your order is processed, your permanent license key is sent to you by email. Then go to the Help menu, click Register TOAD, and enter your new authorization key number, also known as the license or registration key number. Also enter the Site Message such as Company Name. You can even highlight and copy the key number from your email and paste it into the authorization key number box. In the future, when you download upgrades from the Quest web page youll need the license key number. Simply select Help Register TOAD, highlight your number from the authorization key number box, Copy, and Paste the number. NOTE: You may need to restart TOAD to reinitialize your options and enable all functions.

38

Updating TOAD Commercial Version

Upgrades can be obtained from TOADs Download Upgrade page.

To upgrade to the latest version:

Go to the Quest Software homepage at http://www.quest.com 1 Click Products. 2 Click Products List. 3 Click TOAD. 4 Click Download Upgrade. 5 The Download Upgrade page displays. 6 Paste or type in your registration key number in the license key string box. 7 Click the Download button. 8 A toad.zip file will download. It contains a toad.exe file.

The toad.zip file might or might not contain toad.hlp or other TOAD files, depending upon what has been changed in the upgrade. You will need to unzip the TOAD files using your own unzip utility. You will also need to place the files in the appropriate directories and overwrite existing files to implement the upgrade.

Chapter 6

Registration and Upgrades

39

Product authorization errors

The following table shows steps that you can take when encountering an authorization problem or error in TOAD.

Error Message Authorization string has expired.

Explanation and Possible Solution This occurs when the trial authorization for TOAD has expired. If the trial date has not passed, check that the date on your system is set correctly.

Invalid authorization string or site message.

This indicates that the authorization string or the site message has not been entered correctly. Make sure that they have been entered exactly. Note: The site message is case sensitive.

7
TOAD add-on modules
This chapter discusses the optional modules you can purchase that work with and enhance TOAD. The following optional modules are available for TOAD. TOAD PL/SQL Debugger Quest Software SQLab Tuner/Xpert Quest Software SQL Impact RevealNet PL/Formatter RevealNet Knowledge Base

42

TOAD PL/SQL Debugger

You can debug top-level functions, top-level procedures, package functions, package procedures, and triggers.

To run the PL/SQL debugger, you must have a Quest Software license key with the PL/SQL Debugger option activated in the key. You can verify that the Debugger option is activated by checking the TOAD Help About menu and see what options are listed. The PL/SQL debugger lets you step through the code as it executes, line-by-line, statement-by-statement. You can run a debug session with or without argument values. With the click of the toolbar button debugging begins and stops on breakpoints as appropriate or it runs to the end. It sets any IN or IN/OUT argument values in a procedure to NULL. So, for functions that depend on the argument values, you can still check the branching, logic, etc. You can also set argument values in the Execute Procedure dialog and it will use those values. If you are debugging a trigger, then in the Execute Procedure dialog you must set up the anonymous PL/SQL block that will invoke the trigger. You can add, edit, and delete breakpoints. You can also set conditional breakpoints. When you are debugging packages, the debugger displays a list so you can select which package procedure or package function you want to execute. PL/SQL debugger can display a docked window that lets you see all the debugging windows when you are debugging. You can see all 4 windows: Breakpoints, Watches, Call Stack, and DBMS Output. Or you can choose to activate each window separately. After the debug is executed, you can view the DBMS-OUTPUT from the server.

Chapter 7

TOAD add-on modules

43

You cannot debug Java classes, Oracle8 object methods, or anonymous PL/SQL blocks.

These are just a few of the options and features of PL/SQL Debugger. For more information about the TOAD PL/SQL Debugger including details on installing and running the module, refer to TOAD Help or the online information at the Quest Software site www.quest.com.

44

Quest Software SQLab Tuner/Xpert

This is a single add-on product available from Quest that is split into two modules SQLab Tuner and SQLab Expert.

Product SQLab Tuner

Key Capabilities SQLab Tuner is a robust, Windows-based SQL analyzer that can locate and tune "offensive" SQL statements buried within homegrown or commercial applications. SQLab Tuner identifies performance issues and enables Oracle database tuning to resolve bottlenecks. SQLab Xpert takes application tuning to the next level by becoming an instant expert on your Oracle database. SQLab Xpert provides context-sensitive tuning advice for SQL statements based on the Oracle execution plan and the structure of your database.

SQLab Xpert

The Tuner identifies, records, and tunes offensive SQL statements. It analyzes a selected SQL statement and makes recommendations that will resolve bottlenecks and improve performance. The Tuner displays different SQL tuning scenarios so that you can choose the best strategy. It displays tables and indexes associated with the selected SQL statements. It also lets you see the logic behind its recommendation. With Tuner, you select an SQL statement, click the Tuner toolbar, and the statement is passed to the SQLab Tuner for analysis. The Tuner places the resulting statement in the Windows clipboard, so that you can paste it back into TOAD. Xpert checks for common database problems, finds SQL problems, and provides tuning advice to improve performance. You can have Xpert actually rewrite your SQL statement based upon the advice you choose. And you can test the new scenario in a tuning session.

For more information on SQLab Tuner/Xpert, go to the Quest Software site at www.quest.com. To purchase a copy, contact Quest Sales at 949-754-8000 or send email to [email protected].

Chapter 7

TOAD add-on modules

45

Quest Software SQL Impact

SQL Impact SQL Impact provides impact analysis, documentation and auditing to manage the dependencies between database objects and applications source code. SQL Impact precisely determines the components that will be affected by structural changes to a database, drilling down to the lines of source code affected. SQL Impact can document the access pattern of an application, and provides automated quality assurance reports on the SQL embedded in the source code. SQL Impacts Year 2000 option supports Year 2000 projects by identifying all date instances and highlighting problematic date usage in both the database and your source code.

SQL Impact lets you see the impact of a proposed schema change. It determines which components of an application will be affected by the changes. It provides accurate and automatic impact analysis for proposed database changes. You can see how a schema change will affect source code. It shows you all the lines of affected code. It also drills down to the individual lines of source code affected.

For more information on SQL Impact, go to the Quest Software site at www.quest.com. To purchase a copy, contact Quest Sales at 949-754-8000 or send email to [email protected].

46

RevealNet PL/Formatter
PL/Formatter is an optional add on product by RevealNet that formats your SQL for you while you are in TOAD. With PL/Formatter, while you are in TOAD you can easily format or reformat code with a right click of the mouse. It also provides recommendations to improve your code. It checks for over 1000 common coding mistakes. You can set formatting options in the View PL/Formatter Options dialog.

If you had the following query, and you had installed PL/Formatter, then you could select the query, click the right mouse button, and select Format SQL from the popup menu. This is what would happen. select first_name, last_name from employee where first_name like S% order by last_name would get formatted into this: SELECT first_name, last_name FROM employee WHERE first_name LIKE S% ORDER BY last_name

The trial version of the PL/Formatter will not work with TOAD. If you purchase the commercial version of the PL/Formatter, it will work with TOAD.

For more product information visit http://www.revealnet.com. To purchase a copy, contact Quest Sales at 949-754-8000 or send email to [email protected].

Chapter 7

TOAD add-on modules

47

RevealNet Knowledge Base

PL/SQL Knowledge Base is an optional add-on product by RevealNet that is an online reference that helps you understand and use PL/SQL. You can easily search and browse index topics. It has over 1000 reusable code examples and over 200 scripts. It has hundreds of drop-in functions and utilities. Its designed to help you understand and use PL/SQL. You can customize the Knowledge Base. It complements Oracle manuals. Once Knowledge Base is installed, access is as easy as the right click of your mouse while you are in an SQL Edit window. Then select Knowledge Base for access to an encyclopedia of information in an online reference, examples, and even practice tests. Use the search engine to launch a keyword search. Get suggestions on how to improve code in your present script. For more product information visit http://www.revealnet.com. To purchase a copy, contact Quest Sales at 949-754-8000 or send email to [email protected].

8
About Quest Software
Quest Software provides Application and Information Availability solutions to extend the value of your critical business applications:

Server-side Development Ease the development of your Oracle-based applications with the leading PL/SQL server-side development tools. From a graphical user interface, you can combine coding, tuning and debugging capabilities with expert advice and advanced PL/SQL code libraries. Featuring two of the leading development tools for faster server-side coding and editing, you cant go wrong with the Quest Server-side Development Suite.

Change Management Uphold the integrity of enterprise applications when implementing changes from development to production by automating the most complex and error-prone tasks. With the Quest Change Management Suite, you can instantly identify all source code impacted by proposed changes, implement schema changes, track change requests and the progress of developers, bundle changes into a release, and seamlessly apply them to your production environment.

Production Support Maximize the performance of critical business applications and ensure their availability with a complete set of solutions designed to make the most of your production environment. From application and database tuning to monitoring and performance reporting to replication, the Quest Production Support Suite will take application performance to a new level.

49

Output Management and Information Delivery Extend the reach of valuable information generated by corporate applications with the Quest Output Management Suite designed to manage and deliver critical reportbased information to the extended enterprise.

Quest Software is headquartered in Irvine, California and has locations in Atlanta, Boston, Chicago, Dallas, New York, San Francisco, Washington D.C., Germany, the United Kingdom, Canada, Israel, and Australia. Visit www.quest.com for information about Quest Software products and events.

50

Contacting Quest Software

Feel free to contact Quest Software for product information and pricing in any of the following ways: Location USA and Canada Telephone 949-754-8000 FAX: 949-754-8999 UK Germany Australia Email Sales & Marketing Home page Mail address +44-1628-601000 +49-211-770967-0 +61-3-9811-8000 [email protected] http://www.quest.com 8001 Irvine Center Drive Irvine, CA 92618

Technical Support
If you have questions about using a Quest Software product, please contact the technical support staff. Please include the version number. You can find your version number in the Help About menu. If your question is about an error message, include the error text as well. Email [email protected]