Oracle Database User Manual

Oracle® Database

SQL Developer User’s Guide

Release 1.1

B31695-01

December 2006

Provides conceptual and usage information information about Oracle SQL Developer, a graphical tool that enables you to browse, create, edit, and delete (drop) database objects; run SQL statements and scripts; edit and debug PL/SQL code; manipulate and export data; and view and create reports.

Oracle Database SQL Developer User’s Guide, Release 1.1

B31695-01

Primary Author: Chuck Murray

The Programs (which include both the software and documentation) contain proprietary information; they are provided under a license agreement containing restrictions on use and disclosure and are also protected by copyright, patent, and other intellectual and industrial property laws. Reverse engineering, disassembly, or decompilation of the Programs, except to the extent required to obtain interoperability with other independently created software or as specified by law, is prohibited.

The information contained in this document is subject to change without notice. If you find any problems in the documentation, please report them to us in writing. This document is not warranted to be error-free. Except as may be expressly permitted in your license agreement for these Programs, no part of these Programs may be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose.

If the Programs are delivered to the United States Government or anyone licensing or using the Programs on behalf of the United States Government, the following notice is applicable:

U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data delivered to U.S. Government customers are "commercial computer software" or "commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of the Programs, including documentation and technical data, shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement, and, to the extent applicable, the additional rights set forth in FAR 52.227-19, Commercial Computer Software--Restricted Rights (June 1987). Oracle USA, Inc., 500 Oracle Parkway, Redwood City, CA

94065.

The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently dangerous applications. It shall be the licensee's responsibility to take all appropriate fail-safe, backup, redundancy and other measures to ensure the safe use of such applications if the Programs are used for such purposes, and we disclaim liability for any damages caused by such use of the Programs.

Oracle, JD Edwards, PeopleSoft, and Siebel are registered trademarks of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

The Programs may provide links to Web sites and access to content, products, and services from third parties. Oracle is not responsible for the availability of, or any content provided on, third-party Web sites. You bear all risks associated with the use of such content. If you choose to purchase any products or services from a third party, the relationship is directly between you and the third party. Oracle is not responsible for: (a) the quality of third-party products or services; or (b) fulfilling any of the terms of the agreement with the third party, including delivery of products or services and warranty obligations related to purchased products or services. Oracle is not responsible for any loss or damage of any sort that you may incur from dealing with any third party.

Preface ................................................................................................................................................................ vii

Audience...................................................................................................................................................... vii

Documentation Accessibility.................................................................................................................... vii

Related Documents ................................................................................................................................... viii

Conventions ............................................................................................................................................... viii

Third-Party License Information............................................................................................................. viii

1 SQL Developer Concepts and Usage

1.1 Installing and Getting Started with SQL Developer.............................................................. 1-1

1.2 SQL Developer User Interface................................................................................................... 1-2

1.2.1 Menus for SQL Developer.................................................................................................. 1-5

1.3 Database Objects ......................................................................................................................... 1-8

1.3.1 Database Links (Public and Private)................................................................................. 1-8

1.3.2 Directories............................................................................................................................. 1-8

1.3.3 Functions............................................................................................................................... 1-8

1.3.4 Indexes................................................................................................................................... 1-9

1.3.5 Materialized Views.............................................................................................................. 1-9

1.3.6 Materialized View Logs................................................................................................... 1-10

1.3.7 Packages............................................................................................................................. 1-10

1.3.8 Procedures ......................................................................................................................... 1-10

1.3.9 Recycle Bin......................................................................................................................... 1-10

1.3.10 Sequences........................................................................................................................... 1-11

1.3.11 Synonyms (Public and Private) ...................................................................................... 1-11

1.3.12 Tables.................................................................................................................................. 1-11

1.3.13 Triggers .............................................................................................................................. 1-12

1.3.14 Types................................................................................................................................... 1-13

1.3.15 Users (Other Users) .......................................................................................................... 1-13

1.3.16 Views .................................................................................................................................. 1-13

1.3.17 XML Schemas.................................................................................................................... 1-13

1.4 Database Connections ............................................................................................................. 1-14

1.5 Entering and Modifying Data ................................................................................................ 1-15

1.6 Running and Debugging Functions and Procedures ......................................................... 1-16

1.6.1 Remote Debugging........................................................................................................... 1-18

1.7 Using the SQL Worksheet....................................................................................................... 1-19

1.7.1 SQL*Plus Statements Supported and Not Supported in SQL Worksheet................ 1-21

iii

1.7.2 Script Runner..................................................................................................................... 1-22

1.7.3 Execution Plan................................................................................................................... 1-23

1.7.4 Autotrace Pane.................................................................................................................. 1-23

1.7.5 DBMS Output Pane .......................................................................................................... 1-23

1.7.6 OWA Output Pane ........................................................................................................... 1-24

1.8 Using SQL*Plus........................................................................................................................ 1-24

1.9 Using Snippets to Insert Code Fragments............................................................................ 1-24

1.9.1 User-Defined Snippets ..................................................................................................... 1-25

1.10 Reports....................................................................................................................................... 1-25

1.10.1 About Your Database reports ......................................................................................... 1-26

1.10.2 Database Administration reports................................................................................... 1-26

1.10.3 Table reports...................................................................................................................... 1-27

1.10.4 PL/SQL reports................................................................................................................. 1-28

1.10.5 Security reports................................................................................................................. 1-29

1.10.6 XML reports....................................................................................................................... 1-29

1.10.7 Jobs reports ........................................................................................................................ 1-29

1.10.8 Streams reports ................................................................................................................. 1-30

1.10.9 All Objects reports ............................................................................................................ 1-30

1.10.10 Data Dictionary reports ................................................................................................... 1-30

1.10.11 User Defined reports ........................................................................................................ 1-30

1.10.11.1 User-Defined Report Example: Chart..................................................................... 1-31

1.10.11.2 User-Defined Report Example: Dynamic HTML.................................................. 1-32

1.11 SQL Developer Preferences.................................................................................................... 1-33

1.11.1 Environment...................................................................................................................... 1-33

1.11.2 Accelerators (Keyboard Shortcuts) ................................................................................ 1-34

1.11.3 Code Editor........................................................................................................................ 1-34

1.11.4 Database............................................................................................................................. 1-36

1.11.5 Debugger............................................................................................................................ 1-38

1.11.6 Documentation.................................................................................................................. 1-38

1.11.7 Extensions.......................................................................................................................... 1-38

1.11.8 File Types........................................................................................................................... 1-39

1.11.9 PL/SQL Compiler............................................................................................................. 1-39

1.11.10 PL/SQL Debugger............................................................................................................ 1-40

1.11.11 SQL*Plus ............................................................................................................................ 1-40

1.11.12 SQL Formatter................................................................................................................... 1-40

1.11.13 Web Browser and Proxy.................................................................................................. 1-40

1.12 Location of User-Related Information .................................................................................. 1-40

1.13 Using the Help.......................................................................................................................... 1-41

1.14 For More Information.............................................................................................................. 1-42

2 Tutorial: Creating Objects for a Small Database

2.1 Creating a Table (BOOKS)......................................................................................................... 2-1

2.2 Creating a Table (PATRONS) ................................................................................................... 2-3

2.3 Creating a Table (TRANSACTIONS)....................................................................................... 2-4

2.4 Creating a Sequence.................................................................................................................... 2-6

2.5 Creating a View........................................................................................................................... 2-7

2.6 Creating a PL/SQL Procedure.................................................................................................. 2-7

2.7 Debugging a PL/SQL Procedure ............................................................................................. 2-8

2.8 Using the SQL Worksheet for Queries.................................................................................. 2-10

2.9 Script for Creating and Using the Library Tutorial Objects .............................................. 2-10

3 Dialog Boxes for Creating/Editing Objects

3.1 Add Extension............................................................................................................................. 3-1

3.2 Check for Updates ...................................................................................................................... 3-1

3.3 Choose Directory ........................................................................................................................ 3-2

3.4 Create/Edit New Object ............................................................................................................ 3-2

3.5 Create/Edit/Select Database Connection............................................................................... 3-2

3.6 Select Connection........................................................................................................................ 3-4

3.7 Connection Information............................................................................................................. 3-4

3.8 No Connection Found................................................................................................................ 3-4

3.9 Select Library............................................................................................................................... 3-5

3.10 Create Library.............................................................................................................................. 3-5

3.11 Export/Import Connection Descriptors.................................................................................. 3-5

3.12 Create/Edit Database Link........................................................................................................ 3-5

3.13 Create/Edit Index....................................................................................................................... 3-6

3.14 Create/Edit Materialized View Log......................................................................................... 3-6

3.15 Create PL/SQL Package ............................................................................................................ 3-7

3.16 Create PL/SQL Subprogram (Function or Procedure) ......................................................... 3-8

3.17 Create/Edit Sequence................................................................................................................. 3-8

3.18 Create SQL File............................................................................................................................ 3-9

3.19 Create/Edit Synonym ................................................................................................................ 3-9

3.20 Create Table (quick creation) ................................................................................................. 3-10

3.21 Create/Edit Table (with advanced options)........................................................................ 3-11

3.22 Create Trigger........................................................................................................................... 3-20

3.23 Create Type (User-Defined) ................................................................................................... 3-21

3.24 Create/Edit User...................................................................................................................... 3-21

3.25 Create/Edit User Defined Report.......................................................................................... 3-22

3.26 Create/Edit User Defined Report Folder............................................................................. 3-23

3.27 Create/Edit View..................................................................................................................... 3-24

3.28 Create XML Schema ................................................................................................................ 3-29

3.29 Configure File Type Associations.......................................................................................... 3-29

3.30 DDL Panel for Creating or Editing an Object...................................................................... 3-29

3.31 Debugger - Attach to JPDA .................................................................................................... 3-29

3.32 Describe Object Window ........................................................................................................ 3-30

3.33 Edit Value (Table Column Data) ........................................................................................... 3-30

3.34 Enter Bind Values .................................................................................................................... 3-30

3.35 Export (Selected Objects or Types of Objects) ..................................................................... 3-30

3.36 Export Error.............................................................................................................................. 3-31

3.37 Export Table Data .................................................................................................................... 3-31

3.38 External Tools........................................................................................................................... 3-32

3.39 Create/Edit External Tool ...................................................................................................... 3-32

3.40 Filter........................................................................................................................................... 3-33

3.41 Insert Macro.............................................................................................................................. 3-34

3.42 Externally Modified Files........................................................................................................ 3-34

3.43 Filter Object Types................................................................................................................... 3-34

3.44 Filter Schemas........................................................................................................................... 3-34

3.45 Find/Replace Text ................................................................................................................... 3-35

3.46 Go to Line Number.................................................................................................................. 3-35

3.47 Go to Line Number: Error....................................................................................................... 3-35

3.48 Load Preset Key Mappings..................................................................................................... 3-35

3.49 Modify Value............................................................................................................................ 3-35

3.50 Open File ................................................................................................................................... 3-36

3.51 Query Builder........................................................................................................................... 3-36

3.52 Recent Files ............................................................................................................................... 3-36

3.53 Run/Debug PL/SQL............................................................................................................... 3-37

3.54 Create/Edit Breakpoint........................................................................................................... 3-37

3.55 Save/Save As............................................................................................................................ 3-38

3.56 Save Files................................................................................................................................... 3-38

3.57 Unable to Save Files................................................................................................................. 3-38

3.58 Save Style Settings ................................................................................................................... 3-38

3.59 Schema Differences.................................................................................................................. 3-38

3.60 Set Pause Continue .................................................................................................................. 3-39

3.61 Sign In (checking for updates) ............................................................................................... 3-39

3.62 Single Record View.................................................................................................................. 3-39

3.63 Save Snippet (User-Defined).................................................................................................. 3-40

3.64 Edit Snippets (User-Defined) ................................................................................................. 3-40

3.65 SQL History List....................................................................................................................... 3-40

3.66 SQL*Plus Location ................................................................................................................... 3-41

This guide provides conceptual and usage information information about Oracle SQL Developer, a graphical tool that enables you to browse, create, edit, and delete (drop) database objects; run SQL statements and scripts; edit and debug PL/SQL code; manipulate and export data; and view and create reports.

Audience

This guide is intended for those using the Oracle SQL Developer tool.

Documentation Accessibility

Our goal is to make Oracle products, services, and supporting documentation accessible, with good usability, to the disabled community. To that end, our documentation includes features that make information available to users of assistive technology. This documentation is available in HTML format, and contains markup to facilitate access by the disabled community. Accessibility standards will continue to evolve over time, and Oracle is actively engaged with other market-leading technology vendors to address technical obstacles so that our documentation can be accessible to all of our customers. For more information, visit the Oracle Accessibility Program Web site at

Preface

http://www.oracle.com/accessibility/

Accessibility of Code Examples in Documentation

Screen readers may not always correctly read the code examples in this document. The conventions for writing code require that closing braces should appear on an otherwise empty line; however, some screen readers may not always read a line of text that consists solely of a bracket or brace.

Accessibility of Links to External Web Sites in Documentation

This documentation may contain links to Web sites of other companies or organizations that Oracle does not own or control. Oracle neither evaluates nor makes any representations regarding the accessibility of these Web sites.

TTY Access to Oracle Support Services

Oracle provides dedicated Text Telephone (TTY) access to Oracle Support Services within the United States of America 24 hours a day, seven days a week. For TTY support, call 800.446.2398.

vii

Conventions

The following text conventions are used in this document:

Convention Meaning

boldface Boldface type indicates graphical user interface elements associated

italic Italic type indicates book titles, emphasis, or placeholder variables for

monospace Monospace type indicates commands within a paragraph, URLs, code

with an action, or terms defined in text or the glossary.

which you supply particular values.

in examples, text that appears on the screen, or text that you enter.

Third-Party License Information

Oracle SQL Developer contains third-party code. Oracle is required to provide the following notices. Note, however, that the Oracle program license that accompanied this product determines your right to use the Oracle program, including the third-party software, and the terms contained in the following notices do not change those rights.

Apache Regular Expression Package 2.0

Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at:

http://www.apache.org/licenses/LICENSE-2.0

viii

Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.

Antlr v 2.7.3

http://www.antlr.org/rights.html OracleAS TopLink uses Antlr for EJB QL parsing. Antlr (ANother Tool for Language

Recognition), is a language tool that provides a framework for constructing recognizers, compilers, and translators from grammatical descriptions containing C++ or Java actions. The ANTLR parser and translator generator is fully in the public domain.

JGoodies Looks and Forms

permitted provided that the following conditions are met:

■ Redistributions of source code must retain the above copyright notice, this list of

conditions and the following disclaimer.

■ Redistributions in binary form must reproduce the above copyright notice, this list

of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.

■ Neither the name of JGoodies Karsten Lentzsch nor the names of its contributors

may be used to endorse or promote products derived from this software without specific prior written permission.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

SQL Developer Concepts and Usage

Oracle SQL Developer is a graphical version of SQL*Plus that gives database developers a convenient way to perform basic tasks. You can browse, create, edit, and delete (drop) database objects; run SQL statements and scripts; edit and debug PL/SQL code; manipulate and export data; and view and create reports.

You can connect to any target Oracle database schema using standard Oracle database authentication. Once connected, you can perform operations on objects in the database.

You can also connect to schemas for selected third-party (non-Oracle) databases, such as MySQL, Microsoft SQL Server, and Microsoft Access, and view metadata and data.

This chapter contains the following major sections:

Section 1.1, "Installing and Getting Started with SQL Developer" Section 1.2, "SQL Developer User Interface" Section 1.3, "Database Objects" Section 1.4, "Database Connections" Section 1.5, "Entering and Modifying Data" Section 1.6, "Running and Debugging Functions and Procedures" Section 1.7, "Using the SQL Worksheet" Section 1.8, "Using SQL*Plus" Section 1.9, "Using Snippets to Insert Code Fragments" Section 1.10, "Reports" Section 1.11, "SQL Developer Preferences" Section 1.12, "Location of User-Related Information" Section 1.13, "Using the Help" Section 1.14, "For More Information"

1.1 Installing and Getting Started with SQL Developer

To install and start SQL Developer, you simply download a ZIP file and unzip it into a desired parent directory or folder, and then type a command or double-click a file name. You should read the Oracle Database SQL Developer Installation Guide before you perform the installation. After you have read the installation guide, the basic steps are:

SQL Developer Concepts and Usage 1-1

SQL Developer User Interface

Unzip the SQL Developer kit into a directory (folder) of your choice. This

directory location will be referred to as <sqldeveloper_install>. Unzipping the SQL Developer kit causes a directory named sqldeveloper to be

created under the <sqldeveloper_install> directory. It also causes many files and folders to be placed in and under that directory.

2. To start SQL Developer, go to the sqldeveloper directory under the

<sqldeveloper_install> directory, and do one of the following: On Linux and Mac OS X systems, run sh sqldeveloper.sh. On Windows systems, double-click sqldeveloper.exe. If you are asked to enter the full pathname for java.exe, click Browse and find

java.exe. For example, on a Windows system the path might have a name similar to C:\Program Files\Java\jdk1.5.0_06\bin\java.exe.

3. If you want to become familiar with SQL Developer concepts before using the

interface, read the rest of this chapter before proceeding to the next step.

4. Create at least one database connection (or import some previously exported

connections), so that you can view and work with database objects, use the SQL Worksheet, and use other features.

To create a new database connection, right-click the Connections node in the Connections navigator, select New Database Connection, and complete the required entries in the dialog box.

5. If you want to get started quickly with SQL Developer, do the short tutorial in

Chapter 2, "Tutorial: Creating Objects for a Small Database", or work with your

existing database objects.

1.2 SQL Developer User Interface

The SQL Developer window generally uses the left side for navigation to find and select objects, and the right side to display information about selected objects.

Figure 1–1 shows the main window.

1-2 Oracle Database SQL Developer User’s Guide

Figure 1–1 SQL Developer Main Window

SQL Developer User Interface

Note: This text explains the default interface. However, you can

customize many aspects of the appearance and behavior of SQL Developer by setting preferences (see Section 1.11).

The menus at the top contain standard entries, plus entries for features specific to SQL Developer (see Section 1.2.1, "Menus for SQL Developer"), as shown in the following figure.

You can use shortcut keys to access menus and menu items: for example Alt+F for the File menu and Alt+E for the Edit menu; or Alt+H, then Alt+S for Help, then Full Text Search. You can also display the File menu by pressing the F10 key.

Icons under the menus perform the following actions:

■ New creates a new a new database object (see Section 3.4, "Create/Edit New

Object").

■ Open opens a file (see Section 3.50, "Open File").

■ Save saves any changes to the currently selected object.

■ Save All saves any changes to all open objects.

■ Open SQL Worksheet opens the SQL Worksheet (see Using the SQL Worksheet).

If you do not use the drop-down arrow to specify the database connection to use, you are asked to select a connection.

■ Back moves to the pane that you most recently visited. (Or use the drop-down

arrow to specify a tab view.)

SQL Developer Concepts and Usage 1-3

SQL Developer User Interface

■ Forward moves to the pane after the current one in the list of visited panes. (Or

use the drop-down arrow to specify a tab view.)

The left side of the SQL Developer window has tabs and panes for the Connections and Reports navigators, icons for performing actions, and a hierarchical tree display for the currently selected navigator, as shown in the following figure.

The Connections navigator lists database connections that have been created. To create a new database connection, import an XML file with connection definitions, or export or edit current connections, right-click the Connections node and select the appropriate menu item. (For more information, see Section 1.4, "Database

Connections".)

The Reports navigator lists informative reports provided by SQL Developer, such as a list of tables without primary keys for each database connection, as well as any user-defined reports. (For more information, see Section 1.10, "Reports".)

Icons under the Connections tab (above the metadata tree) perform the following actions on the currently selected object:

■ Refresh queries the database for the current details about the selected object (for

example, a connection or just a table).

■ Apply Filter restricts the display of objects using a filter that you specify. For

example, you can right-click the Tables node and specify a filter of EM% to see only tables that start with EM and to have the Tables node label be changed to Tab le s ( E M % ). To remove the effects of applying a filter, right-click the node and select Clear Filter.

The metadata tree in the Connections pane displays all the objects (categorized by object type) accessible to the defined connections. To select an object, expand the appropriate tree node or nodes, then click the object.

The right side of the SQL Developer window has tabs and panes for objects that you select or open, as shown in the following figure, which displays information about a table named BOOKS. (If you hold the mouse pointer over the tab label -- BOOKS in this figure -- a tooltip displays the object’s owner and the database connection.)

1-4 Oracle Database SQL Developer User’s Guide

SQL Developer User Interface

For objects other than subprograms, icons provide the following options:

■ Freeze View (the pin) keeps that object’s tab and information in the window when

you click another object in the Connections navigator; a separate tab and display are created for that other object. If you click the pin again, the object’s display is available for reuse.

■ Edit displays a dialog box for editing the object.

■ Refresh updates the display by querying the database for the latest information.

■ Actions displays a menu with actions appropriate for the object. The actions are

the same as when you right-click an object of that type in the Connections navigator, except the Actions menu does not include Edit.

To switch among objects, click the desired tabs; to close a tab, click the X in the tab. If you make changes to an object and click the X, you are asked if you want to save the changes.

For tables and views, this information is grouped under tabs, which are labeled near the top. For example, for tables the tabs are Columns, Data (for seeing and modifying the data itself), Indexes, Constraints, and so on; and you can click a column heading under a tab to sort the grid rows by the values in that column. For most objects, the tabs include SQL, which displays the SQL statement for creating the object.

You can export data from a detail pane or from the results of a SQL Worksheet operation or a report by using the right-click menu and selecting Export.

The Messages - Log area is used for feedback information as appropriate (for example, results of an action, or error or warning messages). If this area is not already visible, you can display is by clicking View and then Log.

The Compiler - Log area is used for any messages displayed as a result of a Compile or Compile for Debug operation.

1.2.1 Menus for SQL Developer

This topic explains menu items that are specific to SQL Developer.

View menu

Contains options that affect what is displayed in the SQL Developer interface. Options: New View creates a new tab on the left side showing the hierarchy for only

the selected connection; Freeze View keeps the tab and information in the window when you click another object in the Connections navigator; a separate tab and display are created for that other object.

SQL Developer Concepts and Usage 1-5

SQL Developer User Interface

Connection Navigator: Moves the focus to the Connections navigator. Log: Displays the Messages - Log pane, which can contain errors, warnings, and

informational messages. Debugger: Displays panes related to debugging (see Section 1.6, "Running and

Debugging Functions and Procedures").

Run Manager: Displays the Run Manager pane, which contains entries for any active debugging sessions.

Status Bar: Controls the display of the status bar at the bottom of the SQL Developer window.

Toolbars: Controls the display of the main toolbar (under the SQL Developer menus) and the Connections navigator toolbar.

Refresh: Updates the current display for any open connections using the current objects in the affected database or databases.

Snippet: Displays snippets (see Section 1.9, "Using Snippets to Insert Code

Fragments").

Report Navigator: Displays the Report Navigator (see Reports).

Navigate menu

Contains options for navigating to panes and in the execution of subprograms.

Back: Moves to the pane that you most recently visited. Forward: Moves to the pane after the current one in the list of visited panes. Go to Line: Goes to the specified line number and highlights the line in the editing

window for the selected function or procedure. Go to Last Edit: Goes to the last line that was edited in the editing window for a

function or procedure. Go to Recent Files: Displays the Recent Files dialog box, in which you can specify a

function or procedure to go to.

Run menu

Contains options relevant when a function or procedure is selected.

Run [name]: Starts execution of the specified function or procedure. Execution Profile: Displays the execution profile for the selected function or

procedure.

Debug menu

Contains options relevant when a function or procedure is selected. Debug [name]: Starts execution of the specified function or procedure in debug mode. The remaining items on the Debug menu match commands on the debugging toolbar,

which is described in Section 1.6, "Running and Debugging Functions and

Procedures".

Source menu

Contains options for use when editing functions and procedures. Completion Insight, Smart Completion Insight, and Parameter Insight: Display

pop-up windows that list item as you type and from which you can select an item for

1-6 Oracle Database SQL Developer User’s Guide

SQL Developer User Interface

autocompletion. See also the code insight and completion (autocomplete) options for

Code Editor under Section 1.11, "SQL Developer Preferences".

Toggle Line Comments: Inserts and removes comment indicators at the start of selected code lines.

Indent Block: Moves the selected statements to the right. Unindent Block: Moves the selected statements to the left.

Tools menu

Invokes SQL Developer tools. SQL*Plus: Displays a command-line window for entering SQL and SQL*Plus

statements (see Section 1.8, "Using SQL*Plus"). If the location of the SQL*Plus executable is not stored in your SQL Developer preferences, you are asked to specify its location.

External Tools: Displays the External Tools dialog box, with information about user-defined external tools that are integrated with the SQL Developer interface. From this dialog box can add external tools (see Section 3.39, "Create/Edit External Tool"). The Tools menu also contains items for any user-defined external tools.

Preferences: Enables you to customize the behavior of SQL Developer (see

Section 1.11, "SQL Developer Preferences").

Export DDL (and Data): Enables you to export some or all objects of one or more object types for a database connection to a file containing SQL statements to create these objects and optionally to export table data (see the Export (Selected Objects or

Types of Obj ects) dialog box).

Schema Diff: Enables you to compare two schemas to find differences between objects of the same type and name (for example, tables named CUSTOMERS) in two different schemas, and optionally to update the objects in the destination schema to reflect differences in the source schema (see the Schema Differences dialog box).

SQL Worksheet: Displays a worksheet in which you can enter and execute SQL and PL/SQL statements using a specified connection (see Section 1.7, "Using the SQL

Worksheet").

Help menu

Displays help about SQL Developer and enables you to check for SQL Developer updates.

Table of Contents: Displays the table of contents for the help. Full Text Search: Displays a pane for typing character strings or words to search the

help.

Index: Displays a pane for using index keywords to search the help. Check for Updates: Checks for any updates to the selected optional SQL Developer

extensions, as well as any mandatory SQL Developer extensions. (If the system you are using is behind a firewall, see the SQL Developer user preferences for Web Bro wser

and Proxy.)

About: Displays version-related information about SQL Developer and its components.

SQL Developer Concepts and Usage 1-7

Database Objects

1.3 Database Objects

You can create, edit, and delete (drop) most types of objects in an Oracle database by using the right-click menu in the Connections navigator or by clicking the Actions button in the detail pane display. For some objects, you can do other operations, as appropriate for the object type.

Note: The actions available from right-click menus and Actions

buttons depend on the Oracle Database release number for the specified database connection. If an action mentioned in the text is not available with a connection, it may be that the feature was not available in that release of Oracle Database.

If you have connected to any third-party (non-Oracle) databases, such as MySQL, Microsoft SQL Server, or Microsoft Access, you can view their objects using the Connections navigator. (For information about connecting to third-party databases, see the SQL Developer user preferences for Database: Third Party JDBC Drivers.)

Database: Third Party JDBC Drivers

1.3.1 Database Links (Public and Private)

A database link is a database object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system; however, to access non-Oracle systems you must use Oracle Heterogeneous Services. After you have created a database link, you can use it to refer to tables and views in the other database. The Connections navigator has a Database Links node for all database links (public and private) owned by the user associated with the specified connection, and a Public Database Links node for all public database links on the database associated with the connection. For help with specific options in creating a database link, see Section 3.12, "Create/Edit Database Link".

You can perform the following operations on a database link by right-clicking the database link name in the Connections navigator and selecting an item from the menu:

■ Te s t: Validates the database link.

■ Drop: Deletes the database link.

1.3.2 Directories

A directory object specifies an alias for a directory (called a folder on Windows systems) on the server file system where external binary file LOBs (BFILEs) and external table data are located. To create a directory (that is, a directory object), you can use SQL Developer or the SQL statement CREATE DIRECTORY.

You can use directory names when referring to BFILEs in your PL/SQL code and OCI calls, rather than hard coding the operating system path name, for management flexibility. All directories are created in a single namespace and are not owned by an individual schema. You can secure access to the BFILEs stored within the directory structure by granting object privileges on the directories to specific users.

1.3.3 Functions

A function is a type of PL/SQL subprogram, which is a programming object that can be stored and executed in the database server, and called from other programming objects or applications. (Functions return a value; procedures do not return a value.)

1-8 Oracle Database SQL Developer User’s Guide

1.3.4 Indexes

Database Objects

For help with specific options in creating a PL/SQL subprogram, see Section 3.16,

"Create PL/SQL Subprogram (Function or Procedure)".

You can perform the following operations on a function by right-clicking the function name in the Connections navigator and selecting an item from the menu:

■ Open: Displays the function text so that you can view and edit it.

■ Compile: Performs a PL/SQL compilation of the function.

■ Compile with Debug: Performs a PL/SQL compilation of the procedure, with

PL/SQL library units compiled for debugging.

■ Run: Displays the Run/Debug PL/SQL dialog box, and then executes the function

in normal (not debug) mode.

■ Debug: Displays the Run/Debug PL/SQL dialog box, and then executes the

function in debug mode.

■ Execution Profile: Displays the execution profile for the procedure.

■ Rename: Renames the function.

■ Drop: Deletes the function.

An index is a database object that contains an entry for each value that appears in the indexed column(s) of the table or cluster and provides direct, fast access to rows. Indexes are automatically created on primary key columns; however, you must create indexes on other columns to gain the benefits of indexing. For help with specific options in creating an index, see Section 3.13, "Create/Edit Index".

You can perform the following operations on an index by right-clicking the index name in the Connections navigator and selecting an item from the menu:

■ Drop: Deletes the index.

■ Rebuild Index: Re-creates the index or one of its partitions or subpartitions. If the

index is unusable, a successful rebuild operation makes the index usable. For a function-based index, rebuilding also enables the index; however, if the function on which the index is based does not exist, the rebuild operation fails.

■ Rename Index: Changes the name of the index.

■ Unusable Index: Prevents the index from being used by Oracle in executing

queries. An unusable index must be rebuilt, or dropped and re-created, before it can be used again.

■ Coalesce Index: Merges the contents of index blocks, where possible, to free blocks

for reuse.

1.3.5 Materialized Views

A materialized view is a database object that contains the results of a query. The FROM clause of the query can name tables, views, and other materialized views. Collectively these objects are called master tables (a replication term) or detail tables (a data warehousing term). This reference uses "master tables" for consistency. The databases containing the master tables are called the master databases. For help with specific options in creating a materialized view, see Section 3.27, "Create/Edit View", especially the View Information or Materialized View Properties pane.

SQL Developer Concepts and Usage 1-9

Database Objects

1.3.6 Materialized View Logs

A materialized view log is a table associated with the master table of a materialized view. When DML changes are made to master table data, Oracle Database stores rows describing those changes in the materialized view log and then uses the materialized view log to refresh materialized views based on the master table. This process is called incremental or fast refresh. Without a materialized view log, Oracle Database must reexecute the materialized view query to refresh the materialized view. This process is called a complete refresh. Usually, a fast refresh takes less time than a complete refresh.

1.3.7 Packages

A package is an object that contains subprograms, which are programming objects that can be stored and executed in the database server, and called from other programming objects or applications. A package can contain functions or procedures, or both. For help with specific options in creating a package, see Section 3.15, "Create PL/SQL

Package".

You can perform the following operations on a package by right-clicking the package name in the Connections navigator and selecting an item from the menu:

■ New Package Body: Displays a pane in which you can enter text for the package

body.

■ Drop: Deletes the package.

1.3.8 Procedures

A procedure is a type of PL/SQL subprogram, which is a programming object that can be stored and executed in the database server, and called from other programming objects or applications. (Procedures do not return a value; functions return a value.) For help with specific options in creating a PL/SQL subprogram, see Section 3.16,

"Create PL/SQL Subprogram (Function or Procedure)".

You can perform the following operations on a procedure by right-clicking the procedure name in the Connections navigator and selecting an item from the menu:

■ Open: Displays the procedure text so that you can view and edit it.

■ Compile: Performs a PL/SQL compilation of the procedure.

■ Compile with Debug: Performs a PL/SQL compilation of the procedure, with

■ Run: Displays the Run/Debug PL/SQL dialog box, and then executes the

■ Debug: Displays the Run/Debug PL/SQL dialog box, and then executes the

■ Execution Profile: Displays the execution profile for the procedure.

■ Drop: Deletes the procedure.

PL/SQL library units compiled for debugging.

procedure in normal (not debug) mode.

procedure in debug mode.

■ Compile Dependants: Performs a PL/SQL compilation of the procedure and any

relevant dependent subprograms (see the Dependencies tab).

1.3.9 Recycle Bin

The Recycle bin (applicable only to Oracle Database Release 10g) holds objects that have been dropped (deleted). The objects are not actually deleted until a commit operation is performed. Before the objects are actually deleted, you can "undelete"

1-10 Oracle Database SQL Developer User’s Guide

them by selecting them in the Recycle bin and selecting Undrop from the right-click menu.

You can perform the following operations on an object in the Recycle bin by right-clicking the object name in the Recycle bin in the Connections navigator and selecting an item from the menu:

■ Purge: Removes the object from the Recycle bin and deletes it.

■ Flashback to Before Drop: Moves the object from the Recycle bin back to its

appropriate place in the Connections navigator display.

1.3.10 Sequences

Sequences are used to generate unique integers. You can use sequences to automatically generate primary key values. For help with specific options in creating and editing a sequence, see Section 3.17, "Create/Edit Sequence".

1.3.11 Synonyms (Public and Private)

Synonyms provide alternative names for tables, views, sequences, procedures, stored functions, packages, materialized views, Java class database objects, user-defined object types, or other synonyms. The Connections navigator has a Synonyms node for all synonyms (public and private) owned by the user associated with the specified connection, and a Public Synonyms node for all public synonyms on the database associated with the connection. For help with specific options in creating and editing a synonym, see Section 3.19, "Create/Edit Synonym".

Database Objects

1.3.12 Tables

Tables are used to hold data. Each table typically has multiple columns that describe attributes of the database entity associated with the table, and each column has an associated data type. You can choose from many table creation options and table organizations (such as partitioned tables, index-organized tables, and external tables), to meet a variety of enterprise needs. To create a table, you can do either of the following:

■ Create the table quickly by adding columns and specifying frequently used

features. To do this, do not check the Advanced box in the Create Table dialog box. For help with options for creating a table using this quick approach, see Create

Table (quick creation).

■ Create the table by adding columns and selecting from a larger set of features. To

do this, check the Advanced box in the Create Table dialog box. For help with options for creating a table with advanced features, see Create/Edit Table (with

advanced options).

You can perform the following operations on a table by right-clicking the table name in the Connections navigator and selecting an item from the menu:

■ Edit: Displays the Create Table (quick creation) dialog box.

■ Ta b l e: Table actions include Rename, Copy (create a copy using a different name),

Drop (delete the table), Truncate (delete existing data without affecting the table definition), Lock (set the table lock mode: row share, exclusive, and so on), Comment (descriptive comment explaining the use or purpose of the table), Parallel (change the default degree of parallelism for queries and DML on the table), No Parallel (specify serial execution), and Count Rows (return the number of rows).

SQL Developer Concepts and Usage 1-11

Database Objects

■ Export: Enables you to export some or all of the table data to a file or to the system

clipboard, in any of the following formats: XML (XML tags and data), CSV (comma-separated values including a header row for column identifiers), SQL Insert (INSERT statements), or SQL Loader (SQL*Loader control file). After you select a format, the Export Table Data dialog box is displayed.

■ Column: Column actions include Comment (descriptive comment about a

column), Add, Drop, and Normalize.

■ Index: Options include Create (create an index on specified columns), Create Text

(create an Oracle Text index on a column), Create Text (create a function-based index on a column), and Drop.

■ Storage: Options include Shrink Table (shrink space in a table, for segments in

tablespaces with automatic segment management) and Move Table (to another tablespace). The Shrink Table options include Compact (only defragments the segment space and compacts the table rows for subsequent release, but does not readjust the high water mark and does not release the space immediately) and Cascade (performs the same operations on all dependent objects of the table, including secondary indexes on index-organized tables).

■ Analyze: Options include Compute Statistics (compute exact table and column

statistics and store them in the data dictionary), Estimate statistics (estimate table and column statistics and store them in the data dictionary), and Validate Structure (verifies the integrity of each data block and row, and for an index-organized table also generates the optimal prefix compression count for the primary key index on the table). Both computed and estimated statistics are used by the Oracle Database optimizer to choose the execution plan for SQL statements that access analyzed objects.

1.3.13 Triggers

■ Constraint: Options include Enable or Disable Single, Drop (delete a constraint),

Add Check (add a check constraint), Add Foreign Key, and Add Unique.

■ Privileges: If you are connected as a database user with sufficient privileges, you

can Grant or Revoke privileges on the table to other users.

■ Tr i gg e r: Options include Create, Create PK from Sequence (create a before-insert

trigger to populate the primary key using values from a specified sequence), Enable or Disable All, Enable or Disable Single, and Drop (delete the trigger).

You can perform the following operations on a column in a table by right-clicking the column name in the Connections navigator and selecting an item from the menu:

■ Rename: Renames the column.

■ Drop: Deletes the column (including all data in that column) from the table.

■ Encrypt (for Oracle Database Release 10.2 and higher, and only if the Transparent

Data Encryption feature is enabled for the database): Displays a dialog box in which you specify a supported encryption algorithm to be used for encrypting all data in the column. Current data and subsequently inserted data are encrypted.

■ Decrypt (for Oracle Database Release 10.2 and higher, and only if the Transparent

Data Encryption feature is enabled for the database): Decrypts data in the column that had been encrypted, and causes data that is subsequently inserted not to be encrypted.

Triggers are stored PL/SQL blocks associated with a table, a schema, or the database, or anonymous PL/SQL blocks or calls to a procedure implemented in PL/SQL or Java.

1-12 Oracle Database SQL Developer User’s Guide

Oracle Database automatically executes a trigger when specified conditions occur. For help with specific options in creating a trigger, see Section 3.22, "Create Trigger".

1.3.14 Types

A data type associates a fixed set of properties with the values that can be used in a column of a table or in an argument of a procedure or function. These properties cause Oracle Database to treat values of one data type differently from values of another data type. Most data types are supplied by Oracle, although users can create data types.

For help with specific options in creating a user-defined type, see Section 3.23, "Create

Type ( User-Defined)".

1.3.15 Users (Other Users)

Database users are accounts through which you can log in to the database. In the Connections navigator, you can see the Other Users in the database associated with a connection, but the database objects that you are allowed to see for each user are determined by the privileges of the database user associated with the current database connection.

If you are connected as a user with the DBA role, you can create a database user by right-clicking Other Users and selecting Create User, and you can edit an existing database user by right-clicking the user under Other Users and selecting Edit User. For help on options in creating and editing users, see Create/Edit User.

Database Objects

1.3.16 Views

Views are virtual tables (analogous to queries in some database products) that select data from one or more underlying tables. Oracle Database provides many view creation options and specialized types of views (such as materialized views, described in Section 1.3.5, "Materialized Views"), to meet a variety of enterprise needs. For help with specific options in creating and editing a view, see Create/Edit View.

You can perform the following operations on a view by right-clicking the view name in the Connections navigator and selecting an item from the menu:

■ Edit: Displays the Create/Edit View dialog box.

■ Drop: Deletes the view.

■ Compile: Recompiles the view, to enable you to locate possible errors before run

time. You may want to recompile a view after altering one of its base tables to ensure that the change does not affect the view or other objects that depend on it.

1.3.17 XML Schemas

XML schemas are schema definitions, written in XML, that describe the structure and various other semantics of conforming instance XML documents. For conceptual and usage information about XML schemas, see Oracle XML DB Developer's Guide in the Oracle Database documentation library.

You can edit an XML schema by right-clicking the XML schema name in the Connections navigator and selecting Edit from the menu.

SQL Developer Concepts and Usage 1-13

Database Connections

1.4 Database Connections

A connection is a SQL Developer object that specifies the necessary information for connecting to a specific database as a specific user of that database. You must have at least one database connection (existing, created, or imported) to use SQL Developer.

You can connect to any target Oracle database schema using standard Oracle database authentication. Once connected, you can perform operations on objects in the database. You can also connect to schemas for selected third-party (non-Oracle) databases, such as MySQL, Microsoft SQL Server, and Microsoft Access, and view metadata and data.

When you start SQL Developer and whenever you display the database connections dialog box, SQL Developer automatically imports any connections defined in the tnsnames.ora file on your system, if that file exists. By default, tnsnames.ora is located in the $ORACLE_HOME/network/admin directory, but it can also be in the directory specified by the TNS_ADMIN environment variable or registry value or (on Linux systems) the global configuration directory. On Windows systems, if the tnsnames.ora file exists but its connections are not being used by SQL Developer, define TNS_ ADMIN as a system environment variable. For information about the tnsnames.ora file, see the "Local Naming Parameters (tnsnames.ora)" chapter in Oracle Database Net Services Reference.

You can create additional connections (for example, to connect to the same database but as different users, or to connect to different databases). Each database connection is listed in the Connections navigator hierarchy.

To create a new database connection, right-click the Connections node and select New Database Connection. Use the dialog box to specify information about the connection (see Section 3.5, "Create/Edit/Select Database Connection").

To edit the information about an existing database connection, right-click the connection name in the Connections navigator display and select Properties. Use the dialog box to modify information about the connection (see Section 3.5,

"Create/Edit/Select Database Connection").

To export information about the existing database connections into an XML file that you can later use for importing connections, right-click Connections in the Connections navigator display and select Export Connections. Use the dialog box to specify the connections to be exported (see Section 3.11, "Export/Import Connection

Descriptors").

To import connections that had previously been exported (adding them to any connections that may already exist in SQL Developer), right-click Connections in the Connections navigator display and select Import Connections. Use the dialog box to specify the connections to be imported (see Section 3.11, "Export/Import Connection

Descriptors").

To perform remote debugging if you are using the Sun Microsystem's Java Platform Debugger Architecture (JPDA) and you would like the debugger to listen so that a debuggee can attach to the debugger, right-click the connection name in the Connections navigator display and select Remote Debug. Use the dialog box to specify remote debugging information (see Section 3.31, "Debugger - Attach to JPDA").

To delete a connection (that is, delete it from SQL Developer, not merely disconnect from the current connection), right-click the connection name in the Connections navigator display and select Delete. Deleting a connection does not delete the user associated with that connection.

1-14 Oracle Database SQL Developer User’s Guide

Entering and Modifying Data

To connect using an existing connection, expand its node in the Connections navigator, or right-click its name and select Connect. A SQL Worksheet window is also opened for the connection (see Section 1.7, "Using the SQL Worksheet").

To disconnect from the current connection, right-click its name in the Connections navigator and select Disconnect.

Sharing of Connections

By default, each connection in SQL Developer is shared when possible. For example, if you open a table in the Connections navigator and two SQL Worksheets using the same connection, all three panes use one shared connection to the database. In this example, a commit operation in one SQL Worksheet commits across all three panes. If you want a dedicated session, you must duplicate your connection and give it another name. Sessions are shared by name, not connection information, so this new connection will be kept separate from the original.

Advanced Security for JDBC Connection to the Database

You are encouraged to use Oracle Advanced Security to secure a JDBC connection to the database. Both the JDBC OCI and the JDBC Thin drivers support at least some of the Oracle Advanced Security features. If you are using the OCI driver, you can set relevant parameters in the same way that you would in any Oracle client setting. The JDBC Thin driver supports the Oracle Advanced Security features through a set of Java classes included with the JDBC classes in a Java Archive (JAR) file and supports security parameter settings through Java properties objects.

1.5 Entering and Modifying Data

You can use SQL Developer to enter data into tables and to edit and delete existing table data. To do any of these operations, select the table in the Connections navigator, then click the Data tab in the table detail display. The following figure shows the Data pane for a table named BOOKS, with a filter applied to show only books whose rating is 10, and after the user has clicked in the Title cell for the first book.

Icons and other controls under the Data tab provide the following options:

■ Freeze View (the pin) keeps that object’s tab and information in the window when

you click another object in the Connections navigator; a separate tab and display are created for that other object. If you click the pin again, the object’s display is available for reuse.

■ Refresh queries the database to update the data display. If a filter is specified, the

refresh operation uses the filter.

■ Insert Row adds an empty row after the selected row, for you to enter new data.

■ Delete Selected Row(s) marks the selected rows for deletion. The actual deletion

does not occur until you commit changes.

SQL Developer Concepts and Usage 1-15

Running and Debugging Functions and Procedures

■ Commit Changes ends the current transaction and makes permanent all changes

performed in the transaction.

■ Rollback Changes undoes any work done in the current transaction.

■ Sort displays a dialog box for selecting columns to sort by. For each column, you

can specify ascending or descending order, and you can specify that null values be displayed first.

■ Filter enables you to enter a SQL predicate (WHERE clause text without the

WHERE keyword) for limiting the display of data. For example, to show only rows where the RATING column value is equal to 10, specify: rating = 10

■ Actions displays a menu with actions relevant to the table.

When you enter a cell in the grid, you can directly edit the data for many data types, and for all data types you can click the ellipsis (...) button to edit the data. For binary data you cannot edit the data in the cell, but must use the ellipsis button.

In the Data pane for a table or view, you can split the display vertically or horizontally to see two (or more) parts independently by using the split box (thin blue rectangle), located to the right of the bottom scroll bar and above the right scroll bar.

In the Data pane, the acceptable format or formats for entering dates may be different from the date format required by SQL*Plus.

1.6 Running and Debugging Functions and Procedures

You can use SQL Developer to run and debug PL/SQL functions and procedures.

■ To run a function or procedure, click its name in the Connections navigator; then

either right-click and select Run, or click the Edit icon and then click the Run icon above its source listing.

■ To debug a function or procedure, click its name in the Connections navigator. If

the procedure in its current form has not already been compiled for debug, right-click and select Compile for Debug. Then click the Edit icon and click the Debug icon above its source listing.

In both cases, a code editing window is displayed. The following figure shows the code editing window being used to debug a procedure named LIST_A_RATING2, which is used for tutorial purposes in Section 2.7, "Debugging a PL/SQL Procedure".

1-16 Oracle Database SQL Developer User’s Guide

Running and Debugging Functions and Procedures

The code editing window has the following tabs:

■ The Source tab displays a toolbar and the text of the function or procedure, which

you can edit. You can set and unset breakpoints for debugging by clicking to the left of the thin vertical line beside each statement with which you want to associate a breakpoint. (When a breakpoint is set, a red circle is displayed.)

■ The Privileges tab displays, for each privilege associated with the function or

procedure, the grantor and grantee, the object name, and whether the grantee can grant the privilege to other users.

■ The Dependencies tab shows any objects that this function or procedure

references, and any objects that reference this function or procedure.

The Source tab toolbar has the icons shown in the following figure.

■ Run starts normal execution of the function or procedure, and displays the results

in the Running - Log tab.

■ Debug starts execution of the function or procedure in debug mode, and displays

the Debugging - Log tab, which includes the debugging toolbar for controlling the execution.

■ Compile performs a PL/SQL compilation of the function or procedure.

■ Compile for Debug performs a PL/SQL compilation of the function or procedure

so that it can be debugged.

SQL Developer Concepts and Usage 1-17

Running and Debugging Functions and Procedures

The Debugging - Log tab under the code text area contains the debugging toolbar and informational messages. The debugging toolbar has the icons shown in the following figure.

■ Find Execution Point goes to the next execution point.

■ Resume continues execution.

■ Step Over bypasses the next method and goes to the next statement after the

method.

■ Step Into goes to the first statement in the next method.

■ Step Out leaves the current method and goes to the next statement.

■ Step to End of Method goes to the last statement of the current method.

■ Pause halts execution but does not exit, thus allowing you to resume execution.

■ Terminate halts and exits the execution. You cannot resume execution from this

point; instead, to start running or debugging from the beginning of the function or procedure, click the Run or Debug icon in the Source tab toolbar.

■ Garbage Collection removes invalid objects from the cache in favor of more

frequently accessed and more valid objects.

The Breakpoints tab under the code text area displays breakpoints, both system-defined and user-defined.

The Smart Data tab under the code text area displays information about variables associated with breakpoints.

The Data tab under the code text area displays information about all variables. The Wat ches tab under the code text area displays information about watchpoints. If the function or procedure to be debugged is on a remote system, see also

Section 1.6.1, "Remote Debugging".

1.6.1 Remote Debugging

To debug a procedure or function for a connection where the database is on a different host than the one on which you are running SQL Developer, you can perform remote debugging. Remote debugging involves many of the steps as for local debugging; however, do the following before you start the remote debugging:

1. Use an Oracle client such as SQL*Plus to issue the debugger connection command.

Whatever client you use, make sure that the session which issues the debugger connection commands is the same session which executes your PL/SQL program containing the breakpoints. For example, if the name of the remote system is remote1, use the following SQL*Plus command to open a TCP/IP connection to that system and the port for the JDWP session:

EXEC DBMS_DEBUG_JDWP.CONNECT_TCP('remote1', '4000');

The first parameter is the IP address or host name of the remote system, and the second parameter is the port number on that remote system on which the debugger is listening.

2. Right-click the connection for the remote database, select Remote Debug, and

complete the information in the Debugger - Attach to JPDA dialog box.

1-18 Oracle Database SQL Developer User’s Guide

Then, follow the steps that you would for local debugging (for example, see

Section 2.7, "Debugging a PL/SQL Procedure").

1.7 Using the SQL Worksheet

You can use the SQL Worksheet to enter and execute SQL, PL/SQL, and SQL*Plus statements. You can specify any actions that can be processed by the database connection associated with the worksheet, such as creating a table, inserting data, creating and editing a trigger, selecting data from a table, and saving that data to a file.

You can display a SQL Worksheet by right-clicking a connection in the Connections navigator and selecting Open SQL Worksheet, by selecting Too l s and then SQL Worksheet, or by clicking the Use SQL Worksheet icon under the menu bar. In the Select Connection dialog box, select the database connection to use for your work with the worksheet. You can also use that dialog box to create and edit database connections. (You have a SQL Worksheet window open automatically when you open a database connection by enabling the appropriate SQL Developer user preference under Database Connections.)

The SQL Worksheet has the user interface shown in the following figure:

Using the SQL Worksheet

SQL Worksheet toolbar (under the SQL Worksheet tab): Contains icons for the following operations:

■ Execute Statement executes the statement at the mouse pointer in the Enter SQL

Statement box. The SQL statements can include bind variables and substitution variables of type VARCHAR2 (although in most cases, VARCHAR2 is automatically converted internally to NUMBER if necessary); a pop-up box is displayed for entering variable values.

■ Run Script executes all statements in the Enter SQL Statement box using the Script

Runner. The SQL statements can include substitution variables (but not bind

SQL Developer Concepts and Usage 1-19

Using the SQL Worksheet

■ Commit writes any changes to the database, and ends the transaction; also clears

■ Rollback discards any changes without writing them to the database, and ends

■ Cancel stops the execution of any statements currently being executed.

■ SQL History displays a dialog box with information about SQL statements that

■ Execute Explain Plan generates the execution plan for the statement (internally

■ Autotrace generates trace information for the statement. To see the execution plan,

■ Clear erases the statement or statements in the Enter SQL Statement box.

■ To the right of these icons is a drop-down list for changing the database

variables) of type VARCHAR2 (although in most cases, VARCHAR2 is automatically converted internally to NUMBER if necessary); a pop-up box is displayed for entering substitution variable values.

any output in the Results and Script Output panes.

the transaction; also clears any output in the Results and Script Output panes.

you have executed. You can save statements to a file, or append to or overwrite statements on the worksheet (see Section 3.65, "SQL History List").

executing the EXPLAIN PLAN statement). To see the execution plan, click the Explain tab. For more information, see Section 1.7.3, "Execution Plan".

click the Autotrace tab. For more information, see Section 1.7.3, "Execution Plan".

connection to use with the worksheet.

The right-click menu includes the preceding SQL Worksheet toolbar operations, plus the following operations:

■ Open File opens a selected SQL script file in the Enter SQL Statement box.

■ Save File saves the contents of the Enter SQL Statement box to a file.

■ Print File prints the contents of the Enter SQL Statement box.

■ Cut, Copy, Paste, and Select All have the same meanings as for normal text

editing operations.

■ Query Builder opens the Query Builder dialog box, where you can create a

SELECT statement by dragging and dropping table and view names and by graphically specifying columns and other elements of the query.

■ Format SQL formats the SQL statement (capitalizing the names of statements,

clauses, keywords, and so on).

■ Describe, if the name of a database object is completely selected, displays a

window with tabs and information appropriate for that type of object (see

Section 3.32, "Describe Object Window").

■ Save Snippet opens the Save Snippet (User-Defined) dialog box with the selected

text as the snippet text.

Enter SQL Statement: The statement or statements that you intend to execute. For multiple statements, each non-PL/SQL statement must be terminated with either a semicolon or (on a new line) a slash (/), and each PL/SQL statement must be terminated with a slash (/) on a new line. SQL keywords are automatically highlighted. To format the statement, right-click in the statement area and select Format SQL.

You can drag some kinds of objects from the Connections navigator and drop them into the Enter SQL Statement box:

1-20 Oracle Database SQL Developer User’s Guide

+ 82 hidden pages

Oracle Database User Manual

Specifications and Main Features

Frequently Asked Questions

User Manual

Contents

Audience

Documentation Accessibility

Preface

Related Documents

Conventions

Third-Party License Information

SQL Developer Concepts and Usage

1.1 Installing and Getting Started with SQL Developer

1.2 SQL Developer User Interface

1.2.1 Menus for SQL Developer

1.3 Database Objects

1.3.1 Database Links (Public and Private)

1.3.2 Directories

1.3.3 Functions

1.3.4 Indexes

1.3.5 Materialized Views

1.3.6 Materialized View Logs

1.3.7 Packages

1.3.8 Procedures

1.3.9 Recycle Bin

1.3.10 Sequences

1.3.11 Synonyms (Public and Private)

1.3.12 Tables

1.3.13 Triggers

1.3.14 Types

1.3.15 Users (Other Users)

1.3.16 Views

1.3.17 XML Schemas

1.4 Database Connections

1.5 Entering and Modifying Data

1.6 Running and Debugging Functions and Procedures

1.6.1 Remote Debugging

1.7 Using the SQL Worksheet