Skip Headers
Oracle® Database SQL Developer User's Guide
Release 1.1

Part Number B31695-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

Beta Draft

1 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, "Using DB Object Search to Find Database Objects"

Section 1.11, "Reports"

Section 1.12, "SQL Developer Preferences"

Section 1.13, "Location of User-Related Information"

Section 1.14, "Using the Help"

Section 1.15, "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:

  1. 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.

Figure 1-1 SQL Developer Main Window

Main window of the 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.12).

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.

Top of the window: menus and icons

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:

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.

Left side of window: navigation

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.11, "Reports".)

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

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.)

Right side of the window: details about selected objects

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

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.

Connections: 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.

Reports: Displays the Reports navigator (see Section 1.11, "Reports").

DB Object Search: Displays the DB Object Search pane (see Section 1.10, "Using DB Object Search to Find Database Objects").

Snippets: Displays snippets (see Section 1.9, "Using Snippets to Insert Code Fragments").

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 autocompletion. See also the code insight and completion (autocomplete) options for Code Editor under Section 1.12, "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.38, "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.12, "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 Objects) 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 Browser and Proxy.)

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

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.

You can search for specific objects associated with an Oracle database connection by clicking View, then DB Object Search. For more information, see Section 1.10, "Using DB Object Search to Find Database Objects".

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.)

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:

  • Test: 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.) 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.

1.3.4 Indexes

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.26, "Create/Edit View", especially the View Information or Materialized View Properties pane.

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 PL/SQL library units compiled for debugging.

  • Run: Displays the Run/Debug PL/SQL dialog box, and then executes the procedure in normal (not debug) mode.

  • Debug: Displays the Run/Debug PL/SQL dialog box, and then executes the procedure in debug mode.

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

  • Drop: Deletes the procedure.

  • 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" 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. Managing SequencesFor 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. Managing SynonymsFor help with specific options in creating and editing a synonym, see Section 3.18, "Create/Edit Synonym".

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.

  • Table: 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).

  • 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.

  • 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.

  • Trigger: 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.

1.3.13 Triggers

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. Oracle Database automatically executes a trigger when specified conditions occur. Triggers: Usage InformationFor help with specific options in creating a trigger, see Section 3.21, "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.

Data Types: Usage InformationFor help with specific options in creating a user-defined type, see Section 3.22, "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.

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. Managing ViewsFor 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.

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.30, "Debugger - Attach to JPDA").

To estimate or compute statistics for objects in a database schema, right-click the connection name in the Connections navigator display and select Gather Schema Statistics. Statistics are used to optimize SQL execution.

To have the PL/Scope tool collect data for all identifiers in PL/SQL source programs, including identifiers in package bodies, right-click the connection name in the Connections navigator display and select Toggle PL/Scope Identifier Collection. This toggles the setting between IDENTIFIERS:ALL (affects only the PL/SQL code compiled after you specify it) and IDENTIFIERS:NONE. PL/Scope is a compiler-driven tool that collects and organizes data about user-defined identifiers from PL/SQL source code. For information about using PL/Scope, see Oracle Database PL/SQL Language Reference.

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.

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.

Interface for entering and modifying data

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

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.

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".

Interface for debugging a PL/SQL subprogram

The code editing window has the following tabs:

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

Source tab toolbar icons

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.

Debugging toolbar icons

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 Watches 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.

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 Tools 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:

SQL Worksheet interface

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

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

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:

Tabs display panes with the following information:

1.7.1 SQL*Plus Statements Supported and Not Supported in SQL Worksheet

The SQL Worksheet supports some SQL*Plus statements. SQL*Plus statements must be interpreted by the SQL Worksheet before being passed to the database; any SQL*Plus that are not supported by the SQL Worksheet are ignored and not passed to the database.

The following SQL*Plus statements are supported by the SQL Worksheet:

@
@@
acc[ept]
conn[ect]
cl[ear]
def[ine]
desc[ribe]
doc[ument]
exec[ute]
exit (Stops execution and reinstates the specified connection)
ho[st]
pau[se]
pro[mpt]
quit (Stops execution and reinstates the specified connection)
rem[ark]
set pau[se] {ON | OFF}
sta[rt]
timi[ng]
undef[ine]
whenever
xquery

The following SQL*Plus statements are not supported by the SQL Worksheet:

a[ppend]
archive
attr[ibute]
bre[ak]
bti[tle]
c[hange]
col[ulmn]
comp[ute]
copy
del
disc[onnect]
ed[it]
get
help
i[nput]
l[ist]
newpage
oradebug
passw[ord]
print
r[un]
recover
repf[ooter]
reph[eader]
sav[e]
sho[w]
shu[tdown]
spo[ol]
startup
store
tti[tle]
var[iable]

1.7.2 Script Runner

The script runner emulates a limited set of SQL*Plus features. If you do not have SQL*Plus on your system, you can often enter SQL and SQL*Plus statements and execute them by clicking the Run Script icon. The Script Output pane displays the output.

The SQL*Plus features available in the script runner include @, @@, CONNECT, EXIT, QUIT, UNDEFINE, WHENEVER, and substitution variables. For example, to run a script named c:\myscripts\mytest.sql, type @c:\myscripts\mytest in the Enter SQL Statement box, and click the drop-down next to the Execute Statement icon and select Run Script.

The following considerations apply to using the SQL Developer script runner:

  • You cannot use bind variables. (The Execute SQL Statement feature does let you use bind variables of type VARCHAR2, NUMBER, and DATE.)

  • For substitution variables, the syntax &&variable assigns a permanent variable value, and the syntax &variable assigns a temporary (not stored) variable value.

  • For EXIT and QUIT, commit is the default behavior, but you can specify rollback. In either case, the context is reset: for example, WHENEVER command information and substitution variable values are cleared.

  • DESCRIBE works for most, but not all, object types for which it is supported in SQL*Plus.

  • For SQL*Plus commands that are not supported, a warning message is displayed.

  • SQL*Plus comments are ignored.

If you have SQL*Plus available on your system, you may want to use it instead of the script runner. To start SQL*Plus from SQL Developer, click Tools and then SQL*Plus. For information about SQL*Plus, see Section 1.8, "Using SQL*Plus".

1.7.3 Execution Plan

The Execute Explain Plan icon generates the execution plan, which you can see by clicking the Explain tab. The execution plan is the sequence of operations that will be performed to execute the statement. An execution plan shows a row source tree with the hierarchy of operations that make up the statement. For each operation, it shows the ordering of the tables referenced by the statement, access method for each table mentioned in the statement, join method for tables affected by join operations in the statement, and data operations such as filter, sort, or aggregation.

In addition to the row source tree, the plan table displays information about optimization (such as the cost and cardinality of each operation), partitioning (such as the set of accessed partitions), and parallel execution (such as the distribution method of join inputs). For more information, see the chapter about using EXPLAIN PLAN in Oracle Database Performance Tuning Guide.

1.7.4 Autotrace Pane

The Autotrace pane displays trace-related information when you execute the SQL statement by clicking the Autotrace icon. Most of the specific information displayed is determined by the SQL Developer Preferences for Database: Autotrace Parameters. If you cancel a long-running statement, partial execution statistics are displayed.

This information can help you to identify SQL statements that will benefit from tuning. For example, you may be able to optimize predicate handling by transitively adding predicates, rewriting predicates using Boolean algebra principles, moving predicates around in the execution plan, and so on. For more information about tracing and autotrace, see the chapter about tuning in SQL*Plus User's Guide and Reference.

To use the autotrace feature, the database user for the connection must have the SELECT_CATALOG_ROLE privilege.

1.7.5 DBMS Output Pane

The PL/SQL DBMS_OUTPUT package enables you to send messages from stored procedures, packages, and triggers. The PUT and PUT_LINE procedures in this package enable you to place information in a buffer that can be read by another trigger, procedure, or package. In a separate PL/SQL procedure or anonymous block, you can display the buffered information by calling the GET_LINE procedure. The DBMS Output pane is used to display the output of that buffer. This pane contains icons and other controls for the following operations:

  • Enable/Disable DBMS Output: Toggles the SET SERVEROUTPUT setting between ON and OFF. Setting server output ON checks for any output that is placed in the DBMS_OUTPUT buffer, and any output is displayed in the pane.

  • Clear: Erases the contents of the pane.

  • Save: Saves the contents of the pane to a file that you specify.

  • Print: Prints the contents of the pane.

  • Buffer Size: For databases before Oracle Database 10.2, limits the amount of data that can be stored in the DBMS_OUTPUT buffer. The buffer size can be between 1 and 1000000 (1 million).

  • Poll: The interval (in seconds) at which SQL Developer checks the DBMS_OUTPUT buffer to see if there is data to print. The poll rate can be between 1 and 15.

1.7.6 OWA Output Pane

OWA (Oracle Web Agent) or MOD_PLSQL is an Apache (Web Server) extension module that enables you to create dynamic Web pages from PL/SQL packages and stored procedures. The OWA Output pane enables you to see the HTML output of MOD_PLSQL actions that have been executed in the SQL Worksheet. This pane contains icons for the following operations:

  • Enable/Disable OWA Output: Enables and disables the checking of the OWA output buffer and the display of OWA output to the pane.

  • Clear: Erases the contents of the pane.

  • Save: Saves the contents of the pane to a file that you specify.

  • Print: Prints the contents of the pane.

1.8 Using SQL*Plus

You can use the SQL*Plus command-line interface to enter SQL and PL/SQL statements accessing the database associated with a specified connection. To display the SQL*Plus command window, from the Tools menu, select SQL*Plus.

To use this feature, the system on which you are using SQL Developer must have an Oracle home directory or folder, with a SQL*Plus executable under that location. If the location of the SQL*Plus executable is not already stored in your SQL Developer preferences, you are asked to specify its location (see Section 3.65, "SQL*Plus Location").

If you do not have a SQL*Plus executable on your system, you can execute some SQL*Plus statements using the SQL Worksheet (see Section 1.7.1, "SQL*Plus Statements Supported and Not Supported in SQL Worksheet"), and you can also use the SQL Developer script runner feature to emulate a limited set of SQL*Plus features (see Section 1.7.2, "Script Runner").

1.9 Using Snippets to Insert Code Fragments

Snippets are code fragments, such as SQL functions, Optimizer hints, and miscellaneous PL/SQL programming techniques. Some snippets are just syntax, and others are examples. You can insert and edit snippets when you are using the SQL Worksheet or creating or editing a PL/SQL function or procedure.

To display snippets, from the View menu, select Snippets. In the snippets window (on the right side), use the drop-down to select a group (such as Aggregate Functions or Character Functions). In most cases, the fragments in each group do not represent all available objects in that logical grouping, or all formats and options of each fragment shown. For complete and detailed information, see the Oracle Database documentation.

A Snippets button is placed in the right window margin, so that you can display the snippets window if it becomes hidden.

To insert a snippet into your code in a SQL Worksheet or in a PL/SQL function or procedure, drag the snippet from the snippets window and drop it into the desired place in your code; then edit the syntax so that the SQL function is valid in the current context. To see a brief description of a SQL function in a tooltip, hold the pointer over the function name.

For example, you could type SELECT and then drag CONCAT(char1, char2) from the Character Functions group. Then, edit the CONCAT function syntax and type the rest of the statement, such as in the following:

SELECT CONCAT(title, ' is a book in the library.') FROM books;

1.9.1 User-Defined Snippets

You can create and edit snippets. User-defined snippets are intended mainly to enable you to supplement the Oracle-supplied snippets, although you are also permitted to replace an Oracle-supplied snippet with your own version.

When you create a user-defined snippet, you can add it to one of the Oracle-supplied snippet categories (such as Aggregate Functions) or to a category that you create. If you add a snippet to an Oracle-supplied category and if your snippet has the same name as an existing snippet, your snippet definition replaces the existing one. (If you later upgrade to a new version of SQL Developer and if you choose to preserve your old settings, your old user-defined snippets will replace any Oracle-supplied snippets of the same name in the new version of SQL Developer.)

To create a snippet, do any of the following:

  • Open the Snippets window and click the Add User Snippets icon.

  • Select text for the snippet in the SQL Worksheet window, right-click, and select Save Snippet.

  • Click the Add User Snippet icon in the Edit Snippets (User-Defined) dialog box.

To edit an existing user-defined snippet, click the Edit User Snippets icon in the Snippets window.

Information about user-defined snippets is stored in a file named UserSnippets.xml under the directory for user-specific information. For information about the location of this information, see Section 1.13, "Location of User-Related Information".

1.10 Using DB Object Search to Find Database Objects

You can use the DB Object Search pane to find database objects associated with an Oracle database connection and to open editing panes to work with those objects. To move to the Db Object Search pane or to display it if it is not visible, from the View menu, select DB Object Search.

The following figure shows the DB Object Search pane with results from a search for all objects associated with a connection named stacd05_hr that start with EMPLOYEE. (The pane may be displayed on the right side of the SQL Developer window or at the bottom.)

DB Object Search pane

To find objects for an Oracle connection, click Search, select the connection name, enter an object name or a string containing one or more wildcard characters, and press the Enter key. To view or edit one of the objects, double-click its name in the DB Object Search pane.

1.11 Reports

SQL Developer provides many reports about the database and its objects. You can also create your own user-defined reports. To display reports, click the Reports tab on the left side of the window (see SQL Developer User Interface). If this tab is not visible, select View and then Reports.

Individual reports are displayed in tabbed panes on the right side of the window; and for each report, you can select (in a drop-down control) the database connection for which to display the report. For reports about objects, the objects shown are only those visible to the database user associated with the selected database connection, and the rows are usually ordered by Owner. The detail display pane for a report includes the following icons at the top:

The time required to display specific reports will vary, and may be affected by the number and complexity of objects involved, and by the speed of the network connection to the database.

For most reports that contain names of database objects, you can double-click the object name in the report display pane (or right-click the object name and select Go To) to display that object in a detail view pane, just as if you had selected that object using the Connections navigator.

To export a report into an XML file that can be imported later, right-click the report name in the Reports navigator display and select Export. To import a report that had previously been exported, select the name of the report folder name (such as a user-defined folder) in which to store the imported report, right-click, and select Import.

You can create a shared report from an exported report by clicking Tools, then Preferences, and using the Database: User-Defined Extensions pane to add a row with Type as REPORT and Location specifying the exported XML file. The next time you restart SQL Developer, the Reports navigator will have a Shared Reports folder containing that report.

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").

Bind Variables for Reports

For some reports, you are prompted for bind variables before the report is generated. These bind variables enable you to further restrict the output. The default value for all bind variables is null, which implies no further restrictions. To specify a bind variable, select the variable name and type an entry in the Value field. Any bind variable values that you enter are case insensitive, all matches are returned where the value string appears anywhere in the name of the relevant object type.

Related Topics

SQL Developer Concepts and Usage

SQL Developer User Interface

Using the SQL Worksheet

SQL Developer Concepts and Usage

1.11.1 About Your Database reports

The About Your Database reports list release information about the database associated with the selected connection. The reports include Version Banner (database settings) and National Language Support Parameters (NLS_xxx parameter values for globalization support).

1.11.2 Database Administration reports

Database Administration reports list usage information about system resources. This information can help you to manage storage, user accounts, and sessions efficiently. (The user for the database connection must have the DBA role to see most Database Administration reports.)

Database Parameters: Provide information about all database parameters or only those parameters that are not set to their default values.

Storage: Provide usage and allocation information for tablespaces and data files.

Sessions: Provide information about sessions, selected and ordered by various criteria.

Cursors: Provide information about cursors, including cursors by session (including open cursors and cursor details.

All Tables: Contains the reports that are also grouped under Table reports, including Quality Assurance reports.

Top SQL: Provide information about SQL statements, selected and ordered by various criteria. This information might help you to identify SQL statements that are being executed more often than expected or that are taking more time than expected.

Users: Provide information about database users, selected and ordered by various criteria. For example, you can find out which users were created most recently, which user accounts have expired, and which users use object types and how many objects each owns.

1.11.3 Table reports

Table reports list information about tables owned by the user associated with the specified connection. This information is not specifically designed to identify problem areas; however, depending on your resources and requirements, some of the information might indicate things that you should monitor or address.

For table reports, the owner is the user associated with the database connection.

User Tables: Displays information about either all tables or those tables containing the string that you specify in the Enter Bind Variables dialog box (uncheck Null in that box to enter a string).

Columns: For each table, lists each column, its data type, and whether it can contain a null value.

Datatype Occurrences: For each table owner, lists each data type and how many times it is used.

Comments for tables and columns: For each table and for each column in each table, lists the descriptive comments (if any) associated with it. Also includes a report of tables without comments. If database developers use the COMMENT statement when creating or modifying tables, this report can provide useful information about the purposes of tables and columns

Constraints: For each table, lists each associated constraint, including its type (unique constraint, check constraint, primary key, foreign key) and status (enabled or disabled).

Enabled Constraints and Disabled Constraints: For each constraint with a status of enabled or disabled, lists the table name, constraint name, constraint type (unique constraint, check constraint, primary key, foreign key), and status. A disabled constraint is not enforced when rows are added or modified; to have a disabled constraint enforced, you must edit the table and set the status of the constraint to Enabled (see the appropriate tabs for the Create/Edit Table (with advanced options) dialog box).

Primary Key Constraints: For primary key constraint, lists information that includes the owner, the table name, the constraint name, the constraint status (enabled or disabled), and the column name.

Unique Constraints: For each unique constraint, lists information that includes the owner, the table name, the constraint name, the constraint status (enabled or disabled), and the column name.

Foreign Key Constraints: For each foreign key constraint, lists information that includes the owner, the table name, the constraint name, the column that the constraint is against, the table that the constraint references, and the constraint in the table that is referenced.

Check Constraints: For each check constraint, lists information that includes the owner, the table name, the constraint name, the constraint status (enabled or disabled), and the constraint specification.

Statistics: For each table, lists statistical information, including when it was last analyzed, the total number of rows, the average row length, and the table type. In addition, specialized reports order the results by most rows and largest average row length.

Storage - Tables by Tablespace: For each tablespace, lists the number of tables and the total number of megabytes currently allocated for the tables.

Storage - Tablespaces: For each table, lists the tablespace for the table and the number of megabytes currently allocated for the table.

Organization: Specialized reports list information about partitioned tables, clustered tables, and index-organized tables.

Quality Assurance reports

Quality assurance reports are table reports that identify conditions that are not technically errors, but that usually indicate flaws in the database design. These flaws can result in various problems, such as logic errors and the need for additional application coding to work around the errors, as well as poor performance with queries at run time.

Tables without Primary Keys: Lists tables that do not have a primary key defined. A primary key is a column (or set of columns) that uniquely identifies each row in the table. Although tables are not required to have a primary key, it is strongly recommended that you create or designate a primary key for each table. Primary key columns are indexed, which enhances performance with queries, and they are required to be unique and not null, providing some "automatic" validation of input data. Primary keys can also be used with foreign keys to provide referential integrity.

Tables without Indexes: Lists tables that do not have any indexes. If a column in a table has an index defined on it, queries that use the column are usually much faster and more efficient than if there is no index on the column, especially if there are many rows in the table and many different data values in the column.

Tables with Unindexed Foreign Keys: Lists any foreign keys that do not have an associated index. A foreign key is a column (or set of columns) that references a primary key: that is, each value in the foreign key must match a value in its associated primary key. Foreign key columns are often joined in queries, and an index usually improves performance significantly for queries that use a column. If an unindexed foreign key is used in queries, you may be able to improve run-time performance by creating an index on that foreign key.

1.11.4 PL/SQL reports

PL/SQL reports list information about PL/SQL packages, function, and procedures, and about types defined in them.

Program Unit Arguments: For each argument (parameter) in a program unit, lists the program unit name, the argument position (1, 2, 3, and so on), the argument name, and whether the argument is input-only (In), output-only (Out), or both input and output (In/Out).

Unit Line Counts: For each PL/SQL object, lists the number of source code lines. This information can help you to identify complex objects (for example, to identify code that may need to be simplified or divided into several objects).

Search Source Code: For each PL/SQL object, lists the source code for each line, and allows the source to be searched for occurrences of the specified variable.

1.11.5 Security reports

Security reports list information about users that have been granted privileges, and in some cases about the users that granted the privileges. This information can help you (or the database administrator if you are not a DBA) to understand possible security issues and vulnerabilities, and to decide on the appropriate action to take (for example, revoking certain privileges from users that do not need those privileges).

Object Grants: For each privilege granted on a specific table, lists the user that granted the privilege, the user to which the privilege was granted, the table, the privilege, and whether the user to which the privilege was granted can grant that privilege to other users.

Column Privileges: For each privilege granted on a specific column in a specific table, lists the user that granted the privilege, the user to which the privilege was granted, the table, the privilege, and whether the user to which the privilege was granted can grant that privilege to other users.

Role Privileges: For each granted role, lists the user to which the role was granted, the role, whether the role was granted with the ADMIN option, and whether the role is designated as a default role for the user.

System Privileges: For each privilege granted to the user associated with the database connection, lists the privilege and whether it was granted with the ADMIN option.

Auditing: Lists information about audit policies.

Encryption: Lists information about encrypted columns.

Policies: Lists information about policies.

1.11.6 XML reports

XML reports list information about XML objects.

XML Schemas: For each user that owns any XML objects, lists information about each object, including the schema URL of the XSD file containing the schema definition.

1.11.7 Jobs reports

Jobs reports list information about jobs running on the database.

Your Jobs: Lists information about each job for which the user associated with the database connection is the log user, privilege user, or schema user. The information includes the start time of its last run, current run, and next scheduled run.

All Jobs: Lists information about all jobs running on the database. The information includes the start time of its last run, current run, and next scheduled run.

1.11.8 Streams reports

Streams reports list information about stream rules.

Your Stream Rules: Lists information about each stream rule for which the user associated with the database connection is the rule owner or rule set owner. The information includes stream type and name, rule set owner and name, rule owner and name, rule set type, streams rule type, and subsetting operation.

All Stream Rules: Lists information about all stream rules. The information includes stream type and name, rule set owner and name, rule owner and name, rule set type, streams rule type, and subsetting operation.

1.11.9 All Objects reports

All Objects reports list information about objects visible to the user associated with the database connection.

All Objects: For each object, lists the owner, name, type (table, view, index, and so on), status (valid or invalid), the date it was created, and the date when the last data definition language (DDL) operation was performed on it. The Last DDL date can help you to find if any changes to the object definitions have been made on or after a specific time.

Invalid Objects: Lists all objects that have a status of invalid.

Object Count by Type: For each type of object associated with a specific owner, lists the number of objects. This report might help you to identify users that have created an especially large number of objects, particularly objects of a specific type.

Collection Types: Lists information about for each collection type. The information includes the type owner, element type name and owner, and type-dependent specific information.

Dependencies: For each object with references to it, lists information about references to (uses of) that object.

1.11.10 Data Dictionary reports

Data Dictionary reports list information about the data dictionary views that are accessible in the database. Examples of data dictionary views are ALL_OBJECTS and USER_TABLES.

Dictionary Views: Lists each Oracle data dictionary view and (in most cases) a comment describing its contents or purpose.

Dictionary View Columns: For each Oracle data dictionary view, lists information about the columns in the view.

1.11.11 User Defined reports

User Defined reports are any reports that are created by SQL Developer users. To create a user-defined report, right-click the User Defined node under Reports and select Add Report. A dialog box is displayed in which you specify the report name and the SQL query to retrieve information for the report (see Section 3.24, "Create/Edit User Defined Report").

You can organize user-defined reports in folders, and you can create a hierarchy of folders and subfolders. To create a folder for user-defined reports, right-click the User Defined node or any folder name under that node and select Add Folder (see Section 3.25, "Create/Edit User Defined Report Folder").

Information about user-defined reports, including any folders for these reports, is stored in a file named UserReports.xml under the directory for user-specific information. For information about the location of this information, see Section 1.13, "Location of User-Related Information".

For examples of creating user-defined reports, see:

1.11.11.1 User-Defined Report Example: Chart

This example creates a report displayed as a chart. It uses the definition of the EMPLOYEES table from the HR schema, which is a supplied sample schema.

Right-click on User Defined Reports and select Add Report. In the Add Report dialog box, specify a report name; for Style, select Chart; and for SQL, enter the following:

select m.department_id, e.last_name, e.salary
from employees m, employees e
where e.employee_id = m.employee_id
order by 1

The preceding query lists the last name and salary of each employee in each department, grouping the results by department ID (10, 20, 30, ... 110).

Click the Chart Details tab near the bottom of the box; for Chart Type, select BAR_VERT_STACK (bar chart, stacked vertically); and click Apply.

Use the Reports navigator to view the newly created user-defined report. For Connection, specify one that connects to the HR sample schema.

The report is displayed as a chart, part of which is shown in the following illustration. For example, as you can see, department 50 has mainly employees with the lowest salaries, and department 90 consists of the three highest-paid employees.

User-defined chart report, as explained in preceding text.

1.11.11.2 User-Defined Report Example: Dynamic HTML

This example creates a report using one or more PL/SQL DBMS_OUTPUT statements, so that the report is displayed as dynamic HTML.

Right-click on User Defined Reports and select Add Report. In the Add Report dialog box, specify a report name; for Style, select plsql-dbms_output; and for SQL, enter the following:

begin
dbms_output.put_line ('<H1> This is Level-1 Heading </H1>');
dbms_output.put_line ('<H2> This is a Level-2 Heading </H2>');
dbms_output.put_line ('<p> This is regular paragraph text. </p>');
end;

Click Apply.

Use the Reports navigator to view the newly created user-defined report. For Connection, specify any from the list. (This report does not depend on a specific connection of table.).

The report is displayed as formatted HTML output.

1.12 SQL Developer Preferences

You can customize many aspects of the SQL Developer interface and environment by modifying SQL Developer preferences according to your preferences and needs. To modify SQL Developer preferences, select Tools, then Preferences.

Information about SQL Developer preferences is stored under the directory for user-specific information. For information about the location of this information, see Section 1.13, "Location of User-Related Information".

Most preferences are self-explanatory, and this topic explains only those whose meaning and implications are not obvious. Some preferences involve performance or system resource trade-offs (for example, enabling a feature that adds execution time), and other preferences involve only personal aesthetic taste. The preferences are grouped in the following categories.

1.12.1 Environment

The Environment pane contains options that affect the startup and overall behavior and appearance of SQL Developer. You can specify that certain operations be performed automatically at specified times, with the trade-off usually being the extra time for the operation as opposed to the possibility of problems if the operation is not performed automatically (for example, if you forget to perform it when you should).

The undo level (number of previous operations that can be undone) and navigation level (number of open files) values involve slight increases or decreases system resource usage for higher or lower values.

Automatically Reload Externally Modified Files: If this option is checked, any files open in SQL Developer that have been modified by an external application are updated when you switch back to SQL Developer, overwriting any changes that you might have made. If this option is not checked, changes that you make in SQL Developer overwrite any changes that might have been made by external applications.

Silently Reload When File Is Unmodified: If this option is checked, you are not asked if you want to reload files that have been modified externally but not in SQL Developer. If this option is not checked, you are asked if you want to reload each file that has been modified externally, regardless of whether it has been modified in SQL Developer,

Environment: Dockable Windows

The Dockable Windows pane configures the behavior of dockable windows and the shapes of the four docking areas of SQL Developer: top, bottom, left, and right.

Dockable Windows Always on Top: If this option is checked, dockable windows always remain visible in front of other windows.

Windows Layout: Click the corner arrows to lengthen or shorten the shape of each docking area.

Environment: Local History

The Local History pane controls whether information about editing operations on files opened within SQL Developer is kept. If local history is enabled, you can specify how long information is retained and the maximum number of revisions for each file.

Environment: Log

The Log pane configures the colors of certain types of log messages and the saving of log messages to log files.

Save Logs to File: If this option is checked, all output to the Messages - Log window is saved to log files, where the file name reflects the operation and a timestamp. You are also asked to specify a Log Directory; and if the specified directory does not already exist, it is created. Note that if you save log information to files, the number of these files can become large.

Maximum Log Lines: The maximum number of lines to store in each log file.

1.12.2 Accelerators (Keyboard Shortcuts)

The Accelerators pane enables you to view and customize the accelerator key mappings (keyboard shortcuts) for SQL Developer.

Category: Select All or a specific category (Code Editor, Database, Debug, Edit, and so on), to control which actions are displayed.

Actions: The actions for the selected category. When you select an action, any existing accelerator key mappings are displayed.

Accelerators: Any existing key mappings for the selected action. To remove an existing key mapping, select it and click Remove.

New Accelerator: The new accelerator key to be associated with the action. Press and hold the desired modifier key, then press the other key. For example, to associate Ctrl+J with an action, press and hold the Ctrl key, then press the j key. If any actions are currently associated with that accelerator key, they are listed in the Current Assignment box.

Current Assignment: A read-only display of the current action, if any, that is mapped to the accelerator key that you specified in the New Accelerator box.

Load Preset: Enables you to load a set of predefined key mappings for certain systems and external editing applications. If you load any preset key mappings that conflict with changes that you have made, your changes are overwritten.

1.12.3 Code Editor

The Code Editor pane contains general options that affect the appearance and behavior of SQL Developer when you edit functions, procedures, and packages.

Code Editor: Bookmarks

The Bookmarks pane contains options that determine the persistence and search behavior for bookmarks that you create when using the code editor.

Code Editor: Caret Behavior

The Caret Behavior pane contains options that determine the shape, color, and blinking characteristics of the caret (cursor) in the code editor

Code Editor: Code Insight

The Code Insight pane contains options for the logical completion (autocomplete options) of keywords and names while you are coding in the SQL Worksheet.

When you press Ctrl+Space, code insight provides a context-sensitive popup window that can help you select parameter names. Completion insight provides you with a list of possible completions at the insertion point that you can use to auto-complete code you are editing. This list is based on the code context at the insertion point. To exit code insight at any time, press Esc.

You can enable or disable both completion and parameter insight, as well as set the time delay for the popup windows.

Code Editor: Code Insight: Completion

The Code Insight: Completion pane contains options for refining the behavior when matching items are found. For more information, see the explanation for Code Editor: Code Insight.

Code Editor: Display

The Display pane contains general options for the appearance and behavior of the code editor.

Text Anti-Aliasing allows smooth-edged characters where possible.

Code Folding Margin allows program blocks in procedures and functions to be expanded and collapsed in the display.

Visible Right Margin renders a right margin that you can set to control the length of lines of code.

Automatic Brace Matching controls the highlighting of opening parentheses and brackets and of blocks when a closing parenthesis or bracket is typed.

Code Editor: Find Options

The Find Options pane determines which options are used by default at SQL Developer startup for find or find and replace operations. You can choose whether to use the options in effect from the last SQL Developer session or to use specified options.

Code Editor: Fonts

The Fonts pane specifies text font options for the code editor.

Display Only Fixed-Width Fonts: If this option is checked, the display of available font names is restricted to fonts where all characters have the same width. (Fixed-width fonts are contrasted with proportional-width fonts.)

Code Editor: Line Gutter

The Line Gutter pane specifies options for the line gutter (left margin of the code editor).

Show Line Numbers: If this option is checked, lines are numbered.

Enable Line Selection by Click-Dragging: If this option is checked, you can select consecutive lines in the editor by clicking in the gutter and dragging the cursor without releasing the mouse button.

Code Editor: Printing

The Printing pane specifies options for printing the contents of the code editor. The Preview pane sample display changes as you select and deselect options.

Code Editor: Printing HTML

The Printing HTML pane specifies options for printing HTML files from the code editor.

Code Editor: Syntax Colors

The Syntax Colors pane specifies colors for different kinds of syntax elements.

Code Editor: Undo Behavior

The Undo Behavior pane specifies options for the behavior of undo operations (Ctrl+Z, or Edit, then Undo). Only consecutive edits of the same type are considered; for example, inserting characters and deleting characters are two different types of operation.

Allow Navigation-Only Changes to be Undoable: If this option is checked, navigation actions with the keyboard or mouse can be undone. If this option is not checked, navigation actions cannot be undone, and only actual changes to the text can be undone.

1.12.4 Database

The Database pane sets properties for the database connection.

Validate date and time default values: If this option is checked, date and time validation is used when you open tables.

SQL*Plus Executable: The Windows path or Linux xterm command for the SQL*Plus executable. If there is no $ORACLE_HOME on your system, there is no SQL*Plus executable, and you cannot use SQL*Plus with SQL Developer; however, you can still use many SQL*Plus commands in the SQL Worksheet (see Section 1.7, "Using the SQL Worksheet", and especially SQL*Plus Statements Supported and Not Supported in SQL Worksheet).

Default path for storing export: Default path of the directory or folder under which to store output files when you perform an export operation. To see the current default for your system, click the Browse button next to this field.

Database: Advanced Parameters

The Advanced Parameters pane specifies options such as the SQL array fetch size and display options for null values.

Database: Autotrace Parameters

The Autotrace Parameters pane specifies information to be displayed on the Autotrace pane in the SQL Worksheet.

Database: NLS Parameters

The NLS Parameters pane specifies globalization support parameters, such as the language, territory, sort preference, and date format.

Note that SQL Developer does not use default values from the current system for globalization support parameters; instead, SQL Developer by default uses the following parameter values:

NLS_LANG,"AMERICAN"
NLS_TERR,"AMERICA"
NLS_CHAR,"AL32UTF8"
NLS_SORT,"BINARY"
NLS_CAL,"GREGORIAN"
NLS_DATE_LANG,"AMERICAN"
NLS_DATE_FORM,"DD-MON-RR"

Database: ObjectViewer Parameters

The ObjectViewer Parameters pane specifies whether to freeze object viewer windows, and display options for the output. The display options will affect the generated DDL on the SQL tab.

Database: Third Party JDBC Drivers

The Third Party JDBC Drivers pane specifies drivers to be used for connections to third-party (non-Oracle) databases, such as MySQL or Microsoft SQL Server. (You do not need to add a driver for connections to Microsoft Access databases.) To add a driver, click Add Entry and select the path for the driver (for example, C:\Program Files\MySQL\mysql-connector-java-5.0.4\mysql-connector-java-5.0.4-bin.jar).

You must specify a third-party JDBC driver before you can create a database connection to a third-party database of that associated type. (See the tabs for creating connections to third-party databases in the Create/Edit/Select Database Connection dialog box.)

Database: User-Defined Extensions

The User-Defined Extensions pane specifies user-defined extensions that have been added. You can use this pane to add extensions that are not available through the Check for Updates feature. (For more information about extensions and checking for updates, see Section 1.12.7, "Extensions".)

One use of the Database: User-Defined Extensions pane is to create a Shared Reports folder and to include an exported report under that folder: click Add Row, specify Type as REPORT, and for Location specify the XML file containing the exported report. The next time you restart SQL Developer, the Reports navigator will have a Shared Reports folder containing that report

Database: Worksheet Parameters

Autocommit in SQL Worksheet: If this option is checked, a commit operation is automatically performed after each INSERT, UPDATE, or DELETE statement executed using the SQL Worksheet. If this option is not checked, a commit operation is not performed until you execute a COMMIT statement.

Open a worksheet on connect: If this option is checked, a SQL Worksheet window for the connection is automatically opened when you open a database connection. If this option is not checked, you must use the Open SQL Worksheet right-click command or toolbar icon to open a SQL Worksheet.

Max rows to print in a script: Limits the number of rows displayed.

Default path to look for scripts: The default directory where SQL Developer looks when you run a script (using @).

Save bind variables to disk on exit: If this option is checked, bind variables that you enter when running a script are saved on disk for reuse. If you do not want bind variable values stored on disk (for security or other reasons), be sure not to check this option.

1.12.5 Debugger

The Debugger pane contains general options for the SQL Developer debugger. Other panes contain additional specific kinds of debugger options.

Debugger: Breakpoints

The Breakpoints pane sets the columns to appear in the Breakpoints pane and the scope of each breakpoint.

Debugger: Breakpoints: Default Actions

The Breakpoints: Default Actions pane sets defaults for actions to occur at breakpoints. These actions are the same as on the Actions tab in the Create/Edit Breakpoint dialog box.

Debugger: Data

The Data pane enables you to control the columns to appear in the debugger Data pane and aspects of how the data is displayed.

Debugger: Inspector

The Inspector pane enables you to control the columns to appear in the debugger Inspector pane and aspects of how the data is displayed.

Debugger: Smart Data

The Smart Data pane enables you to control the columns to appear in the debugger Smart Data pane and aspects of how the data is displayed.

Debugger: Stack

The Stack pane enables you to control the columns to appear in the debugger Stack pane and other options.

Debugger: Watches

The Watches pane enables you to control the columns to appear in the debugger Watches pane and aspects of how the data is displayed.

1.12.6 Documentation

The Documentation pane provides options for the display of SQL Developer online help.

Display in Window: If this option is checked, help is displayed in a separate window. If this option is not checked, help is displayed in a pane in the SQL Developer User Interface.

Show Tabs: Controls which tabs appear in the Help Center pane (Table of Contents, Index, Full Text Search).

1.12.7 Extensions

The Extensions pane determines which optional extensions SQL Developer uses when it starts. (SQL Developer also uses some mandatory extensions, which users cannot remove or disable.) If you change any settings, you must exit SQL Developer and restart it for the new settings to take effect.

Extensions to Use: Controls the specific optional SQL Developer extensions to use at startup.

Check for Updates: Checks for any updates to the selected optional SQL Developer extensions, as well as any mandatory extensions. (If the system you are using is behind a firewall, see the SQL Developer user preferences for Web Browser and Proxy.)

Automatically Check for Updates: If this option is checked, SQL Developer automatically checks for any updates to the selected optional SQL Developer extensions and any mandatory extensions at startup.

1.12.8 File Types

The File Types pane determines which file types and extensions will be opened by default by SQL Developer. The display shows each file extension, the associated file type, and a check mark if files with that extension are to be opened by SQL Developer be default, such as when a user double-clicks the file name.

Details area at bottom: You can modify the file type, content type (text or binary), and whether to open files with this extension automatically by SQL Developer.

To have files with a specific extension be opened by default by SQL Developer, click the file extension in the list, then check Open with SQL Developer in the Details area. This overrides any previous application association that may have been in effect for that file extension.

To add a file extension, click Add and specify the file extension (including the period). After adding the extension, you can modify its associated information by selecting it and using the Details area.

1.12.9 PL/SQL Compiler

The PL/SQL Compiler pane specifies options for compilation of PL/SQL subprograms.

Generate PL/SQL Debug Information: If this option is checked, PL/SQL debug information is included in the compiled code; if this option is not checked, this debug information is not included. The ability to stop on individual code lines and debugger access to variables are allowed only in code compiled with debug information generated.

Types of messages: You can control the display of informational, severe, and performance-related messages. (The ALL type overrides any individual specifications for the other types of messages.) For each type of message, you can specify any of the following:

  • No entry (blank): Use any value specified for ALL; and if none is specified, use the Oracle default.

  • Enable: Enable the display of all messages of this category.

  • Disable: Disable the display of all messages of this category.

  • Error: Enable the display of only error messages of this category.

1.12.10 PL/SQL Debugger

The PL/SQL Debugger pane controls whether the Probe debugger (developed using the Oracle Probe API) is used to debug PL/SQL.

1.12.11 SQL*Plus

The SQL*Plus pane controls behavior related to SQL*Plus.

SQL*Plus Executable: The Windows path or Linux xterm command for the SQL*Plus executable. If there is no $ORACLE_HOME on your system, there is no SQL*Plus executable, and you cannot use SQL*Plus with SQL Developer; however, you can still use many SQL*Plus commands in the SQL Worksheet (see Section 1.7, "Using the SQL Worksheet", and especially SQL*Plus Statements Supported and Not Supported in SQL Worksheet).

1.12.12 SQL Formatter

The SQL Formatter pane controls how statements in the SQL Worksheet are formatted when you click Format SQL. The options include whether to insert space characters or tab characters when you press the Tab key (and how many characters), uppercase or lowercase for keywords and identifiers, whether to preserve or eliminate empty lines, and whether comparable items should be placed or the same line (if there is room) or on separate lines.

1.12.13 Web Browser and Proxy

The Web Browser and Proxy pane settings are relevant only when you use the Check for Updates feature (click Help, then Check for Updates), and only if your system is behind a firewall.

Browser Command Line: To specify a Web browser other than your default browser, specify the executable file to start that browser. To use your default browser, leave this field blank.

Use HTTP Proxy Server: Check your Web browser options or preferences for the appropriate values for these fields.

1.13 Location of User-Related Information

SQL Developer stores user-related information in several places, with the specific location depending on the operating system and certain environment specifications. User-related information includes user-defined reports, user-defined snippets, SQL Worksheet history, and SQL Developer user preferences.

In most cases, your user-related information is stored outside the SQL Developer installation directory hierarchy, so that it is preserved if you delete that directory and install a new version. The exception to this is on Windows systems, where SQL Developer user preferences are stored under the installation directory. To preserve preferences on Windows systems, use the Check for Updates feature (click Help, then Check for Updates) to upgrade your system.

The user-related information is stored in or under the following location:

The following table shows the typical default locations (under a directory or in a file) for specific types of resources on different operating systems. (Note the period in the name of any directory or folder named .sqldeveloper.)

Table 1-1 Default Locations for User-Related Information

Resource Type Windows Systems Linux or Mac OS X Systems

User-defined reports

C:\Documents and Settings\<user-name>\.sqldeveloper\UserReports.xml

~/.sqldeveloper/UserReports.xml

User-defined snippets

C:\Documents and Settings\<user-name>\.sqldeveloper\UserSnippets.xml

~/.sqldeveloper/UserSnippets.xml

SQL history

C:\Documents and Settings\<user-name>\.sqldeveloper\SqlHistory.xml

~/.sqldeveloper/system/

SQL Worksheet archive filesFoot 1 

C:\Documents and Settings\<user-name>\.sqldeveloper\tmp\

~/.sqldeveloper/tmp/

SQL Developer user preferences

<sqldeveloper_install>\sqldeveloper\sqldeveloper\system\

~/.sqldeveloper/system/


Footnote 1 SQL Worksheet archive files contain SQL statements that you have entered. These files begin with sqldev and then have a random number (for example, sqldev14356.sql). If you close SQL Developer with a SQL Worksheet open that contains statements, you will be prompted to save these files.

To specify a nondefault SQLDEVELOPER_USER_DIR location, do either of the following:

If you want to prevent other users from accessing your user-specific SQL Developer information, you must ensure that the appropriate permissions are set on the directory where that information is stored or on a directory above it in the path hierarchy. For example, on a Windows system you may want to ensure that the sqldeveloper folder and the <user-name>\.sqldeveloper folder under Documents and Settings are not shareable; and on a Linux or Mac OS X system you may want to ensure that the ~/.sqldeveloper directory is not world-readable.

1.14 Using the Help

SQL Developer provides a Help menu and context-sensitive help (click the Help button or press the F1 key in certain contexts).

By default, help from the Help menu is displayed on the right side of the window, with the Help Center area on the far right and containing tabs at the bottom. You can use the Contents, Index, and Search (full-text) tabs or their corresponding Help menu items. Context-sensitive help is displayed in a separate help window. (To change the default help behavior, set SQL Developer Documentation preferences.)

For help displayed in the SQL Developer window, you can close individual Help panes by clicking the X next to their tab names (just as with tabs for tables and other database objects).

To print a help topic, display it in a Help pane and click the Print icon at the top of the pane.

The best way to become familiar with the help, and with SQL Developer itself, is simply to experiment with the Help menu options and the Help button in some dialog boxes.

Tip:

You can download the online help as a single PDF file from:
http://www.oracle.com/technology/products/database/sql_developer/pdf/online_help.pdf

1.15 For More Information

For more information about SQL Developer and related topics, you may find the following resources helpful: