guia suplementaria para el manual de oracle sql

8/9/2019 Guia suplementaria para el manual de Oracle SQL

1/62

User Guide Supplement

6.0


2/62

2008 Quest Software, Inc.

ALL RIGHTS RESERVED.

This guide contains proprietary information protected by copyright. The software described in this guide

is furnished under a software license or nondisclosure agreement. This software may be used or copied

only in accordance with the terms of the applicable agreement. No part of this guide may be reproduced

or transmitted in any form or by any means, electronic or mechanical, including photocopying and

recording for any purpose other than the purchaser's personal use without the written permission of

Quest Software, Inc.

If you have any questions regarding your potential use of this material, please contact:

Quest Software World Headquarters

LEGAL Dept

5 Polaris Way

Aliso Viejo, CA 92656

www.quest.com

email: [email protected]

Please refer to our Web site for regional and international office information.

Trademarks: Quest, Quest Software, the Quest Software logo, Aelita, AppAssure, Benchmark Factory,

Big Brother, DataFactory, DeployDirector, ERDisk, Foglight, Funnel Web, I/Watch, Imceda, InLook,

IntelliProfile, InTrust, IT Dad, I/Watch, JClass, Jint, JProbe, LeccoTech, LiteSpeed, LiveReorg, NBSpool,NetBase, Npulse, PerformaSure, PL/Vision, Quest Central, RAPS, SharePlex, Sitraka, SmartAlarm,

Spotlight, SQL LiteSpeed, SQL Navigator, SQL Watch, SQLab, Stat, Stat!, StealthCollect, Tag and

Follow, Toad, T.O.A.D., Toad World, Vintela, Virtual DBA, Xaffire, and XRT are trademarks and

registered trademarks of Quest Software, Inc in the United States of America and other countries. Other

trademarks and registered trademarks used in this guide are property of their respective owners.

Disclaimer:The information in this document is provided in connection with Quest products. No license,

express or implied, by estoppel or otherwise, to any intellectual property right is granted by this

document or in connection with the sale of Quest products.EXCEPT AS SET FORTH IN QUEST'S

TERMS AND CONDITIONS AS SPECIFIED IN THE LICENSE AGREEMENT FOR THIS PRODUCT,QUEST ASSUMES NO LIABILITY WHATSOEVER AND DISCLAIMS ANY EXPRESS, IMPLIED OR

STATUTORY WARRANTY RELATING TO ITS PRODUCTS INCLUDING, BUT NOT LIMITED TO, THE

IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, OR

NON-INFRINGEMENT. IN NO EVENT SHALL QUEST BE LIABLE FOR ANY DIRECT, INDIRECT,

CONSEQUENTIAL, PUNITIVE, SPECIAL OR INCIDENTAL DAMAGES (INCLUDING, WITHOUT

LIMITATION, DAMAGES FOR LOSS OF PROFITS, BUSINESS INTERRUPTION OR LOSS OF

INFORMATION) ARISING OUT OF THE USE OR INABILITY TO USE THIS DOCUMENT, EVEN IF

QUEST HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.Quest makes no

representations or warranties with respect to the accuracy or completeness of the contents of this

document and reserves the right to make changes to specifications and product descriptions at any time

without notice. Quest does not make any commitment to update the information contained in this

document.

SQL Navigator


Updated Jul y 2008
http://www.quest.com/http://www.quest.com/mailto:[email protected]:[email protected]://www.quest.com/http://www.quest.com/


3/62

Table of Contents

Introduction ......................................................................................................................................................... 7

Purpose of this Supplement ..................................................................................................................................... 8

New Funct ionali ty in SQL Navigator 5.0 ........................................................................................................... 9

ER Diagram ............................................................................................................................................................ 10

Reading the Diagram ............................................................................................................................. 11

Code Road Map ..................................................................................................................................................... 12

Choosing Code to Model ....................................................................................................................... 12

New Map ................................................................................................................................................ 13

CodeXpert .............................................................................................................................................................. 15

SQLMonitor ............................................................................................................................................................ 16

Team Coding for CVS ............................................................................................................................................ 16

Look and Feel Options for SQL Navigator Screens ............................................................................................... 17

Autohide Buttons .................................................................................................................................................... 17

New Funct ionali ty in SQL Navigator 6.0 ......................................................................................................... 19

All-in-one Code Editor ............................................................................................................................................ 20

Split/Compare ........................................................................................................................................ 21

Selection Mode ...................................................................................................................................... 21

Editing Tabs/Spaces .............................................................................................................................. 22

Collapse/Expand Block .......................................................................................................................... 22

Code Completion / Dot Lookup ............................................................................................................. 23

Insert Where Clause Conditions ............................................................................................................ 24

Viewing Results in the Result window ................................................................................................... 25

Creating a New Stored Program ........................................................................................................... 25

Executing Stored Program ..................................................................................................................................... 26


4/62

iv SQL NavigatorUser Guide Supplement

Using the Execution Console ................................................................................................................ 28Code Editor Tab...................................................................................................................................................... 28

Switching Sessions ................................................................................................................................................. 29

Toolbox ................................................................................................................................................................... 30

Code Explorer ....................................................................................................................................... 31

Outline ................................................................................................................................................... 31

DB Explorer ........................................................................................................................................... 32

Describe ................................................................................................................................................ 32

History ................................................................................................................................................... 33

Available Options .................................................................................................................................. 33

Columns ................................................................................................................................................ 34

Dependencies ....................................................................................................................................... 34

PL/SQL Debugger ................................................................................................................................. 34

PL/SQL Debugger .................................................................................................................................................. 35

Setting and Viewing Watched Variables ............................................................................................... 36

Setting and Viewing Breakpoints .......................................................................................................... 37

Evaluating and Modifying Variables ...................................................................................................... 39

National Language Support (NLS) ......................................................................................................................... 41

SQL Modeler ...................................................................................................................................................... 43

SQL Modeler Overview ........................................................................................................................................... 44

Quick Start .............................................................................................................................................................. 45

SQL Modeler Toolbar ............................................................................................................................................. 46

Table Selector ......................................................................................................................................................... 47

Model Area ............................................................................................................................................................. 48

Populating the HAVING Clause .............................................................................................................................. 50

Populating the WHERE Clause .............................................................................................................................. 51

Viewing Joins .......................................................................................................................................................... 53

Building Queries ..................................................................................................................................................... 54

Setting Rules, Options or Criteria for a Query ........................................................................................................ 55

Specifying WHERE Clauses ................................................................................................................. 56

Automatic Insertion of Column Names into a WHERE Clause ............................................................. 57

Generated Query Tab ............................................................................................................................................. 58

Query Results Tab .................................................................................................................................................. 58

Criteria Tab ............................................................................................................................................................. 58


5/62

Table of Contents v

Need more information? ................................................................................................................................... 61

Quest Software product information ...................................................................................................... 61

Contacting Quest Software .................................................................................................................... 61

Contacting Quest Support ..................................................................................................................... 61


6/62


7/62

1

Introduction

Contents

Purpose of this Supplement 8


8/62

8 SQL NavigatorUser Guide Supplement

Purpose of this Supplement

In SQL Navigator 6.0, significant new functionality has been added, including:

Code Editor

Toolbox Streamline workflow for executing and debugging scripts and stored

programs

Some new functionality also had been added in SQL Navigator 5.0:

ER Diagram

CodeXpert

Code Road Map

CVS Team Coding

SQL Monitor

Knowledge Xpert for PL/SQL

Ability to change the look-and-feel of the SQL Navigator windows

Autohide panels

Overviews of the new functionality are included in the next chapter of this

document. For more detailed information about each new function, refer to the

Whats New in this Release topic in the online help which contains links to more

detailed help topics.

In SQL Navigator 4.5, the original Navigator Query Builder was replaced with a

more powerful SQL Modeler tool. The SQL Editor and the Stored Program Editor in

versions prior to SQL Navigator 6.0 are also replaced with the Code Editor.

References to the Query Builder, SQL Editor and Stored Program Editor in the User

Guide should be ignored and this supplement referred to instead.


9/62

2

New Functionality in SQL

Navigator 5.0

Contents

ER Diagram 10

Code Road Map 12

CodeXpert 15

SQLMonitor 16

Team Coding for CVS 16

Look and Feel Options for SQL Navigator Screens 17

Autohide Buttons 17


10/62


ER Diagram

The ER (Entity Relationship) Diagram lets you quickly model a table and

graphically see the dependencies and joins to other tables.

To access the ER diagram

From the Schema Browser, Tables page, right-click a table and select ERDiagram.

Note To ensure indexes are delivered in the correct order in a diagram, the preference

Enable Using DBA dictionary views must be set on. This can be done either when

you connect to the database using valid credentials (by selecting the Enable using

DBA views at log onoption) or after a successful log on, by selecting View |

Preferencesand selecting the Enable Using DBA dictionary viewsoption.

To create a new diagram

1 From the Objectmenus, select ER Diagram. The Create ER Diagram dialog

box appears.

2 From the drop down Schema menu, enter the Schema where your table

resides.

3 Enter the table you want to diagram.

4 Select the number of levels of referential tables you want to load.


11/62

Chapter 2 11

New Functionality in SQL Navigator 5.0

Note The more levels of referential tables you load, the more complicated the diagram willbecome, and the longer SQL Navigator will take to create the diagram.

5 Select your display options. You can show:

Primary keys

Foreign keys

Unique keys Data type

Not nullable

Indexes

If the display option has an icon associated with it, the icon is displayed to

the right of the option. In the diagram, the appropriate icon appears to the left

of the table name.

6 Click OKto generate the diagram.

Reading the Diagram

Each object listing contains:

Name of the table and the schema where it resides (in the title bar)

Columns in the table

Column data type

Whether the column is indexed

Any icons selected in the Display Options area when the diagram was created

Lines connect every two tables where one table is dependent on the other. Lines have

a knob end and an arrow end. The referencing table resides at the knob end, and the

referenced table at the arrow end.


12/62


Code Road MapThe Code Road Map graphically displays the complex PL/SQL interdependencies

within a database. You can think of the Code Road Map as a developers model of

the application code.

The Road Map displays two different levels: code only and code plus data. In the

code only version, you see a graphical representation of the run-time, call-stack

dependencies. The code plus data diagram lets you visually see what database

objects the code references and in what manner (for example, read versus write).

In this diagram, you can also include pertinent triggers and views. Views are

essentially treated as tables.

Choosing Code to Model

Access the Code Road Mapfrom Object | Code Road Map.

When you first open the Code Road Map, there will be no map loaded.


13/62

Chapter 2 13


New MapWhen you choose to create a new map, the Model Code dialog box appears. This

dialog box lets you choose what code you want to map, and what options you want

to use when it is mapped.

Schema

Choose the schema where the code you want mapped is located.

The Road Map displays two different levels: code only and code plus data.

Code Only

In the code only version, you see a graphical representation of the run-time, call-stack dependencies. There are three available options for this mode:

Expand packages for calls into them

Include calls to SYS owned PL/SQL

Include calls to other schemas PL/SQL

Code Plus Data

The code plus data diagram lets you visually see what database objects the

code references and in what manner (for example, read versus write). In this


14/62


diagram, you can also include pertinent triggers and views. Views areessentially treated as tables.

You can choose from a dropdown list of code types. SQL Navigator supports

mapping of functions, packages, procedures and triggers.

Code Unit

Objects of the type chosen above populate this dropdown for code unit.

Select the unit you want to map.

Sub Unit

If the code type you have chosen is a package, the various parts of that

package will be included in the sub unit dropdown, arranged alphabetically.

By default, the first sub unit is selected for mapping.

Levels to Model

Enter the number of levels down from your starting object that you want to

model. The default is ten.

Display Mode

Code Only - Select Code Only to model only code that the object calls.

Code + Data - Select Code + Data to model both code called and data (tables,views, and so on) referenced by the object.

Display Options

These options only affect the visual display of the map. The data in the map

is not affected by these selections.

Expand packages and types for calls

Include triggers (for Code + Data)

Include views (for Code + Data)

Include calls to SYS-owned objects

Include calls to other schema PL/SQL


15/62

Chapter 2 15


CodeXpertCodeXpert is a utility accessible via Editors in SQL Navigator that analyzes the code

against a set of rules for best practices. These rules are stored in a ruleset and can be

either user-defined or pre-defined.

The CodeXpert window opens as a tab within the Output Frame. The window

consists of the CodeXpert toolbars, the Results tab, the CodeXpert Report tab and the

Rules and Statements Totals.

To access CodeXpert

Right-click the editor and select CodeXpert: Code Review.


16/62


SQLMonitorA SQLMonitor tool is now bundled and installed automatically with SQL Navigator.

It is not accessible from SQL Navigator directly but can be accessed via the Start

menu under Quest Software | SQL Navigator 5 | SQL Monitor.

This tool enables the monitoring of connections to databases and SQL queries.

Team Coding for CVS

Team Coding can now use CVS as a 3rd-party version control provider if you install

a CVS client on your system. For more information about how to configure your

system to work with Team Coding, see "CVS Requirements" in the online help.

Team Coding has been tested with:


17/62

Chapter 2 17


CVSNT client and server 2.0.14 (or higher) with sserver, pserver, sspi, ssh,and local.

Look and Feel Options for SQL NavigatorScreens

The look and feel of the SQL Navigator screens can now be changed as required by

selecting from styles listed in the View | Preferences | User Interface | Style menu

option. The look and feel template styles available in SQL Navigator 5.0 are:

Standard

Flat

XP Native (the default style)

Office 2003

Autohide Buttons

The Debugger panel in SQL Navigator is now in autohide mode by default, that is, it

is hidden behind its button when SQL Navigator opens.

You can open a hidden window by pointing the mouse over the window label. Other

hidden windows such as Workspace and Output can be locked open or hidden by

clicking the pin icon located in the top right-hand corner.


18/62


Note The Debugger button is visible only when the Debug function is active.

The following screen shot shows the Workspace panel open and locked.


19/62

3

New Functionality in SQL

Navigator 6.0

Contents

Al l-in-one Code Editor 20

Executing Stored Program 26

Code Editor Tab 28

Switching sessions 29

Toolbox 30

PL/SQL Debugger 35

National Language Suppor t (NLS) 41


20/62


All-in-one Code Editor

The new All-in-one Code Editor combines all the editing features of the SQL Editor

and the Stored Program Editor to handle most SQL and PL/SQL development.

Moreover, it includes the Execution Console as well as the Toolbox to offer a more

flexible editing, executing, and debugging environment.

Each tab, depending on its content, can have one of the two possible layouts: SQL

layout or PL/SQL layout. Accordingly, when you switch tabs the window layout

changes to one of the two. Part of the toolbar changes as well, so only relevant

buttons become visible.

The following screenshot shows the new Code Editor (SQL Layout) with Toolbox

unpinned.


21/62

Chapter 3 21


To access the Code Editor Use one of the following methods to open the Code Editor

From the View menu, select Code Editor.

From the File menu, select New File | SQL Script.

Press Ctrl + M.

Double click to open any stored program.

Split/Compare

Use Split/Compare to split the current editing window in half either horizontally or

vertically, to compare different parts of the same or different scripts and stored

programs.

To split/compare the current scrip t

1 Navigate to the tab you want to split/compare.

2 Select one of the split options from the Code Editor toolbar or the right click

menu.

To compare between di fferent scripts/stored programs

1 Follow the steps above to split the current tab.

2 Right click and select the Second Sourcefrom Split/Compare.

Note The Second Source is only populated with the currently open scripts/stored programs.

Selection ModeSQL Navigator 6.0 introduces the block selection mode that allows the selection of a

rectangular block of code.

To enable block selection mode

From the Code Editor window, right click and select Edit | Selection Mode |

Block.

or

Press Alt+F7.


22/62


Note Highlight using block selection mode is limited to the length of the last line.

To freely select a block of code

Select View | Preferencesand select the Allow Caret after EOLoption in

Code Editor | General.

Enable block selection mode.

Highlight as usual. Now you can see that the selection is not limited to thelength of the last line.

Editing Tabs/Spaces

SQL Navigator 6.0 provides some features to work with "invisible characters" such

as tabs and spaces.

Note For this option to work on tabs, make sure the preference Use TAB charactersinCode

Editor | Generalis selected.

To show tabs/eof/eol

From the Code Editor window, right click and select Edit | Show

Tabs/Eol/Eof.

To switch between tabs and spaces

From the Code Editor window, right click and select Edit | Tabs/Spacesand

select the option accordingly.

Collapse/Expand Block

Instead of displaying arrows in the gutter to signify a block of code, SQL Navigator6.0 provides the Collapse Block functionality. You can collapse/expand a block,

procedure, loop or IF statement by clicking on the - or + symbols to hide/show

codes.


23/62

Chapter 3 23


Note With scripts that are exceptionally long, collapse/expand codes may slow down the

application. You can choose to disable this feature by changing the preference.

Code Completion / Dot Lookup

Smart code completion automatically displays a pick list of matching symbols

(variables, parameters, procedures, types) in the current scope when you start typing

in an identifier.

In the example below, e was typed following the BEGIN statement, and then SQL

Navigator displayed a list of matching types.

Note The list is displayed only when this piece of code belongs to a stored program.


24/62


Dot-lookup automatically displays a pick list of members of a PL/SQL record,

cursor, package or %ROWTYPE record when you type a dot character after a name

of variable.

In the example below, emp. was typed and then SQL Navigator displays a list of

matching members.

Insert Where Clause Condi tions

With the current version of SQL Navigator, you can easily build up SQL statements

using the Add Condition feature in the Code Editor.

To insert where clause conditions

1 Type a SQL statement up to the where clause (that is, Select * from

emp_table).

2 Click the Add Conditionsicon on the Code Editor Toolbar.

or

Select SQL Conditions | Add Conditionsfrom the right click menu to open

the Add Filter window.3 Select and fill in the field, operator and the value(s) accordingly.

4 Click OKto insert the where clause.


25/62

Chapter 3 25


Note The Value(s) field is shown depending on which Operator has been selected.

To remove all conditions

Click the Remove Conditionsicon to remove all conditions.

Note This feature is enabled only when there is no semi-colon in the script.

Viewing Results in the Result window

In SQL Navigator 6, the Result window now offers National Language Support

(multiple languages). In addition to the existing features, it also provides functions

such as: Count dataset rows, Fetch more, Fetch all, and Auto Refresh.

There is also a minor change in the behavior of the Data Grid. When the Updateable

option is turned on, double clicking the cell will open the Cell Editor which offers awider editing space. Otherwise, you can press the F2, Space or Enter keys to edit

straight from the cell.

There can be multiple results displayed, one per statement executed. Each result set

is displayed in a separate tab. When a result tab is selected, the corresponding

statement is focused. Alternatively, you can choose to display the results of a SQL

query as text in the Spool window of the editor.

Long fields are displayed as (Memo). You can view the text of a long field in a pop-up editor.

The processing status of executed SQL queries is displayed in a separate execution

status window.

Note Prior to SQL Navigator 6, the Script tab of the Code Editor (SQL Layout) was known as

SQL tab/window.

Creating a New Stored Program

When you create a new stored program, you specify the type of object you want to

create:

Procedure

Function

Package + Body


26/62


(Object) Type + Body

For each of these PL/SQL constructs, SQL Navigator provides a ready-made

template or "shell" to make coding easier.

You can modify these templates. Look for their names and locations in the opening

comments when the new object is created.

To create a new stored program

1 In the View menu, choose Code Editor.

2 In the Code Editor toolbar, click the Create New Stored Object button.

3 In the New Stored Program dialog, enter a name for the new program.

4 Specify the type of stored program you want to create, and then click OK.

5 Type or insert the PL/SQL code in the editing window.

6 To compile and save the stored program, choose Compilefrom the Objectmenu or toolbar.

Note You can choose to create Package and (Object) Type without a body.

To create a body for an existing package or (object) type

1 Select and open the package or (object) type you want to add a body to.

2 In the Code Editor, click the Bodybutton on the Toolbar to create a body for

that package/type.

3 Type or insert the PL/SQL code in the editing window.

4 To compile and save the stored program, choose Compilefrom the Object

menu or toolbar.

Note If the package/type already has a body, clicking the Body button takes you to the Bodytab.

Executing Stored Program

You can execute valid stored programsincluding procedures, package bodies, and

functionswith options for selecting the entry point, previewing the PL/SQL codeblock, setting IN parameters, displaying OUT parameters, and viewing results.


27/62

Chapter 3 27


Tip If you have the PL/SQL Debugger, you can set breakpoints and watch variables beforeexecution in Debug mode.

When you execute a stored program, SQL Navigator starts the Execution Console,

which allows you to set the various parameters for wrapping an anonymous block

around the stored program.

Note In SQL Navigator 6.0, the Execution Console is displayed at the bottom of the Script

window.

The following graphic shows the Code Editor (PL/SQL Layout) with the Execution

Console positioning at the bottom.

To execute a stored program

Find the stored program, and then use one of the following methods:

Select the stored program and then click the Runbutton in the Sessiontoolbar.

28 SQLN i t


28/62


Select the stored program and then from the Object menu and then choose

Execute.

Select the stored program and then press F9.

In the DB Navigator, DB Explorer or Project Manager, right-click the stored

program and then choose Execute.

In the Code Editor, run an anonymous PL/SQL block to execute the stored

program.

Using the Execution Console

The Execution Console helps you set various parameters for wrapping an anonymous

block around a stored program so that you can execute it. The Execution Console is

automatically triggered when you execute a procedure, package, package body, or

function.

If you generate an anonymous block, the Console assigns a file name to your

block in the following form:

..STB

If the anonymous block is for a packaged procedure, then the assigned name

has an additional componentthe entry point.

If an error occurs when trying to run a generated code block (for example,

due to a syntax error), the text is opened in the Code Editor.

For ease of debugging, in SQL Navigator 6.0 the Execution Console is displayed at

the bottom of the Script window. Its features include:

Set IN and OUT parameters and specify COMMIT and ROLLBACK options

for changes made by the procedure.

View the stub by clicking on the Stubbutton.

PL/SQL Profiler for analysis and tuning.

Code Editor Tab

In SQL Navigator 6.0, multiple instances of the Code Editor are displayed in tab

format at the top of the editor instead of on the Task bar. For ease of navigating

Chapter 3 29


29/62

Chapter 3 29


through multiple Code Editor Tabs, you can set the preference so that tab items

expand onto multiple lines by a specified amount.

Note You can use F6and Shift+F6to move among the tabs.

Preference to expand tab items onto multiple lines

View | Preferences | Code Editor | General | Allow Tab Items to Expand onto

Multiple Lines.

Switching Sessions

When using the Code Editor, you can freely switch to other sessions and continue

working within the same window, with the same script. This feature enables you to

run the same script against different databases without the need to copy it into

another instance of Code Editor. It also allows you to quickly correct the oversight of

opening a file into the wrong session.

In SQL Navigator 6.0, you can switch between sessions by clicking the Send to

Sessionbutton in the Code Editor Toolbar. You can choose from a list of current

sessions or start a brand new session.

Note In SQL Navigator 6.0, you can send the current Code Editor tab or a selected piece of

codes to another session.

To send a block o f code to another session

1 Select a block of code.

2 Click the Send to Sessionicon on the Code Editor toolbar.

3 Select a current session or Send to New Sessions.

30 SQLNavigator


30/62


Toolbox

The Toolbox provides necessary tools for editing and debugging purposes. All tools

have tab-like titles for quick activation using the mouse. Only one tool can be active

at a time.

Code Explorer

Outline

DB Explorer

Describe

History

Columns

Dependencies

PL/SQL Debugger

Note PL/SQL Debugger is only available when the Debugger is enabled.

By default the Toolbox is displayed on the right side of the Code Editor. To change

the position of the toolbox and move among the tools, select the appropriate optionsfrom the Code Editor shortcut menu.

You can also pin/unpin the toolbox to increase the editing space.

Chapter 3 31


31/62

Chapter 3 31


Code Explorer

The Code Editor includes a Code Explorer tool that helps make your work with

packages, procedures, and functions more efficient and error-free. (The Code

Explorer is now located inside the Toolbox.)

Outline

The Outline is a graphic representation of the syntax tree of the current source.

To access the Outline Tool

Click the Outlinetab in the Toolbox.

32 SQLNavigator


32/62


DB ExplorerInstead of switching to the DB Navigator Tree for finding and opening objects, you

can use the DB Explorer in the Toolbox. Although similar in appearance to DB

Navigator, DB Explorer is not a replacement to the familiar database browsing tool.

Rather, it is a light version of the latter integrated into Code Editor for extra

convenience.

The DB Explorer offers a simple filter to help speeding the search process.

To filter the objects in the DB Explorer tree

Type a filter phrase (for example, c%to filter all objects with names starting

with c).

Press Enter.

Expand the tree nodes to display the filtered objects.

You can drag and drop objects from DB Explorer into the Code Editor.

Note You can set the preference to decide the behavior of dragging and dropping table

names.

Describe

This tool displays the data structure for tables, indexes, views and synonyms. The

tool is also accompanied with a basic filter. To increase response time, the drop

down list is not populated with objects when the tool is first activated.

To use the filter

Leave the Filter blank and press Enterto show the complete list of objects.

You can also use wildcards to filter the objects. For example, type t%andpress Enterto display only objects with names starting with "t". Select an

object from the list to show its column names and types.

To access the Describe too l

Click the Describetab in the Toolbox.

Chapter 3 33


33/62

p


HistoryIn SQL Navigator 6.0, the History tool (previously called Statement Recall) is

located inside the Toolbox.

The History tool lists successfully executed SELECT, UPDATE, DELETE

commands and PL/SQL blocks up to 1000 of the most recent ones. In the History

window, each SQL statement is accompanied with the date, time and the schema that

they were executed on.

To access the History Tool

Click the Historytab from the Toolbox.

or

Click the SQL Historybutton to activate the History tab (SQL Layout only).

Note If the Toolbox is unpinned, pressing the button will not make History visible until theToolbox is shown.

Available Options

There are various options to manipulate the saved SQL statement.

Replace- To replace the current contents of the editing window with theselected SQL statement.

Insert- To add the recalled statement to the current contents.

Clipboard- If you want to copy and paste the statement into another

location.

New Tab- If you want to open a new Code Editor tab and paste the

statement into it.

Delete- To drop the statement from the SQL History list.

The combo box at the bottom of the window allows you to choose which action

(New Tab, Insert, Replace or Clipboard) will be executed upon double-clicking an

entry in History.

You can also insert the statements by dragging and dropping them into the Code

Editor.

34 SQL Navigator


34/62


ColumnsThe Columns window provides an easy-to-use tool to arrange and hide/show

columns of the retrieved table in the data grid. .

To access the Columns window

1 Run a valid query statement.

2 Click the Columnstab in the Toolbox.

To hide columns in the retrieved table

Select or deselect the checkbox next to each column name to hide or show

that column.

To arrange columns in the retrieved table

1 Select the column by clicking on its name.

2 Click the Move Upor Move Downbutton.

Note The settings are saved when you refresh or rollback the query. However if you rerun the

query, the settings are set back to the default.

Dependencies

This tool is separated into two tabs which list the Dependants and Depends On

objects of the current script. It also offers some basic functions in the shortcut menu

to manipulate the objects in the list. The available options are: Open, Describe,

Browse Data, Edit Data, and Compile.

To access the Dependencies Tool

Click the Dependenciestab in the Toolbox.

PL/SQL Debugger

SQL Navigators Debugger is integrated into the development environment.

Debugging is functional only when you are connected to an Oracle server via a

SQL*Net connection.

If you are using a local database, such as Personal Oracle, use the loopback

SQL*Net connection to perform interactive debugging.

Chapter 3 35


35/62


Minimum permissions required for using the Debugger

To use the Debugger, you must have the following minimum permissions:

Create session

Alter session

To compile a stored program or trigger with debug information

To be able to watch, evaluate or modify variables of a stored program or trigger, you

must compile the program with debug information.

To watch variables inside the program when execution pauses, use one of the

following methods:

Inside the Code Editor, right click to display the shortcut menu. Select

PL/SQL Debuggerfrom the PL/SQL Debugger sub menu.

Make sure PL/SQL Debugger mode is ON (click the Debuggerbutton on the

Code Editor toolbar), then compile your stored program.

You can set breakpoints and watches at any time before or during debug session.

You do not have to recompile your program after you set breakpoints or watches.

PL/SQL Debugger

In SQL Navigator 6.0, the tools and features for debugging stored programs can be

found inside the PL/SQL Debugger window located in the Toolbox. Those changes

simplify your working environment by reducing the number of floating windows.

With the new Code Editor and the new work flow you can perform all the needed

tasks without switching to other windows.

Some of the debugging features are conveniently placed in the shortcut menu of the

code editor. However, to access the full range of debugging features, click the

PL/SQL Debugger tab in the Toolbox. The available features are listed below:

To Select

Run to the end of the next

breakpoint/Step over/Step into code

PL/SQL Debugger toolbar/shortcuts/Code

Editor toolbar/etc.

Display the execution stack Stack tab

View and modify any variable Locals tab

36 SQL NavigatorU G id S l t


36/62


To Select

Set watch variables Watches tab

Set and remove breakpoints Breakpoints tab

Set an option to either stop execution if

an exception occurs, or ignore

exceptions

Stop on Exception in the Debug menu of

the PL/SQL Debugger

Setting and Viewing Watched Variables

You can select a set of variables to be watched. The values of watched variables are

displayed every time the debugger is pausedthat is, at breakpoints, after every Step

Over or Step Into, and during the automated execution.

Note Increasing the number of variables in the watch variable list may result in slowerdebugger performance.

To add to the list of watched variables

1 Place the cursor on the variable, or highlight it.

2 Right-click and choose Add Watch from the shortcut menu.

or

1 Press Ctrl+F5.

2 In the Watch tab, modify the properties as needed, and then click Addin the

Watch/Break menu.

To remove a watched variable name

1 Open the Watches tab in the PL/SQL Debugger panel.

2 Select the variable name in the watched variables list.

3 Choose Deletefrom the Watch/Break menu.

Displaying watch variables during execution

You can watch the output variables when your procedure is running. Watched

variables are displayed automatically in the PL/SQL Debugger window. If the

window is not displayed, you can activate it by pressing the PL/SQL Debuggerbutton from the Code Editor toolbar.


37/62



38/62


Breakpoints are colored as follows:

Yellow to distinguish the breakpoint line from the other lines of code.

Green for the breakpoint that is currently executing.

Grey for a disabled or invalid breakpoint.

To disable or enable a breakpoint (PL/SQL Debugger window)

You can disable a breakpoint without deleting it. Disabled breakpoints or invalid

breakpoints (those that the Debugger failed to set) are displayed on a gray

background, instead of yellow. Disabled breakpoints are ignored by the debugger

during execution. They can be re-enabled at a later time.

1 Choose the Breakpoints tab from the PL/SQL Debugger.

2 Select the breakpoint and click Enableor Disable.

3 Select Updatefrom the Watch/Break menu.

To set breakpoints action

1 Click the Breakpoints tab in the PL/SQL Debugger window.

2 Select a breakpoint from the list.

3 Choose the action (either Stop or Notify).

4 Select Updatefrom the Watch/Break menu.

Note If Notify is selected, the debugger will not stop at breakpoints. Instead, a message is

displayed in the output window confirming that this breakpoint has been passed.

Setting conditional breakpoints

Normally, setting a breakpoint suspends execution every time the breakpoint is

reached.This may be inconvenient when used within loops or frequently called procedures.

The Debugger allows you to define a condition for a breakpoint to be activated. For

example, you can request that execution stops at a breakpoint only when the value of

a variable exceeds a certain limit.

Note The breakpoint condition is evaluated before the breakpoint statement is executed.

Chapter 3 39

New Functionality in SQL Navigator 6 0


39/62


Example

1 CREATE OR REPLACE PROCEDURE DEBUG_ME AS

2 l _var i abl e BI NARY_I NTEGER;

3 BEGI N

4 l _var i abl e : = 10;

5 END;

If you set up a breakpoint on line 4 with a condition l_variable = 3, the debugger will

break on line 4 regardless of the condition. It does so because at that point the

statement 'l_variable := 10' has not been executed, and the condition 'l_variable = 3'

will not be evaluated correctly.

To define or change a conditional breakpoint

1 Switch to the Breakpoints tab in the PL/SQL Debugger window and select

the breakpoint from the list.

2 Specify a condition in the Condition field.

3 Select Updatefrom the Watch/Update menu.

Evaluating and Modifying Variables

When execution of a procedure is paused at a breakpoint, you can evaluate or modify

any scalar variable within procedure code. If you change the value of a variable, you

can confirm the new value of the scalar variable by evaluating the variable again.

When you evaluate a variable, ensure that only a single scalar variable is

selected.

You cannot modify the values of input variables.

When you evaluate a variable in a current breakpoint, remember that the

current breakpoint has not yet been executed.

Variable values in the "watch" window are updated only at the breakpoint, so

strategically place breakpoints after the watch variable.

To evaluate or modify a variable

1 Select the Locals tab from the PL/SQL Debugger.

2 The value of the variable is displayed in the window. (The values are only

populated when the code is running).



40/62

pp

3 To modify the value, click F2and enter the new value of the variable in

Value column, then press Enter.

Note You cannot make changes to input variables.

To test the result of a variable

To test the effect of different variables in your procedure, use the Locals tab.

For example, if the procedure performs a computation, you can start the procedure,

enter a starting value and watch how the procedure handles the result. If you want to

see a "what if" computation, enter a new value in the Locals tab. Perform the same

procedure and watch the new results.

1 Select the variable in the Locals tab.

2 Press F2and enter a new value in the Value column.

3 Press Enterto modify the value.

Note If you see an error such as "Not compiled with debug info" instead of the variable value,

you need to recompile the procedure with the Debug information and re-execute it in the

debugger to see the value of variables.

Watching CURSOR variables

When evaluating/watching a variable of CURSOR type, its value is displayed in the

following format:

flags:FFF, rowcount:RRR.

RRR determines the number of records fetched so far by the examined cursor.

FFF is a combination of cursor status flags:

%ISOPEN

%FOUND

%NOTFOUND

If you enter the watched variable names as C1%NOTFOUND, C1%FOUND,

C1%ISOPEN, C1%ROWCOUNT, the displayed value is the same as would result

from watching the cursor itself.

Chapter 3 41



41/62

y g

National Language Support (NLS)Previous versions of SQL Navigator used exclusively the single-byte representation

of text data and therefore could not handle properly data in languages other than

English. It was not possible to enter data that contains national characters or

manipulate with objects whose names contain such characters.

In the current version there is now a preference that controls both parts of the NLS

support (Multiple Languages). The two options available in the Sessions preferencesare:

No Support

Display and Edit multi byte data

Note This option is available only for the data in the data grid.


42/62


43/62

4

SQL Modeler

Contents

SQL Modeler Overview 44

Quick Start 45

SQL Modeler Toolbar 46

Table Selector 47

Model Area 48

Populating the HAVING Clause 50

Populating the WHERE Clause 51

Viewing Joins 53

Building Queries 54

Setting Rules, Options or Criteria for a Query 55

Generated Query Tab 58

Query Results Tab 58

Criteria Tab 58



44/62

SQL Modeler Overview

There are three ways to access the SQL Modeler:

Via the SQL Modelermenu item on the SQL Navigator Toolsmenu.

From the SQL Modelerbutton on the parent SQL Navigator window

toolbar.

By selecting Browse Data(Ctrl+B) on an object in the Database Navigator

tree.

The SQL Modeler dialog provides a fast means for creating the framework of a

Select, Insert, Update, or Delete statement. You can select Tables, Views, or

Synonyms, join columns, select columns, and create the desired type of statement.

You can navigate around the SQL Modeler by clicking on items or by using thekeyboard. Up and down arrow keys move through lists, the space bar selects and

deselects check boxes, and you can tab to move forward one area (table, menu, list,

etc) and Shift-Tab to move back one area.

TABLE MODEL AREA Used to graphically lay out a query. For more

information, see Model Area on page 48.

Chapter 4 45

SQL Modeler


45/62

SQL MODELER TOOLBAR Most frequently used Modeler functions. For

descriptions of toolbar items, see SQL Modeler Toolbar on page 46.

GENERATED SQL QUERY Automatically generated SQL as a result of the

model appears in the results grid below the Model Area.

TABLE SELECTOR Lists Tables, Views, and Synonyms available for your

model. For more information, see Table Selector on page 47.

STATUS PANEL Current schema and database.

SPLITTERS The SQL Modeler has two splitters to change how you divide the

screen.

A horizontal splitter between the Model Area and the Criteria/Generated

Query/Query Results dialog. Drag it up or down to see more or less detail.

A vertical splitter between the Model Area and the Table Selector List. Drag

it left or right to see more or less detail.

Note There is also a Hide/Show Table Listtoolbar button to temporarily hide the Table

Selector and show more model details.

Quick Start

Follow this procedure to get started using the SQL Modeler.

To start the SQL modeler

1 Drag and drop Tables, Views, or Synonymsfrom the Table Selectorto the

modeling area.

2 Drag and drop columnsfrom one table to another to create joins between the

tables.3 Click the checkbox by a column to select or deselect it.

4 Click the Save current modelasbutton on the toolbar to save the model todisk.

5 Click the Generated Querytab to view the generated SQL query, and thenclick the Load into SQL Editorbutton to copy the query to the SQL Editor

window.

http://worksql%20navigatoronline%20helpversion%204.4toad%20sql%20modelertoolbar_sql_modeler.htm/http://worksql%20navigatoronline%20helpversion%204.4toad%20sql%20modelertoolbar_sql_modeler.htm/


46/62

SQL Modeler Toolbar

The following commands are available via the toolbar in the SQL Modeler:

Button Command

Create a new model

Open an existing model

Save current model as...

Save model

Print model (not implemented in this release)

Edit current model information

Edit calculated fields

Generate a SELECT statement (has dropdown to

create different types of queries)

Execute a query

Opens the Explain Plan tool window

Load into SQL Editor window

Show/Hide the Table/View/Synonym List

Save sub query and return to master query

Cancel sub query and return to master query

Edit global WHERE clauses

Chapter 4 47

SQL Modeler


47/62

Button Command

Set Global HAVING conditions (must have GroupBy condition)

View joins

Auto join objects

Percentage of zoom for SQL Modeler pane

Table Selector

The Table Selector displays the list of tables available to you for inclusion in your

SQL Model. You can select tables, views, or synonyms from the current schema orany other schema by changing the schema drop down list.

Only those tables, views or synonyms for which you have SELECT privilege will be

listed.

Double-click each desired Table, View, Synonym, or drag and drop them from the

list to the model area. As the objects are presented on the model area, join lines are

drawn from any established foreign keys in the DDL.

To dock or undock the Table Selector dialog

Right-click the Table Selector and select Dockor Undock.



48/62

To add an object to the workspace

Right-click the object and select the Add to Workspacemenu option.

Model Area

Use the model area to visually join or manipulate the Tables, Views, or Synonyms.

You can click a table header and drag and drop the table where you want it in themodel area.

You can establish your own joins by dragging and dropping a column from one table

to another table column. Once the line is drawn, you can double-click the line to

adjust its properties such as Inner Join vs. Outer Join, or Join Test, for example,

equal (=), less than ().

If no table columns are selected, then all columns will be included in the query. If

you want only certain columns, click the checkbox for each desired column. A

checkmark is displayed in the box. The selected column's information appears in the

Criteria grid below.

Right-click the model

Copy Model Image to Clipboard Copies a bitmap image of the model tothe Windows Clipboard.

Tables

Chapter 4 49

SQL Modeler


49/62

Visibility Selects or deselects visible tables in the model. Tables

that are "Hidden" are unchecked in this dialog.

Calculated Fields Adds calculated fields based on other table

columns.

Show

Join Text Displays the column names that comprise the joins.

Adjust Model Origin Adjusts the upper left object to the upper leftof the window.

Indexes Shows the table Indexes "(IDX)" next to each Index

column.

Primary Key Shows the Table Primary Keys as "(PK)" next to each

Primary Key column.

Show Field Type Shows or hides the data type of each tablecolumn.

SQL

Run Query in Thread Runs the query in a way that allows you to

halt it in the middle if necessary.

Global Where Adds a WHERE clause to the query.

Global Having Adds a HAVING clause to the query.

Query Variables Lets you add or edit a variable.

Auto Join All Objects Automatically joins all tables based on DDL

Foreign Key Constraints.

Hide Fields Removes/restores the field views.

Zoom to Table Selects a table to focus.

Toggle Screen Model Temporarily hides the Table Select list and Results

tab.

Optimize All Table Sizes minimizes the size of the tables views.

Arrange Tables Arranges the table views automatically, cascading,

horizontally or vertically.



50/62

Right-click each Table object

Set Table Alias Sets the Table alias.

Alias Field Names Sets and resets alias column names.

Auto Join Automatically joins this table to others based on DDL Foreign

Key Constraints.

Show Schema Name in SQL Shows or Hides the schema name before

each table. Remove Table Permanently removes this table from the model.

Select All Objects Selects all columns in the table.

Unselect All Unselects all columns in the table.

Invert Selection Selects columns that were unselected and vice versa.

Optimize Size Restores the size of the tables to their defaults. Hide -Temporarily hides this table from the model. A hidden table will not

be included in the generated query.

Populating the HAVING Clause

You can automatically populate the Having clause in the SQL generated by the SQL

Modeler in one of two ways.

To automatically populate the Having clause

Do one of the following:

Set a GROUPed BYfield by double-clicking in the group row for

that criteria tab. Then set the Having clause for that field by entering itin the group cond. row. This has the limitation that you can only have

the selected field on the left side of the relational operator. If you need

to have multiple fields on that side of the operator, use the Global

Having feature.

Use the Global Having feature. Global Having entries should be in

the form of .

Chapter 4 51

SQL Modeler


51/62

Example

To construct the following query follow the numbered steps below:

SELECT emp. empno, emp. ename, emp.j ob, emp. mgr , emp. sal ,

emp. comm, emp. deptno

FROM emp

GROUP BY emp. deptno, emp. comm, emp. sal , emp. mgr , emp.j ob,

emp. ename, emp. empno

HAVI NG ( ( emp. sal + NVL ( emp. comm, 0) > 4000) )

1 Open the SQL Modeler (as SCOTT/TIGER).

2 Double-click EMP to add it to the model.

3 Right-click EMP and choose Select All, then deselect Hiredate.

4 On the Criteria tab, double-click the Group Byfield for DEPTNO.

5 Double-click the Group Byfields for COMM, SAL, MGR, JOB, ENAME

and EMPNOas well.

6 Click the Global Havingbutton in the toolbar. Click the Addbutton to add a

new Having clause.

7 Enter the Having clause to say:

EMP. SAL + NVL( EMP. COMM, 0) > 4000

8 Click OK.

View the generated query. It should appear as described above. This query selects all

the employees whose salary plus commission is greater than 4000. The NVL

command substitutes a null value in the specified column with the specified value, in

this case, 0.

Populating the WHERE Clause

There are two ways to populate the "Where" clause in SQL generated by the SQL

Modeler.

To populate the where clause

1 Use the Global Where feature. Global Where entries should be in the form

of .



52/62

2 Enter criteria into the Where Cond. and Or cells of the Criteria tab. Entries

into the cells of the Criteria tab should be in the form of .

Example

To construct the following query follow the numbered steps below:

SELECT dept . dept no, dept . dname, dept . l oc

FROM scot t . deptWHERE ( ( dept . deptno BETWEEN 1 AND 25)

AND ( dept . deptno < 40)

AND ( dept . l oc I S NOT NULL)

1 Open the SQL Modeler (as SCOTT/TIGER).

2 Double-click DEPTto add it to the model.

3 Right-click DEPTand choose Select All.

4 On the Criteria tab, double-click the Where Cond. cell under DEPTNO.

5 Choose


53/62

Viewing JoinsView joins by clicking the View Joinsbutton on the toolbar, or by double-clicking a

join linein the Modeler.

The view joins dialog appears.

From this dialog you can quickly see individual joins, browse through the joins, and

make changes to them.

The top two areas describe the join fields, joined from one table to another.

You can change the Join Type from Inner to Outer. The line color denotes

the type of join.

If you have selected an Outer join, you can make change which table the

outer join is performed on.

You can change the test for the join; that is, you can make it less than or

greater than, for example, instead of equal to. You can delete the current join.



54/62

Click Next Jointo move forward in the join list.

Click Previous Jointo move backward.

Click OKto close the window and return to the SQL Modeler.

Building Queries

1 If you need to clear an existing query from the SQL Modeler window, clickthe New Modelbuttonon the SQL Modeler toolbar.

2 To define the type of statement you want to create, choose SELECT,UPDATE, DELETE, or INSERTfrom the SQL Modeler toolbar.

3 If you want to open a saved query, click Open an existing modelbutton inthe SQL Modeler toolbar, then browse to the saved query.

4 To specify a table name to be used in the query, double-click the Table,

View or Synonymfrom their respective tabs in the Table Selector area.

5 To specify a table column to be used in the query, first select a column name

in the work area, and then either:

Drag it into the data grid in the lower pane of the window, or

Click a selection box to place a checkmark next to the column name.

SQL code is automatically generated when you add table names to the work

area pane and then select column names. To view the SQL code, click the

Generated Querytab in the lower pane of the SQL Modeler window.

6 Set options, rules, and criteria for the query. For more information, see

Setting Rules, Options or Criteria for a Query on page 55.

7 You can view your query as SQL code or as a data grid by clicking the

Generated Queryand Criteriatabs, respectively.

8 To test the query, click the Execute Querybutton. The results appear in the

Query Results tab. From here, you can copy the new query to the SQL Editor

by clicking the Load in the SQL Editorbutton.

Chapter 4 55

SQL Modeler


55/62

9 To save the query, in the SQL Modeler click the Save current model as

button or the Save modelbutton. You can also choose to copy the query tothe clipboard for pasting into another window (for example, the SQL Editor).

To create a table join

1 Add two table names to the work area by double-clicking the tables in the

Table Selector area.

2 In the first table, click the column name that you want to join, and drag it to

the corresponding column name in the second table.

When the join is successfully created, the SQL Modeler draws a connecting

line that represents the join between the two table columns.

To specify criteria for a table join

1 After creating a table join (see instructions above), double-click the

connecting line joining the two columns.

2 Choose a join operator from the list, specify a join type, and then click OK.

To test a query

1 In the SQL Modeler, construct a query.

2 Click Execute query.The result appears in the Query Results tab.

To roll back changes

If the query in the SQL Modeler is an UPDATE, DELETEor INSERTstatement, a

rollback will occur automatically.

To clear SQL Modeler and create a new query

Click the New modelbutton on the SQL Modeler toolbar.

Setting Rules, Options or Criteria for a Query

1 Open a new or existing query and specify the schema, table name and

columns you want to use.

2 In the data grid (lower part of the SQL Modeler window), find the row

heading with the type of rule, option or criteria you want to set (Where, Sort,

Aggregate, and so on).



56/62

3 In the chosen row, click inside a cell belonging to the column for which you

are setting the rule, option or criteria.

4 When a square button appears inside the cell, click it to open the editing

window and view or set the options, rules or criteria.

When a square button appears inside the cell, you can use the spacebar or

Ctrl+Enterto open the column editing window.

Specifying WHERE Clauses

You can specify anyvalid expression in the WHERE clause, no matter which

columns are selected in the Select row of the data grid.

To specify the WHERE clause for a column, use either of the following procedures:

If the column is selectedIf the column to be queried for the WHERE clause is already selected in the list of

columns in the work area above the data grid, deselect it by clearing the checkmark

for the column from the SELECT row of the data grid (but leave the column selected

in the column list).

In the WHERE row, click inside a cell belonging to the column for which you are

setting the WHERE clause. When a square button appears inside the cell, click it to

enter the criteria for the WHERE clause.

Because you cleared the checkmark in the SELECT row, the column will not be

included in the result set, but it willbe used in the WHERE clause of the SQL

statement.

If the column is not selected

If the column to be queried for the WHERE clause is notselected in the list of

columns in the work area above the data grid, then click inside a cell belonging to the

column for which you are setting the WHERE clause. When a square button appears

inside the cell, click it to enter the criteria for the WHERE clause. The WHERE

clause can specify any column regardless of whether it is included in the SELECT

statement or not.

Chapter 4 57

SQL Modeler

A t t i I ti f C l N i t WHERE Cl


57/62

Automat ic Insertion of Column Names into a WHERE Clause

When you enter an expression in a WHERE clause, the SQL Modeler automatically

insert the selected column name into the SQL as a prefix ifthe expression starts with

one of the operators in the following list.

If you start the expression with the name of another column, or with something other

than one of the terms below, then SQL Modeler will notinsert the selected column

name as a prefix to the expression you type. In this way, you can refer to any column

or table name in your WHERE clauseeven columns not named in the SELECTstatement.

Operator Meaning Example

= Equal To where EMPNO=1234

> Greater Than where EMPNO>1234

>= Greater Than or Equal To where EMPNO>=1234< Less Than where EMPNO


58/62

Generated Query TabThis tab shows the automatically generated SQL statement. Any changes made to the

model or column Criteria will automatically regenerate this SQL statement. You

cannot directly edit the SQL on the Generated Query tab dialog.

Right-click the Criteria grid

Copy - copies the query to the clipboard.

Save As- saves the query to a file.

View Query in SQL Editor- copies the query directly to a SQL Editor

window.

Query Results TabThis grid displays the results of executing the generated query. Insert, Update, and

Delete queries can only be executed in the SQL Editor window.

Making changes to the tables or columns, then clicking the Query Results tab will

prompt you whether or not to re-query the data.

Criteria Tab

If individual columns are selected, they will be displayed in the Criteria grid. You

can edit many of the column properties here in the Criteria grid. Table alias, Table

and Schemaare not editable here.

Only fetch unique records checkbox

Applies the DISTINCT command to the query.

Null Value Subst

Double-click to enter a value to substitute for any null values. To clear the cell,

highlight it and press Delete.

Chapter 4 59

SQL Modeler

Aggregate F


59/62

Aggregate F

Double-click to select an aggregate column function, such as Average, Count, Max,

Min, or Sum. To clear the cell, highlight it and press Delete.

Where Cond

Double-click Conditionto display the Input the WHERE Definitiondialog, where

you can set a condition which tests if a column is equal to (or , =, ,

LIKE, BETWEEN) a constant or another field or a condition where the selected field

is in a sub query.

To remove the WHERE definition

Display the Input the WHERE Definitiondialog and click the Remove

Conditionbutton, or highlight the appropriate cell and press Delete.

To reset the dialog box and begin the WHERE condition again

Click Clear Form.

or

Double-click to display the dialog and make changes to the fields.

Or

Double-click to enter another WHERE criteria, which will be grouped

together with the above WHERE criteria using the OR operator. If you wantto group multiple column criteria together using the AND operator, then go

to the expert mode from the Input the WHERE Definition dialog. For more

information about Where criteria, and an example, see Populating the

WHERE Clause on page 51.

Group By

Double-click to select this as a GROUP BY column. A number within parenthesesindicates the order of the columns in the GROUP BY clause. To clear the cell,

highlight it and press Delete.

Having Aggregate

Double-click to select an aggregate column function (such as Average, Count, Max,

Min, or Sum) for the HAVING clause. This allows for the following type of query:

SELECT emp. j ob_i d

FROM empl oyee emp


GROUP BY emp j ob i d


60/62

GROUP BY emp. j ob_i d

HAVI NG ( ( AVG ( emp. sal ary) > 1500) )

To clear the cell, highlight it and press Delete.

Having Cond

Double-click to bring up the GROUP BY Definitionwindow to enter a GROUP BY

condition. To clear the cell, highlight it and press Delete.

Sort

Double-click the Sortrow to select this column as Ascending sort, Descending sort,

or no sort. To clear the cell, highlight it and press Delete.

Visible

Double-click Visibleto toggle whether or not this column is returned in the columnlist (Show or Not Shown).

Field Alias

Double-click to change the field name alias for the selected field.

Table Alias

Double-click to change the table name alias for the selected field.

Ordering Columns

To rearrange the order of the columns, drag and drop them left or right.

Right-click the Criteria grid

Suppress Current Column- Removes this column from the query.

Best Fit (All Columns)- Sets the column width of the Criteria grid to show

all text.

Default Width (All Columns)- Sets the column width of the Criteria grid to

the default width.

Copy Query Grid Image to Clipboard- Copies the Criteria grid to the

clipboard.

5


61/62

5

Need more information?

Quest Software product information

You can contact Quest Software for product information in any of the following

ways:

Web page www.quest.com

E-mail (sales & marketing) [email protected]

Contacting Quest Software

Email [email protected]

Mail Quest Software, Inc.World Headquarters

5 Polaris Way

Aliso Viejo, CA 92656

USA

Web site www.quest.com

Please refer to our Web site for regional and international office information.

Contacting Quest Support

Quest Support is available to customers who have a trial version of a Quest product

or who have purchased a commercial version and have a valid maintenance contract.Quest Support provides around the clock coverage with SupportLink, our web self-

service. Visit SupportLink at http://support.quest.com.


From SupportLink, you can do the following:
http://www.quest.com/mailto:[email protected]:[email protected]://www.quest.com/http://support.quest.com/http://support.quest.com/http://www.quest.com/mailto:[email protected]:[email protected]://www.quest.com/


62/62

pp y g

Quickly find thousands of solutions (Knowledgebase articles/documents)

Download patches and upgrades.

Seek help from a Support engineer.

Log and update your case, and check its status.

View the Global Support Guide for a detailed explanation of support programs,

online services, contact information, and policy and procedures. The guide isavailable at: http://support.quest.com/pdfs/Global Support Guide.pdf.

About Quest Sof tware, Inc

Quest Software, Inc., Microsofts 2007 Global Independent Software Vendor Partner

of the Year, delivers innovative products that help organizations get more

performance and productivity from their applications, databases and Windows

infrastructure. Through a deep expertise in IT operations and a continued focus onwhat works best, Quest helps more than 50,000 customers worldwide meet higher

expectations for enterprise IT. Quest Software helps organizations deliver, manage

and control complex database environments through award-winning products for

Oracle, SQL Server, IBM DB2, Sybase and MySQL. Quest Software can be found in

offices around the globe and at www.quest.com.
http://support.quest.com/pdfs/Global%20Support%20Guide.pdfhttp://www.quest.com/http://www.quest.com/http://support.quest.com/pdfs/Global%20Support%20Guide.pdf

guia suplementaria para el manual de oracle sql

Documents