Developing Oracle Applications Using Toad

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Introduction About the Book Tool for Oracle Application Developers (TOAD) is a powerful graphical user interface for working with Oracle database. This tool allows you to build, auto generate, and execute complex SQL and PL/SQL queries quickly and easily. TOAD provides editors, such as SQL Editor, Procedure Editor, Schema Browser, SQL Modeler, and Quest DBA Module, which allow you to work with Oracle databases. This book describes these editors that provide features, such as hot keys, auto-correct, type-ahead, syntax highlighting, and version control. In addition, the book describes integrated Results panel including the Explain Plans, Auto Trace, and DBMS Output, which ensures efficient editing and testing of databases in Oracle. This book targets Oracle users who want to learn how to use TOAD to work with Oracle database. A prior knowledge of Oracle will be helpful.

Copyright Working with Oracle Database Using TOAD Copyright © 2003 by SkillSoft Corporation All rights reserved. No part of this work may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information storage or retrieval system, without the prior written permission of SkillSoft. Trademarked names may appear in this publication. Rather than use a trademark symbol with every occurrence of a trademarked name, we use the names only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark. Published by SkillSoft Corporation 20 Industrial Park Drive Nashua, NH 03062 (603) 324-3000 [email protected] The information in this book is distributed on an "as is" basis, without warranty. Although every precaution has been taken in the preparation of this work, neither the author nor SkillSoft shall have any liability to any person or entity with respect to any loss or damage caused or alleged to be caused directly or indirectly by the information contained in this work.

Chapter 1: Introducing TOAD Tool for Oracle Application Developer (TOAD) is a graphical user interface to manage Oracle databases. TOAD includes an Integrated Development Environment (IDE) to build, test, and debug PL/SQL queries, such as procedures and triggers. TOAD enables you to create Oracle database objects, such as tables, views, indexes, and constraints. This chapter introduces TOAD Xpert Edition 7.4. It explains how to configure TOAD before you can use it to manage Oracle databases. It describes the various components of TOAD

This watermark does not appear in the registered version - http://www.clicktoconvert.com

including toolbar, Schema Browser, SQL Modeler, SQL Editor, Procedure Editor, and Project Manager. It also describes how you can use various editor options of TOAD.

Overview of TOAD Xpert Edition 7.4 TOAD Xpert Edition 7.4 includes features of the Standard and Professional editions. The Xpert Edition contains the Xpert Tuning tool that helps tune SQL queries. The Xpert Tuning tool allows you to analyze and rewrite SQL queries that are inefficient and can improve performance of the Oracle database. You can download the trial version of the TOAD Xpert Edition 7.4 software free of charge from the Internet from the following URL: http://www.quest.com/solutions/download.asp Note For more information on installing TOAD Xpert Edition 7.4, refer to Appendix A.

Uses of TOAD TOAD is used to perform multiple Oracle based tasks. You can use TOAD to: Create, browse, or alter Oracle database objects. § Build, execute, and tune Oracle queries graphically. § Edit and debug PL/SQL and profile stored procedures that include functions, § packages, and triggers. Search for objects. § Find and fix database related problems using constraints, triggers, extents, indexes, § and grants.

Configuring TOAD When you start TOAD for the first time, the Product Authorization dialog box appears. This dialog box enables you to start the TOAD configuration. You need to configure TOAD before you can use it to work with the Oracle database. To configure TOAD: 1. Start TOAD using the Start menu. The Product Authorization dialog box appears, as shown in Figure 1-1:

Figure 1-1: The Product Authorization Dialog Box If you enter wrong authorization key, an error appears indicating that the authorization key is invalid, as shown in Figure 1-2:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 1-2: Invalid Authorization Key Note

The valid authorization key is provided by Quest Software Inc., when you download the TOAD software. 2. Enter the valid authorization key, as shown in Figure 1-3:

Figure 1-3: Entering Valid Authorization Key 3. Click OK. The TOAD first-time setup screen appears, as shown in Figure 1-4:

Figure 1-4: The TOAD first-time setup Screen 4. Click OK to accept the default configuration option. The TOAD Initial Setup dialog box appears, as shown in Figure 1-5:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 1-5: The TOAD Initial Setup Dialog Box 5. To show the objects in multiple lines, deselect the Show a single line of tabs with scroll buttons. Schema Browser with multiple lines showing all the available objects appear, as shown in Figure 1-6:

Figure 1-6: Multiple Line Schema Browser 6. To change the appearance of Schema Browser for using a drop-down list, select the Use a drop down list to select object types option. Schema Browser with a drop-down list appears, as shown in Figure 1-7:

Figure 1-7: Drop-Down Schema Browser

This watermark does not appear in the registered version - http://www.clicktoconvert.com

7. Select the appearance of Schema Browser and click Next. The configuration options page appears in the TOAD Initial Setup dialog box, as shown in Figure 1-8:

Figure 1-8: The TOAD Configuration Options 8. Click Next. Three options appear in TOAD Initial Setup dialog box, as shown in Figure 1-9:

Figure 1-9: The TOAD Initial Setup Dialog Box Containing Options 9. Select the required option and click Finish to complete the TOAD configuration. The TAOD Server Login dialog box appears, as shown in Figure 1-10:

Figure 1-10: The TOAD Server Login Dialog Box

This watermark does not appear in the registered version - http://www.clicktoconvert.com

10. Select ORCL from the Database drop-down list. 11. Enter scott in the User / Schema text box and tiger in the Password text box. 12. Select Normal as the role of the user from the Connect as drop-down list and click Ok to connect to TOAD. The TOAD IDE appears, as shown in Figure 1-11:

Figure 1-11: The TOAD IDE Note For more information on SQL Editor, refer to Chapter 4.

Components of TOAD The TOAD IDE is a user-friendly environment that provides a graphical user interface to work with the Oracle database. The TOAD IDE provides six components: Schema Browser, SQL Modeler, SQL Editor, Procedure Editor, Script Manager, and Project Manager. You can access these components using the TOAD Main toolbar.

The TOAD Main Toolbar The TOAD main toolbar contains various buttons that are shortcut to frequently used windows and options of TOAD. Figure 1-12 shows the TOAD main toolbar:

Figure 1-12: The Toad Main Toolbar Table 1-1 describes the TOAD main toolbar buttons: Table 1-1: The TOAD Main Toolbar Buttons Button

Name

Description

SQL Editor Window

Opens a new instance of the SQL Editor window

Schema Browser Window

Opens a new instance of the Schema Browser window

Procedure Editor Window

Opens a new instance of the Procedure Editor window

SQL Modeler Window

Opens a new instance of the SQL Modeler window

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Table 1-1: The TOAD Main Toolbar Buttons Button

Name

Description

Explain Plan Window

Opens a new instance of the Explain Plan window for the selected table or view for the current connection

DBMS Output Window

Opens the DBMS Output window for the current connection

Find Object

Opens the Object Search window that allows you to find objects for a particular schema

Save All Options

Saves all the options available with TOAD

Reports

Opens the TOAD Reports window that allows you to generate reports based on database objects such as tables, view, and triggers

Text Editor Window

Opens the Text Editor window that allows you to work with scripts, offline

Toggle Compiling with Debug

Allows you to compile along with debugging

Compile TOAD Options

Opens the TOAD Options dialog box that allows you to configure TOAD options

Execute a Knowledge Xpert Product or Formatter Plus

Allows you to work with add-on Xpert products and Formatter Plus

Script Manager

Opens the Scrip Manager window that allows you to work with frequently used scripts

Configure/Execute External Tools

Opens the TOAD Tool Options dialog box that allows you to add, edit, and delete external applications, such as Notepad, WordPad, and Paint.

Project Manager

Opens the Project Manager window that allows you to manage your work area in one project

Commit

Commits all changes made to the current schema

Rollback

Rollbacks all changes made to the current schema

Show Windows by Connection

Displays the connections made to the database in an ascending sequence

New Oracle Connection

Opens the TOAD Server Logon dialog box that allows you to create a new connection to the Oracle database

Schema Browser

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Schema Browser enables you to work with various Oracle database objects. When you click the Schema Browser Window button from the TOAD main toolbar, the Schema Browser window appears, as shown in Figure 1-13:

Figure 1-13: The Schema Browser Window Tip You can also access the Schema Browser window by selecting Schema Browser from the Database menu. Note For more information on Schema Browser, refer to Chapter 2.

SQL Modeler SQL Modeler allows you to create a framework for SQL queries, such as Insert, Update, Delete, and Select. When you click the SQL Modeler window button on the TOAD main toolbar, the SQL Modeler window appears, as shown in Figure 1-14:

Figure 1-14: The SQL Modeler Window

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Tip You can also access the SQL Modeler window by selecting SQL Modeler from the Database menu. Note For more information on SQL Modeler, refer to Chapter 3.

SQL Editor SQL Editor provides options that allow you to work with SQL statements. It allows you to write, execute, and tune SQL queries. When you click the SQL Editor Window button on the TOAD main toolbar, the SQL Editor window appears, as shown in Figure 1-15:

Figure 1-15: The SQL Editor Window Tip You can also access the SQL Editor window by selecting SQL Editor from the Database menu. Note For more information on SQL Editor, refer to Chapter 4.

Procedure Editor Procedure Editor allows you to create and modify procedures, functions, triggers, and packages. When you click the Procedure Editor Window button on the TOAD main toolbar, the Procedure Editor window appears, as shown in Figure 1-16:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 1-16: The Procedure Editor Window Tip You can also access the Procedure Editor window by selecting Procedure Editor from the Database menu. Note For more information on Procedure Editor, refer to Chapter 5.

Script Manager Script Manager allows you to manage scripts that you need to use frequently. You can organize scripts in different categories that are accessible by using the options provided by Script Manager. When you click the Script Manager button on the TOAD main toolbar, the Script Manager window appears, as shown in Figure 1-17:

Figure 1-17: The Script Manager Window Tip You can also access the Script Manager window by selecting Script Manager from the File menu.

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Note For more information on Script Manager, refer to Chapter 6.

Project Manager To organize your work based on different Oracle and FTP connections, TOAD provides Project Manager. Project Manager allows you to manage the workspace in one project where you can work with the Oracle database. When you click the Project Manager button on the TOAD main toolbar, the Project Manager window appears, as shown in Figure 1-18:

http://www.books24x7.com/book/id_6159/viewer.asp?bookid=6159&chunkid=5 94912279 Figure 1-18: The Project Manager Window Tip You can also access the Project Manager window by selecting Project Manager from the Tools menu. Note For more information on Project Manager, refer to Chapter 7.

The TOAD Main Menu The TOAD main menu includes various options for using the TOAD IDE. The menu bar has pulldown options for each menu. The TOAD main menu includes several menus, such as File, Edit, SQL-Window, Create, Database, Tools, View, DBA, Window, and Help.

The File Menu

This watermark does not appear in the registered version - http://www.clicktoconvert.com

The File menu contains options to establish a new database connection and save or open a file. When you select File from the main menu, the File menu options appear, as shown in Figure 1-19:

Figure 1-19: The File Menu Options You can perform the following task using options available in the File menu: New Connect: Opens the TOAD Server Login dialog box that allows you to create a § new connection to the database End Connection: Closes a particular connection from the database § End All Connections: Closes all the active database connections § Test Connections (Reconnect): Tests the connection to the database, if Oracle drops § the connection Save File: Saves the current file to the location that you specify in the Save As dialog § box Save As: Opens the Save As dialog box that allows you to save the current file with a § different name or in a new location ReOpen File: Provides a list of recently accessed files and allows you to reopen a file § FTP: Opens the FTP Files window that shows all the content present on the FTP § server Compare Files: Opens the Compare Files dialog box that allows you to compare two § SQL files Network Utilities: Opens the Network Utilities window that enables you to work with § Telnet, Ping, FTP, and IP Addresses Script Manager: Opens the Script Manager window § Print Editor: Prints the current document by sending the print command to the printer § Print Setup: Opens the Page Setup dialog box that allows you to set the properties of § the page to be used to print the selected file Exit: Closes the TOAD IDE window §

The Edit Menu

This watermark does not appear in the registered version - http://www.clicktoconvert.com

The Edit menu contains options to cut, copy, paste, or select the required text. When you select Edit from the main menu, the Edit menu options appear, as shown in Figure 1-20:

Figure 1-20: The Edit Menu Options You § § § § § § § § § § § §

can perform the following task using options available in the Edit menu: Undo: Allows you to undo the last keystroke or action performed Redo: Allows you to redo the last undo action Cut: Removes the selected text from the active window Copy: Allows you to create a duplicate of the selected text Paste: Places the text, cut or copied at the position of the cursor Select ALL: Selects all the text displayed on the current window Clear All: Erases all the text that is typed in the editor Popup Menu: Opens the pop-up menu from the selected item Load in External Editor: Opens the text from the SQL editor to an external editor, such as Notepad and WordPad Toggle FullScreen Editor: Enables you to change the editor to appear in the full screen and closes all the results screen Swap This/Prev Lines: Moves the cursor from the current line in the SQL script to the previous line Goto Line: Allows you to go to a specific line of text in the editor

The SQL-Window Menu The SQL-Window menu contains options for execution of SQL queries. When you select SQL-Window from the main menu, the SQL-Window menu options appear, as shown in Figure 1-21:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 1-21: The SQL-Window Menu Options

The Create Menu The options available in the Create menu allow you to create Oracle database objects. When you select Create from the main menu, the Create menu options appear, as shown in Figure 1-22:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 1-22: The Create Menu Options The Create menu options enable you to create constraints, directories, indexes, roles, profiles, sequences, tables, users, and views.

The Database Menu The Database menu contains options to access Schema Browser, SQL Editor, Procedure Editor, and SQL Modeler. You can also import and export tables. When you select Database from the main menu, the Database menu options appear, as shown in Figure 1-23:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 1-23: The Database Menu Options

The Tools Menu The Tools menu contains options to access Project Manager, determine table and index size, rebuild table, rebuild index, and customize a toolbar. When you select Tools from the main menu, the Tools menu options appear, as shown in Figure 1-24:

Figure 1-24: The Tools Menu Options

This watermark does not appear in the registered version - http://www.clicktoconvert.com

The View Menu The View menu contains options to view browser filters, constraints, dependencies, reports, formatting options, explain plan, DBMS output, and end user's session information. When you select View from the main menu, the View menu options appear, as shown in Figure 125:

Figure 1-25: The View Menu Options

The DBA Menu The DBA menu contains options to perform database administrative tasks. You can monitor database, create a new database, generate a schema for scripts, and compare schemas. When you select DBA from the main menu, the DBA menu options appear, as shown in Figure 1-26:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 1-26: The DBA Menu Options

The Window Menu The Window menu contains options to tile windows horizontally or vertically, and cascade them. When you select Window from the main menu, the Window menu options appear, as shown in Figure 1-27:

Figure 1-27: The Window Menu Options

The Help Menu The Help menu contains options to view the TOAD help file, register TOAD, and join the TOAD mailing lists. When you select Help from the main menu, the Help menu options appear, as shown in Figure 1-28:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 1-28: The Help Menu Options

Using Editor Options You can use editor options to set options for editor windows such as SQL Editor and Procedure Editor. You can edit various options of SQL Editor using the Editor Options dialog box. This dialog box appears when you select Editor Options from the Edit menu. Figure 129 shows the Editor Options dialog box:

Figure 1-29: The Editor Options Dialog Box There are five types of editor options: General Options, Highlighting, Key Assignments, Auto Replace, and Code Templates.

General Options General Options include the printing, display, control, and general options.

Printing Options You can use printing options when you want to print the content of the text editor. Printing options enable you to wrap lines and print the line numbers, page numbers, file names, and date. Figure 1-30 shows the various printing options in the right panel of the Editor Options dialog box:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 1-30: Printing Options

Display Options You can use display options to display the content in the text editor according to your requirements. Figure 1-31 shows the various display options in the right panel of the Editor Options dialog box:

Figure 1-31: Display Options There are eight types of display options: Display line numbers in gutter: Displays the line numbers in the gray vertical region on § the left of the text editor. This vertical region is called gutter. You can use the line numbers to view bookmarks and breakpoints. By default, this option is deselected. Show right margin: Displays a gray vertical line on the right of the text editor. By § default, this option is deselected. Show gutter: Displays the gutter in the text editor. By default, this option is selected. If § you deselect this option, you cannot view the line numbers in the text editor. Show word-wrap column: Displays a dashed vertical gray line on the right of the text § editor. To view this line, you need to enter a value greater than zero in the Word wrap column check box. By default, this value is zero. For example, if you enter 40 in the Word wrap column check box, a dashed vertical gray line appears at the column

This watermark does not appear in the registered version - http://www.clicktoconvert.com

position, 40, of the text editor. If any line in the text editor exceeds 40 characters, it will wrap to the next line. By default, this option is selected. Notes § To view word wrapping, select the Word-wrap lines check box in the general options node. § Block cursor on overwrite: Displays the block cursor when you press Insert to overwrite texts. The block cursor reminds that you are overwriting a text. By default, this option is selected. § Show wrapped lines with the |> glyph: Displays a glyph in the gutter for all the lines that are wrapped in the text editor. By default, this option is selected. § Show control characters: Displays various control characters, such as paragraph marks, end-of-line characters, spaces, and tabs, in an SQL script. By default, this option is selected. § Apply capitalization effects: Applies the capitalization effect that you select for the Highlighting options. By default, this option is deselected.

Control Options You can use control options to control the end-user interaction with the text editor. Figure 132 shows the various control options in the right panel of the Editor Options dialog box:

Figure 1-32: Control Options There are five types of control options: Allow TCustomSyntaxMemo to be an IDragSource: Enables you to select and drag text § from the text editor to all Windows applications or another text editor in the second instance of the TOAD IDE. Title as Load/Save Filename parameter: Provides filename parameters when you load § or save a file from the text editor. Track columns in vertical movement: Restores the current column's position as you § move up and down through the lines in the text editor. This helps you to search any text at a specific position. Respond to dropped files: Enables you to drag and drop files from Windows Explorer § to the text editor. Override word-wrap based on line start char: Overrides word wrapping based on the § line start character. For example, type < in the Word wrap override chars text box and

This watermark does not appear in the registered version - http://www.clicktoconvert.com

type 10 in the Word wrap column text box. If you start a line with the < sign in the text editor, this line will not wrap to the next line although it has more than ten characters.

General Options You can use general options to align text in the text editor. Figure 1-33 shows the various general options in the right panel of the Editor Options dialog box:

Figure 1-33: General Options There are seven types of general options: Auto indent: Indents all lines automatically after you set an indent for a specific line in § the text editor Tab to next column: Moves the cursor to the next column in the text editor § Auto select word on double-click: Selects a word in the text editor, when you double§ click the word Insert TABs into text for TAB chars: Inserts a tab space after a character, where the § cursor is positioned in the text editor Insert mix of TAB/SPACE for optimal fill: Inserts a tab space or a blank space § character Cursor beyond EOL: Enables you to type beyond the end of the line in the text editor § Word-wrap lines: Displays the wrapped lines in the text editor §

Highlighting Highlighting options enable you to select the text color, font style, font size, foreground, background, character set, and capitalization effect. When you select Highlighting from the left panel in the Editor Options dialog box, the Highlighting options screen appears, as shown in Figure 1-34:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 1-34: The Highlighting options Screen In the Highlighting options screen, you can highlight the various display items listed in the Display item list box. By default, the preview pane shows the Reserved word display item. The preview pane shows all the reserved words in blue, with the font style as Courier, the font size as 10, the character set as American National Standards Institute (ANSI), and the capitalization effect as Uppercase.

Key Assignments Key Assignments include various options, such as bookmarks, caret movement, clipboard operations, deletion operations, and miscellaneous options. When you select Key Assignments from the left panel in the Editor Options dialog box, the Key assignments screen appears, as shown in Figure 1-35:

Figure 1-35: The Key assignments Screen From the Key assignments screen, you can specify the key sequence for the required Key Assignments options. The Key assignments screen enables you to add, edit, delete, load, and save key sequences.

This watermark does not appear in the registered version - http://www.clicktoconvert.com

In the Key assignments screen, the Activation key sequences text area shows the respective key sequence for the selected option. For example, when you select Set Bookmark 0 from the Bookmarks node, the key sequence to set Bookmark 0 appears, as shown in Figure 136:

Figure 1-36: The Key Sequence for Bookmark 0

Auto Replace Auto Replace options enable you to replace the text that you type in the text editor with the required word. For example, if you type func in the text editor, it will be automatically replaced by function. When you select Auto Replace from the left panel in the Editor Options dialog box, the Auto replace screen appears, as shown in Figure 1-37:

Figure 1-37: The Auto replace Screen

Code Templates

This watermark does not appear in the registered version - http://www.clicktoconvert.com

The Code Templates window defines the templates for SQL Editor. When you select Code Templates from the left panel in the Editor Options dialog box, the Code Templates screen appears, as shown in Figure 1-38:

Figure 1-38: The Code Templates Screen In the Code Templates screen, the text area displays the code template that you select from the scroll pane that contains the Shortcut and Description columns. For example, when you select Anonymous block, the code template for anonymous block appears, as shown in Figure 1-39:

Figure 1-39: The Code Template For Anonymous Block Note Click OK to save your changes and click Cancel to exit the Editor Options dialog box.

Chapter 2: Using Schema Browser

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Tool for Oracle Application Developers (TOAD) provides various windows that allow you to manage and maintain the Oracle database. The Schema Browser window allows you to create and modify various Oracle database objects including tables, views, and indexes. This chapter describes the Schema Browser window and its components, including the Objects panel, the Details panel, and the Schema Browser toolbar. It explains how to create a table, a view, and an index, using the Schema Browser window. It also explains how to use the Tables, Views, and Indexes toolbars.

The Schema Browser Window The Schema Browser window provides a graphical use interface that allows you to work with Oracle database objects. The Schema Browser window contains the Objects panel and the Details panel. These panels provide information about all database objects. To open Schema Browser, select Database -> Schema Browser. The Schema Browser window appears, as shown in Figure 2-1:

Figure 2-1: The Schema Browser Window Note Alternatively, to open the Schema Browser window, click the Schema Browser Window button,

, on the Standard toolbar.

When you open the TOAD application, the SQL Editor window appears by default. To open Schema Browser as the default window, select View -> Options. The TOAD Options dialog box appears. From the right panel, select the StartUp option. The TOAD Options dialog box appears, as shown in Figure 2-2:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 2-2: The TOAD Options Dialog Box: StartUp On the left panel of the TOAD Options dialog box, deselect the SQL Editor check box and select the Browser check box. Schema Browser will be the default window when the TOAD application is opened.

The Objects Panel The Objects panel is the panel on the left side of the Schema Browser window. It provides graphical information about all database objects in the form of tabs. The Objects panel of Schema Browser appears, as shown in Figure 2-3:

Figure 2-3: The Objects Panel To see all the objects available in the Details panel: 1. Select View -> Options. This opens the TOAD Options dialog box. 2. Select the Page 2 option of Schema Browser. The TOAD Options dialog box appears, as shown in Figure 2-4:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 2-4: The TOAD Options Dialog Box: Schema Browser Page 2 3. From the right panel of the TOAD Options dialog box select the check box, Use Multiline tabs on the left side and click OK. This closes the TOAD Options dialog box. 4. Close the current Schema Browser window. 5. Open the Schema Browser window. The Objects panel appears, as shown in Figure 2-5:

Figure 2-5: The Objects Panel with all Objects Table 2-1 describes all the objects provided by Schema Browser in the Objects panel: Table 2-1: Objects in the Objects Panel Object

Description

Constraints

Provides options to enable and disable constraints.

DB Links

Creates scripts for database links and drop existing links.

Dimensions

Redirects queries from a base table to the related view. This option allows you to create, compile, and drop dimensions.

Directories

Provides options to create, modify, and drop a directory.

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Table 2-1: Objects in the Objects Panel Object

Description

Favorites

Provides options to add frequently used objects. You can also create folders that enable you to group frequently used objects.

Indexes

Allows you to create, modify, rebuild, and drop an index.

Java

Provides options to execute and delete a Java object.

Jobs

Allows you to create jobs where you can specify the query to be executed at a given interval. You can also execute, modify, or drop a job.

Libraries

Provides options to create, alter, and delete libraries. Note Libraries are available with Oracle 8i and above.

Policies

Allows you to create, enable, disable, modify, and drop policies.

Procs

Enables you to compile, filter, execute, and drop procedures.

Profiles

Provides options that allow you to add a set of limits on a database resource. It enables you to create, alter, or drop a profile.

Queue Tables

Allows you to create, modify, and delete a queue table. These tables are a table type available with the Advanced Queuing feature.

Roles

Provides options that enable you to create, modify, and delete a role. Roles are groups with related privileges. These privileges are granted to end users or other roles.

Rollback Segments

Provides options to create, modify, and drop a rollback segment, which is an object used by Oracle to store the data required to undo changes that occur due to incomplete transactions.

Sequences

Allows you to create, modify, drop, and provide privileges for a sequence. A sequence is an Oracle database structure used to generate unique integers for primary keys.

Snapshots

Provides options to create, modify, refresh, and delete a snapshot from a database. A snapshot is a partial or complete copy of a particular table.

Snapshot Logs

Creates, modifies, and drops snapshot logs from a database. A snapshot log is a table based on the master table on which the snapshot exists. The snapshot log enables you to update the snapshot when any modifications occur in the master table.

Synonym

Enables you to create, alter, and drop a synonym that is an alias created for a program unit, such as a table, view, or sequence.

Tables

Provides options to add a table in the database. Also enables you to modify the structure of a table in the database.

Tablespaces

Allows you to create, modify, drop, coalesce, and export tablespaces.

Triggers

Allows you to create, enable, disable, compile, and drop triggers. A trigger is a set of SQL statements that is executed when a particular event occurs.

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Table 2-1: Objects in the Objects Panel Object

Description

Types

Provides options to create object types owned by a particular schema.

Users

Creates new end users with grants based on privileges provided to other end users. It also enables you to modify, lock, unlock, and drop end users.

Views

Provides options to create and modify a view. A view is a virtual table that extracts data from a base table. No data from the view is stored in the database.

TOAD allows you to remove any object tabs so that these tabs are invisible in the Objects panel. To remove the Users object from the Objects panel: 1. Right-click on the Objects tabs. A pop-up menu appears, as shown in Figure 2-6:

Figure 2-6: The Pop-up Menu: Objects Panel 2. Deselect the Users object from the pop-up menu. 3. The pop-up menu disappears and the Users object is removed from the Objects panel. TOAD also allows you to configure the sequence in which you want the objects to appear in the Objects panel by selecting the Configure option from the pop-up menu. This shows the Browser Tabs Order dialog box, as shown in Figure 2-7:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 2-7: The Browser Tabs Order Dialog Box

The Details Panel The Details panel is the panel to the right of the Schema Browser window. The Details panel provides information about the objects selected in the Objects panel. The Details panel of Schema Browser appears, as shown in Figure 2-8:

Figure 2-8: The Details Panel Note The tabs available in the Details panel are based on the object type selected from the Objects panel.

The Schema Browser Toolbar When you open the Schema Browser window, you can see the Standard toolbar of TOAD. You also see toolbars as part of the Objects and Details panels. There is a common toolbar provided, as shown in Figure 2-9:

Figure 2-9: The Schema Browser Toolbar

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Table 2-2 describes the buttons provided in the Schema Browser toolbar: Table 2-2: The Schema Browser Toolbar Buttons Button

Button Name

Description

Refresh All Lists

Refreshes all the information provided in the Objects and Details panels.

Refresh Details Panel

Refreshes all information provided in the Details panel only.

Clear All Data Grid Filters

Removes all the filters currently opened in the Details panel.

Change Active Session

Allows you to change the current session by changing the end user connected to the database.

Working with the Tables Object A table is a collection of related information stored in a database. A table contains rows and columns in which data is entered. The Tables object is part of the Oracle database that allows you to store data in each cell formed by a row-column intersection. By default, when you open Schema Browser, the Tables object is selected in the Objects panel and the Details panel contains information related to the Tables object. The Objects panel lists all the tables available in the selected database. A toolbar is also provided that allows you to work with the table object. Note You can change the database from the Database drop-down list provided by the TOAD Server Login dialog box. For more information on the TOAD Server Login dialog box, refer to Chapter 1.

Using the Tables Toolbar TOAD provides buttons that allow you to work with the Tables object. These buttons are placed on the Tables toolbar, as shown in Figure 2-10:

Figure 2-10: The Tables Toolbar Table 2-3 describes the buttons provided in the Tables toolbar: Table 2-3: The Tables Toolbar Buttons Button

Button Name

Description

Create SQL Script

Opens the Table Script Creation dialog box that allows you to create an SQL code for a selected table.

Create Table

Opens the Create Table dialog box that allows you to create a new table for the current database.

Alter/Modify Table

Opens a dialog box for the selected table that allows you to modify the structure of the table.

Export Data as Insert Statement

Opens the Data Export dialog box that allows you to create Insert statements for the selected table.

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Table 2-3: The Tables Toolbar Buttons Button

Button Name

Description

Open Table in SQL Modeler

Opens a dialog box where you can enter the number of referential tables that you want to be shown in the SQL Modeler window. Note For more information on using the SQL Modeler, refer to Chapter 3.

Add or Change Privileges

Opens the Table Privileges dialog box for the selected table that allows you to add or modify privileges granted to database users for the selected table.

Add Constraints

Opens the Create Constraints dialog box that allows you to create a new constraint for the selected table.

Analyze Table

Opens the Analyze Tables dialog box that allows you to collate statistics. This statistics enables you to create more appropriate queries related to the table.

Add Public Synonym

Creates a public synonym for the selected table. Note To create a public synonym, you must connect to TOAD as SYSDBA.

Filter the Table List

Opens the Browser Filters dialog box for the selected table. This allows you to show schemas and default filters using filtering options, such as Starts With, Includes, Ends With, and Not Like. Note When any filter is active, the icon changes to a red filter,

.

Rebuild Table

Opens the Rebuild Table window. This window allows you to drop and rename columns that help you build the selected table again.

Compile Dependencies

Allows you to execute all the dependent procedures in the selected procedure, function, or package.

Drop Selected Table

Allows you to delete the selected table from the database. Note After you drop a table, you can revert the action.

Truncate Table

Deletes all data from the selected table.

The Tables Details Panel The Details panel of the Tables object provides various tabs that allow you to work with different components related to the table. The tabs provided in the Details panel include: Columns: Contains two panels, the upper panel with information about the columns of § the selected table and the lower panel that shows any comments added to the table. It also provides a toolbar with buttons to add or drop a column from the table. Figure 2-11 shows the Columns toolbar:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 2-11: The Columns Toolbar §

Indexes: Contains two panels, the upper panel that shows the list of indexes added to the table and the lower panel that shows the parameters and parameter values for the index. It also provides a toolbar with buttons that helps you work with table indexes. Figure 2-12 shows the Indexes toolbar: Figure 2-12: The Indexes Toolbar

§

Constraints: Contains two panels, the upper panel that shows all constraints added to the table and the lower panel with information related to the constraint selected from the upper panel. It also provides a toolbar with buttons to work with table constraints. Figure 2-13 shows the Constraints toolbar:

Figure 2-13: The Constraints Toolbar §

Triggers: Contains two panels, the upper panel that lists all the triggers available for the selected table and the lower panel provides more information about the trigger selected in the upper panel. It also provides a toolbar with buttons to enable, disable, and drop triggers. Figure 2-14 shows the Triggers toolbar:

Figure 2-14: The Triggers Toolbar §

Data: Lists the data for each column of a table. It provides a toolbar with buttons to work with data values. Figure 2-15 shows the Data toolbar: Figure 2-15: The Data Toolbar

§

Script: Shows the SQL script for the selected table. A script is a file that contains SQL statements for database administration. The Script tab also contains a toolbar that helps you work with the SQL script of the table. Figure 2-16 shows the Script toolbar: Figure 2-16: The Script Toolbar

§

Grants: Lists the privileges granted to specific end users associated to a particular table. It provides a toolbar with three buttons that allow you to add and revoke privileges for a table. Figure 2-17 shows the Grants toolbar: Figure 2-17: The Grants Toolbar

§ §

§

Stats/Size: Contains two panels, the upper panel and the lower panel that provide information about the statistics and size of the selected table. Referential: Contains two panels, the upper panel and the lower panel. The upper panel lists information about those tables, which reference the selected table. The lower panel lists those tables that the selected table refers to. Used By: Provides information, such as owner, type, name, and status for the selected table.

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Creating a Table In Oracle, tables contain information in the form of rows and columns. To create a table, you need to create a code in SQL and PL/SQL. This makes the task of creating a table difficult. Schema Browser enables you to create a table without creating a code. The code is auto generated by TOAD on the basis of the options that you select from the dialog box. The procedure to create a table using Schema Browser is: 1. Click the Create Table button, , on the Table toolbar. This opens the Create Table dialog box, as shown in Figure 2-18:

Figure 2-18: The Create Table Dialog Box Note

You can also select Create -> Table to open the Create Table dialog box. 2. In the Name text box, type the name of the table to be created. Note Providing the table name is mandatory. 3. In the Columns tab, type the column names and related details. The options available to provide information include: i. ii.

iii. iv. v. vi. vii. viii.

ix.

Name: Allows you to enter the name of the column to be created for the table. DataType: Provides a list of data types for the table column. DataType is a standard form of data used to define the information to be provided for a particular column. The data type drop-down list contains Oracle and ANSI data types. Size: Allows you to add the size of the data type. Not Null: Provides a checkbox, which if selected, defines that the column should have some value. Primary Key: Provides a checkbox, which if selected, indicates that the value entered for the particular column is to be unique. Unique: Provides a check box, which if selected, adds a unique constraint for the column. Default: Allows you to set the default value for the new rows of the column. Check Constraint: Allows you to provide the column constraint expression. A constraint is a rule for some particular data, which is applied at a database level. Comment: Allows you to add some text information for the table.

This watermark does not appear in the registered version - http://www.clicktoconvert.com

4. Click the Add button to add a new row to provide information about a new column. 5. Click Drop if you want to delete a selected column. 6. Click Show SQL after you have provided all the information required to create the table. The Sql Statement window appears, as shown in Figure 2-19:

Figure 2-19: The Sql Statement Window 7. Click Clipboard to save the Sql script on the clipboard of the computer. 8. Click Save to File to save the script to the computer's hard disk. This opens the Save As dialog box, as shown in Figure 2-20:

Figure 2-20: The Save As Dialog Box 9. Type the name of the file in the File name text box and Click Save. This saves the Sql script of the table in the specified location.

Adding a Constraint The Schema Browser window allows you to add a constraint to a table using the Create Constraint dialog box. A constraint restricts the value added to the column based on which you create the constraint. TOAD allows you to create Primary Key, Check, Unique, or Foreign Key constraints for a particular column. For example, in the BONUS table, you want to add a constraint on the SAL column. This constraint ensures that the value entered in the SAL column is greater than 100: 1. Select the table BONUS from the Tables tab of the Objects panel. This table is a default table provided by the ORCL schema. 2. Click the Create Constraint button, , on the Table toolbar. This opens the Create Constraint dialog box, as shown in Figure 2-21:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 2-21: The Create Constraint Dialog Box Note

You can also select Create -> Constraint to open the Create Constraint dialog box. 3. In the Constraint Name text box, type the name for the constraint. Note Providing the constraint name is mandatory. 4. Select the Check to define the type constraint to be created. This modifies the appearance of the Create Constraint dialog box, as shown in Figure 2-22:

Figure 2-22: The Create Constraint Dialog Box: Check Constraint 5. In the Check Constraint Condition text area, type SAL>100, as the condition to be checked for the value entered in the SAL column of the BONUS table. 6. Click Execute. If there are no errors, a message box appears, as shown in Figure 223:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 2-23: The TOAD Message Dialog Box 7. Click OK. This adds a constraint on the SAL column and closes the TOAD Message and the Create Constraint dialog boxes.

Modifying a Table The Schema Browser window allows you to modify a table using the Alter/Modify dialog box. TOAD allows you to change columns and conditions of these columns to alter the structure of the table. For example, in the BONUS table, you want to delete the column COMM from and add the column DEPT to the BONUS table: 1. Select BONUS from the Tables tab of the Objects panel. The BONUS table is a default table provided by the ORCL schema. 2. Click the Alter/Modify button, , on the Table toolbar. This opens the Alter/Modify dialog box, as shown in Figure 2-24:

Figure 2-24: The Alter/Modify Dialog Box: BONUS Table 3. In the Columns tab, select the row that provides information about the COMM column. 4. Click Drop. A minus sign appears before COMM. 5. Click Add. A new row is added below the COMM column. A plus sign appears in the first column of the new row added, as shown in Figure 2-25:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 2-25: The BONUS Table Dialog Box 6. Click Show SQL to view the code auto generated for the alteration made to the table. This opens the Sql Statement window, as shown in Figure 2-26:

Figure 2-26: The Sql Statement Window: Alter Table 7. Click Close to close the Sql Statement window. The Alter/Modify dialog box appears again. 8. Click OK. The Information dialog box appears, as shown in Figure 2-27:

Figure 2-27: The Information Dialog Box 9. Click OK. The table is modified and the Information and the Alter/Modify dialog boxes close.

Working with the Views Object A view is an Oracle database object that logically represents one or more tables. It contains data extracted from the base table that already exists. For example, you want to extract

This watermark does not appear in the registered version - http://www.clicktoconvert.com

some data from two different tables and add this data to a single table. These tables have a column that is common and that allows to create a view based on the two tables using the common column. A view is stored in the database and a name is provided to identify the view. TOAD allows you to create a view using Schema Browser. When you open Schema Browser, select the Views object tab from the Objects panel. The Objects panel lists all the views available in the selected database. The Details panel shows information related to the Views object. A toolbar is also provided that allows you to work with the table object.

Using the Views Toolbar TOAD provides buttons that allow you to work with views. These buttons are placed on the Views toolbar, as shown in Figure 2-28:

Figure 2-28: The Views Toolbar Table 2-4 describes the buttons provided in the Views toolbar: Table 2-4: The Views Toolbar Buttons Button

Button Name

Description

Create Script

Creates a script for the selected view and copies it to the operating temporary memory.

Create New View

Opens the Create View Info dialog box that allows you to create a new view for the selected table.

Alter/Modify View

Opens the View Info dialog box for the selected view that allows you to modify the structure of the view.

Load in SQL Editor

Opens the code for the selected view in the SQL Editor window. Note For more information on the SQL Editor window, refer to Chapter 4.

Save Source to File

Opens the Save As dialog box that allows you to save the file that contains the SQL code for the view.

Compile View

Executes the SQL code for the selected view.

Compile All Invalid Views

Executes all the views listed.

Edit Privileges

Adds or replaces privileges for the selected view using the View Privileges dialog box.

Filter Views

Lists the views with a particular condition specified in the Browser Filters dialog box.

Create Public Synonym

Creates a public synonym for the selected view.

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Table 2-4: The Views Toolbar Buttons Button

Button Name

Description Note

To create a public synonym, you must connect to TOAD as SYSDBA.

Compile Dependencies

Executes all the procedures dependent on the selected view.

Drop All Views

Deletes all the views available.

Drop View

Deletes the selected view.

Creating a View A view is an imaginary table that contains data related to tables on which it is based. Creating a view ensures that all the data from the base table is not accessible. It also enables you to present data from the base table in a different format by changing the column name without affecting the base table. Schema Browser enables you to create a view. The procedure to create a view using Schema Browser is: 1. Click the Create New View button, , on the Views toolbar. This opens the Create View Info dialog box, as shown in Figure 2-29:

Figure 2-29: The Create View Info Dialog Box 2. In the Name text box, type the name with which the view is to be created. 3. In the text area, type the query for the view, as shown in Listing 2-1: Listing 2-1: Query for a View

SELECT

This watermark does not appear in the registered version - http://www.clicktoconvert.com

DEPT.DEPTNO, DEPT.DNAME, EMP.EMPNO, EMP.ENAME FROM DEPT, EMP WHERE ((DEPT.DEPTNO = EMP.DEPTNO))

In Listing 2-1, a query is created to show the data from the DEPTNO and DNAME columns of the DEPT table and the EMPNO and ENAME columns of the EMP table based on a common column, DEPTNO. Note DEPT and EMP are default tables provided by the SCOTT schema. 4. Click Format Query to format the code. The code appears, as shown in Figure 2-30:

Figure 2-30: The Create View Info Dialog Box: New_View 5. Click Validate Query to check if there are any errors in the code. i. If there is an error, the Information dialog box appears, as shown in Figure 231:

Figure 2-31: The Error Information Dialog Box

ii.

Click OK to return back to the Create View Info dialog box and correct the code. If there is no error, a dialog box appears, as shown in Figure 2-32:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 2-32: The Query Parsed Information Dialog Box 6. In the Create View Info dialog box, click OK to create the view. This opens the Information dialog box, as shown in Figure 2-33:

Figure 2-33: The Information Dialog Box 7. Click OK. This creates the view and closes the Create View Info dialog box.

Working with the Indexes Object An index is an object associated with a table. A view allows you to fetch unique data from a particular table. The Oracle server uses an index to quickly identify those rows of a table that have unique values and to extract only the required data. TOAD allows you to create an index using Schema Browser. When you open Schema Browser, select the Indexes object tab from the Objects panel. The Objects panel lists all the indexes available in the selected database. The Details panel provides information related to the Indexes object. A toolbar is also provided that allows you to work with the table object.

Using the Indexes Toolbar TOAD provides buttons that allow you to work with indexes. These buttons are placed on the Indexes toolbar, as shown in Figure 2-34:

Figure 2-34: The Indexes Toolbar Table 2-5 describes the buttons provided in the Indexes toolbar: Table 2-5: The Indexes Toolbar Buttons Button

Button Name

Description

Create Script from Index

Opens the script for the selected index and stores it on the temporary memory of the operating system.

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Table 2-5: The Indexes Toolbar Buttons Button

Button Name

Description

Create New Index

Opens the Create Index dialog box that allows you to create a new index for the selected table.

Alter/Modify Index

Opens a dialog box for the selected index that allows you to modify the structure of the index.

Rebuild Index

Rebuilds or updates indexes to include the new data added to the base table.

Analyze Index

Allows you to study the statistics of the selected index.

Drop Index

Deletes the selected index from the list of indexes.

Drop All Index

Deletes all the indexes available in the list of indexes.

Creating an Index Indexes are created to quickly access data. When you need to analyze specific data from a table, you will search the table for the data. An index helps you to extract the data you require from a table and avoids unnecessary searches. Schema Browser enables you to create an index. The procedure to create an index using Schema Browser is: 1. Click the Create New Indexes button, , on the Views toolbar. This opens the Create View Info dialog box, as shown in Figure 2-35:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 2-35: The Create Index Dialog Box 2. In the Name text box, type the name with which the index is to be created. 3. Select the table on which you want to create the index from the Table drop-down list. After you select the table, the Table columns option shows all the columns of the selected table. 4. Select the columns on which you want the index to be based using the arrow buttons. 5. Select either Non-unique, Primary Key, or Unique as the function of the index. 6. Click Show SQL to see the auto generated SQL code. This opens the Sql Statement dialog box, as shown in Figure 2-36:

Figure 2-36: The Sql Statement Dialog Box 7. Click Close to close the Sql Statement dialog box. 8. In the Create Index dialog box, click OK to create the index. The Information dialog box appears, as shown in Figure 2-37:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 2-37: The Index Created Information Dialog Box 9. Click OK to create the index and exit the Create Index dialog box.

Chapter 3: Using SQL Modeler SQL Modeler enables you to build complex SQL queries easily and quickly. It automatically generates SQL query statements, such as Select, Insert, Delete, and Update. SQL Modeler enables you to test auto generated queries, view query results, copy the query to SQL editor, and save it for reuse. This chapter describes the SQL Modeler window and its components, including Table Selector, the model area, the Results panel, and toolbar. It explains how to use the model area. It also explains how to auto generate complex SQL queries, such as join statement, views, and condition statement.

The SQL Modeler Window The SQL Modeler window provides an interface to drag and drop tables, create joins, and build queries. To access the SQL Modeler window, select the SQL Modeler option from the Database menu in the Tool for Oracle Application Developer (TOAD) Integrated Development Environment (IDE). The SQL Modeler window appears, as shown in Figure 31:

Figure 3-1: The SQL Modeler Window Note For more information on the TOAD IDE version 7.4, refer to Chapter 1.

This watermark does not appear in the registered version - http://www.clicktoconvert.com

The SQL Modeler window contains Table Selector, the model area, the Results panel, and toolbar.

Table Selector Table Selector is the right panel in the SQL Modeler window. Table Selector enables you to select tables from a specific list. It also enables you to select views, synonyms, and favorites, which you can create using toolbar options. Figure 3-2 shows Table Selector included in the SQL Modeler window:

Figure 3-2: Table Selector You can view tables from various schemas using the schema drop-down list in Table Selector. With Alias, you can view the aliases entered in the aliases.txt file. There are four tabs in Table Selector: Tables: Provides a list of tables from the selected schema. This tab is selected by § default when you open the SQL Modeler window. Views: Provides a list of views created using the Create View Info window. § Note For more information on creating views, refer to Chapter 2. § §

Synonyms: Provides a list of synonyms created using the Create Synonyms window. You can access this window by selecting Create -> Synonym in the TOAD IDE. Favorites: Provides a list of tables, views, and synonyms, which you often use. You can right-click any item from the list and select the Add to Favorites option. This enables fast and easy access to your favorite items from a large list of items.

The Model Area The model area is the left panel in the SQL Modeler window. The model area enables you to create joins and manipulate tables, views, and synonyms. To add tables to the model area, you can drag tables from Table Selector to the model area. Alternatively, you can doubleclick a table to add it to the model area. For example, when you double-click the EMP table in Table Selector, the model area appears, as shown in Figure 3-3:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 3-3: The Model Area Containing the EMP Table

The Results Panel The Results panel is the lower panel in the SQL Modeler window. Figure 3-4 shows the Results panel:

Figure 3-4: The Results Panel There are four tabs in the Results panel: Criteria § Generated Query § Query Results § Explain Plan §

The Criteria Tab You can access the Criteria page using the Criteria tab. This page enables you to edit the properties of individual columns in a table. For example, selecting SAL from the EMP table in the model area, adds the SAL column to the Criteria page, as shown in Figure 3-5:

Figure 3-5: The Criteria Page: The SAL Column You can edit the following properties by double clicking the cell in each property row: Sort: Sets the sorting order for the column. For example, you can display employee § names in ascending or descending order. Condition: Sets a condition using the Where clause. For example, you can set a § condition to retrieve information about employees with salaries greater than $2,000.

This watermark does not appear in the registered version - http://www.clicktoconvert.com

§ §

§ § § §

§

Or: Sets the Or condition in the SQL query. For example, you can retrieve information about employees with salaries greater than or equal to $5,000. Aggregate Function: Sets aggregate function values, such as minimum, maximum, standard deviation, and count. For example, you can retrieve information about the employee with the maximum salary. Field Name: Changes the name for the column. Visible: Shows or hides the name of table columns in the query result. Group: Sets the Group By status of the column. For example, you can retrieve information about employees grouped by their date of hire. Group Condition: Allows you to enter the Group By condition that is applied on the grouped columns. For example, you can retrieve information about all managers grouped by their departments. Table Alias: Sets an alias name for a table.

The Generated Query Tab You can access the Generated Query page using the Generated Query tab. This page shows the SQL query, which the SQL Modeler automatically generates. For example, adding the EMP table to the model area generates the SQL query on the Generated Query page, as shown in Figure 3-6:

Figure 3-6: The Generated Query Page

The Query Results Tab You can access the Query Results page using the Query Results tab. This page shows the result of the SQL query, which is auto generated on the Generated Query page. For example, when you add the EMP table to the model area and execute the query, the Query Results page appears, as shown in Figure 3-7:

Figure 3-7: The Query Results Page

This watermark does not appear in the registered version - http://www.clicktoconvert.com

The Explain Plan Tab You can access the Explain Plan page using the Explain Plan tab. The Explain Plan page shows the execution plan of the selected SQL query. The execution plan contains information about steps that occur during execution of SQL queries. This plan also includes a row that describes each step of the execution plan. You can select the Explain Plan Current SQL option from the SQL Window menu to view execution plan of SQL queries. Figure 3-8 shows the Explain Plan page:

Figure 3-8: The Explain Plan Page Tip In the SQL Modeler window, you can use the Up and Down arrows to move around in lists, the space bar to select and clear check boxes, the Tab key to move forward one area, and Shift+Tab to move back one area.

Toolbar Toolbar provides several buttons to work with the model area, Table Selector, and the Results panel. Figure 3-9 shows the SQL Modeler toolbar:

Figure 3-9: The SQL Modeler Toolbar Table 3-1 lists all the buttons on the toolbar: Table 3-1: Toolbar Buttons and Description Button

Description Creates a new model Opens an existing model Saves the current model with the required name Saves the current model on the hard disk Prints a model

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Table 3-1: Toolbar Buttons and Description Button

Description Edits current model information Adds fields to the required table Generates the Select query statement Executes the SQL query statement Generates and shows an Explain plan Opens the SQL query in the SQL Editor window Shows or hides Table Selector Saves a subquery Cancels a subquery Sets a global Where clause Sets a global Having clause Shows joins between tables Creates views

Using the Model Area Shortcut Options The model area includes various options to work with the tables that you add from Table Selector. When you right-click in the model area, the shortcut options menu appears, as shown in Figure 3-10:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 3-10: The Shortcut Options Menu There are following options in the shortcut options menu: Copy Model Image to Clipboard § Tables § Show § SQL § Auto Join All § Full Screen Model § Optimize All table Sizes § Auto Arrange Tables § Cascade Tables § Arrange Tables Horizontally § Arrange Tables Vertically §

The Copy Model Image to Clipboard Option The Copy Model Image to Clipboard option copies a bitmap image of the table model to the Windows clipboard. You can use this model in Word documents or presentations.

The Tables Option The Tables option includes two suboptions, Visibility and Calculated Fields. Visibility provides the Table visibility dialog box to show or hide tables in the model area. Figure 3-11 shows the Table visibility dialog box:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 3-11: The Table visibility Dialog Box The Calculated Fields suboption adds a new field to a table. For example, to add a new field, GRADE, to the EMP table: 1. Select Table-> Calculated Fields from the shortcut options menu. The Calculated Fields screen appears. 2. Type GRADE in the New Field Name text box. The Calculated Fields screen appears, as shown in Figure 3-12:

Figure 3-12: The Calculated Fields Screen 3. Select the button. The Calculated Field Definition screen appears, as shown in Figure 3-13:

Figure 3-13: The Calculated Field Definition Screen

This watermark does not appear in the registered version - http://www.clicktoconvert.com

4. Click Ok to add the GRADE field to the Defined Fields list box. The Calculated Fields screen appears. 5. Select EMP from the Attachment table drop-down list in the Calculated Fields screen, as shown in Figure 3-14:

Figure 3-14: Selecting the Attachment Table Tip

Use the

button to remove the GRADE field from the EMP table.

6. Click Close. This adds the GRADE field to the EMP table, as shown in Figure 3-15:

Figure 3-15: The EMP Table Containing the GRADE Field

The Show Option The Show option includes the following suboptions to view information about tables: Join Text: Shows the column names for a join between tables § Adjust Model Origin: Positions table in the model area to the upper origin of the SQL § Modeler window Indexes: Shows table indexes against the respective table columns § Primary Key (PK): Shows the PK in the table § Show Field Type: Shows the data types of all columns in a table § When you add the EMP and DEPT tables in the model area and select all the above suboptions, the model area appears, as shown in Figure 3-16:

Figure 3-16: Using the Show Option of the Model Area

This watermark does not appear in the registered version - http://www.clicktoconvert.com

The SQL Option The SQL option includes the following suboptions to work with SQL queries: Run Query in Thread: Stops the execution of the query in between the execution § process. Distinct Mode: Sets the distinct keyword in the query. § Global Where: Provides the Global WHERE Conditions window to add or remove a § global Where clause in the query. Figure 3-17 shows the Global WHERE Conditions window:

Figure 3-17: The Global WHERE Conditions Window In the above figure, the

button adds a global Where clause, the

button removes a

global Where clause, and the button edits a global Where clause. Global Having: Provides the Global HAVING Conditions window to add or remove a § global Having clause in the query. Figure 3-18 shows the Global HAVING Conditions window:

Figure 3-18: The Global HAVING Conditions Window §

Query Variables: Provides the Query Variables dialog box to add, remove, and edit the variables used in a query. Figure 3-19 shows the Query Variables dialog box:

Figure 3-19: The Query Variables Dialog Box

This watermark does not appear in the registered version - http://www.clicktoconvert.com

The Auto Join All Option The Auto Join All option automatically joins all tables in the model area based on the Foreign Key (FK) constraint. Note For more information on constraints, refer to Chapter 2.

The Zoom to Table Option The Zoom to Table option enables you to focus and view any table from a list of tables included in the model area. This enables fast and easy access to the required table. When you select the Zoom to Table option, the Choose a table to focus dialog box appears, as shown in Figure 3-20:

Figure 3-20: The Choose a table to focus Dialog Box

The Full Screen Model Option The Full Screen Model option hides Table Selector and the Results panel to enlarge the model area. For example, when you select the Full Screen Model option after adding the EMP and DEPT tables, the SQL Modeler window appears, as shown in Figure 3-21:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 3-21: Using the Full Screen Model Option

The Optimize All Table Sizes Option The Optimize All Table Sizes option resizes all tables to their default size after you add them to the model area. This shows all tables in their optimum size.

The Auto Arrange Tables Option The Auto Arrange Tables option positions all tables systematically. For example, when you add the EMP, DEPT, SALGRADE, and BONUS tables to the model area and select the Auto Arrange Tables option, the SQL Modeler window appears, as shown in Figure 3-22:

Figure 3-22: Using the Auto Arrange Tables Option

The Cascade Tables Option The Cascade Tables option cascades all tables one after the other to form a list of table structures. Using this option, you can easily identify the number of tables in the model area.

This watermark does not appear in the registered version - http://www.clicktoconvert.com

For example, when you select the Cascade Tables option, the model area appears, as shown in Figure 3-23:

Figure 3-23: Using the Cascade Tables Option

The Arrange Tables Horizontally Option The Arrange Tables Horizontally option positions all tables horizontally in the model area. For example, when you select the Arrange Tables Horizontally option, the model area appears, as shown in Figure 3-24:

Figure 3-24: Using the Arrange Tables Horizontally Option

The Arrange Tables Vertically Option The Arrange Tables Vertically option positions all tables vertically in the model area. For example, when you select the Arrange Tables Vertically option, the model area appears, as shown in Figure 3-25:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 3-25: Using the Arrange Tables Vertically Option

Using the Table Shortcut Options The table shortcut options enable you to alter properties of a specific table in the model area. For example, when you right-click the EMP table in the model area, the shortcut options menu appears, as shown in Figure 3-26:

Figure 3-26: The Shortcut Options Menu for the EMP Table There are following options in the shortcut options menu: Set Table Alias § Alias Field Names § Auto Join § Describe § Show Schema Name in SQL § Remove Table § Select All § Unselect All § Invert Selection § Optimize Size § Hide §

This watermark does not appear in the registered version - http://www.clicktoconvert.com

The Set Table Alias Option You can use the Set Table Alias option to provide an alias name for the EMP table. For example, to provide an alias name, EMPLOYEE_TABLE, for the EMP table: 1. Select the Set Table Alias option. 2. Enter the alias name, EMPLOYEE_TABLE. The Set Table Alias dialog box appears, as shown in Figure 3-27:

Figure 3-27: The Set Table Alias Dialog Box 3. Click OK to assign the alias name to the EMP table, as shown in Figure 3-28:

Figure 3-28: The EMP Table: Alias Name

The Alias Field Names Option Use the Alias Field Names option to provide an alias name for all columns in the EMP table. When you select this option, the Edit Field Name Aliases dialog box appears, as shown in Figure 3-29:

Figure 3-29: The Edit Field Name Aliases Dialog Box

This watermark does not appear in the registered version - http://www.clicktoconvert.com

The Auto Join Option You can use the Auto Join option to automatically join the EMP table to any other table. SQL Modeler uses FK constraints to auto join tables.

The Describe Option You can use the Describe option to view all the information about the EMP table, such as indexes, constraints, triggers, scripts, partitions, subpartitions, and grants. When you select this option, the Table dialog box for the EMP table appears, as shown in Figure 3-30:

Figure 3-30: Using the Describe Option

The Show Schema Name in SQL Option You can show or hide the schema name in the SQL query using the Show Schema Name in SQL option. This option helps you determine the schema name to which the EMP table belongs. When you select this option and access the Generated Query page, the schema name for the EMP table appears, as shown in Figure 3-31:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 3-31: Using the Show Schema Name in SQL Option

The Remove Table Option You can use the Remove Table option to remove the EMP table from the model area. Selecting this option removes the EMP table from the model area, as shown in Figure 3-32:

Figure 3-32: Using the Remove Table Option

The Select All Option You can use the Select All option to select all columns of the EMP table and edit their properties on the Criteria page. When you select this option, the SQL Modeler window appears, as shown in Figure 3-33:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 3-33: Using the Select All Option

The Unselect All Option You can use the Unselect All option to remove all columns of the EMP table from the Criteria page. When you select this option, the SQL Modeler window appears, as shown in Figure 334:

Figure 3-34: Using the Unselect All Option

The Invert Selection Option The Invert Selection option selects all columns of the EMP table if you have not selected them, or cancels the selection of all the columns of the EMP table after you select them.

The Optimize Size Option The Optimize Size option resizes the EMP table to its default size after you add the table to the model area.

This watermark does not appear in the registered version - http://www.clicktoconvert.com

The Hide Option You can use the Hide option to hide the EMP table in the model area. When you select this option, the model area appears, as shown in Figure 3-35:

Figure 3-35: Using the Hide Option Tip Select Tables -> Visibility to view the EMP table again in the model area.

Auto Generating Complex SQL Queries SQL Modeler enables you to generate complex SQL queries, quickly and easily. You can generate different types of SQL queries, such as join statements, views, and condition statements.

Auto Generating a Join Statement SQL Modeler facilitates the auto join of tables in the model area to create join statement queries. Join statement queries enable you to retrieve, combine, and display information from multiple tables. For example, you can generate the following join statement query in SQL Modeler: Select * from emp, dept where ((dept.deptno = emp.deptno)) In the above query, emp and dept are the names of the employee and department tables, respectively. The deptno column joins the employee and department tables to display information from both tables. To generate the join statement query: 1. Drag the EMP table from Table Selector to the model area. 2. Similarly, drag the DEPT table from Table Selector and drop to the model area. This creates an auto join between the EMP table and DEPT tables for the DEPTNO column. 3. Select the Generated Query tab. The SQL Modeler window auto generates the join statement query, as shown in Figure 3-36:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 3-36: The SQL Modeler Window: Join Statement 4. Select the Execute query button to view the result of the join statement query. The Query Results page appears, as shown in Figure 3-37:

Figure 3-37: The Result of the Join Statement Query

Auto Generating a View SQL Modeler enables you to generate views. A view is a user-defined display of data from multiple tables or views. For example, to display information from the EMP and DEPT tables by using a view: 1. Create a view, MyView. 2. Execute MyView.

Creating a View You can create a view, MyView, to display information about employee numbers, employee names, department numbers, department names, and job titles. To create MyView: 1. Add the EMP and DEPT tables to the model area. 2. Select the required table columns in the model area to generate the SQL query, as shown in Figure 3-38:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 3-38: The Generated Query for the Required View 3. Select Create View by right-clicking in the Results panel. The Create View Info window appears. 4. Specify MyView in the Name text box. The Create View Info window appears, as shown in Figure 3-39:

Figure 3-39: The Create View Info Window 5. Click Format Query in the Create View Info window to align the SQL query. 6. Click Show SQL. The Sql Statement window appears, as shown in Figure 3-40:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 3-40: The Sql Statement Window 7. Click Close. The Create View Info window appears. 8. Click OK. The Information dialog box appears, as shown in Figure 3-41:

Figure 3-41: The Information Dialog Box

Displaying Employee Information After MyView is created, you need to execute the auto generated Create statement to display the employee information. To do this: 1. Select the button to refresh Table Selector. 2. Select the View tab in Table Selector. This adds MyView to the Views list, as shown in Figure 3-42:

Figure 3-42: Table Selector: MyView Note

By default, MyView is changed to uppercase in Table Selector Views list. 3. Double-click MYVIEW from the View list to add MYVIEW to the model area. 4. Remove the EMP and the DEPT tables from the model area.

This watermark does not appear in the registered version - http://www.clicktoconvert.com

5. Select the Execute query button. The query result of MYVIEW appears, as shown in Figure 3-43:

Figure 3-43: The Query Result of MYVIEW

Auto Generating a Condition Statement SQL Modeler allows you to generate queries that retrieve information according to specific conditions. For example, you can generate a query to retrieve information about employees whose job title is either a salesman or manager and salary is greater than $1,500. To generate this query: 1. Add the columns in the EMP table to the Criteria page. 2. Define conditions. 3. View the query result.

Adding EMP Table Columns You need to add the columns in the EMP table to the Criteria page to define conditions for required employee information. To do this: 1. Select the button to create a new SQL model. 2. Double-click the EMP table to add it to the model area. 3. Right-click the EMP table and select the Select All option to display all columns on the Criteria page. The SQL Modeler window appears, as shown in Figure 3-44:

Figure 3-44: The SQL Modeler Window: Criteria Page

Defining Conditions

This watermark does not appear in the registered version - http://www.clicktoconvert.com

After adding the columns in the EMP table, you need to define conditions for the JOB and SAL columns. To do this: 1. Double-click the Condition cell in the JOB column. The WHERE Definition dialog box used to specify the required condition appears. 2. Select the = operator from the drop-down list and type SALESMAN in the text box to define the Where condition. The WHERE Definition dialog box appears, as shown in Figure 3-45:

Figure 3-45: The WHERE Definition Dialog Box 3. Click Ok and double-click the Or cell in the JOB column. The WHERE Definition dialog box appears. 4. Select the = operator from the drop-down list and type MANAGER in the text box to define condition as JOB equal to MANAGER. 5. Click Ok and double-click the Condition cell in the SAL column. 6. Select the > operator from the drop-down list and type 1500 in the text box. The WHERE Definition dialog box appears, as shown in Figure 3-46:

Figure 3-46: The WHERE Definition Dialog Box: SAL Column 7. Click Ok to add the defined conditions. The Criteria page appears, as shown in Figure 3-47:

Figure 3-47: The Criteria Page After Defined Condition 8. Select the Generated Query tab. The Generate Query page appears, as shown in Figure 3-48:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 3-48: The Generate Query Page

Viewing the Result of the Query You can view the result of the query using the Query Results tab. To view the result of the generated query: 1. Select the Query Results tab. The Confirm dialog box appears, as shown in Figure 349:

Figure 3-49: The Confirm Dialog Box 2. Click Yes to execute the condition statement query. The Query Results page appears, as shown in Figure 3-50:

Figure 3-50: The Query Results Page for the Condition Statement

3. Select the button from the toolbar. The Save Current Model dialog box appears. 4. Type MyModule. The Save Current Model dialog box appears, as shown in Figure 351:

Figure 3-51: The Save Current Model Dialog Box

This watermark does not appear in the registered version - http://www.clicktoconvert.com

5. Click Save to save the model as MyModule. 6. Close the SQL Modeler window.

Chapter 4: Using SQL Editor SQL Editor enables you to type, edit, develop, and execute SQL statements and scripts. A script is a set of multiple SQL statements. SQL Editor executes the set of multiple SQL statements and displays the output in the Results panel. You can save the output to view it in different formats, such as HTML (Hypertext Markup Language), XML (eXtensible Markup Language), or Excel sheet. SQL Editor also enables you to edit data in the Results panel and update or modify the database. SQL Editor contains the SQL Editor window. This window provides SQL templates and the table and column select lists to help develop scripts. You can save SQL statements and scripts to reuse them. The hot keys, type-ahead, and auto-correct features of SQL Editor help increase productivity and eliminate errors in scripts. This chapter describes the SQL Editor window and its components, including toolbar, the text editor, and the Results panel. It explains how to recall SQL statements and personal SQL statements. It also explains how to modify, insert, sort, and save data from an editable result set. It also describes how to use editor options for SQL Editor.

The SQL Editor Window The SQL Editor window provides an interface to type SQL statements and scripts to execute them in the Results panel. To access the SQL Editor window, select the SQL Editor option from the Database menu in the TOAD IDE. The SQL Editor window appears, as shown in Figure 4-1:

Figure 4-1: The SQL Editor Window There are two panels in the SQL Editor window, the text editor and the Results panel. This window also includes toolbar buttons to work with the text editor and Results panel. You can set SQL Editor as the startup window on a new connection. To do this, select the Sql Editor check box in the TOAD Options dialog box, as shown in Figure 4-2:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 4-2: The Toad Options Dialog Box

Toolbar There are two types of toolbars in the SQL Editor window, Main and Edit. There are several buttons on the toolbars that enable you to perform different tasks such as save, load, or execute SQL statements, modify result sets, and find or replace text in the text editor.

The Main Toolbar The Main toolbar includes the buttons to execute, add, and recall SQL statements. Figure 43 shows the Main toolbar in the SQL Editor window:

Figure 4-3: The SQL Editor Window: Main Toolbar Table 4-1 lists the Main toolbar buttons and their description: Table 4-1: The Main Toolbar Buttons Button

Description Executes a selected SQL statement Executes a SQL statement at the cursor Executes all SQL statements in the text editor as a script Recalls previous SQL statements Recalls the personal SQL statement Inserts a row to an editable result set

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Table 4-1: The Main Toolbar Buttons Button

Description Deletes a row from an editable result set Updates changes in the database Reverts changes before updating Loads the sql files in the text editor Create an sql file to save statements in the text editor Saves the changes in the sql file Creates a Visual Basic (VB) development tool code statement Removes non-sql syntax from the VB development tool code statement Executes an Explain Plan for the selected statement Tunes the selected statement using the SQLab Xpert tuner Changes the active session of the current window

The Edit Toolbar The Edit toolbar includes the buttons to edit SQL statements in the text editor. Figure 4-4 shows the Edit toolbar in the SQL Editor window:

Figure 4-4: The SQL Editor Window: Edit Toolbar Table 4-2 lists the Edit toolbar buttons and their description: Table 4-2: The Edit Toolbar Buttons Button

Description Deletes a selected part of the SQL statement from the text editor to the clipboard Copies a selected part from the text editor to the clipboard Inserts text to the text editor from the clipboard Selects all texts in the text editor

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Table 4-2: The Edit Toolbar Buttons Button

Description Clears the text editor Finds a specific text in the text editor Finds next occurrence of the selected text in the text editor Finds and replaces a specific text from the text editor Reverts the last change in the text editor Reapplies the revert changes in the text editor Converts the selected text to uppercase Converts the selected text to lowercase Converts the selected text to title case Increases indentation in the text editor Decreases indentation in the text editor Prints the content of the text editor Shows the Table Name Select window to select tables Shows the Column Name Select window to select columns Shows the SQL Templates window to select SQL templates

The Text Editor The text editor enables you to edit SQL statements and scripts. The outcome of the SQL statements that you write in the text editor appears in the Results panel. For example, when you write the select * from emp query in the text editor and click the Execute Query button, the SQL Editor window appears, as shown in Figure 4-5:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 4-5: Using the SQL Editor Window

Bookmarking SQL Statements You can write multiple SQL statements and scripts in the text editor. To access a particular query from multiple SQL statements, the text editor provides the bookmark option. This option enables you to bookmark any SQL query with a number. You can use the bookmark number to access the required SQL statement. For example, to access a select statement using the bookmark option: 1. Place the cursor on the select * from emp query in the text editor. 2. Right-click in the text editor and select Set Bookmark -> Bookmark 0, as shown in Figure 4-6:

Figure 4-6: Setting Bookmark 0 3. Type the select * from dept query in the last line of the text editor. 4. Place the cursor on the select * from dept query and select Set Bookmark -> Bookmark 1, as shown in Figure 4-7:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 4-7: Setting Bookmark 1 5. Scroll to the first line in the text editor. The SQL Editor window appears, as shown in Figure 4-8:

Figure 4-8: The SQL Editor Window: Bookmark 0 6. Right-click in the text editor and select Goto Bookmark -> Bookmark 1. 7. Click the Execute button. The SQL query that contains Bookmark 1 appears, as shown in Figure 4-9:

Figure 4-9: SQL Query with Bookmark 1

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Formatting SQL Statements Formatting improves the visibility of SQL statements. You can use the Formatting tools option to format SQL statements in the text editor. For example, to format the select statement: 1. Type the select statement that you need to format, as shown in Figure 4-10:

Figure 4-10: The Text Editor: Select Statement 2. Right-click in the text editor and select Formatting tools -> Format Code. After formatting, the select statement appears in the text editor, as shown in Figure 4-11:

Figure 4-11: The Formatted Select Statement

The Results Panel The Results panel shows the output of the SQL queries and scripts that you type in the text editor. There are six tabs in the Results panel: Data § Explain Plan § Auto Trace § DBMS Output § Code Statistics § Script Output §

The Data Tab You can access the Data page using the Data tab. The Data page shows the result of the SQL query. For example, to view the result of the select statement query: 1. Type the following SQL statement in the text editor: 2.

Select * from emp where deptno=30

3. Select the button from the Main toolbar to execute the select statement. The Data page in the Results panel appears, as shown in Figure 4-12:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 4-12: The Data Page

The Auto Trace Tab You can access the Auto Trace page using the Auto Trace tab. This page displays the performance statistics of a query. Select the right-click options in the text editor to enable the Auto Trace feature. Figure 4-13 shows the Auto Trace page:

Figure 4-13: The Auto Trace Page

The DBMS Output Tab You can access the DBMS (Database Management System) Output page using the DBMS Output tab. This page displays the result of a DBMS Output statement. For example, to view the result in the DBMS Output page: 1. Type the following DMBS Output statement in the text editor: 2. 3.

Begin Dbms_output.put_line (`This is the dbms output');

4. End; 5. Select the DBMS Output tab in the Results panel. 6. Select the button to activate the DBMS Output. 7. Execute the DMBS Output statement. The DMBS Output page appears, as shown in Figure 4-14:

Figure 4-14: The DMBS Output Page

This watermark does not appear in the registered version - http://www.clicktoconvert.com

The Code Statistics Tab You can access the Code Statistics page using the Code Statistics tab. This page displays the code statistics summary of the selected statement. Figure 4-15 shows the Code Statistics page:

Figure 4-15: The Code Statistics Page

The Script Output Tab You can access the Script Output page using the Script Output tab. This page displays the result of SQL scripts. For example, to view the result in the Script Output page: 1. Type the following script in the text editor: 2.

Select * from dept;

3.

Select * from emp where deptno=30

4. Select the button from the Main toolbar. The Script Output page appears, as shown in Figure 4-16:

Figure 4-16: The Script Output Page

Working with SQL Editor SQL Editor enables you to recall all the SQL statements that you have written in the text editor. It also enables you to add and recall personal SQL statements. A personal SQL statement is one that you always type in the text editor.

Recalling SQL Statements

This watermark does not appear in the registered version - http://www.clicktoconvert.com

To recall SQL statements, select the SQL Command Recall option from the SQL-Window menu. You can also select the

button from the Main toolbar to recall SQL statements.

For example, to recall all the previous SQL statements in the text editor, select the button. The SQL Statement Recall window appears, as shown in Figure 4-17:

Figure 4-17: The SQL Statement Recall Window The SQL Statement Recall window contains the following buttons: Prev: Selects the previous SQL statement § Next: Selects the next SQL statement § Editor Append: Inserts the selected SQL statement in the text editor § Editor Replace: Replaces all the SQL statements in the text editor with the selected § SQL statement Clipboard: Copies the selected SQL statement to the clip board § Remove: Deletes the selected SQL statement from the SQL Statement Recall window § Cancel: Closes the SQL Statement Recall window §

Recalling Personal SQL Statements To recall personal SQL statements, you need to add the selected SQL statement to the SQL Statement Recall window. For example, to recall a personal statement: 1. Type select * from dept in the text editor. 2. Select Add to Personal SQLs from the SQL-Window menu, as shown in Figure 4-18:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 4-18: Selecting the Add to Personal SQLs Option

3. Select the button from the Main toolbar. The SQL Statement Recall window appears to add the required personal SQL statement to the text editor.

Editing Result Sets By default, you cannot edit the result set. You need to use the edit command to edit the result set. In the editable result set, you can modify existing data, insert new rows, or delete existing rows.

Modifying the Existing Data You can modify and update data in the result set using the edit command. For example, to change an employee's name in the emp table: 1. Type the edit emp query in the text editor. The editable result set of the emp table appears in the Results panel, as shown in Figure 4-19:

Figure 4-19: The Editable Result Set

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Note

The green button in the status bar of the SQL Editor window indicates that you can edit records in the Results panel. The red button indicates that you cannot edit records in the Results panel. 2. Select SMITH from the ENAME column and press Enter to edit. A blinking cursor appears. 3. Type SHAWN in the selected cell. The updated result set appears, as shown in Figure 4-20:

Figure 4-20: The Updated Result Set

Inserting a New Row If you select the button from the Main toolbar, it inserts a new row in the editable result set. For example, to add and edit a new row in the emp table: 1. Select the button. This inserts a new row in the editable result set of the emp table. 2. Select the down-arrow button in the EMPNO column to open a calculator, as shown in Figure 4-21:

Figure 4-21: Opening a Calculator 3. Enter the employee number and type SAM, MANAGER, and 7839 as the employee name, job title, and manager code, respectively. 4. Select the down-arrow button in the HIREDATE column to open a calendar, as shown in Figure 4-22:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 4-22: Opening a Calendar 5. Click Today to set the current date as the data of hire. 6. Enter employee salary in the SAL column. 7. Type the department number as 20 in the DEPTNO column. The result set with a new row appears, as shown in Figure 4-23:

Figure 4-23: The Result Set 8. Right-click in the Results panel and select the Record Count option to find the total number of records after inserting a new row. The TOAD Message dialog box appears, as shown in Figure 4-24:

Figure 4-24: The TOAD Message Dialog Box 9. Click OK to exit the TOAD Message dialog box.

Sorting Data in the Result Set SQL Editor enables you to sort data in an ascending or descending order. For example, to sort employee names in an ascending order:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

1. Click the ENAME column header. The Sort options window appears, as shown in Figure 4-25:

Figure 4-25: The Sort options Window 2. Click Apply. This sorts employee names in the ascending order, as shown in Figure 426:

Figure 4-26: Employee Names in the Ascending Order

Saving the Result Set SQL Editor enables you to save result sets in different formats, such as HTML, Excel sheet, XML, XML with XLS, and SQL Loader. For example, to save the result set of the emp table as an Excel sheet: 1. Right-click in the Results panel and select the Save As option. The Save Grid Contents screen appears. 2. Select the XLS File format and enter the file path in the Save Grid Contents screen, as shown in Figure 4-27:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 4-27: The Save Grid Contents Screen 3. Click OK to create the required Excel sheet. The TOAD Message dialog box appears, as shown in Figure 4-28:

Figure 4-28: The Toad Message Dialog Box 4. Click OK and open the EmployeeData.xls file. The Excel sheet appears, as shown in Figure 4-29:

Figure 4-29: The EmployeeData.xls File

Selecting Tables, Columns, and SQL Templates

This watermark does not appear in the registered version - http://www.clicktoconvert.com

SQL Editor enables you to select tables, columns, and SQL templates to add in the text editor. This helps you to find the number of columns in a table as well as the number of tables in a schema.

Using the Table Name Select Window The Table Name Select window provides a list of tables to select from a specific schema. You can select multiple tables to add in the text editor. When you select the button from the Edit toolbar, the Table Name Select window appears, as shown in Figure 4-30:

Figure 4-30: The Table Name Select Window Tip Double-click the table name to add it in the text editor.

Using the Column Name Select Dialog Box The Column Name Select window provides a list of columns to select from a specific table. You can select multiple columns to add in the text editor. When you select the button from the Edit toolbar, the Column Name Select window appears. For example, when you select the DEPT table from the drop-down list, all the columns of the DEPT table appears, as shown in Figure 4-31:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 4-31: The Column Name Select Window Tip Double-click the column name to add it in the text editor. SQL Editor provides the type-ahead feature to quickly complete and execute SQL statements. You can type a table name in the text editor and press dot (.) to view all the columns of the table. For example, when you type, select * from emp, and press, the column names appears, as shown in Figure 4-32:

Figure 4-32: Selecting Columns in the Text Editor

Using the SQL Templates Window The SQL Templates window provides a list of templates to add in the text editor. When you select the button from the Edit toolbar, the SQL Templates window appears, as shown in Figure 4-33:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 4-33: The SQL Templates Window Tip Double-click the required template type to add it in the text editor. Note Click Close to exit the SQL Templates window.

Chapter 5: Using the Procedure Editor The Procedure Editor is a tool that helps you edit, compile, and debug stored procedures, functions, packages, and triggers for an Oracle database. The Procedure Editor functions in a way is similar to the SQL Editor. The Procedure Editor window enables you to write several statements in a single file using SQL Plus scripts. This chapter explains how to work with the Procedure Editor. It describes the Procedure Editor Window, Procedure Editor Toolbar, and the Procedure Editor Panel. The chapter also discusses how to create and debug a procedure and to control versions of the source code.

Overview of the Procedure Editor The Procedure Editor can be used to separate a package specification from the package body by saving both as separate objects. To open the Procedure Editor window: 1. Open TOAD. The TOAD Server Login Window appears, as shown in Figure 5-1:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 5-1: The TOAD Sever Login Window 2. Type the password and click Ok. This opens the TOAD interface. 3. Click Database -> Procedure Editor. This opens the Procedure Editor Window.

The Procedure Editor Window The Procedure Editor window helps you create and compile a procedure. The Procedure Editor window appears, as shown in Figure 5-2:

Figure 5-2: The Procedure Editor Window

The Procedure Editor Toolbar The Procedure Editor includes toolbars that you can use to compile and debug a procedure. Table 5-1 describes each tool button in the Main toolbar: Table 5-1: The Main Toolbar Option

Description Compiles the code. Compiles from the cursor position until the end of the editor.

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Table 5-1: The Main Toolbar Option

Description Compiles to the cursor position from the beginning of the editor. Compiles the current statement. Note The Compile FROM, Compile TO, and Compile the current statement are enabled if you select the Enable Compiling Multiple Objects From a Single File option in the Procedure Editor tab of the TOAD Options dialog box. Loads a SQL file into the Procedure Editor. It provides a drop-down list from which you can choose the most recent files used in the Procedure Editor window. Saves the contents of the Procedure Editor as a new file. Saves the contents of the Editor to the file. Checks out the file of the source control. Checks in the file to the source control. Loads the source from existing objects. Reloads objects from the database. Creates a new PL/SQL object, such as a procedure, function, or trigger. Compiles dependant procedures. Tunes the current statement using the SQLab Xpert tuner. Changes active session for the window.

You can also format the contents of the Procedure Editor using the context menu that provides options from the menus. The options of the context menu are: Describe: Provides a pop-up menu that explains the selected object. If the selected § object is a procedure, it displays information about the procedure. Compile: Compiles the procedure that is currently open. § Close File/Tab: Closes the current file or tab. § New File/Tab: Adds another tab. § Comment Block/Uncomment Block: Adds or removes the slash (/) that denotes a § comment entry before the selected lines. Find Closing Blocks: Finds the closing parenthesis. Finds a corresponding End for a § Begin and a corresponding End If for an If. Procedure Editor Desktop: Enables you to customize the Procedure Editor toolbar by § enabling you to display the Navigator panel, Debug panel, and Status toolbar. You can also customize your Procedure Editor toolbar from this submenu. Debug: Enables you to access debugging commands, such as add watch and set § break points.

This watermark does not appear in the registered version - http://www.clicktoconvert.com

§

Read Only: Toggles the Read-Only status of a file.

The Procedure Editor Panels The Procedure Editor is divided into the left and right panels. The left panel contains the Package Navigation list. The Package Navigation list contains a list of objects or the contents of the package.

The Left Panel The Left panel contains information about a package. A package consists of the package specification, or spec, and the body of the package. Functions are displayed as f() and procedures are displayed as p(). The toolbars in the Package Navigator list are: §

: Refreshes the packages in the Package Navigator list.

§ §

: Sorts the packages in alphabetical order. : Closes the Package Navigator.

The Right Panel The right panel contains the code for selected object. You can open multiple tabs at a time where each tab contains the contents of an object. For example, you can open a procedure, a function, or a trigger at the same time. You can arrange these tabs by dragging and dropping them anywhere on the screen. To include multiple objects, such as a procedure and a function in one file, you need to enable the Enable compiling multiple objects from a single file feature. To enable the feature, select View -> Options. The TOAD Options dialog box appears, as shown in Figure 5-3:

Figure 5-3: The TOAD Options Dialog Box Select the option, Enable compiling multiple objects from a single file. Other options available in the TOAD Options dialog box are: Reload files when activating TOAD if the file time stamp is changed: Enables you to § edit procedures using an external editor. By default, this option is unchecked. If a

This watermark does not appear in the registered version - http://www.clicktoconvert.com

§ §

§

§

§ §

§ § §

§ § § §

§

§

Procedure Editor window is activated, TOAD checks for the date stamp of the file and checks for modifications. If the file is modified, you will be prompted to reload the file. Highlight names of Stored Procedures: Highlights the functions and procedure names in the Procedure Editor and the SQL Editor windows. This option is checked, by default. Include Owner when extracting source from database: Includes the name of the owner in the command prompt. If you select this option when exporting an object, the object is prefixed with the name of the owner. This option is not selected, by default. Enable compiling multiple objects from a single file: Enables you to compile more than one object in a source file. This option is not selected, by default. Note You must not select the Enable compiling multiple objects from a single file option when you use the PL/SQL Debugger because the PL/SQL Debugger can handle only one file object at a time. Use "CREATE" instead of "CREATE OR REPLACE" when loading database objects: Does not overwrite an object, such as a procedure or a function. This option is not selected, by default. Automatically show objects on Select Object from Database Window: Bypasses the Object Type filter and displays all the objects. This option is not selected, by default. Search for Dependant Objects following a compile: Checks for all dependant objects of the currently compiled object. For example, procedure1 calls procedure2 and you make modifications to procedure2 and compile it. Procedure1 is flagged as invalid and you need to recompile procedure1. TOAD checks for such dependencies and forces you to compile procedure1. This option is selected, by default. Show Package Navigator panel: Displays the hierarchy of packages and functions in the left panel of the Procedure Editor. This option is selected, by default. Sort Package Navigator Panel: Displays the procedures and functions arranged alphabetically. This option is selected, by default. Force lower-case object names in Navigator: Displays the procedure and function names in lower-case. If this option is not selected, the names are displayed as they appear in the database. This option is selected, by default. Notification when compile process is complete: Plays the TOADLOAD.WAV file which is a croak sound when the procedure has been compiled. Only show one Procedure Editor per database connection: Enables one Procedure Editor window for one database connection. This option is not selected, by default. Toggle Modified Flag after compiling source from database: Toggles the Modified flag and indicates that the source has been modified. Allow compiling when source loaded from database: Enables you to compile the code immediately when the source is loaded from the database. If this option is not selected, TOAD prompts you to save the code on the disk before you compile it. This option is not selected, by default. Load Packages into separate tabs when loading source from database: Loads the specification and the body of the package in different tabs. The specification and the body of the package have the same name and are identified by the letters S and B. If this option is not selected, the entire package is loaded in the same tab in the Procedure Editor. Use Multi-line tabs in the editor: Enables you to display the tabs in a single line or in multiple lines when there are many tabs to fit in one line. This option is selected, by default.

The Procedure Editor Options You can set various options, such as specifying the printing and display options, for the Procedure Editor window. To set options for the Procedure Editor window, select Edit -> Editor Options. The Editor Options - PLSQL window appears, as shown in Figure 5-4:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 5-4: The Editor Options - PLSQL Window The options in the left panel of the Editor Options window are: Printing options: Include options to wrap line when printing, print line numbers, print § filename in header, print date in header, and print page numbers. Display options: Display line numbers in gutter, show right margin, show gutter, show § word wrap column, block cursor on overwrite, show wrapped lines with the glyph, show control characters, and apply capitalization effect. The Control options include title as load/save filename parameter, track columns in vertical movement, respond to dropped files, and override word wrapped lines based on line start character. General options: Include options to auto indent, tab to next column, auto select word § on double-click, insert tabs into text for tab characters, insert mix of tab/space for optimal fill, cursor beyond eol, and word wrap lines. You can change the highlighting options for the keywords in the code using the Highlighting options screen, as shown in Figure 5-5:

Figure 5-5: The Highlighting Options Screen You can select an option from the display items and choose a different color for the foreground and background. You can also choose a different font size and style, such as bold and italics. You can set these options for the display items, such as identifier, string,

This watermark does not appear in the registered version - http://www.clicktoconvert.com

and symbol. After you have selected the options, you can see the preview in the same window. To choose a different setting, select the required options and click OK. You can set Key assignments using the Key Assignments in the Editor Options. The Key Assignments window appears, as shown in Figure 5-6:

Figure 5-6: The Key Assignments Window The The § § § § §

Key Assignments window helps you assign a specific key or key combination to a task. various items for which you can set your key combinations are: Bookmarks: Enables you to set different key combinations for the bookmarks. Caret movements: Enables you to set different key combinations for the movement of the cursor. Clipboard operations: Enables you to set a different key combination for clipboard operations, such as copy and paste. Deletion operations: Enables you to set a different key combination for tasks, such as deleting a word, character, or line. Miscellaneous: Enables you to set a different key combination for miscellaneous tasks, such as toggle between insert and overwrite modes, undo, and redo previous tasks.

For example, to change the key combination from to Ctrl and C to Ctrl and A, to copy from the clipboard: 1. Click the Key Assignments options in the Editor Options window. 2. Select the plus symbol, (+), next to Clipboard operations and select the Copy to clipboard option. This activates the add, edit, and delete sequence options and the default key combination appears in the Activation key sequences text box, as shown in Figure 5-7:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 5-7: The Key Assignments 3. Click the Edit sequence option. The Edit Sequence dialog box appears, as shown in Figure 5-8:

Figure 5-8: The Edit Sequence Window for Copy: Step 1 4. Enter a different key combination for copy to clipboard, Ctrl and A, and click Next. The Edit key pair for the copy to clipboard dialog box appears, as shown in Figure 5-9:

Figure 5-9: The Edit Sequence Window for Copy: Step 2 In this step, you can specify an alternate key combination for the task. For example, by default, the task of copying can be performed with the Ctrl and C and Ctrl and Ins key combinations. Similarly, you can specify two different key combinations in two steps. 5. Click Finish to apply the changes. You can also save these changes to a file and load them from the file. Similarly, you can specify different key combinations for the Deletion operations, such as delete character at cursor, delete character before caret, delete previous word, and delete next word. You can also specify key combinations for miscellaneous operations, such as undo, redo, and print selection. TOAD provides you with the option of replacing specific words. Click the auto replace option in the Editor Options window. The Auto replace window appears, as shown in Figure 5-10:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 5-10: The Auto replace Window The Auto replace window also shows the Current selection text box. To include a new word and replace it with another word, type the words in the Current selection text box and click Add definition. You can replace and delete an existing word. Using TOAD, you can set code templates. Code templates are templates that contain specific information and are referred to using a shortcut name. To see various templates, click Code Templates. The Code Templates window appears, as shown in Figure 5-11:

Figure 5-11: The Code Templates Window To use the existing shortcuts, press Ctrl and Spacebar in the Procedure Editor window. This displays the list of existing shortcuts. You can also click any of the shortcuts and the related information is displayed in the preview window. You can either use these shortcuts or create your own shortcuts. To create a new shortcut, click Add definition and enter a name and description.

Using Procedure Templates TOAD provides predefined templates to create a procedure. To create a procedure:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

1. Click Create new PL/SQL object, , on the Main toolbar. The New Procedure Create Options dialog box appears, as shown in Figure 5-12:

Figure 5-12: The New Procedure Create Options Dialog Box The various types of objects are procedure, function, package, and trigger. 2. Select Procedure from the Object Type drop-down list. 3. Type a name for the procedure, for example, Proc 1. 4. Select the default template and click OK. This opens the Procedure Editor window, as shown in Figure 5-13:

Figure 5-13: The Procedure Window For example, create a procedure based on the emp table. The procedure must calculate the commission for the records where Job is ANALYST. The commission must be set to 50 percent of the salary. To do this you need to write the procedure within the Begin and End statements. Figure 5-14 shows the Procedure Editor window with the code:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 5-14: The Procedure Editor Window with the Code 1. Save and compile the procedure. 2. Select Compile from the context menu or the Compile button on the Main toolbar. 3. Execute the procedure by selecting the Execute without Debugging option from the context menu. The Set Parameters window appears, as shown in Figure 5-15:

Figure 5-15: The Set Parameters Window 4. Click OK. This displays a message box that displays the message, Procedure Completed. Check whether or not the table has been modified. 5. Open a new SQL Window and type the statement, select * from emp. Figure 5-16 shows the result of the query:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 5-16: The SQL Window Note

For more information on SQL Editor, refer to Chapter 4.

Debugging a Procedure TOAD provides you with a user-friendly interface to debug procedures. You can set watches, break points, and call stacks to help debug procedures. A break point is a marker in your code where you want to stop the execution of code when debugging. A watch is specified in the code to display the execution of the code at a specific point. A call stack window displays the functions and procedures in the order that these functions and procedures are called, where the recent procedure or function is listed first.

Using Break Points You can access the Break Points window from the Debug menu. The Procedure Editor window shows the Break Points, Watches, Call Stack, and DBMS Output window. Figure 517 shows the Break Point window in the Procedure Editor window:

Figure 5-17: The Break Point Window You can set Break Point commands using the buttons on the Break Points toolbar. The options of the Break Points toolbar are described in Table 5-2: Table 52: The Break Points Toolbar

This watermark does not appear in the registered version - http://www.clicktoconvert.com

O p t i o n

D e s c r i p t i o n E d i t s t h e s e l e c t e d b r e a k p o i n t . A d d s a b r e a k

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Table 52: The Break Points Toolbar O p t i o n

D e s c r i p t i o n p o i n t a t t h e c u r s o r p o i n t . D e l e t e s t h e s e

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Table 52: The Break Points Toolbar O p t i o n

D e s c r i p t i o n l e c t e d b r e a k p o i n t . D i s a b l e s t h e s e l e c t

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Table 52: The Break Points Toolbar O p t i o n

D e s c r i p t i o n e d b r e a k p o i n t . E n a b l e s t h e s p e c i f i c b r

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Table 52: The Break Points Toolbar O p t i o n

D e s c r i p t i o n e a k p o i n t . P l a c e s t h e c u r s o r o n t h e li n e

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Table 52: The Break Points Toolbar O p t i o n

D e s c r i p t i o n t h a t i s r e f e r e n c e d b y t h e s e l e c t e d b r e

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Table 52: The Break Points Toolbar O p t i o n

D e s c r i p t i o n a k p o i n t .

To add a break point to the procedure, p3: 1. Click the Add Break Point button on the Break Point toolbar. Alternatively, you can select the Add Break Point options from the context menu. The Breakpoint Properties dialog box appears, as shown in Figure 5-18:

Figure 5-18: The Break Point Properties Dialog Box 2. Select a line number in your procedure and click OK. This adds a break point at a specific point. To edit a break point, select the break point and click the Edit Break Point option from the Break Point toolbar. You can also set properties for a break point. Various break points that can be specified are: Standard: Causes a break when stepping through the code. It does not have an § associated condition.

This watermark does not appear in the registered version - http://www.clicktoconvert.com

§

Conditional: Causes a break only when a specific condition is met when you execute the code. For example, to set a conditional break point, select the Edit Breakpoint option Note

§

If you undock these windows from the bottom of the Procedure Editor window, double-click the vertical bar to the left of the docked windows. from the context menu. Enter the condition for the breakpoint, for example "sal > 5000". When you execute the procedure, the debugger will stop at the breakpoint only if the condition is met. The operators you can use to specify the conditions are less than or equal to (<=), does not equal (<>), greater than or equal to (>=), less than (<), greater than (>), and equal to (=). Pass count: Causes a break only if a loop has occurred a certain number of times. You can provide the pass count for For, Do while, or If Endif loops. For example, if you type the pass count as four, the debugger will stop before the fourth pass through the break point.

Double-click the break point to set properties for a break point. The Break Point Properties dialog box appears, as shown in Figure 5-18.

Note You can also combine the conditional and the pass count break points. If you specify the condition and pass count, the break will occur when the condition is satisfied.

You can disable break points temporarily, using the Disable Break Point option in the Break Point toolbar. Alternatively, you can select the Disable Breakpoint options from the context menu. Disabled breakpoints appear gray in the Breakpoints window. To enable a disabled break point, click the Enable option in the Break Point toolbar. You can also delete the break point, if it is not required. To delete a break point, click the Delete Break Point option from the Break Point toolbar. Alternatively, you can select the break point and press Delete. The options of the context menu are edit breakpoint, add breakpoint, enable breakpoint, disable breakpoint, delete breakpoint, and view source. The other options are: § § § § §

Enable All Breakpoints: Enables all breakpoints. Disable All Breakpoints: Disables all breakpoints. Delete All Breakpoints: Deletes all breakpoints. Stay on Top: Enables the window to stay above all other windows. Dockable: Enables the windows to be docked with Watches, Call Stacks, and DBMS Output windows.

Using Watches Watches display the code execution at a specific point. Click Debug -> Watches to open the Watches window. You can set watch on implicit and explicit variables using the options in the Watch window toolbar. The options in the Watch window toolbar are: Open edit window § Add a watch at the cursor §

This watermark does not appear in the registered version - http://www.clicktoconvert.com

§ § §

Delete the selected watch Enable the selected watch Disable the selected watch

TOAD can perform watches on several complex data types. You can add a watch in more than one way. To add a watch, click a variable in the Procedure Editor and click the Add Watch option in the toolbar. The variable is added to the list of watches. Alternatively, you can select Debug -> Add Watch at cursor. The Watch Properties dialog box appears as shown in Figure 5-19:

Figure 5-19: The Watch Properties Dialog Box In the Expression box, type the variable you want to watch. If you choose a variable that belongs to a package, you need to select the Package Variable check box. You also need to select the Owner and Package name check boxes. You can set watches on data types, such as data, time, and number. You can also set watches on array values and record types. For example, you can declare an array called arr1 that contains 10 elements. You can set a watch on the first element of the array, arr(1). If you set the Repeat Count option to 4, you can watch elements from 1 to 4. Enter the number of significant digits to be displayed and click OK. Selecting the Break on value change option causes the Debugger to break when the value changes. This option is unavailable when you set watches for a record. After you have set watches, you can edit the watch properties. To modify the watch properties, double-click the watch you want to modify. The Watch Properties dialog box appears. Modify the options and click OK. Note You cannot format watches on records. You can set as many watches you want but too many watches may harm the performance of the Debugger. You can temporarily disable a watch. To do this, double-click the watch in the Watches window and uncheck the Enabled checkbox. Alternatively, you can select the Disable Watch option from the context menu. The disabled watches appear gray marked with the word, Disabled. To disable all watches, select the Disable All Watches option from the context menu. If you want to enable watches again, select the Enable All option from the context menu. You can also double-click the watch and select the Enabled check box in the Watch Properties dialog box. If you select the option Add Watch at Cursor, from the Main toolbar, TOAD sets the watch on the selected text. If no text is selected, it uses the text under the cursor. This is because if

This watermark does not appear in the registered version - http://www.clicktoconvert.com

you have a recordname.fieldname, you can only highlight the recordname to set a watch on it. If you have an expression like arr(2), where arr is an array, then you need to highlight the entire expression to add a watch. The § § § §

configurations on which you cannot set watches using TOAD are: Package variables. Arithmetic in watches. For example, you cannot watch the addition of two variables. Trigger :new.column or :old.column value. Implicitly defined variables when they are also explicitly defined. For example, Listing 5-1 shows the code that defines an explicit variable, count_var that you cannot watch as it loops: Listing 5-1: Code to Show a Function with an Explicit Variable Declaration

create or replace function calc_bonus (salary_in number) return number is count_var number; comm_out number; begin /* Set bonus earned equal to 25 percent of the employee's salary. */ for count_var in 1..5 loop comm_out := salary_in * 0.25; end loop; return bonus_out; end f_calc_bonus;

Listing 5-1 shows the code where the count_var is explicitly declared. To set a watch on the count_var variable, you need to remove the explicit declaration by deleting the following statement from Listing 5-1: count_var number; You can now set a watch for the variable, count_var.

Using Call Stacks The Call Stack window displays the procedure and functions that are called. It displays the names of procedures or functions that are called. For example, if you are accessing line 5 of Proc1 and you move to line 1 of Proc2, the Procedure name and line numbers will be displayed as: Proc2(1) Proc1(5) You can navigate among multiple procedures using the Call Stack window. To navigate among procedures, double-click the procedure name. Alternatively, select the View Source options form the context menu.

This watermark does not appear in the registered version - http://www.clicktoconvert.com

You can dock the Breakpoints, Watches, and DBMS Output windows with the Call Stack window. All these windows appear at the bottom of the Procedure Edit window. If you have undocked these windows from the bottom one at a time, you need to double-click the vertical bar to the left of the docked windows.

Note If you use the Oracle 8.0.5 version or earlier, set the BLANK_TRIMMING value to TRUE in the init.ora file to see the Call Stack tab. If this is not done, the Call Stack menu option will be disabled, and the tab will not be displayed on the Breakpoints, Watches, Call Stack, and DBMS Output window.

Source Control The Source Code Control (SCC) is a Microsoft Application Programming Interface (API) supported by TOAD. The Source Control checks different versions of the source code. It defines a standard interface between various development environments and source control products. You can access the SCC options from the Procedure Editor window. The § § §

following third party utilities support the SCC API and are compatible with TOAD: Microsoft Visual SourceSafe 6.0 PVCS 6.7 Quma Version Control System 3.5 (QVCS)

You can use the functions of the SCC after an entry is created in the registry by each SCCcompliant product. The registry identifies it as the SCC provider and identifies the location of the DLL. To access the functions of the SCC API, select the Source Control option form the context menu in the Procedure Editor window. The SCC API enables you to perform the following functions: Check-out file: Enables you to reload the file from the source control file. Only one § person can check out a file at a time. This is the most recent file and may be different from the file that is stored locally. Check-in File: Sets the file that is checked in, to read-only mode. § Undo-checkout: Rejects any changes and reverts to the original read-only version. § When you select this option, any changes made when the file is checked out, cannot be retrieved. Get latest version: Restores the file from the source control and saves the file on you § local computer. The file that is restored becomes read-only. To make changes to the file you need to check out the file. Add File: Adds a file to source control and makes the local copy of your file read-only. § Select project: Enables you to select a project file that you can add to the source § control. To use the functions of the SCC, you need to install the server and the client on your computer. After you install the client on your computer, you need to confirm whether or not the client is configured to support the SCC API. You can confirm this by checking that the client has created the SCC API registry key. To do this: 1. Select View -> Options -> Source Control. This opens the Source Control dialog box, as shows in Figure 5-20:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 5-20: The Source Control Dialog Box 2. Select your SCC provider and select the required options. 3. Open your file in the Procedure Editor. 4. Right-click in the Procedure Editor window -> Source Control -> Select Project. The SCC provider displays a dialog box where you need to select your project. Based on the provider and the configuration, you need to login and browse for a database. It may provide a place for you to set a working folder. Note The working directory for the selected project must be the same as the directory where you have loaded the file. To store the file in the Source Control, select Source Control -> Add File from the context menu to add the file to the Source Control database. This file is then marked as checked in. You then need to checkout the file to modify it.

Chapter 6: Using Script Manager A script is a set of multiple SQL statements. Script Manager enables you to organize scripts into several categories and access the categories in the Script Manager window. Script Manager also enables you to add, edit, remove, and execute scripts in the Script Manager window. This chapter describes the Script Manager window and its components, including toolbar, the Script panel, and the Status panel. It also describes how to manage categories and scripts. It explains how to create a new category, add scripts to a category, combine multiple scripts from a category, and execute these scripts using the Quest ScriptRunner window.

The Script Manager Window The Script Manager window enables you to manage and organize scripts into categories. A category is a Script Datafile with the .sdf extension. You can access the Script Manager window from the Tool for Oracle Application Developers (TOAD) Integrated Development Environment (IDE). Note For more information on the TOAD IDE, refer to Chapter 1.

This watermark does not appear in the registered version - http://www.clicktoconvert.com

When you click the button on the Main toolbar in the TOAD IDE, the Script Manager window appears, as shown in Figure 6-1:

Figure 6-1: The Script Manager Window Note You can also access the Script Manager window by selecting the Script Manager option from the File menu. The Script Manager window contains a toolbar and the Scripts and Status panels.

The Script Manager Toolbar The Script Manager toolbar contains various buttons that enable you to add, rename, or remove categories, and execute scripts from a particular category. Figure 6-2 shows the Script Manager toolbar:

Figure 6-2: The Script Manager Toolbar Table 6-1 describes the Script Manager toolbar buttons: Table 6-1: Script Manager Toolbar Buttons Button

Description Executes scripts selected in the Script Manager window. Sets execution options for all scripts. Loads an existing category in the Script Manager window. Adds a new category to the Script Manager window.

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Table 6-1: Script Manager Toolbar Buttons Button

Description Renames the current category in the Script Manager window. Removes the current category from the Script Manager window. Provides a list of active sessions. Changes the active session of the Script Manager window.

The Scripts Panel The Scripts panel provides a list of scripts from a selected category. Using the Scripts panel, you can execute, edit, remove, and view scripts from a particular category. For example, when you select the DBA category from the Category drop-down list, the Scripts panel appears, as shown in Figure 6-3:

Figure 6-3: The Scripts Panel In the Scripts panel, the Include column contains check boxes that allow you to select a script that you need to view or execute. The + button selects all the check boxes and the button clears all the check boxes. The Directory column shows the file path of a script. There are six buttons in the Scripts panel: Add: Inserts a new script from a particular category. § Edit: Modifies the script entry for the selected script in a category. § Remove: Removes the selected script from a category. § Move: Moves the selected script to the destination directory. § View: Shows the selected scripts in a notepad window. § Combine: Joins multiple scripts to create a script. § Tip Click the column header in the Scripts panel to sort rows in ascending or descending order.

The Status Panel

This watermark does not appear in the registered version - http://www.clicktoconvert.com

The Status panel displays the output of a script after it executes. For example, when you select a script in the Scripts panel and execute it, the Status panel appears, as shown in Figure 6-4:

Figure 6-4: The Status Panel

Managing Categories To organize and manage scripts easily, Script Manager enables you to group scripts in categories. By default, all scripts that TOAD includes are grouped into three categories: DBA, Oracle 8i Data Dictionary, and Schema Objects. The DBA.sdf category file contains scripts that are required for database administration. Script Manager enables you to add, rename, or remove categories in the Script Manager window.

Adding a New Category You can include a new category in the Script Manager window to add and organize scripts according to your needs. For example, to add a new category, MyCategory: 1. Click the button on the Script Manager toolbar. The Add Category dialog box appears. 2. Type MyCategory in the Add Category dialog box, as shown in Figure 6-5:

Figure 6-5: The Add Category Dialog Box 3. Click OK to create the MyCategory.sdf file. The Scripts panel appears, as shown in Figure 6-6:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 6-6: The Scripts Panel Containing MyCategory

Renaming a Category The Script Manager window enables you to rename a category using the Rename category dialog box. For example, to rename a category: 1. Add a new category, Category1, in the Category drop-down list. 2. Click the button on the Script Manager toolbar to rename the category. The Rename category dialog box appears, as shown in Figure 6-7:

Figure 6-7: The Rename category Dialog Box 3. Type Custom in the Rename category dialog box, as shown in Figure 6-8:

Figure 6-8: Renaming Category1

This watermark does not appear in the registered version - http://www.clicktoconvert.com

4. Click OK to view the new name in the Category drop-down list.

Removing a Category To remove an existing category from the Script Manager window, select a category from the Category drop-down list. For example, to remove the Custom category: 1. Select the Custom category from the Category drop-down list, as shown in Figure 6-9:

Figure 6-9: Selecting the Custom Category

2. Click the button to remove the Custom category. The Confirm dialog box appears, as shown in Figure 6-10:

Figure 6-10: The Confirm Dialog Box 3. Click Yes to remove the Custom category from the Category drop-down list.

Managing Scripts The Script Manager window enables you to manage scripts by performing several tasks. For example, you can add, edit, view, combine, and execute scripts from MyCategory.

Adding Scripts to a Category You need to use the Add button in the Scripts panel to add scripts to MyCategory. To do this: 1. Select MyCategory from the Category drop-down list. The Scripts panel appears, as shown in Figure 6-6. 2. Click Add from the Scripts panel. The Add Script Entry dialog box appears. 3. Select General Purpose from the Use drop-down list. 4. Type Database Profiles in the Description text box. The Add Script Entry dialog box appears, as shown in Figure 6-11:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 6-11: The Add Script Entry Dialog Box 5. Click Add in the Add Script Entry dialog box to add the relevant script. The Save As dialog box appears. 6. Select the control_file.sql script in the Save As dialog box, as shown in Figure 6-12:

Figure 6-12: The Save As Dialog Box 7. Click Save to add the script in the Add Script Entry dialog box, as shown in Figure 613:

Figure 6-13: Adding the control_file.sql Script

This watermark does not appear in the registered version - http://www.clicktoconvert.com

8. Click OK. The control_file.sql script is added to MyCategory, as shown in Figure 6-14:

Figure 6-14: Adding a Script to MyCategory

Editing Script Entries You can use the Edit button in the Scripts panel to edit script entries from MyCategory. For example, to edit the script entry of the control_file.sql script: 1. Select the control_file.sql script check box in the Include column. 2. Click Edit. The Edit Script Entry dialog box appears, as shown in Figure 6-15:

Figure 6-15: The Edit Script Entry Dialog Box 3. Edit the script entry according to your requirements. 4. Click OK to update the changes.

Removing Scripts from Script Manager You can remove scripts from MyCategory using the Remove button in the Scripts panel. When you select a script and click Remove, the Confirm dialog box appears, as shown in Figure 6-16:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 6-16: The Confirm Dialog Box for Removing Scripts

Moving Scripts The Script Manager window enables you to move a script from one directory to another. You need to use the Move button in the Scripts panel to change scripts directories. For example, to move a script from the DBA directory to the Desktop directory: 1. Select the script that you need to move. 2. Click Move from the Scripts panel. The Change all script directories dialog box appears, as shown in Figure 6-17:

Figure 6-17: The Change all script directories Dialog Box 3. Click the button. The Browse for Folder dialog box appears. 4. Select Desktop in the Browse for Folder dialog box, as shown in Figure 6-18:

Figure 6-18: The Browse for Folder Dialog Box 5. Click OK to change the script directory. The Change all script directories dialog box appears, as shown in Figure 6-19:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 6-19: The Change all script directories Dialog Box 6. Click OK to move the required script to the Desktop directory.

Viewing Scripts The Script Manager window enables you to view scripts in a notepad window. You need to use the View button in the Scripts panel to view the necessary scripts. For example, to view the control_file.sql script: 1. Select the control_file.sql script check box from the Include column. 2. Click View from the Scripts panel. The Notepad window appears, as shown in Figure 6-20:

Figure 6-20: The Notepad Window

Combining Scripts You can combine multiple scripts to form a script in the Script Manager window. You need to use the Combine button in the Scripts panel to combine the required scripts. For example, you can combine the control_file.sql and profile.sql scripts to create a script, MyScript. To create MyScript: 1. Add the profile.sql script in the Add Script Entry dialog box, as shown in Figure 6-21:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 6-21: Adding the profile.sql Script 2. Click OK to add the profile.sql script to MyCategory. 3. Select the check boxes in the Include column for the control_file.sql and profile.sql scripts. 4. Click Combine from the Scripts panel. The Create new script from selected scripts dialog box appears. 5. Type the new script name as MyScript, as shown in Figure 6-22:

Figure 6-22: The Create new script from selected scripts Dialog Box 6. Click OK to create MyScript.sql. The TOAD dialog box appears, as shown in Figure 623:

Figure 6-23: The TOAD Dialog Box 7. Click OK to exit the TOAD dialog box.

Executing Scripts To execute scripts, you need to use the button on the Script Manger toolbar. For example, to execute MyScript: 1. Add the MyScript.sql script to MyCategory.

This watermark does not appear in the registered version - http://www.clicktoconvert.com

2. Select the MyScript.sql script check box from the Include column. 3. Click the button on the Script Manager toolbar to execute the required script. The Status panel appears, as shown in Figure 6-24:

Figure 6-24: Execution of the MyScript.sql Script

Using the Script Execution Options Script Manager enables you to set execution options for all scripts to execute. You can set execution options using the Oracle Script Manager Options dialog box. When you click the button on the Script Manager toolbar, the Oracle Script Manager Options dialog box appears, as shown in Figure 6-25:

Figure 6-25: The Oracle Script Manager Options Dialog Box

This watermark does not appear in the registered version - http://www.clicktoconvert.com

There are three types of execution options: Suppress errors: Hides all errors that Script Manager encounters when a script § executes. By default, this option is cleared. Show output in editor: Displays the script output in the Script Output page of SQL § Editor. By default, this option is selected. You can display the output using the following suboptions: § § §

Use single editor: Displays all scripts in the SQL Editor window. Use separate editor for each script: Displays each script in a new SQL Editor window. Place script in editor: Displays all scripts in the text editor of the SQL Editor window.

Note §

For more information on SQL Editor, refer to Chapter 4.

Spool output to file: Enables you to save the script output in a text file. By default, this option is cleared. You can save the script output using the following suboptions: § §

Use single file: Saves the output of all scripts in a text file. Use separate file for each script: Saves the output for each script in separate text files.

Using ScriptRunner Script Manager provides a script execution utility to edit and execute scripts. This utility called ScriptRunner helps execute long scripts. While the script executes in ScriptRunner, you can continue to work with the Script Manager window. To execute scripts in ScriptRunner, you need to select the Run in Quest ScriptRunner option from the Script Manager toolbar, as shown in Figure 6-26:

Figure 6-26: Selecting the Run in Quest ScriptRunner Option For example, to execute the MyScript.sql script using ScriptRunner: 1. Select MyCategory from the Category drop-down list in the Script Manager window. 2. Select the MyScript.sql script check box from the Include column, as shown in Figure 6-27:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 6-27: Selecting the MyScript.sql Script to Execute 3. Select the Run in Quest ScriptRunner option from the Script Manager toolbar. The Quest ScriptRunner window appears, as shown in Figure 6-28:

Figure 6-28: The Quest ScriptRunner Window

The Quest ScriptRunner Window The Quest ScriptRunner window is divided into two panels, Editor and Results. The ScriptRunner toolbar contains various buttons that enable you to load, save, and execute scripts in ScriptRunner. Figure 6-29 shows the ScriptRunner toolbar:

Figure 6-29: The ScriptRunner Toolbar Table 6-2 describes the ScriptRunner toolbar buttons: Table 6-2: The ScriptRunner Toolbar Buttons Button

Description

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Table 6-2: The ScriptRunner Toolbar Buttons Button

Description Executes the script you load in the Editor panel. Executes a script from the current cursor position to the end of the script. Executes a script from its beginning to the current cursor position. Executes the statement where you have position the cursor. Executes a script in a step-by-step process. Stops the executing script midway. Commits the script execution. Rolls back the script execution. Loads a script file in the Editor panel. Saves text in the Editor panel in the script file format. Saves the changes you make in the Editor panel. Shows all the saved scripts in the My Scripts dialog box. Performs knowledge lookup for the TOAD Xpert edition.

Loading and Executing Scripts ScriptRunner enables you to load and run a script in the Editor panel to view the script output in the Results panel. For example, to load and execute the whoami.sql script that shows the current end user's logged in name: 1. Click the button on the ScriptRunner toolbar. The Open dialog box appears. 2. Select the whoami.sql script from the Open dialog box, as shown in Figure 6-30:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 6-30: The Open Dialog Box 3. Click Open to load the whoami.sql script in the Editor panel. The Editor panel appears, as shown in Figure 6-31:

Figure 6-31: The Editor Panel

4. Click the button to execute the whoami.sql script. The Quest ScriptRunner window displays the script output in the Results panel, as shown in Figure 6-32:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 6-32: The Results Panel

Using ScriptRunner Options ScriptRunner options enable you to customize the display of scripts in the Editor panel and the output of that script in the Results panel. It also enables you to set the script output print options and the ScriptRunner default options. When you select Edit-> Options in the Quest ScriptRunner window, the Options window appears, as shown in Figure 6-33:

Figure 6-33: The Options Window The four pages in the Options window enable you to edit the following options: Applications § Printing §

This watermark does not appear in the registered version - http://www.clicktoconvert.com

§ §

Default script Menu shortcut

Application Options You can access application options from page 1 of the Options window. Application options enable you to change fonts in the Editor and Results panel. For example, to change the fonts of text in the Editor panel, click Editor Font in the Fonts frame. The Font dialog box appears, as shown in Figure 6-34:

Figure 6-34: The Font Dialog Box Note Select the required font and click OK to change the font for the Editor panel. Similarly, use the steps listed above to change the font for the Results panel.

Printing Options Printing options enable you to customize the script output. You can access printing options from page 2 of the Options window. To access page 2, click the button in the Options window. The Printing Options window appears, as shown in Figure 6-35:

Figure 6-35: The Printing Options Window

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Default Script Options Default script options enable you to set default options for the Quest ScriptRunner window. You can specify the escape character, limit the output of Select query statements, and change the default buffer size of the DBMS output. You can access default script options from page 3 of the Options window. To access page 3, click the button in the Options window. The Default Script Options window appears, as shown in Figure 6-36:

Figure 6-36: The Default Script Options Window

Menu Shortcut Options Menu shortcut options enable you to customize the shortcut keys you use in the Quest ScriptRunner window. You can access menu shortcut options from page 4 of the Options window. To access page 4, click the button in the Options window. The Menu Shortcut Settings window appears, as shown in Figure 6-37:

Figure 6-37: The Menu Shortcut Settings Window To customize the shortcut key of a command, click the required command and press keys that you want to use as shortcut for that command. Click Ok to save the changes or Cancel to exit the Options window.

Chapter 7: Using the Project Manager

This watermark does not appear in the registered version - http://www.clicktoconvert.com

The Project Manager component of the Tool for Oracle Application Developers (TOAD) helps organize the work area. It enables you to display the contents of various Oracle databases and File Transfer Protocol (FTP) connections in one project. Project Manager also enables you to easily transfer files from one folder to another. A project is called a node. It contains database objects. The Project Manager allows you to easily transfer files from one folder to another. You can customize the Project Manager by configuring the right-click, drag and drop, and the double-click operations for the Project Manager. This chapter explains how to use and configure the Project Manager.

Introducing the Project Manager The Project Manager provides a centralized screen to manage frequently used database objects in Oracle, such as folders, files in folders, tables, and schemas. It is a good idea to use Project Manager because it enables you to work on various objects simultaneously. It also allows you to drag and drop database objects from one project to another. A Project Manager can contain: Project folders: The parent folder, which can contain files, file folders, FTP folders, and § schemas. File folders: The folder on the local computer or network. § Files: File on the local computer or network. § FTP folders: Folders on an FTP server that contains FTP files. § FTP files: FTP files on an FTP server. § Schema: Links to a schema on a database that contains database objects. § Database objects: Objects stored on the database such as a table or a view. § To open the Project Manager window, you need to start TOAD and select Tools -> Project Manager. The Projects window appears, as shown in Figure 7-1:

Figure 7-1: The Project Manager Window

The Project Toolbar The Project Manager toolbar contains buttons to load and save project files and add items to Project Manager, as shown in Figure 7-2:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 7-2: The Project Toolbar Table 7-1 describes the options of the Project toolbar: Table 7-1: Options of the Project Toolbar Icon

Icon Name

Description

Add item

Displays a drop-down list of items to add to Project Manager.

Load project file

Loads a current project file from the disk.

Save project file as

Saves a project file to a disk with a specific name.

Save project file

Saves a project file to a disk.

Print

Prints a project file.

Expand node

Expands all nodes.

Collapse node

Collapses all nodes.

Toggle reorder mode

Toggles the reorder mode and helps move items around in Project Manager.

Settings

Enables you to modify settings for Project Manager.

Working with the Project Manager You can include a project, a folder, folder items, an FTP folder, FTP folder items, a schema, or database objects in the Project Manager. To add a project, Project1, to the Project Manager, you need to click the drop-down arrow of the Add item button and select the Project option. Type Project1 and press Enter.

Adding Database Objects to the Project Manager After adding a project, you need to add database objects to the project. To add a folder to the project, you need to: 1. Click Project1 and select the Folder option from the Add item tool button in the Project toolbar. The File folder properties dialog box appears, as shown in Figure 7-3:

Figure 7-3: The File folder properties Dialog Box

This watermark does not appear in the registered version - http://www.clicktoconvert.com

You can select the directory from the File folder properties dialog box that you want to include in the Project Manager. Specify the files you want to include in the Filter text box. For example, if you want only SQL files, you need to specify *.sql in the Filter text box. 2. Click the ellipses button to select a folder. The Browse for folder dialog box appears, as shown in Figure 7-4:

Figure 7-4: The Browse for Folder Dialog Box 3. Click the required folder and click OK. The selected folder appears in the Project Manager, as shown in Figure 7-5:

Figure 7-5: An Empty Projects Window Double-click the Adobe GoLive folder to open the folder. To view the contents of the Adobe GoLive folder, you need to: 4. Select the Refresh folder links option in the context menu. A message box appears with the message Rebuild links to all folder files for selected directory nodes. 5. Click Yes. The contents of the Adobe GoLive folder are listed in the Project Manager window, as shown in Figure 7-6:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 7-6: Folder Items in a Project Window To add an FTP folder to the project, select the FTP folder option from the Add item tool button on the Project toolbar. The Server Settings dialog box appears, as shown in Figure 7-7:

Figure 7-7: The Server Settings Dialog Box The § § § § §

Server Settings dialog box contains the following options: Host name: Is the name of the remote computer from which you want to upload files. User name: Is the login name used to connect to the FTP server. Password: Is the password used to connect to the FTP server. Directory: Is the folder on the FTP server in which you want to store the uploaded files. Client directory: Is the folder in which the downloaded files are saved.

You can add FTP folder items to an FTP folder. To do this, you need to click the FTP folder to which you want to add FTP folder items and click the Add items tool button. The Select files dialog box appears. Select the file you want to add to the FTP folder and click OK. You can download and edit the files later. To add a schema to your project, you need to: Click the Schema option from the Add item tool button in the Project toolbar. The Select Session dialog box appears, as shown in Figure 7-8:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 7-8: The Select Session Dialog Box After creating a schema, you can add a database object such as a table or a view. To add a database object, click the Add item tool button in the Project toolbar and click Database object. Note The Database object option in the Add item tool button is activated only when a schema exists in the project. The Object Search window appears, as shown in Figure 7-9:

Figure 7-9: The Object Search Window The Object Search window contains options such as: Schemas/Owners: Specifies the schema to which the search element belongs or the § owner of a database object. Search for: Enables you to specify search criteria for a specific database object. § Object Search: Displays the Object Names, Column Names, Trigger Names, Index § Columns, and Constraint Columns options. The Object Search option also shows a drop-down list to specify how object names must match specific criteria. The options in the drop-down list are: Text occurs anywhere, Starts with text, and Exact Match. Source Search: Searches procedure code and trigger code. § You can also include the Sys and System tables in your search and specify if you want a case-sensitive search. The Object Search window also contains the Results panel, where the result of a specific query is displayed. For example, to search the field ename in the Scott schema, you need to:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

1. Type ename in the Search for text box. 2. Select Column Names from the Object Search options. 3. Select the Starts with text option from the drop-down list in the Object Search options and click the Search button. Note If you select the Case sensitive search option, the search may not yield any results because the column ename is written as ENAME in the EMP table. 4. Figure 7-10 shows search results for a specific query:

5.

Figure 7-10: Object Search Window Displaying Search Results

The Projects Window in Figure 7-6 displays two records with Object Name as ename. They are columns of a table. One record belongs to the Emp table. The other record belongs to the Bonus table. Both the tables are owned by Scott. To add the Emp table to the Project Manager, you need to select the Add to Project Manager option from the context menu. To know more about the Emp table, double-click the record in the Object Search window shown in Figure 7-10, where the Parent is Emp. Figure 7-11 shows the structure of the Emp table:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 7-11: The Structure of the Emp Table You can modify the Emp table in this window. Using this window, you can perform tasks, such as: Show or hide table or column comments § Add a column to the table § Delete a column from the table § Figure 7-12 shows the Project Manager window, after you have added a folder, a file folder, an FTP folder, and a schema:

Figure 7-12: The Project Manager Window with Database Objects Some of the tabs available in the Figure 7-12 are: Columns: Displays the structure of the table. The Columns tab enables you to add or § delete a column from the table.

This watermark does not appear in the registered version - http://www.clicktoconvert.com

§

§

§ §

§ §

Indexes: Displays the existing indexes in the table. The Indexes tab enables you to create a script for the selected index, create, modify, or rebuild an index, and analyze or drop an index. Constraints: Displays existing constrains on the table. The Constraints tab enables you to add a constraint to the table, enable or disable all or selected constraints, or drop all constraints. Triggers: Displays the triggers in the existing table. The Triggers tab enables you to enable or disable all or selected triggers and drop or compile a trigger. Data: Displays the records of the table. The Data tab enables you to iterate through the records of the table, add or delete records, sort, filter records, refresh data, or commit all modifications to the table. Scripts: Displays the script for the table. Grants: Displays the list of end users who have been granted privileges. The Grant tab enables you to revoke all privileges for a table.

Working with Files and Folders You can rename objects such as folders or schemas. To rename an object, click the item in Project Manager and select the Rename option from the context menu. You can also rename an object by selecting the item and pressing F2. You can also remove or delete a database object. To remove or delete an object from Project Manager, select the Remove or Delete option from the context menu. If you select the Remove option, the item is removed only from Project Manager. It continues to exist on the hard disk. If you select the Delete option, the object is deleted both from Project Manager and the hard disk. Note Ensure that you do not delete an FTP folder or an FTP folder item because the selected files will be deleted from the server directory. To copy a database object such as a file folder item from Project1 to Project2, you need to: 1. Select the file to be copied in Project Manager. 2. Select the Copy option from the context menu. The Destination projects dialog box appears, as shown in Figure 7-13:

Figure 7-13: The Destination projects Dialog Box 3. Select the project to which you want to copy the file and click OK.

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Working with Project Nodes An object such as a folder or an FTP folder is called a node because it contains other database objects, such as folder file items and FTP folder items. The Project Manager window in Figure 7-6 displays a minus symbol, -, beside the project folder, Project1, and the folder, Adobe GoLive. The minus symbol means that the item is a node that can be expanded or collapsed. A node is added to a project when you include an item in Project Manager. When you add contents to an item, such as a folder or a schema, the minus symbol changes to the plus symbol, +, indicating that the node is collapsed. You can save the settings for a project. For example, in a project if you have specific folders, folder items, and schemas, these are your settings. You can save the project on your computer. To save the project, click the Save project file as button, , in the standard toolbar. This opens the Save as dialog box that is used to specify where you want to save the project.

To open the saved project, click the Load project file button, dialog box. Select the required project file and click Open.

. This opens the Open

Configuring the Project Manager Window You can configure the Project Manager window as required. For example, you can specify the command that TOAD should execute when you drag an item to the Projects window. You can also configure the double-click action and context menus.

Using the General Tab To change the current configurations, click the Settings button, Configuration dialog box appears, as shown in Figure 7-14:

, on the toolbar. The

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 7-14: The General Tab of the Configuration Dialog Box The General tab of the Configurations dialog box enables you to associate specific editors with their file extensions. The General tab contains the following options: SQL Editor file load options: Loads a file in the SQL Editor. This option enables you to: § § § §

§

Reload the file in existing window. Load in a new window. Navigate to a previous location.

Save server passwords: Prompts you to enter the password to log on to the Oracle database if this option is not selected. Note To save the server password, select View -> Options -> Oracle -> Save passwords for Oracle connections.

You can add an association to the list of existing associations. To do this, click the Add button in the General tab of the Configuration dialog box. The External application association dialog box appears, as shown in Figure 7-15:

Figure 7-15: The External Application Association Dialog Box You can add an external application to the Project Manager. For example, to add Internet Explorer to the list of external programs, you need to: 1. Specify the title as IExplorer. 2. Locate IExplorer.exe on the hard disk.

This watermark does not appear in the registered version - http://www.clicktoconvert.com

3. Specify the extension as .HTM or .HTML and click OK. When you add an application to the existing list of applications, it appears as an option in the context menu in the Project Manager window.

Using the Drag and Drop Operations Tab You can customize the drag and drop options for items in Project Manager. To do this, click the Drag and drop operations tab in the Configuration dialog box. The Drag and drop operations tab of the Configuration dialog box appears, as shown in Figure 7-16:

Figure 7-16: The Drag and Drop Operations Tab of the Configuration Dialog Box The options in the When source is column represent the items to be dragged. The options in the and destination is column represent the items on which you want to drop the selected item. The options in the perform this action column indicate the action to be performed when you drag an item to another. For example, if you click a file folder item and drag it to the trash can, the file folder item is deleted, by default. You can specify that when you click a file folder item and drag it to the trash can, the file folder item must not be deleted. To do this, you need to: 1. Click the row where the value of the When source is column is File folder and the value of the and destination is column is Trash can. 2. Click the Delete option in the perform this action column. A drop-down list appears. 3. Select the option <none> from the drop-down list and click OK. This procedure ensures that when you drag a file folder item to the trash can, it is not deleted. You can similarly set other options.

Using the Double-click Operations Tab You can set a different double-click operation for a specific item in the Project Manager. To configure double-click actions, click the Double-click operations tab in the Configuration

This watermark does not appear in the registered version - http://www.clicktoconvert.com

dialog box. The Double-click operations tab of the Configuration dialog box appears, as shown in Figure 7-17:

Figure 7-17: The Double-click Operations Tab of the Configuration Dialog Box You can specify a double-click action different from the default action for a specific item. For example, when you double-click a procedure item in the Project Manager, TOAD describes the procedure. There are other options you can set for the context menu, such as Data Definition Language (DDL) to Clipboard, Load into Procedure Editor, Load DDL into SQL Editor, and DDL to file. When you click an option in the perform this action column, a drop-down list appears, which displays the available options for the specific item. Select the required option and click OK.

Using the Popup menus Tab You can customize the contents of pop-up menus to display options different from the default options. To configure pop-up menus, click the Popup menus tab in the Configuration dialog box. The Popup menus tab of the Configuration dialog box appears, as shown in Figure 7-18:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 7-18: The Popup Menus Tab of the Configuration Dialog Box All the available right-click options are selected by default. You can specify options in the required order. Note The Add, Remove, Rename, and Properties option in the pop-up menu remain for all the items in the Project Manager. To exclude an option from the pop-up menu, deselect the option in the Popup menus tab of the Configuration dialog box. For example, if you want only the Explore and Refresh folder links options for a file folder pop-up menu, deselect the Delete option. This will display only the selected options in the Popup menus tab.

Using the Data Definition Language Tab TOAD creates a DDL script for each database object. The DDL for an object is the Create and Drop statements. You can specify the DDL script inclusions for a database object. DDL script inclusions specify tasks such as creating an index, a trigger, a primary key, a foreign key, or a constraint, which can be performed on existing database objects using DDL scripts. To configure DDL script inclusions for a table, click the Tables tab of the DDL tab in the Configuration dialog box. The Tables tab of the DDL tab appears, as shown in Figure 7-19:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 7-19: The Tables Tab of the DDL Script Inclusions Window The Tables tab also contains the DROP option, which enables you to drop an existing database object. When you deselect an option, you cannot perform the specific task. You can modify script inclusions for database objects such as indexes, view, users, and tablespaces. To modify script inclusions for indexes on existing tables, select the Indexes tab in the Configuration dialog box. The Indexes tab appears, as shown in Figure 7-20:

Figure 7-20: The Indexes Tab of the DDL Script Inclusions Window

This watermark does not appear in the registered version - http://www.clicktoconvert.com

To modify script inclusions for views, select the Views tab in the DDL script inclusions tab of the Configuration dialog box. The Views tab appears, as shown in Figure 7-21:

Figure 7-21: The Views Tab of the DDL Script Inclusions Window To modify the script inclusions for the TOAD users, select the Users tab in the DDL script inclusions tab of the Configuration dialog box. The Users tab appears, as shown in Figure 722:

Figure 7-22: The Users Tab of the DDL Script Inclusions Window

This watermark does not appear in the registered version - http://www.clicktoconvert.com

To modify script inclusions for a tablespace, select the Tablespaces tab in the DDL script inclusions tab of the Configuration dialog box.

Chapter 8: Administering a Database Tool for Oracle Application Developers (TOAD) allows you to access the Oracle database using a Graphical User Interface (GUI). Using TOAD you can create, alter, or drop a database object. You can also modify the data, tune the database, and perform administrative tasks, such as creating table spaces, exporting and importing database objects, and monitoring database performance. This chapter explains the components of the Database Administrator (DBA) menu and describes how to create a database using the Database (DB) wizard. It also explains how to work with TOAD utilities, such as Export, Import, and SQL*Loader.

Overview of DBA TOAD provides the DBA module, which extends the functionality of the DBA menu, the Schema Browser, and the Create menu. The options available on the DBA menu are: Database Monitor: Monitors database performance with the help of charts, such as § Logical Input/Output (I/O), Physical I/O, Event Waits, Sessions, and Call Rates. Instance Manager: Checks the status of various databases you can access. § Database Health Check: Performs various checks on a database that includes listing § tables that do not contain a primary key, tables with more than six indexes, unusable indexes, and invalid objects. The results are saved in an RTF, HTML, or a text file. You can send these results through mail. Top Session Finder: Helps you find resource-intensive sessions in a database and sort § the sessions based on specific parameters. Operating System Utilities: The various operating system utilities are: § § § § §

§ § § § §

§

Unix Monitor: Monitors database performance with charts, such as CPU Usage, Process Queues, and Disk I/O in kilobytes per second (KB/Sec). Unix Job Scheduler: Schedules tasks to a computer running Unix. Windows Registry Parms: Enables running TOAD and Oracle efficiently by setting the Windows registry parameters. Unix Kernel Parms: Enables running TOAD and Oracle efficiently by setting the Unix kernel parameters.

Oracle Parameters: Allows you to edit the existing parameters of the Oracle database. National Language Support (NLS) Parameters: Allows you to view the session, instance, and database settings and modify the session and instance parameters. Tablespaces Map: Provides a graphical view of the tables in the database. Control Files: Allows you to view the contents of the control file of the associated database. Pinned Code: Allows you to save the code in the memory buffer. When you execute a select statement or a procedure, Oracle compiles the code and stores the code in memory. This is called a parsed statement. As a result, when you execute the statement again, Oracle need not compile the code. The pinned code stored in the memory is executed directly. This improves performance because you need not compile statements every time you want to execute them. New Database Wizard: Allows you to create an Oracle database.

This watermark does not appear in the registered version - http://www.clicktoconvert.com

§ §

§ § § § § §

Identify Space Deficits: Displays tables with insufficient disk space to allocate their next extent. Redo Log Manager: Allows you to maintain the redo logs for a database. The redo records that are stored in a redo log buffer are written to the redo log files when the buffer is full or the associated transaction is committed. Using the Redo Log Manager, you can manage the size and names of these buffers. Log Switch Frequency Map: Shows when the database performs a log switch. Log Miner: Analyzes a database corrupted by System Change Number (SCN) and helps you recover the transaction that occurred before the corruption. Export Utility Wizard: Exports a database object using the DBA routines. Import Utility Wizard: Imports a database object using the DBA routines. General Schema Script: Uses the selected schema or definition file to create Data Definition Language (DDL) or definition files for selected objects. Compare Schemas: Allows you to load def files, and save the migration or synchronization report to a script file.

Creating a Database TOAD enables you create a database using the Database wizard, which automatically assigns tablespaces across hard disks.

Using the Database Wizard To create a database from the command prompt, you need to know the syntax and need to specify whether you want to create database objects, such as indexes, views, and tablespaces. TOAD provides a Database wizard that allows you to create a database. A database created using the Database wizard can be started and shut down using the Instance Manager. It is a utility that allows you to check the connection status of a database. When you create a database, a script file, .bat or .sh file, that the Database wizard creates, the orapwd file is called. The orapwd is the Password File creation utility in Oracle. The Password File creation utility creates a password, which allows the client to connect using the SYSDBA account. Additionally, you need to set the parameter, remote_password_login = EXCLUSIVE, in the INIT.ORA file to start and shut down the Oracle database from the client with a SYSDBA account. Note The database to be started and shut down need not be created using the Database wizard but a password file and the remote_password_login = EXCLUSIVE parameter must be set in the INIT.ORA file. To create a database, DB-1, using the Database wizard: 1. Select DBA -> New Database. The Basic Information dialog box appears, as shown in Figure 8-1:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 8-1: The Basic Information Dialog Box The options available in the Basic Information dialog box are: Database name: Name of the database you want to create. The Database § name is also the unique System Identifier (SID) for the database. A database name can be eight characters long. Database location: Indicates the location of the database. The options § available are: § §

Local: Creates a database in a specified location on your computer. Remote: Creates a database on a remote computer running Windows or Unix.

Directories: Contains two options:

§ § §

Oracle base: The parent directory of the Oracle Home directory. Oracle home: The directory where Oracle is installed.

Application type: Specifies the type of database you want to create. The application type could be either an online transaction processing (OLTP)/Web, Multi purpose, or Data warehouse. Depending on the application you choose, the block size is allocated for the database. Block size: Specifies the size of the block allocated to the database depending § on the application type you choose. The default block size is 4 KB. Oracle version: Allows you to choose the version of Oracle you are using. § 2. Click Next. The INIT.ORA factors dialog box appears, as shown in Figure 8-2: §

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 8-2: The INIT.ORA Factors Dialog Box In the INIT.ORA factors dialog box you can set various options, such as: Users: Sets the number of users who can access the database. By default, the § number of users is set to 16. CPUs: Sets the number of available CPUs. By default, the value is one. § CPU MHz: Sets the CPU speed in Megahertz. § RAM: Specifies the available RAM. By default, the value of RAM is set to 512. § Disk controller: Specifies the cache size. § 3. Click Next. The Tablespace mode selection dialog box appears, as shown in Figure 83:

Figure 8-3: The Tablespace Mode Selection Dialog Box If you select the Automatic option, TOAD places the tablespaces across the hard drives and optimizes the placing of the tablespaces for speed or drive space. If you select the Manual option, you need to specify the size of the tablespaces and how to place them. 4. Select Automatic and click Next. The Automated tablespace mode dialog box appears, as shown in Figure 8-4:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 8-4: The Automated Tablespace Mode Dialog Box If you choose to optimize for speed, TOAD uses an algorithm to determine the speed of a volume. TOAD then assigns tablespaces to volumes based on their speed. By default, the Optimize for Speed option is selected. If you choose to optimize for space, the volume with the maximum space is chosen to assign a tablespace. 5. Click Add to add an additional volume. The Add Volume dialog box appears, as shown in Figure 8-5:

Figure 8-5: The Add Volume Dialog Box 6. After specifying the volume options, click OK. If you specify the Manual option, in Figure 8-3, the User defined tablespace mode dialog box appears, as shown in Figure 8-6:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 8-6: The User Defined Tablespace Mode Dialog Box 7. Click Next after you make the required modifications. The Build database dialog box appears, as shown in Figure 8-7:

Figure 8-7: The Build Database Dialog Box The Generation options are: Create database now: Saves the information provided in each step of the § wizard to a script and executes the script. This option is activated only if you select the Local option in the first step of the DB wizard. Save information to a batch file: Saves the database information to a .bat file § that you can execute later, to create a database. You need to create a .bat file before you save the database information. 8. Click Finish to execute the database scripts. If you select the Create database now option, a dialog box displays the database creation message. If you select the Save information to a batch file option, the dialog box prompts whether or not you want to create a .bat file. Click yes to create a .bat file, if you have not created one already.

Monitoring a Database After you create a database, you need to monitor the database performance at regular intervals. To monitor the performance of your database, use the Database Monitor, which consists of nine charts.

This watermark does not appear in the registered version - http://www.clicktoconvert.com

To open the Database Monitor, select DBA -> Database Monitor. The Database Monitor window appears that shows all the nine charts in three rows and three columns. The Database Monitor window contains an option to refresh the monitor. You can set the refresh rate to 30 seconds, 1 minute, or 5 minutes or you can select the Auto refresh data option in the Database Monitor window to automatically refresh the monitor at specified intervals. The various charts provided by the Database Monitor are Logical I/O, Physical I/O, Event Waits, Sessions, Call Rates, Miss Rates, SGA Memory usage, Shared Pool, and Indexed Queries.

Logical I/O Chart Logical I/O refers to I/O that occurs in memory, which will be written to the disk later. The Logical I/O chart plots the usage of the logical input and output on the graph. Figure 8-8 shows the Logical I/O chart:

Figure 8-8: The Logical I/O Chart

Physical I/O Chart The Physical I/O chart plots the usage of the physical input and output on the graph. Physical I/O means I/O that occurs on the hard disk. Figure 8-9 shows the Physical I/O chart:

Figure 8-9: The Physical Chart

Event Waits Chart Some of the events in Oracle are read, write, and checkpoint. Waits are events for which a current session has to wait. For example, a lock in Oracle is an event wait because you cannot start another event until the previous event has not completed on a database object, such as a table or a view. The Event Waits chart plots the time waited per second in milliseconds for a specific usage. Figure 8-10 shows the Event waits chart:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 8-10: The Event Waits Chart You can also collect data for event waits for a control file I/O, single block read, multi-block read, direct path read, file I/O, and log write.

Sessions Chart The Sessions chart displays the sessions used when you monitor the database. Figure 8-11 shows the Sessions chart:

Figure 8-11: The Sessions Chart

Call Rates Chart The Call Rates chart plots the calls per second for the Parse, Execute, Commit, and Rollback events on the graph. Figure 8-12 shows the Call Rates chart:

Figure 8-12: The Call Rates Chart

Miss Rates Chart The Miss Rates chart provides a graphical representation that shows when the data was not available in memory and had to be retrieved from the hard disk or when the procedure codes were not available in memory and they had to be recompiled. Figure 8-13 shows the Miss Rates chart shows the percentage missed for the Buffer cache, SQL area, and Latch:

Figure 8-13: The Miss Rates Chart

SGA Memory Usage Chart

This watermark does not appear in the registered version - http://www.clicktoconvert.com

The SGA Memory usage chart shows memory used for Fixed SGA, Log Buffer, Buffer Cache, Shared Pool, Large Pool, and Java Pool. The SGA memory usage and the Shared pool charts explain the usage of various components in the shared pool and SGA, respectively. Figure 8-14 shows the SGA Memory Usage chart:

Figure 8-14: The SGA Memory Usage Chart

Shared Pool Chart The Shared Pool chart plots the memory usage for the Shared Pool. It also shows the memory allocated to Miscellaneous, Dictionary Cache, Library Cache, Free memory, and the SQL Area. Figure 8-15 shows the Shared Pool chart:

Figure 8-15: The Shared Pool Chart

Indexed Queries Chart The Indexed Queries chart shows a graphical representation on the usage of indexes in queries. Figure 8-16 shows the Indexed Queries chart:

Figure 8-16: The Indexed Queries Chart

Using the Instance Manager The Instance Manager is a tool to manage Oracle parameters. It can also be used to start and shut down the Oracle instances. The options available in the Instance Manager window are: Refresh rate: A drop-down list that allows you to choose a time interval to refresh the § data in the window. Auto refresh data: Refreshes the data automatically after a specified time interval. § Polling priority: An operating system priority that is assigned to a database thread. For § example, if you select the Idle option, the thread executes only when the system is idle. Figure 8-17 shows the Instance Manager window:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 8-17: The Instance Manager Window The symbols in the Instance Manager window are: : Denotes that TOAD cannot determine the connection status of the database. § : Denotes that TOAD failed to connect to the Oracle database if this symbol § appears in the Database column. If this symbol appears in the Listener column, the symbol denotes that the Transparent Network Substrate (TNS) ping did not return a response. If this symbol appears in the Node column, the symbol denotes that TOAD is unable to connect to the server. : Denotes that the database is started. § You can also start a database from the Instance Manager. To start a database, select a database from the list and click Startup in the Instance Manager window. The Startup dialog box appears, as shown in Figure 8-18:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 8-18: The Startup Dialog Box The § § §

Connect Information option has the following fields: Username: Enter a user name to connect using the SYSDBA or SYSOPER account. Password: Enter a password for the user. Connect as: Allows you to select SYSDBA or SYSOPER from the drop-down list.

The Startup option shows: Open: Opens a database and allows end users to connect to the database. § Mount: Mounts a database but does not open it. When a database is mounted, you § can perform basic modification tasks that you cannot perform when the database is open. Nomount: Starts a database. § The Startup Dialog box also shows the Force and Exclusive options. You need to select the Force option when you want to force the database to shut down and restart. When you force the database to shut down, the database is shut down in the Abort mode. You need to restart the database if you try to open a database that is already open or if an error has occurred. If you select the Exclusive option, the database can be opened only by the current instance. Note The Exclusive option cannot be used with Shared, Parallel, or Nomount database modes. If no mounting option is specified, the Exclusive option is selected, by default. If you do not select the Exclusive option, the database can be opened by multiple instances, simultaneously. You also need to specify the full path for the parameter file or select the parameter file using the Browse button in the Startup dialog box. Select the required options and click OK to start the database.

This watermark does not appear in the registered version - http://www.clicktoconvert.com

You can shut down the database using the Instance Manager. To shut down the database, click Shut down in the Instance Manager window. The Shut down dialog box appears, as shown in Figure 8-19:

Figure 8-19: The Shut down Dialog Box You need to specify a username that can connect as SYSDBA. The options available in the shut down mode are: Normal § Immediate § Abort § When the database is shut down, an entry is made in the Status change listing, and the Server Manager or SQL report is appended to the Detail Log. When you shut down a database, TOAD creates a folder called DBA in the TOAD directory. TOAD stores an .ora file for the parameters, which are not default parameters, in the DBA folder. In addition, a file called startupshutdownlog.txt, which contains the contents of the detail tab is created. When the Instance Manager performs a shut down, it queries the V$parameter table to build an INIT.ORA file for a subsequent startup. This file is stored in directory, TOAD\DBA and is named, pfile_SID.ora, where SID is the database alias. To perform this query, a temporary database connection is used which uses the shut down connection information provided in the shut down dialog box. You can also alter the settings for the database. To alter the database settings, click Alter in the Instance Manager. The Alter dialog box appears, as shown in Figure 8-20:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 8-20: The Alter Dialog Box The Alter dialog box allows you to modify the state of the selected database.

Managing Sessions When an end user logs on to the Oracle database, Oracle assigns a session to the end user. The Top Session Finder window allows you to locate the session that utilizes the maximum resources, such as CPU and memory. Using the Top Session Finder, you can easily sort the sessions based on various factors, such as Memory, CPU, and I/O. To open the Top Session Finder window, select DBA -> Top Session Finder. The Top Session Finder window appears, as shown in Figure 8-21:

Figure 8-21: The Top Session Finder Window The Top Session Finder window allows you to sort the sessions based on the usage of resources or a combination of parameters. The window also contains a toolbar that allows you to perform various tasks. Table 8-1 describes the options on the toolbar:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Table 8-1: Top Session Finder Options Option

Option Name

Description

Options

Shows various options, such as Single and Multiple parameter select.

List sessions

Lists all the current session of the database.

Create New Profile

Allows you to create a new profile Note A profile is a set of restrictions on a database resource.

Save Settings to Profile Cursors

Saves the current settings to a Profile cursor.

Delete Profile Cursor

Deletes a Profile Cursor.

Change Sessions for this Window

Allows you to switch from one session to another.

Note The profiles are stored in a file called Topsess.ini. To revert to the default settings, delete the Topsess.ini file. When you select the Options button on the Session toolbar, the options displayed are: Single Parameter Select: Lists the sessions based on the resources utilized. If you § select the Single Parameter Select option, each database instance is listed in the descending order of usage based on the selected parameter. Multiple Parameter Select: Allows you to assign weights to two or more parameters. § The sessions are then sorted by the weighted sum of the statistics. When you select the Multiple Parameter Select option, the Stored Profiles list and the Weight column appear in the Top Session Finder window. Short Parameter List: Shortens the parameter list to approximately 35 statistics. If you § do not select this option, the parameter list includes all the items in the V$statname view. Exclude System Sessions: Does not include the background sessions of Oracle. § Exclude Inactive Sessions: Displays only the active sessions. § You can also choose to exclude sessions that were inactive for a specified period of time. Note A session uses various resources. Oracle calculates the resource usage of a given session. For a given parameter, Oracle weighs the result of various statistics, such as memory and I/O, and then calculates the effects of various values on this parameter. Weight means weighing the importance of a given statistic on the parameter. The higher the effect of a statistic on a parameter, the higher is the weightage. The Top Session Finder window also shows the sessions and their resource utilization in a pie chart. You can limit the pie chart to show a specified number of sessions. The pie chart can handle only 1050 sessions. If you select the option, Single Parameter Select, select a parameter from the Parameter column. The data is displayed in the Dataset tab, which displays the session specific information, such as logon time, SID, and the name of the terminal. The Pie Chart tab displays a graphical representation of the selected parameter.

This watermark does not appear in the registered version - http://www.clicktoconvert.com

To display session information based on multiple parameters, select the option, Multiple Parameter Select. Select a profile from the Stored Profiles drop-down list and click List Sessions on the toolbar. The results are displayed on the Dataset tab, which displays a list of SIDs that are connected to the database and the unweighted value of each statistic with the weighted sum of all statistics. For example, to find the session information for the parameter, physical writes, based on the Stored Profile, CPU: 1. Select the parameter, physical writes from the Parameter column. 2. Select CPU from the Stored Profiles drop-down list. 3. Click List Sessions on the toolbar. The results are displayed in the Dataset tab, as shown in Figure 8-22:

Figure 8-22: The Dataset Tab of the Top Session Finder Window You can print the dataset using the Print Grid option and save the dataset using the Save As option from the context menu. The data is represented using a Pie chart, as shown in Figure 8-23:

Figure 8-23: The Pie Chart To view information about a specific session, click a slice on the pie chart.

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Based on the session information, you can end or kill a session using the Kill/Trace Session window. This window also allows you to view the various sessions and track their progress. To open this window, select DBA -> Kill/Trace Session. The Kill/Trace Session window appears, as shown in Figure 8-24:

Figure 8-24: The Kill/Trace Session Window Some of the toolbar buttons in the Kill/Trace window are described in Table 8-2:

Table 8-2: The Kill/Trace Toolbar Options Option

Option Name

Description

Change active session for this window.

Allows you to change the active session for the Kill/Trace window.

Refresh the screen.

Allows you to refresh the screen and display new database sessions.

Kill the selected session.

Ends the selected session.

Start trace for this session.

Starts the trace for this session.

Stop trace for this session.

Stops the trace for this session.

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Note Trace is a utility, which maintains a record of all the activities and resources used by a session. You need to keep a record of the resources used for various sessions when you are tuning a database. Trace is an important aspect of tuning a database but is optional because trace requires a lot of resources. Oracle generates statistics on every statement executed by a session.

To kill a session, select the session and select the Kill option from the context menu or click the Kill selected session button,

, from the toolbar.

Note To use the Kill/Trace option, you need access to the V$ tables, such as V$lock, V$locked_object, and V$open_cursor.

Using the DBA Wizards You can export and import database objects, such as tables and views using the command prompt. TOAD provides the Export and Import wizards, which enable you to transfer database objects. TOAD also provides the SQL*Loader Utility, which helps you build a control file for a database.

The Export Utility Wizard The Export Utility wizard provided by TOAD allows you to easily export database objects, such as tables, tablespaces, and databases. For example, to export the Emp table, using the Export Utility wizard: 1. Select DBA -> Export Utility Wizard. The Export Utility Wizard dialog box appears, as shown in Figure 8-25:

Figure 8-25: The Export Utility Wizard Dialog Box 2. Click Next. The Tables to export tab appears, as shown in Figure 8-26:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 8-26: The Tables to Export Tab of the Export Utility Wizard 3. Select Emp and click Next. The Select objects to export dialog box appears, as shown in Figure 8-27:

Figure 8-27: The Select Objects to Export Dialog Box 4. Select the required options and click Next. The Enter an output file name dialog box appears, as shown in Figure 8-28:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 8-28: The Enter an Output File Name Dialog Box In Figure 8-28, you need to specify: An output file name: Name of the file to be exported. An output file has the § extension, .dmp. A log file name: Name of the log file in which information about the export and § error messages are recorded. A log file has the extension, .log. A parameter file name: Name of the file that contains a list of export § parameters. A parameter file has the extension, .dat. 5. Type emp as the output file name, which is then displayed as emp.dat. When you press the TAB key to move to the next field, the Log file name and Parameter file name are displayed as exp_emp.log and exp_emp.dat, respectively. 6. Click Next. The Select an action dialog box appears, as shown in Figure 8-29:

Figure 8-29: The Select an Action Dialog Box

This watermark does not appear in the registered version - http://www.clicktoconvert.com

If you select the Execute now option and click Finish, the process of exporting begins. If you select the Schedule to run later option and click Finish, the NT Job Scheduler dialog box appears, as shown in Figure 8-30:

Figure 8-30: The NT Job Scheduler Dialog Box Using the options in the NT Job Scheduler dialog box, you can specify an exact time on a specific date when you want the export process to begin. The sub-options in the Frequency option allow you to specify whether or not you want to perform the export process only once, every day, on a weekly basis, or on a monthly basis. 7. Select the required options if you want to schedule the export for a later date and click Ok. When the table is being exported, it shows the Export Watch window, as shown in Figure 8-31:

Figure 8-31: The Export Watch Window

This watermark does not appear in the registered version - http://www.clicktoconvert.com

The Export Utility wizard helps export database objects of an end user to another database using the Export users option in Figure 8-25. To export a user, HR: 1. Select the Export users and click Next. The Select the users dialog box appears, as shown in Figure 8-32:

Figure 8-32: The Select the Users Dialog Box 2. Select the user, HR from the list of Available users and click the Add button, . The Select objects to export dialog box appears, as shown in Figure 8-27. 3. Select the required objects you want to export and click Next. The Enter an output file name and a log file name dialog box appears, as shown in Figure 8-28. 4. Type the output file name as HR and press Tab. The Log and the Parameter file name text boxes are automatically filled. 5. Click Next. The Select an action dialog box appears. You can select any of the following options: Execute now § Schedule to run later § Just build parameter file § 6. Select the Execute now option and click Finish. The process of exporting the user, HR, begins. You can export an entire database or a tablespace or use a parameter file to export a database object. Note To export a tablespace, you need the SYSDBA account privileges.

The Import Utility Wizard The Import Utility wizard is similar to the Export Utility wizard and allows you to import database objects between databases. You need to import a database or a database object if you want to: Migrate from previous version of Oracle to the latest version. § Move the database form one computer to another. § Create another database with the same table or table structure. § In addition, the Import Utility wizard also allows you to unzip files before importing them. You can import database objects, such as tables, users, databases, and tablespaces. To import the table, Emp:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

1. Select DBA -> Import Utility Wizard. The Import Utility Wizard appears, as shown in Figure 8-33:

Figure 8-33: The Import Utility Wizard 2. Click Next. The Specify source and destination dialog box appears, as shown in Figure 8-34:

Figure 8-34: The Specify Source and Destination Dialog Box The options available in the Choose the method of selecting tables are: Import all tables: Imports all tables in the schema. § Manually enter table names: Allows you to type the names of the tables. § Copy corresponding tables: Shows a list of available tables from which you § can choose the table to be copied. 3. If you select the option, Manually enter table names, the Enter names of tables dialog box appears, as shown in Figure 8-35:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 8-35: The Enter Names of Tables Dialog Box 4. Type the name of the required table as Emp and click Next. 5. If you select the Copy corresponding tables option, the Select the tables to import dialog box appears, as shown in Figure 8-36:

Figure 8-36: The Select the Tables to Import Dialog Box

6. Click the required table in the Available tables list box and click the Add button, The Select objects to import dialog box appears, as shown in Figure 8-37:

.

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 8-37: The Select Objects to Import Dialog Box 7. Click Next. The Enter the export file name dialog box appears, as shown in Figure 838:

Figure 8-38: The Export File Name Dialog Box 8. Type the Export file name as Emp and press Tab. The other text boxes are automatically filled. Specify a name for the Index file and click Next. The Finish dialog box appears, as shown in Figure 8-39:

Figure 8-39: The Finish Dialog Box

This watermark does not appear in the registered version - http://www.clicktoconvert.com

9. Click Finish. The Import Watch window appears, as shown in Figure 8-40:

Figure 8-40: The Import Watch Window

The SQL*Loader Wizard SQL*Loader is a tool used to load text data to an Oracle database. You can define the table name, column width, and the column or row separators in a separate file called control file and provide a reference to the data file in the control file. When you execute the SQL*Loader, you need to specify the control file and the data is loaded in the desired table. The SQL*Loader utility provides a GUI interface to load data to an Oracle database. To use the SQL*Loader wizard: 1. Create a table, for example, Employee and add records to the table. Note For more information on how to create and modify a table using Schema Browser, refer to Chapter 2. 2. Save the table as an ASCII file after you create the table. To do this, open the Schema Browser and select the Save As option from the context menu. The Save Grid Contents dialog box appears, as shown in Figure 8-41:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 8-41: The Save Grid Contents Dialog Box The ASCII, Comma delimited option is selected by default. In the Save to option, select a location for the file, for example, d:\employee.dat and click Ok. The structure of the table is saved as a text file, which can then be specified as the input file in the SQL*Loader wizard. 3. Select DBA -> SQL*Loader Wizard. The SQL*Loader wizard appears, as shown in Figure 8-42:

Figure 8-42: The SQL*Loader Wizard 4. Click Next. The Specify input files dialog box appears, as shown in Figure 8-43:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 8-43: The Specify Input File Dialog Box 5. Click Add to add an input file. The Add input file dialog box appears, as shown in Figure 8-44:

Figure 8-44: The Add Input File Dialog Box The options available in the Add input file dialog box are: Input file name: Name of the Input file or the data file. § Bad file name: Name of the file that contains records that are not copied to the § database because the specified delimiter may be different from the required delimiter. A bad file is similar to an input file but has a .bad extension Discard file: File that contains records that were not copied to the database § because they do not match any specified criteria. Record format: Contains the following options: § §

§ §

Stream: Specifies the end of the record and is the default option. When the Stream option is specified, a line is read until an end-of-record marker, for example, a newline character, is found. Fixed: Specifies the length of the record. In the Fixed record format, each record must be a fixed number of bytes in length Variable: Specifies the size of the record field length.

The Discard up to text box specifies the number of records that can be discarded when copying to the database. 6. Select the input file name, employee.dat. 7. Set the Record format as Stream and click OK. 8. Click Next in the Specify input files dialog box. The Select the destination table dialog box appears, as shown in Figure 8-45:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure 8-45: The Select Destination Table 9. The Table Name Select dialog box appears, as shown in Figure 8-46:

Figure 8-46: The Table Name Select Dialog Box 10. Select the table, Employee and click Ok. The Select destination table dialog box appears, as shown in Figure 8-45. 11. Type a comma (,) in the All fields delimited by drop-down list and click Next. The Global options dialog box appears, as shown in Figure 8-47:

Figure 8-47: The Global Options Dialog Box

This watermark does not appear in the registered version - http://www.clicktoconvert.com

12. Click Next. The Control and log files dialog box appears, as shown in Figure 8-48:

Figure 8-48: The Control and Log Files Dialog Box 13. Specify the location of a control file name, for example d:\emp.ctl. The same name appears in the Log file name text box. 14. Click Preview to view the contents of the control file. 15. Click Save settings to save these settings, and click Next. The Finish dialog box appear, as shown in Figure 8-49:

Figure 8-49: The Finish Dialog Box 16. Click Execute now to begin the process. After the records are copied to the database, a Stop Watch window shows the status of the process. When the process is successfully completed a message is displayed.

Appendix A: Installing TOAD The TOAD software, created for the Windows operating system, helps develop PL/SQL. The graphical user interface that TOAD provides allows developers to improve the performance and productivity of the Oracle database. TOAD, a product of Quest Software Inc., is used to automate development of Oracle databases. Note You can download the trial version of the TOAD software free of charge from the Internet using the following URL: http://www.quest.com/solutions/download.asp

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Identify Editions of TOAD Quest Software provides four editions of TOAD: Standard, Professional, Xpert, and Suite. The trial versions of all these editions are available free of charge.

TOAD Standard Edition The Standard Edition of TOAD provides features that enable you to work with the Oracle database. The Standard Edition helps you create code and build and manage database objects. Formatter Plus provided by the Standard Edition allows you to check syntax and format PL/SQL code offline.

TOAD Professional Edition The Professional Edition of TOAD provides an integrated PL/SQL Debugger that helps troubleshoot database code. Using the PL/SQL Debugger, you can analyze code line-by-line while executing the code on the server. The Professional Edition includes all features provided by the Standard Edition and allows you to add breakpoints, watch and modify variables, and view the execution stack.

TOAD Xpert Edition The Xpert Edition contains the Xpert Tuning tool that helps tune SQL queries. The Xpert Tuning tool allows you to analyze and rewrite SQL queries that are inefficient and can improve performance of the database. The Xpert Edition includes all the features of the Professional Edition.

TOAD Suite Edition The TOAD Suite Edition includes all the features provided by the Xpert Edition and modules, such as DataFactory Developer Edition, QDesigner PhysicalArchitect, and Benchmark Factory for Oracle. The Suite Edition enables you to design, develop, test and manage Oracle databases. The DataFactory Developer Edition is a data generator that allows you to test databases with valid data. The QDesigner PhysicalArchitect is a design tool that integrates objectoriented and physical data modeling capabilities. The Benchmark Factory for Oracle is a tool that provides features to test user load on the Oracle database. You can also use it for load testing, capability planning, and tuning performance.

Installing the TOAD Xpert Edition 7.4 To install the TOAD Xpert Edition 7.4, you must first install the Oracle database that is a prerequisite for installing TOAD. The installation process for all editions of TOAD is similar to the installation process of the Xpert Edition. The minimum requirements for installing TOAD are: Windows 98, NT, Windows 2000, or Windows XP operating system. § Oracle 7.3 or later. § Note For more information about installation of Oracle9i, refer Appendix B.

This watermark does not appear in the registered version - http://www.clicktoconvert.com

To install TOAD Xpert Edition: 1. Select the destination directory. 2. Select the backup directory. 3. Install the UNIX Job Scheduler scripts. 4. Install Xpert Option and the Server Side objects. 5. Authorize End Users.

Selecting the Destination Directory To select the destination directory: 1. Download TOAD Xpert Edition 7.4 and save the setup file on the computer. 2. Double-click the setup.exe file. The Welcome page of the setup wizard appears. 3. Click Next to start the installation. This opens the Software License Agreement page. 4. Click Next. This opens the Select Installation Type page, as shown in Figure A-1:

Figure A-1: The Select Installation Type Page In the Select Installation Type page, the types of installation provided are: Local TOAD Install: Allows you to install all the features of the software § provided in the Xpert Edition. Network Install - Client: Allows you to install the software on a computer § functioning as a client. Network Install - Server: Allows you to install the software on a computer § functioning as a server. 5. Select the Local TOAD Install type of installation and click Next. This opens the Select Destination Directory for Client Install page, as shown in Figure A-2:

Figure A-2: The Select Destination Directory for Client Install Page

This watermark does not appear in the registered version - http://www.clicktoconvert.com

6. Click Browse to change the location of the directory or select the drive for installing TOAD. The Select Destination Directory dialog box appears, as shown in Figure A-3:

Figure A-3: The Select Destination Directory Dialog Box 7. Select the location of the directory and click OK. The Select Destination Directory dialog box closes and the path of the directory appears in the Select Destination Directory for Client Install page.

Selecting the Backup Directory After selecting the destination directory, you need to select the backup directory. To do this: 1. Click Next in the Select Destination Directory for Client Install page. The Backup Replaced Files page appears, as shown in Figure A-4:

Figure A-4: The Backup Replaced Files Page 2. Click Next. The Select Backup Directory for Client Install page appears, as shown in Figure A-5:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure A-5: The Select Backup Directory for Client Install Page 3. Click Browse to change the location of the directory or select the drive for copying the replaced files. The Select Backup Directory dialog box appears, as shown in Figure A-6:

Figure A-6: The Select Backup Directory Dialog Box 4. Select the location of the directory and click OK. The Select Backup Directory dialog box closes and the path of the directory appears in the Select Backup Directory for Client Install page.

Installing the UNIX Job Scheduler Scripts To install the UNIX Job Scheduler scripts required for TOAD: 1. Click Next in the Select Backup Directory for Client Install page. This opens the Install the UNIX Job Scheduler & Back Up Modifiable TOAD Files page, as shown in Figure A-7:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure A-7: The Install the UNIX Job Scheduler & Back Up Modifiable TOAD Files Page Figure A-7 provides options to create backup copies for three types of TOAD files, SQL Templates, Script Manager Scripts, and UNIX Job Scheduler Scripts. 2. Click Next. This opens the Syntax Highlighting Parser Script Selection page, as shown in Figure A-8:

Figure A-8: The Syntax Highlighting Parser Script Selection Page 3. Click Next. This opens the Ready to Install page, as shown in Figure A-9:

Figure A-9: The Ready to Install Page

This watermark does not appear in the registered version - http://www.clicktoconvert.com

4. Click Next to start the process of installation. This opens the Installing screen that contains the path of the directory for copying the TOAD application, as shown in Figure A-10:

Figure A-10: The Installing Screen 5. After the installation is completed the Create TOAD Desktop Icon dialog box appears, as shown in Figure A-11:

Figure A-11: The Create TOAD Desktop Icon Dialog Box 6. Click Yes. The TOAD Formatter Plus install screen appears, as shown in Figure A-12:

Figure A-12: The TOAD Formatter Plus install Screen

Installing Xpert Option and the Server Side Objects After installing Formatter Plus, you need to install Xpert Option to tune SQL statements in TOAD. To install Xpert Option: 1. The Installation of Xpert option screen appears, as shown in Figure A-13:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure A-13: The Installation of Xpert Option Screen 2. The Run TOAD Xpert Server Side Objects install dialog box appears, as shown in Figure A-14:

Figure A-14: The Run TOAD Xpert Server Side Objects install Dialog Box 3. Click Yes to install the Server Side objects. This opens the Server Side Installation dialog box, as shown in Figure A-15:

Figure A-15: The Server Side Installation Dialog Box 4. Click Next to install SQLab Objects. This opens the Server Side Installation dialog box for selecting or creating the schema to be used, as shown in Figure A-16:

Figure A-16: The Server Side Installation: Selecting a Schema

This watermark does not appear in the registered version - http://www.clicktoconvert.com

5. Select SCOTT from the Use existing user selected below drop-down list. 6. Click Next. This opens the Server Side Installation dialog box for selecting a tablespace, as shown in Figure A-17:

Figure A-17: The Server Side Installation: Selecting a Tablespace 7. Click Next. This opens the Server Side Installation dialog box that indicates the beginning of the installation process, as shown in Figure A-18:

Figure A-18: The Server Side Installation Dialog Box: Installation 8. Click Install. The installation process to create server-side objects on ORCL begins. While installing the server objects, the Log On as SYS/SYSDBA dialog box appears, as shown in Figure A-19:

Figure A-19: The Log On as SYS/SYSDBA Dialog Box

This watermark does not appear in the registered version - http://www.clicktoconvert.com

9. Enter scott in the first text box and tiger in the second text box and click OK. This completes the installation process and the Server Side Installation dialog box appears, as shown in Figure A-20:

Figure A-20: The Server Side Installation Dialog Box: Installation Complete

Authorizing End Users You need to authorize end users for using TOAD to manage the Oracle database. To authorize users: 1. Click Next in the Server Side Installation dialog box, shown in Figure A-20. This opens the dialog box with options for authorization of users, as shown in Figure A-21:

Figure A-21: Authorizing Users 2. Click Next. This opens the Installation Log dialog box, as shown in Figure A-22:

Figure A-22: The Installation Log Dialog Box

This watermark does not appear in the registered version - http://www.clicktoconvert.com

3. Click Next. This opens the Installation complete dialog box, as shown in Figure A-23:

Figure A-23: The Installation Complete Dialog Box 4. Click Finished to complete the installation. After the installation is complete, the Upgrade Notice dialog box appears, as shown in Figure A-24:

Figure A-24: The Upgrade Notice Dialog Box 5. Click Next. This opens the Quest Software TOAD Xpert Edition dialog box, as shown in Figure A-25:

Figure A-25: The Quest Software TOAD Xpert Edition Dialog Box 6. Click Finish. This completes the installation of the TOAD Xpert Edition and opens the Release Notes, Readme Installation notes and starts the TOAD application.

This watermark does not appear in the registered version - http://www.clicktoconvert.com

After the installation process is complete, you can open TOAD by double-clicking the TOAD

shortcut , created on the desktop. You can also open TOAD by selecting, Start -> Programs -> Quest Software -> TOAD -> TOAD, as shown in Figure A-26:

Figure A-26: Startup Menu for TOAD Note For more information on the TOAD interface, refer to Chapter 1.

Appendix B: Installing Oracle9i Database Oracle9i database (DB) is the latest release of Oracle database. In addition to quality and performance-improvement techniques, Oracle9i database provides eXtensible Markup Language (XML) database functionality with the Oracle XML DB feature. Oracle9i helps improve the productivity and efficiency of a Database Administrator (DBA) and provides built-in Online Analytical Processing (OLAP) functionality and optimizations for Windows and Linux operating systems. This Appendix describes how to install Oracle9i database on the Windows NT or Windows 2000 server. The installation assumes that you are installing Oracle for the first time.

Installation Prerequisites Before you install Oracle9i on Windows NT or Windows 2000, your computer must have the following specifications: A Pentium III or Pentium IV PC with 800 MHz processor. § 256 MB RAM, although 512 MB RAM is preferred. If 256 MB RAM is available, ensure § that Windows manages 400 MB virtual memory. At least 10 GB free space on the hard disk. § Windows 2000 Server installed with the service pack 1 or 2. § Caution If Microsoft Internet Information Service (IIS) executes in the background on port 80 by default, disable the Web server before starting the Oracle9i installation process. You can purchase a full copy of Oracle9i from Oracle. Tip You cannot install Oracle products on Windows ME or Windows XP Home Edition. To install Oracle9i, you need 10 GB free space, for the following: 1.5 GB to copy zipped source files. §

This watermark does not appear in the registered version - http://www.clicktoconvert.com

§ § §

1.5 GB to unzip the source files. 2 GB to install Oracle9i software. 2 GB to 5 GB to install Oracle data files.

Installing Oracle Database To install Oracle database: 1. You need the Oracle9i Server CD to install Oracle Database. The installation process begins automatically as soon as you run the CD. If the process does not begin automatically, double-click the setup icon. Figure B-1 shows the first screen that appears when you insert the CD:

Figure B-1: Oracle9i Server - Autorun The options in this figure are: Install/Deinstall Products: Allows you to install or uninstall an Oracle product § Explore CD: Displays the contents of the CD. § Browse Documentation: Allows you to view the documentation for Oracle § products. 2. Click Install/Deinstall Products. The welcome screen appears, as shown in Figure B-2:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure B-2: The Welcome Screen The options available in Figure B-2 are: Products: Allows you to uninstall previously installed Oracle products. § About Oracle Universal Installer: Shows information about the Oracle § Universal Installer. Exit: Allows you to quit the installation process. § Help: Provides online help to install Oracle database. § Installed Products: Shows a list of the products installed on a computer. § Previous: Displays the previous step of the Installation Wizard. § Next: Displays the next step of the Installation Wizard. § 3. Click Next to proceed. The Oracle Universal Installer: File Locations dialog box appears, as shown in Figure B-3:

Figure B-3: The File Locations Dialog Box §

Source provides the location of the files you want to install. The location is displayed in the Path text box as E:\stage\products.jar. This means the files are located in the folder, stage on drive E, which is the CD drive. If you have unzipped the source files to a specific directory, the Source text box displays the location of that directory.

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Destination is the location on the hard disk where you can install files. Figure B-3 shows the Name text box that displays the name of the folder in which you can install Oracle9i. The Path text box displays the location of the file as C:\oracle\ ora90. You can also change the destination using the Browse button, but do not change the directory structure. Note The hard disk drive you choose to install Oracle on must have a minimum of 10 GB free space. 4. Click Next. The Oracle Universal Installer: Available Products dialog box appears, as shown in Figure B-4: §

Figure B-4: The Available Products Dialog Box The three products provided by Oracle are: Oracle9i Database 9.0.1.1.1: Installs a preconfigured database, the required § management tools, and the basic client for the Oracle database server. Oracle9i Client 9.0.1.1.1: Installs the management tools, network services and § utilities, development tools, and the basic client software. Oracle9i Management and Integration 9.0.1.0.1: Installs the management § server, management tools, Oracle Internet Directory, Oracle Internet Server, and the basic client software. The Oracle9i Database 9.0.1.1.1 option is selected by default. To install Oracle Client software: iv.

Select the Oracle9i Client 9.0.1.1.1 option from the Oracle Universal Installer: Available Products dialog box. The Oracle Universal Installer: Installation Types dialog box appears, as shown in Figure B-5:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure B-5: Installation Types for a Client

v.

The options available in this dialog box are: Administrator: Installs the management console, management tools, § networking services, utilities, and the basic client software. Oracle9i installation with the Administrator option selected, requires 647 MB of disk space. Runtime: Installs tools for application development, networking § services, and the basic client software. Oracle9i installation with the Runtime option selected, requires 486 MB of disk space. Custom: Allows you to choose individual components of the database. § The space required for a custom installation will depend on the space required for each component. Click Next to proceed with the installation of Oracle Client.

To install the management tools for Oracle: vi.

Select the Oracle9i Management and Integration 9.0.1.0.1 option from the Oracle Universal Installer Available Products dialog box. The Oracle Universal Installer Installation Types dialog box appears, as shown in Figure B-6:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure B-6: Installation Types Dialog Box

vii.

Options available in this dialog box are: Oracle Management Server: Installs the middle-tier server to manage § the enterprise using Oracle Enterprise Manager. To install Oracle Management Server, you need 749 MB of disk space. Oracle Internet Directory: Installs Oracle Internet Directory, client § toolset, Oracle Directory Manager, and a client-side development kit. To install Oracle Internet Directory, you need 1.36 GB of disk space. Oracle Integration Server: Installs a database configured with § advanced queuing and Oracle JVM along with Oracle Workflow. To install Oracle Integration Server, you need 1.37 GB. Custom: Allows you to choose from a list of components. § Click Next to proceed with the installation of management tools for Oracle.

5. To start installing the Oracle server, click Next. The Oracle Universal Installer: Installation Types Dialog Box appears, as shown in Figure B-7:

Figure B-7: Oracle Universal Installer: Installation Types Dialog Box

This watermark does not appear in the registered version - http://www.clicktoconvert.com

The various installation types available are: Enterprise Edition: Allows you to handle large amounts of data for high-end § applications, such as high volume On-Line Transaction Processing (OLTP) and query-intensive data warehousing, and Internet applications. The Enterprise Edition requires 1.76 GB of disk space. Standard Edition: Allows you to create workgroup level applications. This § edition includes basic management tools, and Web features for developing business applications. The Standard Edition requires 1.72 GB of disk space. Personal Edition: Supports single user development and deployment that need § to be compatible with the Enterprise and Standard Edition. The Professional Edition requires 1.72 GB of disk space. Custom: Allows you to choose the components of a database you want to § install. 6. Select Enterprise Edition and click Next. The Oracle Universal Installer: Database Configuration dialog box appears, as shown in Figure B-8:

Figure B-8: Database Configuration Dialog Box Various options available in this dialog box are: General Purpose: Installs a pre-configured database. § Transaction Processing: Installs a pre-configured database that is optimized § for processing transactions. Data Warehouse: Installs a pre-configured database that is optimized for data § warehousing. Customized: Creates a customized database. This takes longer than creating § a general-purpose database. Software Only: Installs the software and not the database. §

Configuring Oracle Database You need to configure the database by specifying the type of installation and by providing the database identifier. To configure the database: 1. Select the General Purpose options and click Next. The Oracle Universal Installer: Database Identification dialog box appears, as shown in Figure B-9:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure B-9: The Database Identification Dialog Box 2. Type ORCL as the Global Database Name. The SID is automatically set to ORCL. 3. Click Next. The Oracle Universal Installer: Database File Location dialog box appears, as shown in Figure B-10:

Figure B-10: The Database File Location Dialog Box 4. Click Next. The Oracle Universal Installer: Database Character Set dialog box appears, as shown in Figure B-11:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure B-11: The Database Character Set Dialog Box The options in the Database Character Set dialog box are: Use the default character set: Allows you to set the default language for the § database. Use Unicode (UTF8) as the character set: Allows you to store multiple § language groups. Choose one of the common character sets: Allows you to choose from a list of § common character sets. 5. Click Next. The Oracle Universal Installer: Summary dialog box appears, as shown in Figure B-12:

Figure B-12: The Summary Dialog Box The Summary dialog box displays information, such as: Global Settings: Shows the Source, Destination, and Installation Type. § Product Languages: Shows English as the set language. § Space Requirements: Shows the space required to install Oracle database. § 6. Click Install. The installation begins and the Oracle Universal Installer: Install dialog box appears, as shown in Figure B-13:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure B-13: The Install Dialog Box The progress bar shows 100% when the installation is complete. The Oracle Universal Installer: Configuration Tools dialog box appears, as shown in Figure B-14:

Figure B-14: The Configuration Tools Dialog Box When a tool is being installed, the dialog box shows the status as in progress and the tools that are yet to be installed as pending. After the Oracle Net Configuration Assistant tool is installed, the Starting Oracle HTTP service tools utility is installed. This opens a command window and the installation continues. When installation of the Oracle Database Configuration Assistant tool is in progress, the Oracle Database Configuration Assistant screen appears, as shown in Figure B-15:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure B-15: The Oracle Database Configuration Assistant Screen When the options in the screen are installed, a check mark appears next to each option. When the installation is complete, the progress bar is 100% and the completion screen of the Oracle Database Configuration Assistant appears, as shown in Figure B-16:

Figure B-16: The Completion Screen The Password Management option allows you to change the password for a schema. When you click the Password Management option, the Password Management window appears, as shown in Figure B-17:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure B-17: The Password Management Window To set a new password, type the password in the New Password field and retype the password in the Confirm Password field. After you set new password and click OK, the Oracle Universal Installer Configuration Tools dialog box appears again. All the other tools are installed and the installation is complete. The Oracle Universal Installer: End of Installation dialog box appears, as shown in Figure B-18:

Figure B-18: The Oracle Universal Installer: End of Installation Dialog Box After installing the database, if you want to install another product, you need to click the Next Install button. After Oracle9i is installed, the Start Menu shows Oracle Installation Products and OraHome90. The Oracle Installation Products tools help you install and remove of Oracle products from the computer. The set of tools in the OraHome90 option includes: Application Development § Configuration and Migration Tools § Enterprise Management Packs § Enterprise Manager Quick Tools § Integrated Management Tools § Oracle HTTP Server §

This watermark does not appear in the registered version - http://www.clicktoconvert.com

When installed on Windows NT or Windows 2000, the Oracle9i database runs automatically as a service. Figure B-19 shows the Services Console:

Figure B-19: The Services Console OracleServerORCL is the database instance. Other instances are: OracleOraHome90TNSListener: Listener process for remote connections to the § database. OracleOraHome90Agent: Oracle Agent that communicates with management services. § OracleOraHome90HTTPServer: The Oracle/Apache Web server. § The OracleServerORCL service is set to start automatically. As a result, the database starts and mounts each time the computer is switched on. You can also manage database processes directly using the Enterprise Manager Console in Stand-alone mode. To do this, click Start -> Programs -> Oracle -> OraHome90 -> Enterprise Manager Console and select Launch Standalone.

Testing the Installation After the database is installed, you need to check whether the database is running. To do this, open the SQL*Plus tool to connect to the database. To test the installation, click Start -> Programs -> Oracle -> OraHome90 -> Application Development program -> SQL Plus. This opens the Log on screen that prompts you for the user name, password, and host string. The user name for the default database administrator is SYSTEM and the default password is MANAGER. If you are connecting to a local Oracle database, the host string is not required. Type the user name and password in the Log on screen and click OK. The Oracle SQL*Plus window appears, as shown in Figure B-20:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Figure B-20: The Oracle SQL*Plus Window