Skip Headers
Oracle® SQL Developer User's Guide
Release 3.0

Part Number E17472-05
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

5 SQL Developer Dialog Boxes and Wizards

SQL Developer uses dialog boxes for creating and editing database connections and objects in the database (tables, views, procedures, and so on). The dialog boxes sometimes have multiple tabs, each reflecting a logical grouping of properties for that type of object.

For an explanation of any dialog box or tab, click the Help button or press the F1 key.

The dialog boxes and wizards are not presented here in any rigorous order, because the help for each is an independent piece of information and is normally seen when you click Help or press F1 in that context.

Note:

For all Name fields, any name that you type is automatically converted to and stored in the database metadata in uppercase, unless you enclose the name in quotation marks (" "). (Names of database objects in SQL and PL/SQL statements are not case-sensitive.)

To include lowercase characters, special characters, or spaces in object names, enclose the name in quotation marks (" ") when you type it. Example: "My table"

5.1 Add Extension

This dialog box is displayed when you click Add in the File Types pane of SQL Developer Preferences.

Extension: Specify the file extension, including the period (for example, .xyz).

After you click OK, you can select that extension and modify its details, including the file type, content type, and whether to have files with the extension automatically opened by SQL Developer.

5.2 Add Schema Error

This dialog box is displayed when you specify an invalid file after clicking Add in the XML Schemas pane of SQL Developer Preferences. A list of the errors is displayed.

Ignore errors and continue registration: Ignores the errors and allows the process of registering the specified XML schema or schemas after you click OK. Choose this option only if you are sure that the apparent errors can be ignored.

Skip registration and open using SQL Developer: Opens an editing window in which you can correct any errors.

5.3 Analyze Database Migration

This dialog box is displayed if you right-click a third-party database connection and select Analyze Database. You specify the database connection for the migration repository and the location and name of the Microsoft Excel spreadsheet file (.xls) to be created with information to help you estimate the effort required to migrate the third-party databases in the migration repository to Oracle.

Repository Connection: Database connection for the migration repository.

Estimation Output File (.xls): Microsoft Excel spreadsheet file that will contain information to help you estimate the migration effort. It will contain worksheets for Summary (executive summary), Instructions (steps and assumptions), and various detailed information.

5.4 Application Migration

The Application Wizard enables you to migrate a DBLib or CTLib Sybase application to Oracle by specifying a directory with source code from the DBLib or CTLib Sybase application. You will be able to see what changes must be made to migrate the application to Oracle, and to perform the migration.

The Welcome page includes a description of the wizard's operation and options. If you want to continue to see this Welcome page in the future, do not enable (do not check) the Do not show this page again option.

Follow the instructions on each page of the wizard.

5.5 Change Type

Use this dialog box to change the data type of a column in a captured model before you perform the migration.

Source Data Type: Specify the new data type for the column.

Any remaining fields in the dialog box depend on the Source Data Type that is selected.

5.6 Check for Updates

When you click Help and then Check for Updates, you can check for and download available SQL Developer updates. The following pages may be displayed. (If you have enabled the SQL Developer preference to check for updates automatically at startup, and if you click to see available updates at startup, the Updates page is displayed.)

If you are unable to check for updates because your system is behind a firewall, you may need to set the SQL Developer user preferences for Web Browser and Proxy.

  1. Source: Select the source or sources to be checked for available updates: any or all of some specified online update centers, or a local ZIP file containing an update bundle. You can also click Add to add a user-defined update center.

  2. Updates: If any updates are available from the selected source or sources, select those that you want to download.The available updates include certain third-party JDBC drivers, which require that you agree to the terms of their licenses.

    The Show Upgrades Only option restricts the display to upgrades of currently installed SQL Developer components. To enable the display of all new and updated components, whether currently installed or not, uncheck this option.

    After you click Next, you may be prompted to enter your Oracle Web Account user name and password. If you do not have an account, you can click the Sign Up link.

  3. License Agreements (displayed only if you selected any updates that require a license agreement): For each update that requires you to agree to the terms of a license, review the license text and click I Agree. You must do this for each applicable license.

  4. Download: If you selected any updates to download, this page displays the progress of the download operation.

  5. Summary: Displays information about the updates that were downloaded. After you click Finish, you will be asked if you want to install the updates now and restart SQL Developer.

5.7 Check Out from CVS

Use this dialog box to check out modules from a CVS repository.

Connection Name: Name of the connection to the repository

Module Name: Name of the module to be checked out.

Path: Path to the module.

Get/Refresh Module List: Displays the list of modules or updates the current display.

Destination Folder: Folder into which to place the checked out files.

Use Revision or Tag: If this option is checked, the revision or tag that you specify in the text box is used. To see the available tags, click the binoculars icon.

Prune Empty Folders: If this option is checked, empty folders are removed from the working directory.

5.8 Choose Directory

This is a standard box for choosing a directory in which to place files: use Location to navigate to (double-clicking) the folder in which to save the files, or enter a directory name. If the directory does not already exist, it is created.

5.9 Component Palette

The Component Palette displays the elements that you can drag and drop. The components available for selection in the palette vary depending on the content of the active editor window.

To insert a component into a file open in the active editor, drag the component from the palette to an insertion point in the editor. In some file types you can click a component in the palette and then click in the editor to insert the component.

5.10 Component Palette: Configure Component Palette

Lets you configure the component palette. Note that some page types cannot be edited or removed, and most existing component types cannot be added to, edited, or removed.

Add: Displays the Component Palette: Create Palette Page dialog box.

Remove: Deletes the selected page from the palette.

Rename: Renames a specified page.

5.11 Component Palette: Create Palette Page

Lets you create a new page for the component palette. Specify a name of the page, and select the type of page from a list.

Page Name: Name of the page. Suggestion: Include the type of page in the name, perhaps naming pages in the form name_type_page.

Page Type: Page type, selected from the list.

5.12 Component Palette: New/Edit Code Snippet

Lets you create or edit a code snippet, which you will be able to drag into files that you edit.

Name: Name for the code snippet.

Image: Icon image to be associated with the snippet.

Code: Code for the snippet.

5.13 Component Palette: New Section / Rename Section

Lets you create or rename a section in the Code Snippets panel of the Component Palette.

You can create sections in the Code Snippets panel to organize your snippets better. For example, if you have a group of code snippets that pertain to mathematical functions, you can create a new section called Math and group the related snippets under it.

Name: Name of the section.

5.14 Confirm Drop Application

This dialog box is displayed when you right-click an Application Express application and select Drop. To drop the application, click Yes; to keep (not drop) the application, click No.

If the application contains an uninstall script, that script is run before the application is dropped.

5.15 Confirm Running SQL

This dialog box is displayed in certain situations when SQL Developer needs to run a setup script on the server. The script is displayed in a text box, where you can view or edit the contents. To allow the script to run, click Yes; to prevent.the script from running, click No.

5.16 Connection Has Uncommitted Changes

This dialog box is displayed if you try to end the active database session while there are transactions to be committed. Select the appropriate option and click OK.

To commit the changes and end the session, select Commit Changes. To roll back the changes and end the session, select Rollback Changes. To cancel the attempt to end the session, select Abort Connection Disconnect. (Selecting Abort Connection Disconnect and clicking OK has the same effect as clicking Cancel.)

5.17 Create New Object

This dialog box is displayed if you click File, then New. Specify the type of object to create. After you click OK, the dialog box for creating that type of object is displayed.

5.18 Create/Edit Credential

This dialog box or pane is used for creating a new Oracle Scheduler credential or editing an existing credential. For more information about job scheduling, see Section 1.12, "Scheduling Jobs Using SQL Developer".

(To create a credential, SQL Developer internally uses the DBMS_SCHEDULER.CREATE_CREDENTIAL procedure.)

Name: Name of the credential. Cannot be set to NULL. It is converted to upper case unless enclosed in double-quotes. For an existing credential, this field is read-only; to change the name, you must drop the credential and create a new credential with the desired name.

Schema: Schema for the credential. If it is not specified, the schema is that for the connection you are using.

Description: Optional text string that can be used to describe the credential.

User Name: User name to use for login to the host operating system or remote Oracle database. This cannot be set to NULL and is case sensitive. It cannot contain double quotes or spaces.

Password: Password for the user name. This cannot be set to NULL and is case sensitive. The password is stored obfuscated and is not displayed in the Scheduler dictionary views.

Database Role: (Reserved for future use.)

Windows Domain: For a Windows remote executable target, this is the domain that the specified user belongs to. The domain is converted to uppercase automatically.

5.19 Create/Edit CVS Connection

This information applies to creating or editing a CVS (Concurrent Versions System) connection. For information about SQL Developer support for versioning and CVS, see Section 1.10.

Connection

Access Method: The method by which the client will gain access to and authenticate against the server. The methods available depend on which CVS preferences you have set; the available methods might include External, Password Server, Secure Shell via SSH2, and [Other].

Most of the remaining Connection fields apply only to specific Access Method values.

User Name: A CVS user name known to the repository.

Host Name: Qualified host name or IP address of the CVS server system.

Port: TCP/IP port number on which the repository is listening.

Repository Path: The location of the CVS repository software. The seeded / can be overwritten with a path in the format suitable for your operating system, for example c:\cvs. A simple formatting error, such as a forward slash instead of a backslash, will result in a message asking you to enter a valid repository path.

SSH2 Key: Path and file name for the SSH2 private key file for this connection. You can generate a SSH2 private key file using Generate SSH2 Key Pair.

Generate SSH2 Key Pair: Displays a dialog box for generating an SSH2 key pair (that is, a private key file and a public key file). You specify the private key file in the SSH2 Key box. You add the details of the public key file to the list of public keys on the CVS server system

Use HTTP Proxy Settings: Check (enable) this option if you are behind a firewall and need to use HTTP to access the CVS server.

External Locator Configuration: Displays the External Locator Configuration dialog box, in which you can edit the details of the remote shell client and remote server program.

Root

Value of CVSROOT: CVS root variable made up from the information that you have already provided. This variable provides the client with access details when contacting the server. The format of the seeded variable is: :accessmethod:username@serverlocation:repositorypath

You would not normally need to change this value. One instance when you would change this value is when you are attempting to connect to a CVSNT server through a firewall. In this case, you would add proxy information to the beginning of the username portion, so that the CVS root variable would take the following form: :accessmethod:proxy=proxyname;proxyport=portnumber:username@serverlocation:repositorypath

Test

Test Connection: Attempts to establish a connection to the CVS repository.

Status: Displays the result of the test (success or an error message).

Name

Connection Name: Name to identify the connection to the CVS repository. The default name is the same as the CVSROOT value.

Summary

Displays the connection information that you have specified. To make any corrections, click Back as needed and modify the information. To create the connection, click Finish.

5.20 Create/Edit/Select Database Connection

The database connection dialog box displays any existing connections. Depending on the context, you can select a connection to connect to the database, edit the information about existing connections, or specify information while creating a new connection. (See Creating and Editing Connections.)

Connection Name: An alias for a connection to the database using the information that you enter. (The connection name is not stored in the database, and the connection is not a database object.) Suggestion: Include the database name (SID) and user name in the connection name. Example: personnel_herman for connecting to the personnel database as user Herman.

Username: Name of the database user for the connection. This user must have sufficient privileges to perform the tasks that you want perform while connected to the database, such as creating, editing, and deleting tables, views, and other objects.

Password: Password associated with the specified database user.

Save Password: If this option is checked, the password is saved with the connection information, and you will not be prompted for the password on subsequent attempts to connect using this connection.

Information for Database-Specific Tabs:

Oracle tab

The following information applies to a connection to an Oracle Database.

Role: The set of privileges to be associated with the connection. For a user that has been granted the SYSDBA system privilege, you can specify a connection that includes the privilege.

Connection Type: Select Basic, TNS, LDAP (Lightweight Directory Access Protocol), Advanced, or Local/Bequeath (using bequeath protocol: if the client and database exist on the same computer, then a client connection can be passed directly to a dedicated server process without going through the listener). (The display of fields changes to reflect any change in connection type.)

OS Authentication: If this option is checked, control of user authentication is passed to the operating system (OS). This allows the specified user to connect to the database by authenticating that user's OS username in the database. No password is associated with the connection since it is assumed that OS authentication is sufficient. For information about using OS authentication, see Oracle Database JDBC Developer's Guide.

Kerberos Authentication: If this option is checked, credentials can be shared across many Kerberos-enabled applications (for example, to have the same username and password for both the operating system and Oracle Database). Thick driver configuration is done through sqlnet.ora (sqlnet.authentication_services=(KERBEROS) and related parameters), so no username and password are needed. Thin driver configuration uses the configuration (.conf) file and the credentials cache, and uses a service principal and password. For more information about Kerberos authentication options, see Database: Advanced. For information about configuring Kerberos authentication, see Oracle Database Advanced Security Administrator's Guide.

Proxy Connection: If this option is checked, proxy authentication will be used, as explained in Section 1.4.5, "Connections with Proxy Authentication". Displays the Oracle Proxy Authentication dialog box.

Basic connection type

Hostname: Host system for the Oracle database.

Port: Listener port.

SID: Database name.

Service Name: Network service name of the database (for a remote database connection over a secure connection).

TNS connection type

Network Alias: Oracle Net alias for the database. (The list for selecting a network alias is initially filled from the tnsnames.ora file on your system, if that file exists.)

Connect Identifier: Oracle Net connect identifier.

LDAP connection type

Enterprise users are authenticated with the Lightweight Directory Access Protocol (LDAP) server. The user login information must be configured in the LDAP server and mapped to a schema in the database. Support for LDAP-compliant directory servers provides a centralized vehicle for managing and configuring a distributed Oracle network. The directory server can replace client-side and server-side localized tnsnames.ora files.

LDAP Server: Select from the list (from <DIRECTORY_SERVER> entries in the ldap.ora file); or enter the directory server location and port (either SSL or non-SSL), for example: system123.example.com:389:636 (ldap-system:nonssl-port:ssl-port)

Context: LDAP administrative context. The contexts available in the selected server are listed.

DB Service: Database connection information: click Load to display a list of database services associated with the selected context. (If an error is displayed, no database services are associated with this context.) If a connection uses the OCI/Thick driver (see the Use OCI/Thick preference under Database: Advanced), the system on which SQL Developer is running must have an Oracle Client installation that contains the JDBC and orai18n libraries, these libraries must be present on the path, and the Oracle Client installation must be version 10.2 or later.

Advanced connection type

Custom JDBC URL: URL for connecting directly from Java to the database; overrides any other connection type specification. If you are using TNS or a naming service with the OCI driver, you must specify this information: Example:

jdbc:oracle:thin:scott/@localhost:1521:orcl

Note that in this example, the "/" is required, and the user will be prompted to enter the password.

To use a custom JDBC URL, the system on which SQL Developer is running must have an Oracle Client installation that contains the JDBC and orai18n libraries, is present on the path, and is version 10.2 or later.

TimesTen tab

The following information applies to a connection to an Oracle TimesTen In-Memory Database.

For Username and Password, specify the user name and password of the user account in the TimesTen database.

DSN: Data source name. Select an existing DSN (if any are displayed), or User-specified to create a new DSN. A DSN is a character string that identifies a TimesTen database and includes connection attributes to be used when connecting to the database. A DSN has the following characteristics: its maximum length is 32 characters; it cannot contain spaces; and it consists of ASCII characters except for the following: []{},;?*=!@\

Connection Type (if DNS is user-specified): C/S for client-server mode or Direct for direct mode

Connection String: Connection attributes including database attributes, first connection attributes, general connection attributes, NLS attributes, and Cache Connect attributes. (See Oracle In-Memory Database Cache User's Guide for information about the attributes.)

Oracle Password (for Cache): The password for the TimesTen user account on the Oracle Database. (See the TimesTen documentation for more information.)

For more information about SQL Developer support for TimesTen, see Section 1.18.

For detailed usage and reference information about Oracle TimesTen, see the online documentation that is included in the TimesTen installation. For additional information, go to: http://www.oracle.com/technetwork/database/timesten/

Access tab

For a connection to a Microsoft Access database, click Browse and find the database (.mdb) file. However, to be able to use the connection, you must first ensure that the system tables in the database file are readable by SQL Developer. To do this using Access 2003:

  1. Open the database file in Microsoft Access.

  2. Click Tools, then Options, and on the View tab ensure that System Objects are shown.

    (With Access 2007, to view the system tables, click the Office button, then Access Options, then Navigation Options; and in Display Options group ensure that Show System Objects is enabled.)

  3. Click Tools, then Security, and, if necessary, modify the user and group permissions for the MSysObjects, MsysQueries, and MSysRelationships tables as follows: select the table and give the Admin user at least Read Design and Read Data permission on the table.

  4. Save changes and close the Access database file.

  5. Create and test the connection in SQL Developer.

DB2 tab

The following information applies to a connection to an IBM DB2 database.

Note that to connect to an IBM DB2 database, you must first download the db2jcc.jar and db2jcc_license_cu.jar files, and then click Tools, then Preferences, and use the SQL Developer user preference pane for Database: Third Party JDBC Drivers to add these files.

Platform: UDB or iSeries.

Host Name: Host system for the IBM DB2 database.

Port: TCP/IP Port on which the IBM DB2 server will listen.

Enter Database: Name of the IBM DB2 database.

JDBC tab

The following information applies to a JDBC connection.

JDBC-ODBC Bridge or Other Third Party Driver: Indicates a JDBC to ODBC bridge driver or another third-party driver.

Data Source (JDBC-ODBC Bridge): Name of an existing ODBC data source.

Extra Parameters (JDBC-ODBC Bridge): Additional parameters for the connection.

JDBC URL (Other Third Party Driver): URL for connecting directly from Java to the database; overrides any other connection type specification.

Driver Class (Other Third Party Driver): The name of the driver class that will be used for the connection (for example, com.microsoft.jdbc.sqlserver.SQLServerDriver). This name can be found in the JDBC driver specification (usually shipped with the driver).

MySQL tab

The following information applies to a connection to a MySQL database.

Note that to connect to a MySQL database, you must first download the appropriate MySQL connection driver, and then click Tools, then Preferences, and use the SQL Developer user preference pane for Database: Third Party JDBC Drivers to add the driver.

Host Name: Host system for the MySQL database.

Port: TCP/IP Port on which the MySQL server will listen.

Choose Database: Name of the MySQL database.

Zero Date Handling: Because the MySQL JDBC driver cannot handle the default 0000-00-00 date, specify one of the following options for handling this date: Set to NULL to set it to a null value, or Round to 0001-01-01 to set it to 0001-01-01.

SQL Server and Sybase tabs

The following information applies to a connection to a Microsoft SQL Server or Sybase Adaptive Server database.

Note that to connect to a Microsoft SQL Server or Sybase Adaptive Server database, you must first download the appropriate connection driver, and then click Tools, then Preferences, and use the SQL Developer user preference pane for Database: Third Party JDBC Drivers to add the driver.

Host Name: Host system for the Microsoft SQL Server or Sybase Adaptive Server database.

Port: TCP/IP Port on which Microsoft SQL Server or Sybase Adaptive Server will listen.

Retrieve Database: Name of the Microsoft SQL Server or Sybase Adaptive Server database.

Teradata tab

The following information applies to a connection to a Teradata database.

Note that to connect to a Teradata database, you must first download the tdgssconfig.jar and a terajdbc4.jar files, and then click Tools, then Preferences, and use the SQL Developer user preference pane for Database: Third Party JDBC Drivers to add these files. (See also the readme.txt file that is included with the tdgssconfig.jar and a terajdbc4.jar files.)

Host Name: Host system for the Teradata database.

DBS Port: TCP/IP Port on which the Teradata server will listen.

Charset: Character set for the data.

TMODE: Transaction mode: ANSI, TERA (Teradata), or DEFAULT.

To add a connection parameter to the list in the box, click Add; to delete a connection parameter from the list, click Delete.

Creating and Editing Connections

To create a new connection when no connections exist, enter the connection information and click Connect. To test the connection before you create it, click Test.

To create a new connection when one or more connections already exist, click to select an existing connection, change the Connection Name to the desired name, edit other connection information as needed, and click Save or Connect to create the new connection. To test the connection before you create it, click Test.

To edit an existing connection, click in its entry in the Connection Name column, change any connection information except the connection name, and click Save or Connect. To test the connection before you save changes to it, click Test.

5.21 Rename Model (Migration)

This dialog box is displayed when you right-click a captured or converted model and select Rename Model. To rename the model, change the name and click OK.

5.22 Delete Confirmation (Migration)

This dialog box is displayed when you right-click a migration repository object under a captured or converted model and select Delete. To delete the object, click Yes.

If you do not want to be asked to confirm deletions of migration repository objects in the future, enable (check) Skip This Message Next Time. This will cause future deletions to occur when you right-click and select Delete.

5.23 Rename Database Item (Migration)

This dialog box is displayed when you right-click a database object under a captured or converted model and select Rename. To rename the object, change the name and click OK.

5.24 Select Connection

Use this dialog box to select a database connection for use with a specific SQL Developer feature (for example, the SQL worksheet or the Reports navigator). After you click OK, the interface for the component is displayed , with the current user the same as the one specified in the connection.

To create a new database connection, click the plus (+) icon; to edit the selected database connection, click the pencil icon. In both cases, a dialog box for specifying connection information is displayed (see Section 5.20, "Create/Edit/Select Database Connection").

5.25 Connection Information

Use this dialog box to specify the user name and password for the selected database connection.

If the specified user name does not exist in the database associated with the connection, or if the specified password is not the correct one for that user, the connection is refused.

5.26 No Connection Found

This dialog box is displayed when you attempt to perform an operation that requires a database connection, but no connection currently exists for that operation. For example, you might have opened a SQL file but not selected a connection, or the connection might have disconnected; or you might have tried to perform a schema copy operation without specifying both the From Schema and To Schema connections.

To select a connection in the SQL Worksheet, click OK to close this dialog box, then select a connection from the drop-down list in the SQL Worksheet icon bar.

5.27 Connection Rename Error

This dialog box is displayed when you attempt to rename a database connection to a name that is already used for another connection. For example, you might have forgotten to enter a new name for the connection that you want to rename.

To rename the connection, click OK to close this dialog box, then specify a unique connection name.

5.28 New Folder (Connections)

This dialog box enable you to create or rename a folder for organizing database connections. If you are creating a folder, enter the name of the new folder; if you are renaming a folder, replace the existing name with the desired new name. For information about using folders, see Section 1.4.1, "Using Folders to Group Connections".

5.29 Continue After Pause

This dialog box is displayed when a PAUSE statement is encountered in a script that you are running in the SQL Worksheet.

To continue execution at the statement after the PAUSE statement, click Yes. To stop execution and not continue with the statement after the PAUSE statement, click No.

5.30 Select Library

This dialog box is displayed when you click Browse in the Database pane when setting SQL Developer Preferences. Use this box to select the library for the specified JDBC driver class.

5.31 Create Library

This dialog box is displayed when you click New in the Select Library dialog box, which is displayed when you click Browse in the Database pane when setting SQL Developer Preferences. Use this box to create the library for the specified JDBC driver class.

5.32 Data Import Wizard (Load Data)

Use this wizard to import data into a table. For example, if you right-click the Tables node or a table name in the Connections navigator and select Import Data, you can specify the .source file (such as a spreadsheet or a delimited file) from which to import data. You create a table and import data into it, or import data into an existing table.

Data Preview

You can specify preferences that affect the preview display of data to be imported. Several default values are determined by the Database: Import (Load) user preferences.

Header: If this option is enabled, a header row (not data to be imported) is assumed to start before (Before Skip) or after (After Skip) the number of rows for Skip Rows.

Skip Rows: The number of rows at the start to skip (that is, not be considered as data to be imported). Thus, the combination of Header and Skip Rows determines the total number of rows at the start that are not considered data to be imported.

Format: Format of the file containing data to be imported. For example: .xls (Microsoft Excel), .csv (comma separated value), .dsv (delimiter separated value), or .tsv (tab separated value).

Preview Limit: The maximum number of rows of data to be displayed in the preview pane in the lower part of the box. When the wizard creates a new table, the preview data is used to calculate the size of the columns; therefore, ensure that the preview is a good sample of the data. The displayed data is affected by the Preview Limit and by the Database: Import (Load) user preference for Preview File Read Maximum, which limits the total number of bytes read from the file.

Encoding: Character set used for encoding of the data to be imported.

Delimiter, Line Terminator, Left Enclosure, Right Enclosure: Select or type the character in the input file that is used for each of these.

Import Method

Import Method: One of the following methods for loading the table definitions and data:

The number of remaining fields on this page and their availability depend on the load method and whether or not you invoked the wizard on a specific table.

Table Name: Target table in which to import the data.

Staging Table Name: External table to be used for loading the target table.

Commit and Drop Staging Table: If this option is enabled, the staging table is committed and dropped after the import operation. If this option is not enabled, the staging table is not committed or dropped.

Send Create Script to Worksheet: If this option is enabled, after you click Finish SQL Developer does not immediately perform the import operation, but instead opens a SQL Worksheet with statements that will be used if you click the Run Script icon in the worksheet.

Choose Columns

Available Columns: Lists the columns from which you can select for import into columns in the table. To select one or more columns, use the arrow buttons to move columns from Available to Selected.

Selected Columns: Lists the columns whose data is to be imported into columns in the database table. To change the order of a selected column in the list for the import operation, select it and use the up and down arrow buttons.

Column Definition

Enables you to specify information about the columns in database table into which to import the data.

Match By: The kind of automatic matching from source to target columns: by Name, by Position, or None (use the default). The default for existing tables is by name if a header is present in the file, and by position if no header is present.

Source Data Columns and Target Table Columns: You can select a source file data column to display its target (Oracle) column properties. For Data Type, select one of the supported types for this import operation. For a VARCHAR2 or NUMBER column, you must specify an appropriate Size/Precision value. You can specify whether the column value can be null (Nullable?), and you can specify a default value (Default).

Finish

Verify Parameters Before Import: You are encouraged to verify the import parameters. If any test fails, the Information column contains a brief explanation, and you can go back and fix any errors before you can click Finish.

To perform the import operation, click Finish. The action taken will depend on the load method selected and on whether you specified to send the script statements to a SQL Worksheet.

5.33 Export/Import Connection Descriptors

The Export Connection Descriptors dialog box exports information about one or more database connections to an XML file. The Import Connection Descriptors dialog box imports connections that have been exported. Connections that you import are added to any connections that already exist in SQL Developer.

File Name: Name of the XML file to contain exported information or that contains information to be imported. Use the Browse button to specify the location.

Connections: Names of connections that you can select for the export or import operation.

5.34 Create/Edit Database Link

The following information applies to a database link, which is a database object in one database that enables you to access objects on another database, as explained in Section 1.3.6, "Database Links (Public and Private)".

Public: If this option is checked, the database link is public (available to all users). If this option is not checked, the database link is private and is available only to you.

Schema: Database schema in which to create the database link.

Name: Name of the database link. Must be unique within a schema.

Host Name: The service name of a remote database. If you specify only the database name, Oracle Database implicitly appends the database domain to the connect string to create a complete service name. Therefore, if the database domain of the remote database is different from that of the current database, you must specify the complete service name.

Current User: Creates a current user database link. The current user must be a global user with a valid account on the remote database. If the database link is used directly, that is, not from within a stored object, then the current user is the same as the connected user.

Fixed User: Creates a fixed user database link, for which you specify the user name and password used to connect to the remote database.

Shared: If this option is checked, a single network connection is used to create a public database link that can be shared among multiple users. In this case, you must also specify the Authentication information.

Authentication - User Name and Password: The user name and password on the target instance. This information authenticates the user to the remote server and is required for security. The specified user and password must be a valid user and password on the remote instance.

DDL tab

You can review and save the SQL statement that SQL Developer will use to create the database link.

5.35 Create/Edit Index

The following information applies to an index, which is a database object that contains an entry for each value that appears in the indexed column or columns of the table or cluster and provides direct, fast access to rows, as explained in Section 1.3.10, "Indexes". For detailed information about all index-related options, see the CREATE INDEX reference section in Oracle Database SQL Language Reference.

Advanced: If this option is checked, the dialog box changes to enable you to set advanced properties (select Advanced on the left side).

Schema: Database schema that owns the table associated with the index.

Table: Name of the table associated with the index.

Name: Name of the index. Must be unique within a schema.

Index Type: Normal for a standard Oracle index, in which case you also specify non-unique, unique, or bitmap, as well as one or more index expressions; or Text for an Oracle Text index (created with INDEXTYPE IS CTXSYS.CONTEXT), in which case you specify the column to be indexed.

Non-unique means that the index can contain multiple identical values; Unique means that no duplicate values are permitted; Bitmap stores rowids associated with a key value as a bitmap.

Index: A list of index expressions, that is, the table columns or column expressions in the index. To add an index expression, click the Add Column Expression (+) icon; this adds a column name here and in Column Expression, where you can edit it. To delete an index expression, click the Remove Column Expression (X) icon; to move an index expression up or down in the list, click the Move Column Up and Move Column Down icons. An index must have at least one index expression.

For example, to create an index on the AUTHOR_LAST_NAME column of the BOOKS table from the tutorial (see Section 4.1, "Create a Table (BOOKS)"), click the + icon, and select AUTHOR_LAST_NAME in Column Name or Expression (next field), which changes BOOKS to AUTHOR_LAST_NAME in the Index field.

Column Name or Expression: A column name or column expression. A column expression is an expression built from columns, constants, SQL functions, and user-defined functions. When you specify a column expression, you create a function-based index.

Order: ASC for an ascending index (index values sorted in ascending order); DESC for a descending index (index values sorted in descending order).

Properties

Enables you to specify index properties such as compression, parallelism, and storage options.

Compress: If this option is checked, key compression is enabled, which eliminates repeated occurrence of key column values and may substantially reduce storage. If this option is checked, you can enter an integer to specify the prefix length (number of prefix columns to compress).

Parallel: If this option is checked, parallel creation of the index is enabled. You can also enter an integer in the text box to specify the degree of parallelism, which is the number of parallel threads used in the parallel operation. (Each parallel thread may use one or two parallel execution servers.) If you specify Parallel without entering an integer, the optimum degree of parallelism is automatically calculated.

Storage Options: Enables you to specify storage options for the index. Displays the Storage Options dialog box.

Partitions

When applicable, enables you to specify whether the index is not partitioned, locally partitioned, or globally partitioned. If you specify Local or Global, additional fields are displayed relevant to the selected partitioning option.

Local: Specifies that the index is partitioned on the same columns, with the same number of partitions and the same partition bounds as its associated table. Oracle Database automatically maintains local index partitioning as the underlying table is repartitioned.

Global: Specifies that the partitioning of the index is user defined and is not equipartitioned with the underlying table. You can partition a global index by range or by hash. In both cases, you can specify up to 32 columns as partitioning key columns. The partitioning column list must specify a left prefix of the index column list. If the index is defined on columns a, b, and c, then for the columns you can specify (a, b, c), or (a, b), or (a, c), but you cannot specify (b, c) or (c) or (b, a). If you omit the partition names, then Oracle Database assigns names of the form SYS_Pn.

5.36 Create Filter

This dialog box is displayed when you click New to add a user-defined exclusion filter when importing files into a repository.

Filter: Shell filename pattern, which can contain both normal characters and meta-characters, including wildcards. (See the supplied Selected Filters list for typical patterns.) For example, to exclude files with the extension xyz, enter the following: *.xyz

When you click OK, the specified filter is added to the Selected Filters list.

5.37 Create/Edit Job Class

This dialog box or pane is used for creating a new Oracle Scheduler job class or editing an existing job class. For more information about job scheduling, see Section 1.12, "Scheduling Jobs Using SQL Developer".

(To create a job class, SQL Developer internally uses the DBMS_SCHEDULER.CREATE_JOB_CLASS procedure.)

Name: Name of the job class. If you specify a schema, it must be SYS. For an existing job class, this field is read-only; to change the name, you must drop the job class and create a new job class with the desired name.

Description: Optional text string that can be used to describe the job class.

Logging Level: Specifies how much information is written to the job log: RUNS (detailed information for all runs of each job in this class), FULL (RUNS plus all operations performed on all jobs in this class), or OFF (no logging).

Log Retention Period (days): Number of days that job log entries for jobs in this class are retained. The range of valid values is 0 through 999. If set to 0, no history is kept. If NULL (the default), retention days are set by the log_history Scheduler attribute (set with SET_SCHEDULER_ATTRIBUTE).

Resource Consumer Group: Resource consumer group this class is associated with. If no resource consumer group is specified, the job class is associated with the default resource consumer group.

Service Name: The database service that the jobs in this class will have affinity to. If no service is specified, the job class will belong to the default service, which means it will have no service affinity and any one of the database instances within the cluster might run the job.

5.38 Create/Edit Materialized View Log

User this dialog box to create of edit a materialized view log, which is a table associated with the master table of a materialized view. For more information, see Section 1.3.15, "Materialized View Logs".

Schema: Database schema in which to create the materialized view log.

Name: Name of the master table of the materialized view to be associated with this materialized view log.

Properties tab

Tablespace: Tablespace in which the materialized view log is to be created.

Logging: LOGGING or NOLOGGING, to establish the logging characteristics for the materialized view log.

Row ID: Yes indicates that the rowid of all rows changed should be recorded in the materialized view log; No indicates that the rowid of all rows changed should not be recorded in the materialized view log.

Primary Key: Yes indicates that the primary key of all rows changed should be recorded in the materialized view log; No indicates that the primary key of all rows changed should not be recorded in the materialized view log.

New Values: INCLUDING saves both old and new values for update DML operations in the materialized view log; EXCLUDING disables the recording of new values in the materialized view log. If this log is for a table on which you have a single-table materialized aggregate view, and if you want the materialized view to be eligible for fast refresh, you must specify INCLUDING.

Cache: For data that will be accessed frequently, CACHE specifies that the blocks retrieved for this log are placed at the most recently used end of the least recently used (LRU) list in the buffer cache when a full table scan is performed. This attribute is useful for small lookup tables. NOCACHE specifies that the blocks are placed at the least recently used end of the LRU list.

Parallel: If this option is checked, parallel operations will be supported for the materialized view log.

Object ID: For a log on an object table only: Yes indicates that the system-generated or user-defined object identifier of every modified row should be recorded in the materialized view log; No indicates that the system-generated or user-defined object identifier of every modified row should not be recorded in the materialized view log.

Sequence: Yes indicates that a sequence value providing additional ordering information should be recorded in the materialized view log; No indicates that a sequence value providing additional ordering information should not be recorded in the materialized view log. Sequence numbers (that is, Yes for this option) are necessary to support fast refresh after some update scenarios.

Available Filter Columns: Additional columns, which are non-primary-key columns referenced by subquery materialized views, to be recorded in the materialized view log. To select one or more filter columns, use the arrow buttons to move columns from Available to Selected.

DDL tab

You can view a SQL CREATE statement that reflects the current definition of the object, or a SQL ALTER statement to modify an existing object to reflect your changes.

To save the SQL statement to a script file, click Save and specify the location and file name.

5.39 Create PL/SQL Package

Use this dialog box to create a package to contain PL/SQL subprograms (functions or procedures, or a combination).

Schema: Database schema in which to create the PL/SQL package.

Name: Name of the package. Must be unique within a schema.

Add New Source in Lowercase: If this option is checked, new text is entered in lowercase regardless of the case in which you type it. This option affects only the appearance of the code, because PL/SQL is not case-sensitive in its execution.

The package is created and is displayed in the Editor window, where you can enter the details.

5.40 Create PL/SQL Subprogram (Function or Procedure)

Use this dialog box to create a PL/SQL subprogram (function or procedure). A function returns a value; a procedure does not return a value.

Specify the information for the package and for each parameter, then click OK to create the subprogram and have it displayed in the Editor window, where you can enter the details.

Schema: Database schema in which to create the PL/SQL subprogram.

Name: Name of the subprogram. Must be unique within a schema.

Add New Source in Lowercase: If this option is checked, new text is entered in lowercase regardless of the case in which you type it. This option affects only the appearance of the code, because PL/SQL is not case-sensitive in its execution.

Parameters tab

For each parameter in the procedure to be created, specify the following information.

Name: Name of the parameter.

Type: Data type of the parameter.

Mode: IN for input only, OUT for output only, or IN OUT for input and output (that is, the output is stored in the parameter overwriting its initial input value).

Default Value: Optionally, the default value if the parameter is omitted or specified as null when the subprogram is called.

To add a parameter, click the Add (+) icon; to delete a parameter, click the Remove (X) icon; to move a parameter up or down in the list, click the up-arrow or down-arrow icon.

DDL tab

You can view a SQL CREATE statement that reflects the current definition of the object, or a SQL ALTER statement to modify an existing object to reflect your changes.

5.41 Create Program

This dialog box is used for creating a new Oracle Scheduler program, which can include creating a new program using details from an existing program (Create Like). For more information about job scheduling, see Section 1.12, "Scheduling Jobs Using SQL Developer".

(To create a program, SQL Developer internally uses the DBMS_SCHEDULER.CREATE_PROGRAM procedure.)

Name: Name of the program. The name has to be unique in the SQL namespace. For example, a program cannot have the same name as a table in a schema.

Schema: Schema for the program.

Description: Optional text string that can be used to describe the program.

Enabled: If this option is specified, validity checks will be made and the program will be created enabled if all the checks are successful. If this option is not specified, the program is not created enabled.

For the rest of the definition, click the tab for the program type (PL/SQL code, Stored Procedure, or Executable file).

PL/SQL tab

The program is a PL/SQL block. Job or program arguments are not supported when the job or program type is PLSQL_BLOCK. In this case, the number of arguments must be 0.

Enter or paste in the complete PL/SQL code, or edit the existing code.

Stored Procedure tab

The program is a PL/SQL or Java stored procedure, or an external C subprogram. Only procedures, not functions with return values, are supported. PL/SQL procedures with INOUT or OUT arguments are not supported.

Schema: Schema of the stored procedure. I not specified, the schema of the job is assumed.

Procedure: Name of the stored procedure.

Arguments: For each argument: name, data type, default value, and whether it is an input, output, or input/output argument.

Executable tab

The program is external to the database. External programs implies anything that can be executed from the operating system's command line. AnyData arguments are not supported.

Executable: Name of the external executable, including the full path name, but excluding any command-line arguments. If the action starts with a single question mark ('?'), the question mark is replaced by the path to the Oracle home directory for a local job or to the Scheduler agent home for a remote job. If the action contains an at-sign ('@') and the job is local, the at-sign is replaced with the SID of the current Oracle instance.

Arguments: For each argument: name, data type, default value, and whether it is an input, output, or input/output argument

5.42 Create/Edit Role

This dialog box is used for creating a new database role or editing an existing database role. The role properties are grouped under several tabs.

To create or edit a role, the user associated with your database connection must have the DBA role. You should also be familiar with the main concepts and techniques documented in Oracle Database Administrator's Guide.

Role tab

Specifies general properties for the role.

Role Name: The role name string. For an existing role, this field is read-only; to change the name, you must drop the user and create a new user with the desired name.

New Password: If you specify a password, the role is a local role and a user must specify the password to the database when enabling the role. If you do not specify a password, the role is authorized by the database and no password is required to enable the role.

If you specify a password, you must type the same password string for Confirm Password.

Granted Roles tab

Specifies roles to be granted to the role being created or modified. For each listed role, you can check Granted to grant the specified role to this role, Admin to permit the role to grant this role to other users or roles, and Default to use the default settings for Granted and Admin.

For convenience, you can click buttons to affect all settings (Grant All, Revoke All, Admin All, Admin None, Default All, Default None); then, you can specify other settings for individual roles.

System Privileges tab

Specifies privileges to be granted to the user. For each privilege, you can check Granted to grant the privilege, and Admin Option to permit the user to grant the privilege to other users.

For convenience, you can click buttons to affect all settings (Grant All, Revoke All, Admin All, Admin None); then, you can specify other settings for individual privileges.

SQL tab

Displays the SQL statements that SQL Developer will use to create (after executing a CREATE ROLE statement) a new user or to edit an existing role. This display is read-only; if you want to make any changes, go back to the relevant tabs and make the changes there.

5.43 Create/Edit Sequence

The following information applies to a sequence, which is an object from which multiple users may generate unique integers. You can use sequences to automatically generate primary key values.

Schema: Database schema in which to create the sequence.

Name: Name of the sequence. Must be unique within a schema.

Increment: Interval between successive numbers in a sequence.

Start with: Starting value of the sequence.

Min value: Lowest possible value for the sequence. The default is 1 for an ascending sequence and -(10^26) for a descending sequence.

Max value: Highest possible value for the sequence. The default is 10^27 for an ascending sequence and -1 for a descending sequence.

Cycle: Indicates whether the sequence "wraps around" to reuse numbers after reaching its maximum value (for an ascending sequence) or its minimum value (for a descending sequence). If cycling of values is not enabled, the sequence cannot generate more values after reaching its maximum or minimum value.

Cache and Cache size: If Cache is checked, sequence values are preallocated in cache, which can improve application performance; Cache size indicates the number of sequence values preallocated in cache. If Cache is not checked, sequence values are not preallocated in cache.

Order: Indicates whether sequence numbers are generated in the order in which they are requested. If no ordering is specified, sequence numbers are not guaranteed to be in the order in which they were requested.

DDL tab

You can review the SQL statement that SQL Developer will use to create a new sequence or that reflects any changes you have made to the sequence properties.

5.44 Create SQL File

Use this dialog box to create a SQL script file and to open the file in a SQL Worksheet for editing.

File Name: Name and extension of the file to be created. The default and recommended extension is .sql.

Directory Name: Directory path for the file. To specify a directory, you can click Browse. The default directory is the Location of User-Related Information.

5.45 Create/Edit Synonym

The following information applies to a synonym, which is an alternative name for a table, view, sequence, procedure, stored function, package, materialized view, Java class database object, user-defined object type, or another synonym.

Public: If this option is checked, the synonym is accessible to all users. (However each user must have appropriate privileges on the underlying object in order to use the synonym.) If this option is not checked, the synonym is a private synonym, and is accessible only within its schema.

Schema: Database schema in which to create the synonym.

Name: Name of the synonym. A private synonym must be unique within its schema; a public synonym must be unique within the database.

For - Referenced Schema: Schema containing the object or name to which this synonym refers.

Object Based: Specify the object to which this synonym refers.

Name Based: Enter the name of the object to which this synonym refers.

DDL tab

You can review the SQL statement that SQL Developer will use to create a new synonym or that reflects any changes you have made to the synonym properties.

5.46 Create Table (quick creation)

This dialog box (if you do not check the Advanced box) creates a new table quickly by specifying columns and some frequently used features. (If you need to add or change features after you create the table, you can edit the table by clicking the Modify icon while viewing the table or by right-clicking its name in the Connections navigator and selecting Properties, which displays the Create/Edit Table (with advanced options) dialog box.)

To create a new table, the only things you must do are specify the schema and the table name, add the necessary columns, and click OK. Although it is not required, you should also specify a primary key.

Advanced: If this option is checked, the dialog box changes to include an extended set of features for creating the table. For example, you must check this option if you want to create a partitioned table, an index-organized table, or an external table.

Schema: Database schema in which to create the table.

Name: Name of the table. Must be unique within a schema.

Table tab (quick creation)

Specifies properties for each column in the table.

Columns: Lists the columns currently in the table.

Note:

To add a column after the currently selected column, click Add Column; to delete a column, select it and click Remove Column.

Column Name: Name of the column. Must be unique within the table. Suggestion: For a new column, replace any default name, such as COLUMN1.

Type: Data type for the column. The drop-down list includes only selected frequently used data types. To specify any other type for the column, you must use the Columns panel of the Create/Edit Table (with advanced options) dialog box.

Size: For VARCHAR2 data, the maximum size of the column data; for NUMBER data, the maximum number of digits.

Not Null: If this option is checked, the column must contain data; you cannot specify no value or an explicit null value for this column when you insert a row. If this option is not checked, the column can contain either data or no data.

Primary Key: If this option is checked, the column is the primary key, or part of the primary key, for the table. The primary key is the column, or set of columns, that uniquely identifies each row in the table. A primary key column cannot be null.

If you want to have the primary key values automatically populated by a convenient method that uses a before-insert trigger and a sequence, then before you finish creating the table, you must check the Advanced box and use the Primary Key tab, starting with the Populate Primary Key Column field.

To add another column, click Add Column. When you are finished adding columns, either click OK or click the DDL tab to review the CREATE TABLE statement.

DDL tab (quick creation)

You can review and save the CREATE TABLE statement that SQL Developer will use to create a new table or that reflects any changes you have made to the table properties. If you want to make any changes, go back to the Table tab and make the changes there.

When you are finished, click OK.

5.47 Create/Edit Table (with advanced options)

The table dialog box is used for creating a new table or editing an existing table. The table properties are grouped in several panes.

To create a new table, the only things you must do are specify the schema and the table name, add the necessary columns, and click OK. Although it is not required, you should also specify a primary key using the Primary Key pane. For other table-related features, use the appropriate tabs; the order in which you visit tabs usually does not matter, although you might find it convenient to visit them in the sequence in this topic. If you are editing an existing table, you can visit the tabs in any order.

If you click OK before you are finished creating or editing the table, right-click the table name in the Connections navigator, select Edit, and continue creating or editing the table.

Schema: Database schema in which to create the table.

Name: Name of the table. Must be unique within a schema.

Type: The type of table:

The following panes may also be available. (Some panes are available only for tables of specific types or with specific features.)

Columns pane

Specifies properties for each column in the table.

Columns: Lists the columns currently in the table. To add a column, click the Add Column (+) icon; to delete a column, select it and click the Remove Column (X) icon; to move a column up or down in the table definition, select it and use the up-arrow and down-arrow buttons.

To copy in column definitions from another table, click the Copy Columns icon to display the Copy Columns dialog box.

Note:

After you add a column, to add another column, click the Add Column (+) icon.

Name: Name of the column. Must be unique within the table. Suggestion: For a new column, replace any default name, such as COLUMN1.

Datatype: Simple indicates a simple (non-object) data type; Complex indicates an object type. For a complex type, you must specify the schema and the type name (for example, MDSYS and SDO_GEOMETRY for the Oracle Spatial geometry type).

Type: Name of the data type. Most of the remaining information depends on the specific type.

Precision: For numeric data, the precision (total number of significant digits that can be represented) of the column data.

Scale: For numeric data, the scale (number of digits after the decimal point) of the column data.

Size: For character data, the maximum size of the column data.

Units: For character data, the units represented by the Size: BYTE for bytes or CHAR for characters. This attribute is important if the database can contain data in Unicode format, with multiple bytes for each character.

Default: For relevant types, the default value inserted into the column if no value is specified when a row is inserted.

Cannot be NULL: If this option is checked, the column must contain data; you cannot specify no value or an explicit null value for this column when you insert a row. If this option is not checked, the column can contain either data or no data. A primary key column (see Primary Key pane) cannot be null.

Comment: Optional descriptive comment about the column.

To add another column, click the Add Column (+) icon.

Primary Key pane

Specifies the primary key for the table. The primary key is the column, or set of columns, that uniquely identifies each row in the table.

An index is automatically created on the primary key.

Name: Name of the constraint to be associated with the primary key definition. Must be unique within the database.

Enabled: If this option is checked, the primary key constraint is enforced: that is, the data in the primary key column (or set of columns) must be unique and not null.

Available Columns: Lists the columns that are available to be added to the primary key definition.

Selected Columns: Lists the columns that are included in the primary key definition.

To add a column to the primary key definition, select it in Available Columns and click the Add (>) icon; to remove a column from the primary key definition, select it in Selected Columns and click the Remove (<) icon. To move all columns from available to selected (or the reverse), use the Add All (>>) or Remove All (<<) icon. To move a column up or down in the primary key definition, select it in Selected Columns and use the arrow buttons.

The remaining fields (Populate Primary Key Column through Trigger Name) appear only when you are creating a table. They are not available when you are editing an existing table.

Populate Primary Key Column: When you are creating a table, if you want to use a trigger and a sequence to have a unique value automatically inserted into the primary key column when you insert a new row, specify the primary key column.

From: An existing sequence that you select, or a new sequence whose name you enter. (For a new sequence, SQL Developer creates the sequence automatically using the name that you enter.)

Trigger Name: The name for the before-insert trigger that will be automatically created. This trigger uses the sequence to generate a new value for the primary key when a row is inserted. For an example of using this technique, see the tutorial section Section 4.3, "Create a Table (TRANSACTIONS)".

Unique Constraints pane

Specifies one or more unique constraints for the table. A unique constraint specifies a column, or set of columns, whose data values must be unique: each data value must not be null, and it must not be the same as any other value in the column.

For a multicolumn unique constraint, the combination of values must be unique, and no column in the constraint definition can have a null value. For example, if you specify the office_name and city columns for a unique constraint, you could not have two Sales offices in Chicago, but you could have a Sales office in Chicago and a Sales office in Atlanta.

Unique Constraints: Lists the unique constraints currently defined on the table. To add a unique constraint, click the Add button; to delete a unique constraint, select it and click the Remove button.

Note:

After you add a unique constraint, to add another unique constraint, click the Add button.

Name: Name of the unique constraint. Must be unique within the database.

Enabled: If this option is checked, the unique constraint is enforced.

Available Columns: Lists the columns that are available to be added to the unique constraint definition.

Selected Columns: Lists the columns that are included in the unique constraint definition.

To add a column to the unique constraint definition, select it in Available Columns and click the Add (>) icon; to remove a column from the unique constraint definition, select it in Selected Columns and click the Remove (<) icon. To move all columns from available to selected (or the reverse), use the Add All (>>) or Remove All (<<) icon. To move a column up or down in the unique constraint definition, select it in Selected Columns and use the arrow buttons.

Foreign Keys pane

Specifies one or more foreign keys for the table. A foreign key specifies a column ("local column"), each of whose data values must match a value in the primary key or unique constraint of another table.

Foreign Keys: Lists the foreign keys currently defined on the table. To add a foreign key, click the Add button; to delete a foreign key, select it and click the Remove button.

Note:

After you add a foreign key, to add another foreign key, click the Add button.

Name: Name of the foreign key definition. Must be unique within the database.

Enabled: If this option is checked, the foreign key is enforced.

Referenced Schema: Name of the schema containing the table with the primary key or unique constraint to which this foreign key refers.

Referenced Table: Name of the table with the primary key or unique constraint to which this foreign key refers.

Referenced Constraint: Name of the primary key or unique constraint to which this foreign key refers.

Associations: Local Column: Lists the column in the currently selected (local) table that is included in the foreign key definition. For each local column in the foreign key definition, select the name of a column in the local table.

Associations: Referenced Column on [table]: For each local column, identifies the column in the other (foreign) table that must have a value matching the value in the local column.

Check Constraints pane

Specifies one or more check constraints for the table. A check constraint specifies a condition that must be met when a row is inserted into the table or when an existing row is modified.

Check Constraints: Lists the check constraints currently defined on the table. To add a check constraint, click the Add button; to delete a check constraint, select it and click the Remove button.

Note:

After you add a check constraint, to add another check constraint, click the Add button.

Name: Name of the check constraint definition. Must be unique within the database.

Enabled: If this option is checked, the check constraint is enforced.

Condition: Condition that must be met for a row. Can be any valid CHECK clause (without the CHECK keyword). For example, to indicate that the value in a numeric column named RATING must be from 1 to 10, you can specify: rating >=1 and rating <= 10

To add another check constraint, click the Add button.

Indexes pane

Specifies properties for each index on the table.

Indexes: Lists the indexes currently defined on the table. To add an index, click the Add Index (+) icon; to delete an index, select it and click the Remove Index (X) icon.

Note:

After you add an index, to add another index, click the Add Index (+) icon.

Name: Name of the index. Must be unique within the schema.

Index: A list of index expressions, that is, the table columns or column expressions in the index. To add an index expression, click the Add Column Expression (+) icon; this adds a column name here and in Column Expression, where you can edit it. To delete an index expression, click the Remove Column Expression (X) icon; to move an index expression up or down in the list, click the Move Column Up and Move Column Down icons. An index must have at least one index expression.

For example, to create an index on the AUTHOR_LAST_NAME column of the BOOKS table from the tutorial (see Create a Table (BOOKS)), click the + icon, and select AUTHOR_LAST_NAME in Column Name or Expression (next field), which changes BOOKS to AUTHOR_LAST_NAME in the Index field.

Column Name or Expression: A column name or column expression. A column expression is an expression built from columns, constants, SQL functions, and user-defined functions. When you specify a column expression, you create a function-based index.

Order: ASC for an ascending index (index values sorted in ascending order); DESC for a descending index (index values sorted in descending order).

Column Sequences pane

Enables you to specify sequences and before-insert triggers to be used in populating a column with values. This approach is especially convenient for automatically populating primary key column values with unique values.

Column: Name of the column for which a sequence and a trigger are to be used to insert unique values. The data type of the column must be numeric.

Sequence: None causes no sequence to be used; Existing Sequence uses the sequence that you specify; New Sequence creates a new sequence with a default or specified name.

Trigger: Before-insert trigger that automatically inserts the next value of the specified sequence into the column when a new row is inserted.

Table Properties pane

Enables you to specify table properties such as compression, parallelism, and storage options.

Compress (heap-organized tables only): If this option is checked, data segments are compressed to reduce disk use. This clause is especially useful in environments such as data warehouses, where the amount of insert and update operations is small, and in OLTP environments.

Parallel: If this option is checked, parallel creation of the table is enabled, and the default degree of parallelism is set for queries and the DML INSERT, UPDATE, DELETE, and MERGE statements after table creation. You can also enter an integer in the text box to specify the degree of parallelism, which is the number of parallel threads used in the parallel operation. (Each parallel thread may use one or two parallel execution servers.) If you specify Parallel without entering an integer, the optimum degree of parallelism is automatically calculated.

Storage Options: Enables you to specify storage options for the table. Displays the Storage Options dialog box.

LOB Parameters pane

Specifies storage options for LOB (large object) columns, enabling you to override the default storage options.

Column: Name of the LOB column.

LOB Parameters: If this option is checked, the specified values for the remaining field are used. If this option is not checked, the default values for all fields are used.

Segment: LOB segment ID.

Tablespace: Name of the tablespace for the LOB data.

Store in Row: If this option is checked, the LOB value is stored in the row (inline) if its length is less than approximately 4000 bytes minus system control information.

Cache: Specifies how Oracle Database should store blocks in the buffer cache:

Retention: If this option is checked, old versions of this LOB column and retained. You can specify this option only if the database is running in automatic undo mode and if you do not specify a Pct Version value.

Logging: <DEFAULT> means to use the Oracle Database default. ON means that the table creation and any subsequent direct loader (SQL*Loader) and direct-path INSERT operations against the table, partition, or LOB storage are logged in the redo log file. OFF means that these operations are not logged in the redo log file.

Chunk: The number of bytes to be allocated for LOB manipulation. If the value is not a multiple of the database block size, then the database rounds up in bytes to the next multiple. The maximum value is 32768 (32K), which is the largest Oracle Database block size allowed. The default CHUNK size is one Oracle Database block.

Pct Version: Specifies the maximum percentage of overall LOB storage space used for maintaining old versions of the LOB. The default value is 10, meaning that older versions of the LOB data are not overwritten until they consume 10% of the overall LOB storage space. You can specify a Pct Version value whether the database is running in manual mode (where it is the default) or automatic undo mode (where Retention is the default). You cannot specify both a Pct Version value and the Retention option.

Free Pools: Specifies the number of groups of free lists for the LOB segment, usually the number of instances in a Real Application Clusters environment or 1 for a single-instance database. You can specify this option only if the database is running in automatic undo mode. You cannot specify both a Free Pools value and the Free Lists fields.

Extents - Initial: Size of the first extent of the table. Specify K (kilobytes) or M (megabytes) for the unit associated with the number.

Extents - Next: Size of the next extent to be allocated to the table. Specify K (kilobytes) or M (megabytes) for the unit associated with the number.

Extents - Min: Minimum number of extents allocated when the table is created.

Extents - Max: Maximum number of extents allocated when the table is created. Unlimited (if checked) means that there is no maximum (and any specified maximum is ignored).

Extents - Pct Increase: Percentage that each extent grows over the previous extent.

Buffer Pool: <DEFAULT> means to use the Oracle Database default. KEEP means to put blocks from the segment into the Keep buffer pool; maintaining an appropriately sized Keep buffer pool lets Oracle retain the database object in memory to avoid I/O operations. RECYCLE means to put blocks from the segment into the Recycle pool; an appropriately sized Recycle pool reduces the number of objects whose default pool is the Recycle pool from taking up unnecessary cache space.

Free Lists: Number of free lists for each of the free list groups for the table. The default and minimum value for this parameter is 1, meaning that each free list group contains one free list.

Free List Groups: Number of groups of free lists for the table. The default and minimum value for this parameter is 1. Oracle uses the instance number of Real Application Clusters instances to map each instance to one free list group.

Partitioning pane

Specifies partitioning options for a partitioned table, which is a table that is organized into smaller and more manageable pieces called partitions. SQL queries and DML statements do not need to be modified in order to access partitioned tables; however, after partitions are defined, DDL statements can access and manipulate individuals partitions rather than entire tables or indexes. Also, partitioning is entirely transparent to applications.

Partition By: The type of partitioning: RANGE partitions the table on ranges of values from the column list (which for an index-organized tablet must be a subset of the primary key columns of the table); HASH partitions the table using the hash method (rows assigned to partitions using a hash function on values found in columns designated as the partitioning key); LIST partitions the table on lists of literal values from column (useful for controlling how individual rows map to specific partitions).

Available: Lists the columns whose values are available to be used in assigning rows to partitions.

Selected: Lists the column whose values are to be used in assigning rows to partitions.

To add a column to the partitioning definition, select it in Available Columns and click the Add (>) icon; to remove a column from the partitioning definition, select it in Selected Columns and click the Remove (<) icon. To move all columns from available to selected (or the reverse), use the Add All (>>) or Remove All (<<) icon. To move a column up or down in the partitioning definition, select it in Selected Columns and use the arrow buttons.

Subpartition By: The partitioning type to be used to create subpartitions within each range partition. Use the Available and Selected column lists select and deselect a column for subpartitioning.

Subpartition Templates pane

Specifies subpartitioning options for a partitioned table. The options depend on the subpartition type, and might include the following.

Hash Quantity: Hash subpartition quantity.

Tablespaces: Available and Selected tablespaces for storage of the data in a subpartition.

Subpartition Templates: Specifications (subpartition templates) to control the placement of rows in each subpartition. Click the Add (+) icon to add a subpartition template that is appropriate for the subpartition type.

Subpartition Details: For each subpartition template, specify a name and (if relevant) a value or set of values that is appropriate for the subpartition type.

Storage: Enables you to specify a tablespace for the subpartition.

Partition Definitions pane

Defines each partition for a partitioned table. The options depend on the partition type, and might include the following.

Partitions: Specifications to control the placement of rows in each partition. Click the Add (+) icon to add a partition specification that is appropriate for the partition type.

Partition Details: For each partition specification, specify a name and (if relevant) a value or set of values that is appropriate for the subpartition type.

Storage: Enables you to specify a tablespace for the partition.

Subpartitions: Enables you to specify subpartition information.

Index Organized Properties pane

Specifies options for an index-organized table, which is a table in which the rows, both primary key column values and nonkey column values, are maintained in an index built on the primary key. Index-organized tables are best suited for primary key-based access and manipulation.

PCTTHRESHOLD: The percentage of space reserved in the index block for an index-organized table row; must be large enough to hold the primary key. All trailing columns of a row, starting with the column that causes the specified threshold to be exceeded, are stored in the overflow segment. PCTTHRESHOLD must be a value from 1 to 50; the default is 50.

Key Compression: If this option is checked, key compression is enabled, which eliminates repeated occurrence of primary key column values in index-organized tables. In the box to the right of this field, you can specify the prefix length, which is the number of prefix columns to compress. (This value can be from 1 to the number of primary key columns minus 1; the default prefix length is the number of primary key columns minus 1.)

Include Column: Column at which to divide an index-organized table row into index and overflow portions. The primary key columns are always stored in the index. The Include Column can be either the last primary key column or any non-primary-key column. All non-primary-key columns that follow the Include Column are stored in the overflow data segment.

Mapping Table: If this option is checked, SQL Developer creates a mapping of local to physical ROWIDs and store them in a heap-organized table. This mapping is needed in order to create a bitmap index on the index-organized table. If the index-organized table is partitioned, then the mapping table is also partitioned and its partitions have the same name and physical attributes as the base table partitions.

Overflow: Specifications for the overflow segment. The options are the same as in the Storage Options dialog box.

External Table Properties pane

Specifies options for an external table, which is a read-only table whose metadata is stored in the database but whose data in stored outside the database. Among other capabilities, external tables enable you to query data without first loading it into the database.

Access Driver: The access driver of the external table. The access driver is the API that interprets the external data for the database: ORACLE_LOADER or ORACLE_DATAPUMP. You must specify the ORACLE_DATAPUMP access driver if you specify the AS subquery clause to unload data from one Oracle database and reload it into the same database or a different Oracle database.

Access Type: Type of data to be automatically converted during loads and unloads: BLOB or CLOB.

Default Directory: A default directory object corresponding to a directory on the file system where the external data sources may reside. The default directory can also be used by the access driver to store auxiliary files such as error logs.

Project Column: Determines how the access driver validates the rows of an external table in subsequent queries. ALL processes all column values, regardless of which columns are selected, and validates only those rows with fully valid column entries. If any column value would raise an error, such as a data type conversion error, the row is rejected even if that column was not referenced in the select list. REFERENCED processes only those columns in the select list.

The ALL setting guarantees consistent result sets. The REFERENCED setting can result in different numbers of rows returned, depending on the columns referenced in subsequent queries, but is faster than the ALL setting. If a subsequent query selects all columns of the external table, then the settings behave identically.

Reject Limit: The number of conversion errors can occur during a query of the external data before an Oracle Database error is returned and the query is aborted.

Access Parameters: Values to the parameters of the specific access driver for this external table.

Location Specifications: One or more external data sources. Each is usually a file, but it need not be. Oracle Database does not interpret this clause; it is up to the access driver to interpret this information in the context of the external data. Use the Add (+) icon to add each location specification.

Comment pane

Optional descriptive comment about the table.

DDL pane

You can review and save the CREATE TABLE statement that SQL Developer will use to create a new table or that reflects any changes you have made to the table properties. If you want to make any changes, go back to the relevant tabs and make the changes there.

To save the SQL statement to a script file, click Save and specify the location and file name.

When you are finished, click OK.

5.48 Storage Options

This dialog box is displayed if you click Storage Options in the Properties pane when creating or editing a table or an index. It enables you to override the default storage options.

Tablespace: Name of the tablespace for the table or index.

Pct Free: Percentage of space in each of the data blocks of the table or index reserved for future updates. You can enter a value from 0 through 99.

Pct Used: Minimum percentage of used space that Oracle maintains for each data block. A block becomes a candidate for row insertions when its used space falls below the Pct Used value. You can enter a value from 1 through 99.

Logging: <DEFAULT> means to use the Oracle Database default. ON means that the table creation and any subsequent direct loader (SQL*Loader) and direct-path INSERT operations against the table, partition, or LOB storage are logged in the redo log file. OFF means that these operations are not logged in the redo log file.

Ini Trans: Number of update transaction entries for which space is initially reserved in the data block header.

Max Trans: Number of transaction entries that could concurrently use data in a data block. This parameter has been deprecated. Oracle Database now automatically allows up to 255 concurrent update transactions for any data block, depending on the available space in the block.

Extents - Initial: Size of the first extent of the table or index. Specify K (kilobytes) or M (megabytes) for the unit associated with the number.

Extents - Next: Size of the next extent to be allocated to the table or index. Specify K (kilobytes) or M (megabytes) for the unit associated with the number.

Extents - Min: Minimum number of extents allocated when the table or index is created.

Extents - Max: Maximum number of extents allocated when the table or index is created. Unlimited (if checked) means that there is no maximum (and any specified maximum is ignored).

Pct Increase: Percentage that each extent grows over the previous extent.

Buffer Pool: <DEFAULT> means to use the Oracle Database default. KEEP means to put blocks from the segment into the Keep buffer pool; maintaining an appropriately sized Keep buffer pool lets Oracle retain the database object in memory to avoid I/O operations. RECYCLE means to put blocks from the segment into the Recycle pool; an appropriately sized Recycle pool reduces the number of objects whose default pool is the Recycle pool from taking up unnecessary cache space.

Free Lists: Number of free lists for each of the free list groups for the table or index. The default and minimum value for this parameter is 1, meaning that each free list group contains one free list.

Free List Groups: Number of groups of free lists for the table or index. The default and minimum value for this parameter is 1. Oracle uses the instance number of Real Application Clusters instances to map each instance to one free list group.

5.49 Create/Edit Tablespace

This dialog box is used for creating a new tablespace or editing an existing tablespace. The tablespace properties are grouped under several tabs. For more information, see the CREATE TABLESPACE statement in Oracle Database SQL Language Reference.

Name: Name of the tablespace.

Tablespace Type: Permanent (contains persistent schema objects that are stored in data files), Temporary (contains schema objects only for the duration of a session, and stored in temp files), or Undo (a type of permanent tablespace used by Oracle Database to manage undo data if you are running your database in automatic undo management mode).

Properties tab

Specifies general properties for the database role.

File Type: Small File (a traditional Oracle tablespace, which can contain 1022 data files or temp files, each of which can contain up to approximately 4 million (2^^22) blocks) or Big File (contains only one data file or temp file, which can contain up to approximately 4 billion (2^^32) blocks).

Management Type: Local (uses bitmaps in the tablespaces themselves to manage extents) or Dictionary (uses the data dictionary to manage extents).

Auto Allocate: If this option is enabled, Oracle Database determines the optimal size of additional extents, with a minimum extent size of 64 KB.

Uniform Size (available only if Auto Allocate is disabled): You can specify an extent size or use the default size of 1 MB. All extents in the tablespace are of this size. Locally managed temporary tablespaces can only use this type of allocation.

Initial Extent: Size of the first extent of the tablespace. Specify K (kilobytes) or M (megabytes) for the unit associated with the number.

Next Extent: Size of the next extent to be allocated to the tablespace. Specify K (kilobytes) or M (megabytes) for the unit associated with the number.

Min Extents: Minimum number of extents allocated when the tablespace is created.

Max Extents: Maximum number of extents allocated when the tablespace is created. Unlimited (if checked) means that there is no maximum (and any specified maximum is ignored).

Pct Increase: Percentage that each extent grows over the previous extent.

Block Size: You can specify a nonstandard block size for the tablespace. The integer you specify in this clause must correspond with the setting of one DB_nK_CACHE_SIZE parameter setting.

Online Status: Online (makes the tablespace available immediately after creation to users who have been granted access to the tablespace) or Offline (make the tablespace unavailable immediately after creation).

Default Logging: Logging (causes the creation of a database object, as well as subsequent inserts into the object, to be logged in the redo log file), No Logging (causes these operations not to be logged in the redo log file), or File System Logging (valid only for logging of SecureFile LOB segments; specify this if you want to log only metadata changes).

Force Logging: If this option is enabled, Oracle Database will log all changes to all objects in the tablespace except changes to temporary segments, overriding any NOLOGGING setting for individual objects. The database must be open and in READ WRITE mode.

Segment Management: Auto (the database manages the free space of segments in the tablespace using a bitmap; also called automatic segment-space management), or Manual (the database to manage the free space of segments in the tablespace using free lists).

Table Compression: Type of compression for table data in the tablespace: No Compress (no compression performed), Compress (basic table compression: Oracle Database attempts to compress data during direct-path INSERT operations when it is productive to do so), Compress for All Operations (OLTP table compression: Oracle Database compresses data during all DML operations on the table), or Compress for Direct Load (same as Compress).

File Specifications tab

Specifies data files for the tablespace. For more information, see the ALTER TABLESPACE statement in Oracle Database SQL Language Reference.

File Specifications: Contains one entry for each data file in the tablespace. To add a file, click the Add (plus sign) icon; to remove a file from the tablespace (but not delete the file itself), click the Remove (X) icon.

File Specification Properties: Displays properties of the selected data file.

File Name: Name of the data file.

File Size: Maximum size for the file; you can specify K (kilobytes) or M (megabytes) for the unit of measure.

Reuse Existing File: If this option is enabled and if a file with the same name already exists, the existing file is used. If this option is disabled and if a file with the same name already exists, a warning message is displayed.

Auto Extend: If this option is enabled, automatic extension is performed for a new or existing data file or temp file.

Next Size: The size in bytes of the next increment of disk space to be allocated automatically when more extents are required. The default is the size of one data block.

Max Size: The maximum disk space allowed for automatic extension of the data file.

DDL tab

Displays the SQL statements that SQL Developer will use to create a new tablespace or to edit an existing tablespace. This display is read-only; if you want to make any changes, go back to the relevant tabs and make the changes there.

5.50 Create Trigger

The following information applies to a trigger, which is which is a stored PL/SQL block associated with a table, a schema, or the database, or an anonymous PL/SQL block or a call to a procedure implemented in PL/SQL or Java. The trigger is automatically executed when the specified conditions occur.

Schema: Database schema in which to create the trigger.

Name: Name of the trigger. Must be unique within the database.

Add New Source in Lowercase: If this option is checked, new text is entered in lowercase regardless of the case in which you type it. This option affects only the appearance of the code, because PL/SQL is not case-sensitive in its execution.

Trigger tab

Trigger Type: The type of object on which to create the trigger: TABLE, VIEW, SCHEMA, or DATABASE. (The remaining items depend on the type of trigger.)

Table Owner or View Owner: For a trigger on a table or a view, the name of the owner of the table or the view.

Table Name or View Name : For a trigger on a table or a view, the name of the table or the view.

Before or After: For a trigger on a table, select Before to cause the database to fire the trigger before executing the triggering event, or select After to cause the database to fire the trigger after executing the triggering event.

Statement Level or Row Level: For a trigger on a table, Statement Level fires the trigger once before or after the triggering statement that meets the optional trigger constraint defined in the WHEN condition; Row Level fires the trigger once for each row that is affected by the triggering statement and that meets the optional trigger constraint defined in the WHEN condition.

Insert, Update, Delete: For a trigger on a table or a view, Insert fires the trigger whenever an INSERT statement adds a row to a table or adds an element to a nested table; Update fires fire the trigger whenever an UPDATE statement changes a value in one of the columns specified in Selected Columns (or in any column if no columns are specified); Delete fires the trigger whenever a DELETE statement removes a row from the table or removes an element from a nested table.

Referencing - Old: For a trigger on a table, the correlation names in the PL/SQL block and WHEN condition of a row trigger to refer specifically to old value of the current row.

Referencing - New: For a trigger on a table, the correlation names in the PL/SQL block and WHEN condition of a row trigger to refer specifically to new value of the current row.

Available Columns: For a trigger on a table, lists the columns from which you can select for use in an Update trigger definition.

Selected Columns: For a trigger on a table, lists the columns used in an Update trigger definition.

When: For a trigger on a table, an optional trigger condition, which is a SQL condition that must be satisfied for the database to fire the trigger. This condition must contain correlation names and cannot contain a query.

Schema: For a trigger on a schema, the name of the schema on which to create the trigger.

Available Events: For a trigger on a schema or database, lists events from which you can select for use in the trigger definition.

Selected Events: For a trigger on a schema or database, lists events used in the trigger definition.

DDL tab

You can view a SQL CREATE statement that reflects the current definition of the object, or a SQL ALTER statement to modify an existing object to reflect your changes.

5.51 Create Type (User-Defined)

This dialog box is displayed when you right-click Types in the Connections navigator and select Create Type to create a user-defined type. After you complete the information in this dialog box and click OK, a SQL Worksheet is displayed in which you must specify the appropriate definition of the type.

Schema: Database schema in which to create the type.

Name: Name of the type. Must be unique within its schema.

Type: Select the type of data type to be created: array type, object type specification, object type specification and type body, or table type.

For more information about creating a user-defined type, see the CREATE TYPE statement in Oracle Database SQL Language Reference.

5.52 Create/Edit User

This dialog box is used for creating a new database user or editing an existing database user. The user properties are grouped under several tabs.

To create or edit a database user, the user associated with your database connection must have the DBA role. You should also be familiar with the main concepts and techniques documented in Oracle Database Administrator's Guide.

User tab

Specifies general properties for the database user.

User Name: The user name string. For an existing user, this field is read-only; to change the name, you must drop the user and create a new user with the desired name.

New Password: Password string for the new user, or new password for an existing user. You must also type the same password string for Confirm Password.

Password Expired: If this option is checked, the password is marked as expired, and the user must change the password before being permitted to connect to the database.

Account Locked: If this option is checked, the user will not be permitted to connect to the database until a DBA user unlocks the account associated with this user.

Roles tab

Specifies roles to be granted to the user. For each role, you can check Granted to grant the role, Admin to permit the user to grant the role to other users, and Default to use the default settings for Granted and Admin.

For convenience, you can click buttons to affect all settings (Grant All, Revoke All, Admin All, Admin None, Default All, Default None); then, you can specify other settings for individual roles.

System Privileges tab

Specifies privileges to be granted to the user. For each privilege, you can check Granted to grant the privilege, and Admin to permit the user to grant the privilege to other users.

For convenience, you can click buttons to affect all settings (Grant All, Revoke All, Admin All, Admin None); then, you can specify other settings for individual privileges.

Quotas tab

Specifies disk usage limits on specified tablespaces for the user. If you check Unlimited, there is no disk usage limit on the tablespace.

SQL tab

Displays the SQL statements that SQL Developer will use to create (after executing a CREATE USER statement) a new user or to edit an existing user. This display is read-only; if you want to make any changes, go back to the relevant tabs and make the changes there.

5.53 Create/Edit User Defined Report

The following information applies to a user-defined report. For information about how to create a user-defined report, as well as examples of creating such reports, see Section 1.14.15, "User Defined reports".

Add Child: Add a child report in this report.

Test: Tests the report definition by running it in a separate window. This feature enables you to test the report before creating it.

Details tab

Name: Name of the user-defined report.

Style: Report style: Table (default), Code (formats the code in the output), Chart (bar or pie chart; see Section 1.14.15.1, "User-Defined Report Example: Chart" for an example), Gauge (dial or status meter; see Section 1.7.10, "Gauges: In the SQL Worksheet and User-Defined Reports"), plsql-dbms_output (dynamic HTML; see Section 1.14.15.2, "User-Defined Report Example: Dynamic HTML" for an example), or Script (executable script).

Description: Optional description of the report.

ToolTip: Optional tooltip text to be displayed when the mouse pointer stays briefly over the report name in the Reports navigator display.

SQL Statement: The complete SQL statement for retrieving the information to be displayed in the user-defined report. As a trivial example, the statement SELECT user "Current User" FROM DUAL displays Current User as the heading and the name of the user associated with the current database connection.

Suggestion: Look at the SQL statements for various SQL Developer-supplied reports; check the Messages - Log pane below the report results, or click the SQL icon under the Report Results tab.

Binds tab

Name: Name of the bind variable.

Prompt: String displayed when the user is prompted to enter a value. Example: Table name

Default: Default value if the user does not enter a value at the prompt. To accept the Oracle SQL value, specify NULL_VALUE.

ToolTip: Optional tooltip text to be displayed when the mouse pointer stays briefly over the bind variable name.

Chart Details tab

Available if the report type is Chart.

Chart Type: Bar chart with horizontal or vertical bars, or pie chart.

3D Graph: True for a three-dimensional appearance; False for a two-dimensional appearance.

Gradient Effect: True for a gradient effect; False for no gradient effect.

Chart Style: Thematic name for the overall appearance of the chart.

Show Grid: True to show the grid lines; False to hide the grid lines.

Show Legend: True to show the chart legend; False to hide the chart legend.

Gauge Details tab

Available if the report type is Gauge.

Gauge Type: Dial (like a fuel gauge in an automobile) or Status meter (bar representation).

Query Based: True if the minimum, maximum, low, and high values are specified in the SQL query; False to specify the minimum, maximum, low, and high values in the remaining fields.

Min: Minimum value displayed on the gauge.

Max: Maximum value displayed on the gauge.

Low: "Low" value; usually greater than Min and less than High.

High: "High" value; usually greater than Low and less than Max.

5.54 Create/Edit User Defined Report Folder

The following information applies to a folder for organizing user-defined reports. Each folder can contain reports and other folders (subfolders). For example, you can create a folder named Sales, and then under that folder create folders named Sales by District and Sales by Product.

For information about how to create user-defined reports and folders for these reports, see Section 1.14.15, "User Defined reports".

Name: Name of the folder.

Description: Optional description of the folder.

ToolTip: Optional tooltip text to be displayed when the mouse pointer stays briefly over the folder name in the Reports navigator display.

5.55 Create/Edit View

The view dialog box is used for creating or editing a view or materialized view. You can use the SQL Query tab or a series of panes to specify the query part of the view definition, and you can use one or more other panes (depending on the type of view) for other parts of the definition.

For Edit View, you cannot change the definition of an existing view, but you can define, modify, or drop view constraints.

If you click OK before you are finished creating or editing the view, right-click the view name in the Connections navigator, select Edit, and continue creating or editing the view.

Schema: Database schema in which to create the view.

Name: Name of the view. Must be unique within a schema.

Advanced: If this option is checked, the dialog box changes to include a pane that provides an extended set of features for creating the view.

SQL Query tab or pane

As a tab (if you did not check the Advanced box), it contains the SQL code for the query part of the view definition, using the SELECT and FROM keywords and usually a WHERE clause with whatever syntax is needed to retrieve the desired information.

As a pane (if you checked the Advanced box), it presents options for building specific parts of the query.

For example, the following query, from the Create a View tutorial topic, selects columns from the PATRONS and TRANSACTIONS tables, ordering them first by values in the PATRON_ID column in the PATRONS table and then by values in the TRANSACTION_TYPE column in the TRANSACTIONS table. The result is a listing by patron ID of all patrons who had transactions, and for each listed patron the transaction information listed by transaction type

CREATE VIEW patrons_trans_view AS
  SELECT p.patron_id, p.last_name, p.first_name, 
    t.transaction_type, t.transaction_date
  FROM patrons p, transactions t
  WHERE p.patron_id = t.patron_id
  ORDER BY p.patron_id, t.transaction_type;

SQL Parse Results: If you click Test Syntax, displays any SQL syntax errors, or displays a message indicating no errors if there are no syntax errors.

Revert: Cancels any edits you have made in the SQL Query box, and displays the contents of the box before these edits.

Test Syntax: Checks the statement in the SQL Query box for any SQL syntax errors.

Test Query: Displays a dialog box that runs the SQL query and indicates the result. If there is an error in the query, such as an invalid character or a missing expression, the error is displayed.

Quick-Pick Objects pane

Specifies objects that you can use in the SELECT, FROM, and WHERE clauses of the view definition. Identify the tables and views on which this view is based, and the columns in those tables and views that are used in the definition of this view. To see the results of your quick-pick specification, either check Auto-Query or click Query.

Schema: Database schema containing the objects to be selected.

Type Filter - Filter Types: Enables you to limit the display of objects available for selection to certain types of database objects (for example, to show only tables or views).

Name Filter: Enables you to limit the display of objects available for selection according to a character string in the name, with the percent sign (%) as a wildcard character. For example, to limit the display of available tables and views to those whose names start with the string EM, specify the following name filter: EM%

Auto-Query: If this option is enabled, the display of available objects is automatically refreshed when you specify or change the Type Filter or Name Filter value.

Query: Refreshes the display of available objects based on the Type Filter and Name Filter values.

Available: Lists the objects (typically, tables and views in a hierarchical display) from which you can select objects to use in the SELECT, FROM, and WHERE clauses of the view definition.

Selected: Lists the objects (typically, columns) that you can use in the SELECT, FROM, and WHERE clauses of the view definition.

To add an object as selected, select it in Available and click the Add (>) icon; to remove an object as selected, select it in Selected and click the Remove (<) icon. To move all objects from selected to available, use the Remove All (<<) icon. To move an object up or down in the selected list, select it in Selected and use the arrow buttons.

For the example in DDL tab or pane, select the DEPTNO and SAL columns from the EMP table.

FROM Clause pane

Specifies the tables and views that you can use in the FROM clause of the view definition.

Type Filter - Filter Types: Indicates whether or not you have limited the types of database objects to be displayed in the Available List (by clicking the Filter Types and selecting any combination of Materialized Views, Tables, and Views).

Naming Filter: Substring for limiting object names to appear as available. For example, to display only objects with names that start with EM, specify EM% (with the percent sign as a wildcard character).

Auto-Query: If this option is enabled, the display of available objects is automatically refreshed when you specify or change the Type Filter or Name Filter value.

Query: Refreshes the display of available objects based on the Type Filter and Name Filter values.

Available: Lists the tables and views that are available to be selected for use in the FROM clause of the view definition.

Selected: Lists the tables and views that you can use in the FROM clause of the view definition.

To add an object as selected, select it in Available and click the Add (>) icon; to remove an object as selected, select it in Selected and click the Remove (<) icon. To move all objects from available to selected, use the Add All (<<) icon; to move all objects from selected to available, use the Remove All (<<) icon.

If you attempt to remove any objects that have dependencies in the SQL query, a Confirm Remove box warns you that the dependencies will be removed with the FROM expression; to cancel the remove operation, click No.

Alias: Alias for the table or view.

For the example in DDL tab or pane, select the EMP table.

Join: If you select two tables and click this button, the Edit Join dialog box is displayed.

SELECT Clause pane

Specifies objects that you can use in the SELECT clause of the view definition.

SELECT List: Lists the objects (typically, columns) that you can currently use in the SELECT clause. To add an object, click the Add (+) icon; to delete an object, select it and click the Delete (X) icon; to move an object up or down in the view definition, select it and use the up-arrow and down-arrow buttons.

Note:

After you add an object, to add another object, click the Add (+) icon.

Expression: Column name or an expression. For expressions, you can type them, or you can use the Expression Palette to add object names and function names.

Validate: Checks the validity of the Expression entry.

For the example in DDL tab or pane, select DEPTNO column and the MIN(emp.sal) and MAX(emp.sal) functions.

WHERE Clause pane

Specifies the WHERE clause of the view definition.

WHERE: The text of the WHERE clause, without the WHERE keyword. You can type the text completely; or you can type some of the text and use the Expression Palette to add object names, function names, and operators.

Example (from the Create a View tutorial exercise): p.patron_id = t.patron_id

GROUP BY Clause pane

Specifies a clause to be used to group the selected rows based on the value of columns for each row and return a single row of summary information for each group. The GROUP BY clause groups rows but does not guarantee the order of the result set; to order the groupings, use the ORDER BY clause.

Available: Lists the tables and views, and the columns in each, that are available to be selected for use in the GROUP BY clause of the view definition.

Selected: Lists the tables and views, and the columns in each, that you can use in the GROUP BY clause of the view definition.

To add an object as selected, select it in Available and click the Add (>) icon; to remove an object as selected, select it in Selected and click the Remove (<) icon. To move all objects from available to selected, use the Add All (<<) icon; to move all objects from selected to available, use the Remove All (<<) icon.

HAVING Clause pane

Specifies an expression that must be satisfied for rows to be processed by the GROUP BY clause. For example, HAVING MIN(salary) < 30000 causes the GROUP BY clause to consider only rows where the minimum value of the relevant salary values is less than 30000.

HAVING: You can type the complete expression text, or you can use the Expression Palette to add object names, function names, and operators to the expression text.

ORDER BY Clause pane

Specifies one or more columns or column expressions whose values will be used to sort the results returned by the view. Without an ORDER BY clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order.

ORDER BY List: Lists the objects (typically, columns) that you can currently use in the ORDER BY clause. To add an object, click the Add (+) icon; to delete an object, select it and click the Delete (X) icon; to move an object up or down in the view definition, select it and use the up-arrow and down-arrow buttons.

Note:

After you add an object, to add another object, click the Add (+) icon.

ORDER BY Expression Filter: For each column or column expression, you can type the text completely into the Expression box; or you can type some of the text and use the Expression Palette to add object names, function names, and operators.

Validate: Tests the validity of the syntax for the expression.

Order: ASC for ascending (expression values sorted in ascending order); DESC for descending (expression values sorted in descending order).

Nulls Ordering: NULLS FIRST to have null expression values appear before non-null values; NULLS LAST to have null expression values appear after non-null values. ("Before" and "after" positions are determined by the Order value.)

View Information or Materialized View Properties pane

Options for a standard view:

Restrict Query: If this option is checked, you can enable one of the following options

Force on create: If this option is checked, the view is created even if it has errors in its definition. This option is useful if you want to create the view regardless of any errors, and go back and correct the errors later. If this option is not checked, the view is not created is its definition contains any errors.

Options for a materialized view:

Refresh Options:

Method: The method of refresh operation to be performed:

When: The type of refresh operation to be performed:

Type: Refresh type, which determines the type of materialized view:

Start on: Starting date and time for the first automatic refresh operation. Must be in the future.

Next: Time for the next automatic refresh operation. The interval between the Start on and Next times establishes the interval for subsequent automatic refresh operations. If you do not specify a value, the refresh operation is performed only once at the time specified for Start on.

Constraints: If this option is checked, more rewrite alternatives can be used during the refresh operation, resulting in more efficient refresh execution. The behavior of this option is affected by whether you select Enforced or Trusted.

Enforced: Causes only enforced constraints to be used during the refresh operation.

Trusted: Enables the use of dimension and constraint information that has been declared trustworthy by the database administrator but that has not been validated by the database. If the dimension and constraint information is valid, performance may improve. However, if this information is invalid, then the refresh procedure may corrupt the materialized view even though it returns a success status.

Materialized View Options:

Parallel: If this option is checked, parallel operations will be supported for the materialized view, and you can specify a number for the default degree of parallelism for queries and DML on the materialized view after creation.

Enable Cache: If this option is checked, the blocks retrieved for this table are placed at the most recently used end of the least recently used (LRU) list in the buffer cache when a full table scan is performed. This setting is useful for small lookup tables. If this option is not checked, the blocks are placed at the least recently used end of the LRU list.

Build Type: Specifies when to populate the materialized view. Immediate indicates that the materialized view is to be populated immediately. Deferred indicates that the materialized view is to be populated by the next refresh operation. If you specify Deferred, the first (deferred) refresh must always be a complete refresh; until then, the materialized view has a staleness value of unusable, so it cannot be used for query rewrite.

Enable Query Rewrite: If this option is checked, the materialized view is enabled for query rewrite, an optimization technique that transforms a user request written in terms of master tables into a semantically equivalent request that includes one or more materialized views.

Prebuilt Option: If this option is checked, an existing table is registered as a preinitialized materialized view. This option is particularly useful for registering large materialized views in a data warehousing environment. The table must have the same name and be in the same schema as the resulting materialized view, and the table should reflect the materialization of a subquery. Reduced Precision authorizes the loss of precision that will result if the precision of the table or materialized view columns do not exactly match the precision returned by subquery. No Reduced Precision requires that the precision of the table or materialized view columns match exactly the precision returned by subquery, or the create operation will fail.

Index Storage Options:

Use Index: If this option is checked, a default index is created and used to speed up incremental (fast) refresh of the materialized view. If this option is not checked, this default index is not created. (For example, you might choose to suppress the index creation now and to create such an index explicitly later.)

Use Tablespace: If this option is checked, you can specify the tablespace in which the materialized view is to be created. If this option is not checked, the materialized view is created in the default tablespace of the schema containing the materialized view.

DDL tab or pane

You can view a SQL CREATE statement that reflects the current definition of the object, or a SQL ALTER statement to modify an existing object to reflect your changes.

To save the SQL statement to a script file, click Save and specify the location and file name.

5.56 Create XML Schema

This dialog box enables you to create an XML schema object that can be associated with XML document instances.

Name: Name of the XML schema object.

After you click OK, an editing pane is displayed for specifying the URL of the XML schema.

5.57 Color Palette and Custom Colors

You can use the color palette editor to select a color from the supplied Available Colors or and saved Custom Colors.

You can also create by using a gradient box or by speechifying the RGB (Red, Green, Blue) values for the color.

5.58 Configure Extension

This dialog box, which is displayed if you click Configure for Versioning Support in the Extensions preferences pane, enables you to select from among available versioning support extensions for SQL Developer. For information about using versioning with SQL Developer, see Section 1.10.

If you change any existing settings, you will need to restart SQL Developer.

5.59 Configure File Type Associations

This dialog box, which is displayed the first time you start SQL Developer, enables you to associate certain file types with SQL Developer. If a file type is associated with SQL Developer, files with that type's extension will automatically be opened by SQL Developer when you double-click the file name. Any previous association for that file type is replaced.

If you do not associate a file type with SQL Developer, any existing association for that file is unchanged.

After you close this box, you can change the associations for these file types and many others by clicking Tools and then Preferences, and selecting File Types (see Section 1.15.8, "File Types").

5.60 Copy Columns

This dialog box is displayed if you click the Copy Columns icon when specifying column definitions for a table.

Schema: The schema that owns the table from which to copy column definitions.

Table: The table within the selected schema.

Columns: A list of the columns in the table. Select one or more columns to be copied into the table that you are creating or editing, and click OK.

5.61 Custom Filters

This dialog box is displayed if you right-click and select Customize Filters in the History tab for a .sql file or a SQL Trace (.trc) file. You can modify an existing filter or create a new filter.

Filter List: Names of the available filters. To edit an existing filter, select its name; the details for that filter are displayed in the dialog box. To remove an existing filter, select its name and click Remove.

To create a new filter, click Add and specify the filter name.

To restore the filters to those at SQL Developer installation, click Restore Defaults. This deleted any filters that have been added since installation.

Simple Expression: Create the filter by selecting a column and operator and by specifying a value. To select a value from a list, click Insert.

Complex Expression: Create the filter by entering a complex expression.

5.62 Database Copy (Schema Objects)

This dialog box is displayed if you click Tools, then Database Copy. Specify the type of operation, and the connections for the source and destination schemas. All database objects are copied from the source schema to the destination schema, subject to any restrictions depending on the type of operation, which determines the behavior if objects of the same name exist in the destination schema.

Source/Destination pane

Source Connection: Database connection for the schema from which to copy the objects.

Destination Connection: Database connection for the schema to which to copy the objects

Create Objects: Copies the objects to new objects in the destination schema only if an existing object of that type with the same name does not already exist.

Truncate Objects: Deletes existing rows in any existing table with the same name, and then loads rows from the source.

Drop Objects: Drops any existing table with the same name, and then creates and loads it from the source.

Copy Summary pane

You can review the SQL statements that will be used to perform the copy operation according to your specifications.

To go back and make any changes, click Back.

To perform the copy operation, click Finish. After the copy operation completes, a log file is displayed.

5.63 Database Schema Differences

This interface is displayed if you click Tools, then Database Diff. You can find differences between objects of the same type and name (for example, tables named CUSTOMERS) in two different schemas, and optionally update the objects in one schema (destination) to reflect differences in the other schema (source).

Use the Source/Destination pane to specify the source and destination database connections. Database objects in the schemas associated with these connections will be compared. The schemas for the source and destination connections can be in the same database or different databases.

Source/Destination pane

Source Connection: Database connection for the source schema (the schema in which selected objects are to be compared with objects in the destination schema).

Destination Connection: Database connection for the database that contains the destination schema (the schema containing one or more objects of the same type and name as those selected in the source schema). The selected connection can be the same as, or different from, the connection for the source schema.

Diff Objects: Check the types of objects that you want to be compared in the source and destination connections. You can click Toggle All to check and uncheck all individual types. You must select at least one object type.

Proceed to Summary: If this option is checked, clicking Next takes you directly to the Diff Summary pane.

Specify Objects pane

You can limit the types or objects, and the objects within selected types, for the comparison operation. You can specify a Name string filter (including % wildcard characters) and optionally an object type (or All for all object types), and click Lookup to display objects from the source connection that satisfy the filter

Lookup: Click Lookup to display a list of objects that meet the selection criteria for the selected connection. Use the arrow keys to move selected objects or all objects from the available objects box to the selected objects box.

Diff Summary pane

You can review the information that will be used to compare the source and destination connections, according to your specifications.

To go back and make any changes, click Back as needed.

To perform the comparison, click Finish. The results are displayed in a Diff Report window, where you can see the DDL statements to update the objects in the destination schema to reflect differences from the source schema. To create a file containing these DDL statements, click the Generate Script (SQL) icon in that window. To toggle the display between all objects and only those objects with differences, click Show Equal Objects.

5.64 DDL Panel for Creating or Editing an Object

You can review and save the SQL statement that SQL Developer will use to create or edit the object, to reflect any changes you have made to the object's properties. If you want to make any changes, go back to the relevant panels and make the changes there.

To save the SQL statement to a script file, click Save and specify the location and file name.

5.65 Debugger - Attach to JPDA

This dialog box is displayed when you right-click a database connection name and select Remote Debug. Use this dialog box 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. For more information about remote debugging, see Section 1.6.2, "Remote Debugging".

Host: Name or IP address of the remote host on which SQL Developer should listen for the database to connect.

Port: Listening port number on the remote host. You can choose any valid port number that is not in use by another process.

Timeout: The number of seconds that SQL Developer will wait for the remote database to make a debugging connection.

Don't Show Dialog Box Before Connecting: If this option is checked, this dialog box will not be displayed before future connections for remote debugging.

5.66 Deploy or Import Application

Use this wizard to deploy or import an Application Express application into a specified target schema.

Deploy to Connection or Specify File to Import

Choose Connection to Deploy Application: For a deploy operation, specify the database connection for the target schema into which to deploy the application.

Specify File to Import: For an import operation, specify the location and name of the SQL file containing the exported application (usually the output of a previous "export application" operation).

Choose Import Options

Specifies options for the application to be deployed or imported.

Workspace: Name of the Application Express workspace.

Parse As Schema: Schema against which all of the application's SQL and PL/SQL will be parsed.

Application Name: Name of the application.

Application Alias: Alias for the application. It is recommended that you never hard code the application ID into your application, but instead use the application alias or a built-in substitution string (such as APP_ID and APP_ALIAS).

Build Status: RUN_ONLY or RUN_AND_BUILD

Application ID: Specify whether to have an application ID assigned automatically, to use an existing listed ID, or to specify a new ID. Use these options to avoid application ID conflicts, such as when you need to have two versions of the same application in the same instance. For example, you might be migrating an application to a production instance and still need to maintain the development version.

ID Currently Used by and Overwrite: If the specified Application ID is currently used by another application, you can enable Overwrite to have the application ID instead associated with the application being deployed or imported.

Summary

Displays the selected options for the application to be deployed or imported. To make any changes, click Back. To perform the operation, click Finish.

5.67 Describe Object Window

This window is displayed when you select a database object name in the SQL Worksheet, right-click, and select Describe. The information is read-only, and is displayed using tabs that are appropriate for the type of object.

For example, if the display is for a table, the information displayed is similar to that in the Create/Edit Table (with advanced options) dialog box.

5.68 Edit/View Value (Data or Table Column)

This dialog box enables you to view and (if applicable) edit data in certain dialog box fields, in a cell in the table Data grid, in some other grid display (for example, to edit the value of a single column within a row), or in a filter specification. If you are permitted to modify the data, you can change the data value and then click OK. (If you are not permitted to modify the data, the Value display is read-only.)

The specific options for editing the value available depend on the context, such as the data type of the column associated with that cell in the grid. For example, for BLOB or BFILE data you can:

For date-based values (such as for CREATED as the filter criterion), you can specify a date or click Set Today to specify today's date.

5.69 Enter Bind Values

This dialog box enables you to enter values for each bind variable. If the NULL option is checked, you cannot enter a value in this dialog box.

5.70 Erase from Disk

This dialog box asks you to confirm your action if you select one or more files in the Files navigator and press the Delete key. To perform the deletion, click Yes; to cancel the deletion, click No.

5.71 Error Writing to Export File

This box is displayed if you tried to export table data to a file, but the directory or folder path does not exist.

Click OK to close the box, then enter a valid path in the Export dialog box and click Apply.

5.72 Export Error

This dialog box is displayed when you tried to export some or all objects of one or more types of database objects to a file containing SQL statements, but did not include some essential information, which might include one or more of the following:

5.73 Export Data

This dialog box prompts you to specify the location and name of a text file to contain the output of the export operation, such as data values during a debug operation.

5.74 External Locator Configuration

This dialog box is displayed if you click External Locator Configuration when creating a CVS repository. Specify the information required to connect to the remote repository when the method by which the client will gain access to and authenticate against the server is External.

Set Remote Shell: If this option is checked, external repositories are accessed through a remote shell utility, usually rsh (the default) or ssh.

Set Remote Shell: If this option is checked, you specify the name of the CVS program on the remote server. (It is unlikely to need to be changed from the default, and should only be changed in cooperation with the administrator of the CVS remote server.)

5.75 External Tools

This dialog box is displayed when you click Tools and then External Tools. It displays information about user-defined external tools that are integrated with the SQL Developer interface.

Find Tools: Checks for any tools that Oracle offers for your consideration, and adds them to the list if they are not already included.

New: Starts a wizard for defining a new external tool (see Section 5.76, "Create/Edit External Tool").

Edit: Displays a dialog box for editing the selected external tool (see Section 5.76, "Create/Edit External Tool").

5.76 Create/Edit External Tool

This interface is displayed as a wizard if you are creating a new external tool, and as a dialog box if you are editing an existing external tool (see Section 5.75, "External Tools").

External Program Options

Program Executable: Path of the program executable for the tool.

Arguments: Arguments (parameters) to be passed to the program. You can click Insert to insert a macro for the argument (see Section 5.89, "Insert Macro").

Run Directory: Directory in which to run the program. You can click Insert to insert a macro for the directory (see Section 5.89, "Insert Macro").

Command Sample: A read-only sample display of the command to run the program.

Display Options

Specify how the external tool should appear when displayed in menu or toolbar items.

Caption for Menu Items: The text string that will appear for any menu item that calls the external tool. To indicate the mnemonic character, use the ampersand before the character. For example: &Mytool for the "M" to be underlined and used as the mnemonic

ToolTip Text: Text for the tooltip to be displayed when the mouse pointer hovers over the icon for the tool in a toolbar.

Icon Location: File path of the icon associated with the tool. Click Browse to specify a graphics file, or Use Default to use the default icon (if you previously specified a nondefault icon).

Preview: A read-only display of the menu item and its associated icon.

Integration Options

Specify how the external tool will be integrated with SQL Developer.

Add Items to Menus: Check any menus on which you want to include an item for this tool.

Add Buttons to Toolbars: To add the icon for this tool to the SQL Developer main toolbar, check Main Toolbar.

After Tool Exits: To have SQL Developer reload any open files after the tool exits, check Reload Open Files.

Availability Options

Specify when the external tool is enabled. In contexts where the tool is not enabled, its menu item and icon are grayed out.

Always: Makes the tool always available.

When a File is Selected or Open in the Editor: Makes the tool available only when a file is selected or open, such as when the SQL Worksheet is open.

When Specific File Types are Selected: Makes the tool available only when files of the specified type or types are selected. Use the arrow buttons to move desired types from Available Types to Selected Types.

5.77 Choose Offline Options

This dialog box is displayed when you click Tools, then Migration, then Create Database Capture Scripts. It specifies options for creating an offline capture properties (.ocp) file, which you can later specify as the Offline Capture Source File on the Source Database page of the Migration Wizard.

Output Directory: Converted model containing tables whose data is to be moved to the corresponding Oracle database tables.

Generate for: Windows Batch File generates a .bat file to be run on Windows systems; Linux Shell Scripts generates .sh files to be run on Linux systems.

For a MySQL migration, if you generate .sh files, you must also execute the following command to make the .sh files executable and the .ocp file writable:

chmod 755 *

Platform: The IDM DB2, MySQL, Microsoft SQL Server, Sybase Adaptive Server, or Teradata version for which to generate the scripts.

5.78 Edit Font Location

Lets you specify the locations for fonts to be used in PDF generation, and the order in which the locations are searched.

Add Entry: Lets you select a location to be added to the list.

Edit Entry: Lets you change the location for the selected item in the list.

Remove: Deletes the selected location from the list.

Up and down arrows: Let you move selected items up or down to change the order of items in the list.

5.79 Edit Header or Footer (PDF Formatting)

In the user preferences for PDF Format: Header and Footer under , you can edit the header and footer separately to specify text to be used.

To include the date in that text, click the Insert Date icon to display a dialog box to specify the desired date format, and whether or not to have the inserted date be updated automatically to reflect any update of the PDF file.

5.80 Edit Join

This dialog box lets you edit the join specification for a join view.

Swap: Reverses the order of the tables.

Join Type:

Natural: If this option is checked, a natural join will be performed. A natural join is based on all columns in the two tables that have the same name. It selects rows from the two tables that have equal values in the relevant columns. When specifying columns that are involved in the natural join, do not qualify the column name with a table name or table alias.

On: Using the ON clause to specify a join condition lets you specify join conditions separate from any search or filter conditions in the WHERE clause.

Using: When you are specifying an equijoin of columns that have the same name in both tables, the USING column clause indicates the columns to be used. You can use this clause only if the join columns in both tables have the same name. Within this clause, do not qualify the column name with a table name or table alias. In an outer join with the USING clause, the query returns a single column which is a coalesce of the two matching columns in the join.

5.81 Feature Missing

This dialog box is displayed if you try to use a SQL Developer feature that requires that the specified Oracle Database feature also be installed. To use the SQL Developer feature, install the Oracle Database feature if you are authorized to do so; otherwise, see the database administrator for assistance.

5.82 Feature Required

This dialog box is displayed if you try to use a SQL Developer feature that requires the licensing of the specified feature for Oracle Database. If you do not have a license for the specified feature, you must click No.

If you have a license for the feature on the database or databases on which you plan to use the feature this time, you can click Yes.

To manage the license information for any database connections (and to minimize prompts for license-related information), you can specify user preferences for Database: Licensing.

To purchase any required license, contact your Oracle sales representative or authorized Oracle Reseller, or go to the Oracle Store to buy online.

5.83 File Processing

This dialog box is displayed if you right-click a directory or file in the Files navigator and select Tag SQL. In the selected file or in files under the selected directory, all SQL statements that match a specified pattern are rewritten so that a comment is inserted after the SELECT keyword in the main query block that uniquely tags the statement.

For example, a PL/SQL source containing the statement "SELECT 1 FROM dual" would be rewritten into something like "SELECT /* PREFIX 00f7d2 */ FROM dual". This allows easy identification of problematic statements that appear in performance views such as V$SQL.

Tag Prefix: Text to appear after "PREFIX" in inserted comments.

Selected Extensions: Extensions of files on which to perform the SQL statement rewrite operations.

5.84 Filter

This dialog box is displayed when you right-click a connection node or an object type node (such as Tables) in the Connections navigator and select Apply Filter. Use this box to limit the number of objects of that type that are displayed, according to one or more filter criteria that you specify. For each criterion, specify the following:

For example, to display only tables with names that start with EM, specify NAME, LIKE, and EM% (with the percent sign as a wildcard character).

Any remaining options in this dialog box depend on the context for the filter option.

Override Schema Filter (object type node filters): If this option is checked, any filter criterion specified at the connection level is ignored, and only the object type node filter criteria are applied.

Include Synonyms (object type node filters): If this option is checked, synonyms for objects of this object type are included.

To add another filter criterion, click the Add (+) icon; to delete a criterion, select it and click the Delete (X) icon; to move a criterion up or down in the list, select it and use the arrow icons.

To apply the filter criteria to the Connections navigator display, click OK.

To remove the effects of applying a filter, right-click the object type node in the Connections navigator display and select Clear Filter.

5.85 Filter Object Types

This dialog box filters (restricts) the types of objects to be displayed for the schema associated with the selected user.

Available Object Types: Lists the types of objects that are available to be added to the display.

Displayed Object Types: Lists the types of objects that are included in the display.

To add a type of object to the display, select it in Available Object Types and click the Add (>) icon; to remove a type of object from the display, select it in Displayed Object Types and click the Remove (<) icon. To move all types of objects from available to displayed (or the reverse), use the Add All (>>) or Remove All (<<) icon.

5.86 Filter Schemas

This dialog box enables you to restrict the schemas that are displayed under Other Users in the Connections navigator.

Available Schemas: Lists the schemas that are not currently displayed under Other Users in the Connections navigator, but that are available to be added to the list of displayed users.

Displayed Schemas: Lists the schemas that are to be included in the display under Other Users in the Connections navigator.

To add a schema to the display, select it in Available Schemas and click the Add (>) icon; to remove a schema from the display, select it in Displayed Schemas and click the Remove (<) icon. To move all schemas from available to displayed (or the reverse), use the Add All (>>) or Remove All (<<) icon.

Only display schemas with visible objects: Limits the display to available schemas that have any database objects that are visible to the database user associated with the current connection.

5.87 Filter Error

This dialog box is displayed if you did not specify any data for an export operation. Be sure to specify Filter Data options that select some data for the export operation.

5.88 Find/Highlight

This dialog box is displayed when you right-click in the table data grid and select Find/Highlight to find, and optionally highlight with a specified background color, occurrences of a specified string value in the table data. Type the string value, and press the down-arrow to search forward from the currently selected grid cell or the up-arrow to search backward.

Search goes to matching cells but does not highlight; Highlight goes to matching cells and highlights.

Text matching options: You can ignore case, require a whole word match, require a starting with match, or allow the value to wrap across cell boundaries.

Color (Highlight only): Specifies the background color for highlighting.

Persist Highlight (Highlight only): If this option is enabled, matches are added to the Persisted Highlights list.

Highlight Row (Highlight only): If this option is enabled, the entire row that contains the cell with a matching value is highlighted.

5.89 Insert Macro

This dialog box is displayed when you click Insert when specifying external program options (see Section 5.76, "Create/Edit External Tool"). It enables you to insert a sample text string into the relevant field for the external program option; you can then edit that string to suit your needs. (This is somewhat analogous to using snippets to insert text strings into the SQL Worksheet.)

Select the desired type of macro, read its description to ensure that it is what you want, and click OK. For some macros, a sample expansion is included.

5.90 Externally Modified Files

This dialog box filters is displayed when an external application has modified a file that you have open in SQL Developer. You are asked if you want to reload the externally modified file.

If you are offered the choice of Yes or No: If you click Yes, the externally modified file overwrites any changes that you might have made in SQL Developer. If you click No, the externally modified file will be overwritten by your version when you save the file in SQL Developer.

If you are offered only the choice of OK (or Help): Click OK and then do one of the following:

5.91 Find/Replace Text

This dialog box specifies a text string to find, optionally a replacement text string, and search options.

Text to Search For: Text string to search for.

Replace With: If you check this option, enter a text string to replace the text string that is being searched for.

Options: Options to control the search behavior: Match Case makes the search case-sensitive; Search from Beginning starts the search at the beginning instead of at the text cursor; Highlight All Occurrences highlights all occurrences of the search string instead of just the first one; Wrap Around searches across line breaks; Whole Word Only find the search string only if it is a complete word and not just part of a word; Regular Expressions means that the search string is a regular expression; Selected Text Only means to search only in the text block that you have selected.

Direction: Forward starts the search from the cursor in the direction of normal text flow; Backward starts the search from the cursor in the opposite direction of normal text flow.

5.92 Find Result

This box is displayed if you specify text to search for in the Find/Replace Text dialog box that is not in the SQL Worksheet.

If you think that the text is in the worksheet, retry your query, and check the spelling of the text to search for.

5.93 Format Properties

This box is displayed if you right-click and select Advanced Format in the editor for a subprogram, package, view, or trigger. You can specify a set of formatting rules different from those if you had selected Format (that is, different from the Database: SQL Formatter user preferences).

Output Destination: Editor applies the selected Output Type formatting in the current editing pane; Clipboard applies the formatting on the clipboard, so that you can paste it into a pane or window of your choice.

Output Type: A set of formatting rules associated with an output type: SQL or another type from the list.

5.94 Generate Patch

You can generate a patch containing changes that have been made to files. The patch can be applied to another set of checked out CVS files so that your changes are incorporated into them. A patch must be applied to the same revision/tag from which it was generated.

Source Files or Project: Name and location of the project or set of files will be the subject of the patch.

Patch Target: Where the generated patch will be sent: System Clipboard (from which you can paste the patch), File (accept the shown name and location, or specify different ones), or Open Patch File Editor (where you can edit and then save it).

Output Format: One of the standard diff formats: Unified, Context, or Standard.

5.95 Go to Bookmark

Use this box to specify the bookmark to go to in the selected function or procedure. After you enter the bookmark and click Go, the line associated with that bookmark is highlighted.

5.96 Go to Line Number

Use this box to specify the line number to go to in the selected function or procedure. After you enter the line number and click the Go icon, that line is highlighted.

5.97 Go to Line Number: Error

This error box tells you that you entered an invalid line number in the Go to Line Number box, probably because you entered a line number greater than that of the last line in the function or procedure.

5.98 History

The History window displays the differences between different revisions of the same file. You can see the local history of a file and, when appropriate, the version history of a file.

Revision Filter: Use to filter the list of revisions displayed. You can edit any of the filters, or add new filters, using the Customize Filters button.

Customize Filters button: Opens the Custom Filters dialog box, in which you can edit the filter values of existing filters or add new filters.

Refresh button: Updates the display.

Go to First / Previous / Next / Last Difference buttons: Moves the cursor to the first, previous, next, or last difference.

Purge Local History button: Removes the local history versions of the files.

Restore from Revision button: Makes the current version of the file (in the right-hand area) the same as the revision of the file (in the left-hand area).

Generate Patch button: Opens the Generate Patch dialog, where you can generate a patch containing changes that have been made to the files.

Left-hand and right-hand boxes: The versions are aligned line by line. Lines with differences are highlighted using shaded boxes, joined as appropriate. You cannot edit the left-hand area; you can edit the right-hand area.

5.99 Import to CVS

This interface is displayed when you click Versioning, then CVS, then Import Module. It enables you to import local files into the repository as a CVS module.

Module

Select the connection name, enter a name for the module, and optionally enter a descriptive comment about the import operation.

Tags

Select the connection name, enter a name for the module, and optionally enter a descriptive comment about the import operation.

Sources

Source Folder: Location from which files will be copied for the import operation.

Filters

You can configure filters to be used for excluding folders and files from the import operation. Use the arrow keys to move selected filters or all filters between Available Filters and Selected Filters.

To create a filter and add it to the Selected Filters list, click New to display the Create Filter dialog box.

Options

You can specify options to be used during the import operation.

Use File Modification Time: If this option is checked, the file's modification time is used as the time of import. If this option is not checked, the time when the import operation is performed is used as the time of import.

Perform Module Checkout: If this option is checked, the modules are checked out after they are imported.

Summary

You can review the information that will be used to perform the import operation.

To go back and make any changes, click Back as needed.

To perform the import operation, click Finish.

5.100 Invalid Database Version

The feature you are trying to use is not supported with Oracle Database Release 10 and earlier releases.

To use the feature, you must connect to an Oracle database with a version number of 11.1 or higher.

5.101 Load Keyboard Scheme

This dialog box is displayed when you select Load Keyboard Scheme from More Actions when specifying shortcut key preferences for SQL Developer. You can 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.

You can specify Default to restore the shortcut key mappings to the SQL Developer defaults for your system.

5.102 Log In to CVS

Use this dialog box to log in to the specified CVS repository. You must know the password for the specified user.

Connect Automatically on Startup: If this option is checked, a login operation is performed when you start SQL Developer.

5.103 Maintain Spatial Metadata

This dialog box is displayed if you select a table and specify Update Spatial Metadata. Internally, SQL Developer uses the information in this box to modify the relevant row in the xxx_SDO_GEOM_METADATA and xxx_SDO_INDEX_INFO views, which are documented in Oracle Spatial Developer's Guide.

Table Name: Name of a feature table that has a column of type SDO_GEOMETRY.

Spatial Index Name: Name of the spatial index associated with the specified combination of table and column names.

Spatial Column: Name of the column of type SDO_GEOMETRY in the specified feature table.

Coordinate System: The SRID (spatial reference identifier) value for the coordinate system for all geometries in the column, or null if no specific coordinate system should be associated with the geometries. For example, 8307 is the SRID value for the "WGS 84 longitude/latitude" coordinate system.

Spatial Dimensions: The dimensional information (DIMINFO column in USER_SDO_GEOM_METADATA). Note that for geodetic layers, you must specify the dimensional extents in the index metadata as -180,180 for longitude and -90,90 for latitude, and the tolerance value is interpreted as meters.

Name: Name of the dimension, for example, X or Longitude.

Lower Boundary: Lower-left point of the dimension. For geodetic data, must be -180 for longitude and -90 for latitude.

Upper Boundary: Upper-right point of the dimension. For geodetic data, must be 180 for longitude and 90 for latitude.

Tolerance: A level of precision with spatial data, reflecting the distance that two points can be apart and still be considered the same (for example, to accommodate rounding errors). For geodetic data, tolerance is specified as meters. (Tolerance is explained in detail in Oracle Spatial Developer's Guide.)

5.104 Manage Columns

Use this dialog box to reorder, hide, or show columns in the display when you are editing a table or data in a table. To move a column higher or lower in the display order, click the column and then click the appropriate icon on the left. To hide a column or to show a hidden column, click the column if necessary and click the appropriate icon between the Shown Columns and Hidden Columns lists.

When you are finished, click OK. (Your action affects only the current display; it does not change the table definition or any data in the table.)

5.105 Modify Value

This dialog box is displayed when you right-click a variable in the Data or Smart Data pane during debugging and select Modify Value. You can modify the value for the selected data item (primitive value, string, or reference pointer) during debugging. Note: You cannot undo the action after you click OK, so be careful when making any changes.

Current Value: The value of the data item.

New Value: The new value for the data item (enter or select from a drop-down list).

Interpret New Value as Object Address: If this option is checked, the New Value entry is interpreted as a memory address pointer to an object or array in the heap of the program you are debugging. For a string, this box must be checked check if the value you enter in the New Value field is the memory address of an existing string

5.106 Data Move Details

This dialog box is displayed when you click Migration, then Migrate Data. It specifies the source and target information for online data migration, which is explained in Section 2.2.8.

Source Connection: Database connection from which data is to be migrated.

Target Connection: Database connection to which data is to be migrated.

Converted Model: Converted model containing tables whose data is to be moved to the corresponding Oracle database tables.

Use qualified names from converted model for insert: If this option is checked, object names are qualified by the schema name.

5.107 New Procedure (Refactoring)

This dialog box is displayed if you are editing a procedure, select one or more PL/SQL statements, right-click, and select Refactoring, then Extract Procedure. The selected statements are encapsulated into the procedure to be created.

Defined Locally: For a standalone procedure, defines the newly refactored code in the definition section of the original procedure.

Stored: For a standalone procedure, defines the newly refactored code in a new standalone procedure.

Name: Name of the procedure to encapsulate the selected statements. For a packaged procedure, the newly extracted procedure text is placed immediately after the current procedure.

5.108 No Lock

This dialog box is displayed if multiple instances of SQL Developer are running on the system and if you attempt to open a unit test object, such as a test or suite, when that object is already open in another instance of SQL Developer. Thus, the current instance of SQL Developer is unable to get a lock on that object at this time.

Either use the other instance of SQL Developer to view or modify the object, or close the object in the other instance of SQL Developer and then open the object in this instance.

5.109 No Object Found

This dialog box is displayed if no objects could be found to satisfy the requested operation, such as trying to perform a "Describe" operation when the currently selected object is not valid for a SQL*Plus DESCRIBE statement.

5.110 No Object Selected

This dialog box is displayed if no object was selected for the requested operation, such as trying to perform a "Describe" operation when no object is selected in the SQL Worksheet.

5.111 Object Preferences

This dialog box lets you specify preferences for the display of data output during debugging. For a specified data type and its subclasses, you can control what to display in value columns, what to show when expanding the object (expressions, fields, or both), and (for fields) which fields to show and which to hide.

5.112 Offline Generation Error - Destination Directory

This dialog box is displayed if you specify a nonexistent directory or folder for the generation of offline data move files. Check the spelling of the path that you entered, or create the desired directory or folder, and try the operation again.

5.113 Open File

This is a standard box for selecting a file to open: use Location to navigate to (double-clicking) the folder with the file to open, then click the file to select it.

5.114 Oracle-Only Report

This dialog box is displayed if you select a non-Oracle (third-party) database connection for a report that applies only to Oracle database connections. Be sure to select an Oracle connection.

5.115 Oracle Proxy Authentication

This dialog box is displayed if you enable the Proxy Connection option in the Create/Edit/Select Database Connection dialog box. For an explanation of proxy authentication, see Section 1.4.5, "Connections with Proxy Authentication".

Proxy Type: User Name for authentication by proxy user name and password, or Distinguished Name for authentication by proxy user name and distinguished name.

Proxy User: Name of the user to be used for authentication for this connection.

Proxy Password (if Proxy Type is User Name): Password for the specified proxy user.

Distinguished Name (if Proxy Type is Distinguished Name): Distinguished name for the specified proxy user.

5.116 Paste

This dialog box is displayed if you click Edit, then Extended Paste. It shows a list of clipboard items, so that you can select the content to be pasted. Click OK to paste the selected content into the current location.

Clipboard Items: Clipboard items with content from copy operations. Usually displays the first line of the content.

Item Content: The content of the selected clipboard item.

5.117 Privilege Warning for Migration

This dialog box is displayed if you click Verify in the Quick Migrate box and the database user for the connection does not have all privileges necessary for a multischema migration. For multischema migrations, this user must granted the RESOURCE role with the ADMIN option; and this user must also be granted the CREATE ROLE, CREATE USER, and ALTER ANY TRIGGER privileges, all with the ADMIN option.

If you are performing a single-schema migration, you can ignore this warning.

5.118 Recent Files

This dialog box displays files recently opened in SQL Developer.

Files: A list of files opened in SQL Developer, with the most recent file first. The Show All option determines whether the list includes only files opened implicitly or files opened implicitly or explicitly.

Show All: If this option is checked, the list includes both explicitly and implicitly opened files; if this option is not checked, the list includes only implicitly opened files. Explicitly opened files are those that you opened directly; implicitly opened files are those that SQL Developer opened to support your work (for example, while you were debugging).

5.119 Create Repository

This dialog box is displayed if you click Migration, then Repository Management, then Create Repository.

Create Repository: Name of the database connection to use to create a migration repository. The objects associated with the migration repository are created in the schema of the user associated with the selected connection.

5.120 Delete or Truncate Repository

The Delete Repository dialog box is displayed if you click Migration, then Repository Management, then Delete Repository; the Truncate Repository dialog box is displayed if you click Migration, then Repository Management, then Truncate Repository.

Deleting a repository removes all schema objects that are used for the migration repository. Truncating a repository deletes all data from schema objects that are used for the migration repository, but does not delete the schema objects themselves, effectively leaving you with an empty repository.

Repository: Name of the database connection in which to delete or truncate the migration repository.

5.121 Capture Microsoft Access Exporter XML

This dialog box is displayed if you click Migration, then Capture Exporter XML.

File Path: File path to the .xml file that was produced when you ran the appropriate version of the exporter tool for Microsoft Access (when you clicked Migrations, then Microsoft Access Exporter, then the appropriate version for your version of Microsoft Access).

5.122 Rename Local Variable

This dialog box is displayed if you right-click a variable name in the display of the source code for a function or procedure, and select Refactoring and then Rename Local Variable. Specify the desired new name for the variable.

5.123 Rename Tab

This dialog box is displayed if you right-click a tab name and select Rename. You can change the name of the tab to something more descriptive in this case, for example, changing Script Output to Employees Query.

The new name that you specify is used only for as long as the tab is displayed. If you close the tab and cause it to be displayed again, the default tab name appears.

5.124 Rename Procedure

This dialog box is displayed if you try to rename a procedure. Specify a unique new name for the procedure.

5.125 Select Current Repository

This dialog box is displayed if you click Migration, then Repository Management, then Select Current Repository. You can use this dialog box to reconnect to a migration repository after you have disconnected (using Migration, then Repository Management, then Disconnect Migration Repository). In addition, if you have multiple migration repositories, and you can use this dialog box to switch from one to another.

Select Current Repository: Name of the database connection with the migration repository to be used for all operations relating to migrating third-party databases to Oracle.

5.126 Cannot Capture Table

This dialog box is displayed if you try to capture a third-party database before establishing and connecting to a current migration repository.

If no migration repository exists, create one by clicking Migration, then Repository Management, then Create Repository.

To make an existing migration repository the current one, right-click its connection in the Connections navigator and select Associate Migration Repository.

To open a connection to the migration repository, expand the node for its connection in the Connections navigator.

5.127 Report Panel

This panel is mainly used to display specific SQL Developer Reports. See the help for the relevant report or report type.

This panel is also used for displays of some other features, such as Monitor Sessions on the Tools menu.

5.128 Reset Expired Password (Enter New Password)

This dialog box is displayed if you attempt to create a new database connection or open an existing connection, and if the password associated with the used for the connection has expired. It is also displayed only if an OCI (thick) driver is available; if an OCI driver is not available, an error message is displayed instead of this dialog box.

To reset the password, enter the current password for the specified user, enter the new password, confirm the password (type the same new password), and click OK.

5.129 Revision Lister

This dialog box is displayed if you click List Revisions in the Subversion: Branch/Tag dialog box. It contains a list of revisions in the repository.

Select the desired revision to use, and click OK.

5.130 Run/Debug/Profile PL/SQL

Use this box to specify parameter values for running, debugging, or profiling a PL/SQL function or procedure. (If you specify a package, select a function or procedure in the package.) A profile operation runs the function or procedure and collects execution statistics; it also requires auxiliary structures in the user schema. For information, see Section 1.6.4, "Using the PL/SQL Hierarchical Profiler".

Comment (Profile only): Descriptive comment to be included in the execution profile.

Target: Name of the function or procedure to run or to run in debug mode. (You have a choice only if you specified a package that has more than one subprogram.)

Parameters: List of each parameter for the specified target. The mode of each parameter can be IN (the value is passed in), OUT (the value is returned, or IN/OUT (the value is passed in, and the result of the function or procedure's action is stored in the parameter).

PL/SQL Block: A block of PL/SQL code created by SQL Developer. You should change the formal IN and IN/OUT parameter specifications in this block to actual values that you want to use for running or debugging the function or procedure.

For example, to specify 10 as the value for an input parameter named in_rating, change IN_RATING => IN_RATING to IN_RATING => 10.

When you click OK, SQL Developer runs the function or procedure.

If you are debugging a function or procedure, the debugging toolbar and one or more windows for debug-related information are displayed, as explained in Section 1.6, "Running and Debugging Functions and Procedures".

5.131 Create/Edit Breakpoint

Use this box to create or edit a breakpoint to use when debugging a PL/SQL function or procedure.

Definition tab

Specify the definition of the breakpoint.

Breakpoint Type: Type of breakpoint, indicating when the breakpoint will occur. Options include breaking when one of the following occurs: a specific line of code (Source); exception class or other class; method, file, or watch.

Breakpoint Details: Options depend on the breakpoint type.

Breakpoint Group Name: Breakpoint group in which to include this breakpoint. Breakpoint groups can be edited, enabled, and disabled.

Conditions tab

Specify any conditions that apply to the breakpoint.

Condition: A SQL condition (WHERE clause without the WHERE keyword) restricting when the breakpoint occurs. For example, to specify that the condition should occur only when status_code is greater than 10, specify:

status_code > 10

Thread Options: You can specify whether the breakpoint occurs for all threads, or only when the breakpoint is hit by threads that either do or do not have a specified name.

Pass Count: The number of times the debugger should allow execution to pass over the breakpoint before the breakpoint occurs.

Actions tab

Specify the actions to be taken when the breakpoint occurs. The options you specify override any default values on the Debugger: Breakpoints: Default Actions pane for SQL Developer Preferences.

Halt Execution: Pauses execution when the breakpoint occurs.

Beep: Beeps when the breakpoint occurs.

Log Breakpoint Occurrence: Sends a message to the log window when the breakpoint occurs. You can also specify the following to be included in each display: a tag, and a condition to be evaluated.

Enable/Disable a Group of Breakpoints: Enables or disables the specified breakpoint group when this breakpoint occurs.

5.132 Save/Save As, or Select File

This is a standard box for saving information to a file or for selecting a file: use Location to navigate to (double-clicking) the folder in which to save or open the file, then specify the file name (including any extension) and, if necessary, the file type.

5.133 Save Files

This box asks if you want to save the specified files before another action occurs (for example, saving procedures you had been editing before disconnecting).

5.134 Unable to Save Files

This box informs you that SQL Developer is unable to save the specified file or files. To cancel the attempt to save the files and to return to edit the relevant object, click Cancel.

5.135 Save Style Settings

This dialog box is displayed when you click Save As in the Code Editor: PL/SQL Syntax Colors pane when setting SQL Developer Preferences. You can save the specified color settings as a named color scheme, which adds it to the drop-down list for Scheme in that pane.

5.136 Schema Differences Source or Destination Error

This error box is displayed if you click Apply before specifying the source or the destination, or both, for a schema differences operation.

Click OK to close the error box, then follow the instructions for performing the schema differences operation, as explained in Section 5.63, "Database Schema Differences".

5.137 Script Execution Failed

This error box is displayed if the script generated by the Quick Migrate procedure fails before it completes its execution. The Build pane displays the error that caused the failure.

To close the error box and open the script in a SQL Worksheet window, where you can edit the text and run the corrected script, click Yes; or to close the error box without opening the script in a SQL Worksheet window, click No.

5.138 Script Generation Complete

This information box is displayed after you generate the controlling script and related files for performing an offline capture of a third-party database, as explained in Section 2.2.5.1, "Offline Capture".

Click OK to close the box. Later, run the controlling script to generate output containing the converted model.

5.139 Set Data Mapping

This dialog box is displayed if you right-click a captured model and select Set Data Mapping. You can use this dialog box to specify source data type mappings when migrating the specified third-party database to Oracle. If you are editing an existing mapping, you can change only the Oracle data type, precision, and scale information.

Show only data types used in the source model: If you check this option, only data types used in the selected captured model are shown. If you do not check this option, all valid data types for the source (third-party) database are shown.

Source Data Type: Data type name in the third-party database.

Oracle Data Type: Data type name in Oracle Database.

Type: System for a system-defined data type, or User for a user-defined data type.

Add New Rule: Displays the Add/Edit Rule dialog box, for specifying a mapping for another data type.

Edit Rule: Displays the Add/Edit Rule dialog box, for editing the selected mapping.

Remove Rule: Deletes the selected mapping.

5.140 Add/Edit Rule

This dialog box is displayed if you click Add New Rule or Edit Rule in the Set Data Mapping dialog box, which is used for specifying source data type mappings when migrating a specified third-party database to Oracle.

Source Data Type: Data type name in the third-party database.

Oracle Data Type: Data type name in Oracle Database.

Precision and Scale: Precision and scale values to be used for the source data type and Oracle data type during the conversion.

5.141 Set Encoding

This dialog box is displayed if you right-click a CVS connection and select Set Encoding. Specify a character set for the connection. The character set that you choose is applied to the encoding of files under CVS control through that connection.

Platform Default (Newline Conversions): Uses the character set specified for the platform/operating system. Newline conversions for files crossing different platforms are handled automatically.

IDE Global Setting: Uses the default character set for the integrated development environment (IDE).

Other: Uses the selected character set.

5.142 Set Pause Continue

This dialog box is displayed if you enter the SQL*Plus statement SET PAUSE ON in the SQL Worksheet and then run the worksheet contents as a script. After the SET PAUSE ON statement is processed, execution pauses (and this dialog box is displayed) after each statement until the SET PAUSE OFF statement is processed.

To have execution continue at the next statement, click OK.

5.143 Sign In (checking for updates)

This dialog box is displayed if any of the updates that you selected during the check for updates process are on a remote site that requires you to log in. Currently, all updates are on the Oracle Technology Network (OTN), so you must enter your OTN user name and password.

User Name: Your user name at the remote site.

Password: Your password at the remote site.

Sign Up: If you do not have an account at the remote site, click this link.

Find Password: If you have an account at the remote site but cannot remember your password, click this link.

5.144 Single Record View

The main use for this box, which is displayed by right-clicking the display grid for an object and selecting Single Record View, is to edit data for a table or view, one record at a time. After you change data in any cells in a row, you can apply the changes by clicking Apply or by navigating to another record. (For non-Data grids, the cells are read-only.)

Navigation icons: First (<<) moves to the first record, Previous (<) moves to the previous record, Next (>) moves to the next record, and Last (>) moves to the last record.

Apply: Applies changes made to the current data record.

Cancel: Cancels changes made to the current data record, and closes the box.

5.145 Save Snippet (User-Defined)

Use this box to create a user-defined snippet. For information about how to create user-defined snippets, including options for snippet categories, see Section 1.8.1, "User-Defined Snippets".

Category: Existing or new category in which to place the snippet. To create a new (user-defined) category, type the category name instead of selecting a category name from the list.

Name: Name of the snippet, as it will be displayed when users see the list of available snippets in the specified category. If an existing Oracle-supplied snippet has the same name in the same category, the user-defined snippet definition replaces the Oracle-supplied definition.

ToolTip: Optional tooltip text to be displayed when the mouse pointer stays briefly over the snippet name in the display of snippets in the specified category.

Snippet: Text that will be inserted for this snippet.

5.146 Edit Snippets (User-Defined)

This box displays any existing user-defined snippets, and enables you to add, edit, or delete user-defined snippets.

To edit an existing user-defined snippet, select its row and click the Edit User Snippet icon, which displays the Save Snippet (User-Defined) dialog box.

To create a new user-defined snippet, click the Add User Snippet icon, which displays the Save Snippet (User-Defined) dialog box.

To delete a user-defined snippet, select its row and click the Delete User Snippet icon.

5.147 Show SQL

This displays SQL statements reflecting the current context and content (if applicable).

5.148 Start Date, End Date, Repeat Interval, Comments (Scheduler)

Dialog boxes are available for specifying start and end dates, a repeat interval, and optional comments for an Oracle Scheduler job. For more information about job scheduling, see Section 1.12, "Scheduling Jobs Using SQL Developer".

Start Date, End Date

For Start Date and End Date, specify the date and time, or click Set Today to specify the current date and time.

Tabs are provided for Frequency and various refinements of the frequency. Use as many as you need for specifying when to run the job.

Repeat Interval

Frequency tab: Specify a frequency for the job (such as DAILY or WEEKLY),

Other tabs (most in the form "By xxxxx"): Select (check) Enable to be able to specify the value or values relevant to that frequency category.

A box at the bottom displays the keywords and values for the statement that SQL Developer will use to call the DBMS_SCHEDULER.CREATE_SCHEDULE procedure.

Comments

Optional descriptive comment about the scheduler object.

5.149 Subversion: Add Property

Use this dialog box to add a versioning property for the currently selected file or folder.

5.150 Subversion: Add to Source Control

Use this dialog box to bring a new file under Subversion control.

Files List: Lists the names and physical locations of the files that will be added to Subversion.

5.151 Subversion: Apply Patch

Use this dialog box to apply a previously generated patch. A patch must be applied to the same revision/tag from which it was generated.

The name and location are displayed for the project or set of files to which the patch will be applied.

Patch Source: Specify where the patch will be obtained from: the system clipboard or a file.

System Clipboard: Obtains the patch from the system clipboard.

File: Obtains the patch from a file. If you select this option, a suggested location and name for the patch file is already entered in the text box. You can change this by typing a new location and name into the box, or by using the Browse button to find a new location

5.152 Subversion: Branch/Tag

This dialog box is displayed when you right-click a remote directory in the Subversion repository and select Branch/Tag. Create a branch by copying the current working copy or a revision from the repository to a selected location in the repository.

From: Location of the working copy or revision.

Working Copy: Causes the current working copy to be copied.

HEAD Revision: Causes the HEAD revision (the latest revision in the repository) to be copied.

Use Revision: Causes the revision specified in the text box to be copied. To see a list of revisions from which you can choose, click List Revisions.

To: Destination location.

Comment: Optional descriptive comment.

Switch to new branch/tag: If this option is checked, the existing working copy is switched to the new branch.

After you click OK, the SVN Console - Log pane is displayed at the bottom, with messages about commands that were executed.

5.153 Subversion: Check Out from Subversion

Use this dialog box to check out modules from a Subversion repository, to. create the initial local copies of files and folders that are being stored within a Subversion repository. It is these local copies, stored outside the Subversion repository, that you work on. This location and the files within it constitute the Subversion "working copy".

Note: With Subversion, there is no "check in" procedure, so you do not check in files that you have updated and check them out again when you next want to work on them. Instead, when you have finished working on your local copies of files, you commit them to the Subversion repository to bring the files held there up to date. Also, if you want to incorporate into your local copies changes that other developers have committed to the Subversion repository, you update them.

Destination: Directory or folder into which to place the checked out files. If this destination is not empty, a warning message will be displayed asking if you are sure you want to check out into this directory. (Attempting to check out files into a non-empty destination might reflect a mistake in specifying the destination, or it might be your intention.)

Use Revision: If this option is checked, the revision you specify in the text box is used. To see the available revisions, click the binoculars icon.

Depth: The level of recursion for selecting files to be checked out, from Infinity (all children at all levels under the selected item in the Versioning browser hierarchy( through Empty (only this item and no children).

5.154 Subversion: Commit Resources

Use this dialog box to commit individual files to the Subversion repository. If a file is a child of a changed parent that has not been committed, you must either first commit the parent or instead commit the working copy.

The committed files will replace those in the repository as the most recent. Other developers who subsequently check out or update from these files will see the file changed in comparison with the previous version held in the repository.

Files List: Lists the names and physical locations of the files that will be committed to the Subversion repository.

Keep Locks: Retains the locks that you previously obtained on the files that you are about to commit. This will mean that other developers will still not be able to commit changes they may have made to the files.

Comments: Comments to accompany the commit action. You will later be able to see these comments when viewing the list of versions of a particular file.

Template or Previous Comments: A template with comment text for the Comments box.

5.155 Subversion: Commit Working Copy

Use this dialog box to commit the working copy to the Subversion repository. The committed files will replace those in the repository as the most recent. Other developers who subsequently check out or update from these files will see the file changed in comparison with the previous version held in the repository.

Files List: Lists the names and physical locations of the working copy that will be committed to the Subversion repository.

Keep Locks: Retains the locks that you previously obtained on the files that you are about to commit. This will mean that other developers will still not be able to commit changes they may have made to the files.

Comments: Comments to accompany the commit action. You will later be able to see these comments when viewing the list of versions of a particular file.

5.156 Subversion: Confirm Checkout

This dialog box is displayed if you attempt to check out from the repository root, as opposed to from the branches, tags, or trunk of the repository. To proceed with the checkout from the root, click Yes; to cancel this request, click No.

Skip This Message Next Time: If you enable this option, on future requests to check out from the repository root, this dialog box will not be displayed and the operation will proceed as if you had clicked Yes.

5.157 Subversion: Create Remote Directory

Use this dialog box to create a remote directory for a connection in a Subversion repository.

Directory Name: Directory name to be associated with the specified URL.

Comments: Optional descriptive comment.

5.158 Subversion: Create Subversion Repository

This information applies to creating a Subversion repository. A connection to the repository will be created automatically. For information about SQL Developer support for versioning and Subversion, see Section 1.10.

Repository Path: Location for the new Subversion repository. You can Browse to select the location.

File System Type: Data storage system type for the repository. For information about choosing a system, see "Version Control with Subversion" at http://svnbook.red-bean.com/.

Connection Name: Name for this connection. If you leave this box blank, the connection will be given a name based on the URL of the repository location.

5.159 Subversion: Create/Edit Subversion Connection

This information applies to creating or editing a Subversion connection. For information about SQL Developer support for versioning and Subversion, see Section 1.10.

Repository URL: Full, valid URL for the location of the Subversion repository. The following are URL schemas and the access methods they map to:

Connection Name: Name for this connection. If you leave this box blank, the connection will be given a name based on the URL of the repository location.

User Name: User name known to the repository, if the repository requires user and password validation.

Password: Password for the specified user, or blank if a password is not required.

Test Read Access: Attempts to establish a connection for read access to the Subversion repository.

Status: Displays the result of the test (success or an error message).

5.160 Subversion: Delete Resources

Use this dialog box to delete the selected directory (folder) in the repository.

Comment: Comment explaining the deletion.

5.161 Subversion: Edit Configuration File

This dialog box is displayed if you click Edit "server" in the Versioning: Subversion: General preferences pane. You can modify the Subversion configuration file directly.

Reset: Discards any changes that you have made and leaves the dialog box open.

To save any changes and close the box, click OK; to discard any changes and close the box, click Cancel.

5.162 Subversion: Export Files

Use this dialog box to copy files from the Subversion repository to a local file system directory, or to copy working copies to a local file system directory.

Working Copy Path: The location of the files that will be copied for export. Only files that are under Subversion control will be exported.

Destination Path: A path that includes the directory where you want the files to be copied to.

5.163 Subversion: Export Subversion Connections

Use this dialog box to export the details of one or more current Subversion connections to a file. The details can subsequently be imported from the file to re-create the connections.

File Name: The location and name for the file that will contain the connection details, or browse to a file/location using the Browse button.

Connections: Select one or more connections whose details will be exported.

5.164 Subversion: Ignore

Use this dialog box to mark a file, or a pattern that identifies common file names, as content that Subversion should ignore. (This dialog box sets the svn:ignore property for the specified content.)

Often, a directory contains files that should not be kept under version control. For example, log files from a debug or batch operation do not need to be tracked or merged, yet they are often in the same directory as the shared code for a project. Such files should be marked to be ignored by Subversion.

5.165 Subversion: Import Subversion Connections

Use this dialog box to import the details of Subversion connections from a previously created file.

File Name: The location and name for the file that contains the connection details, or browse to a file/location using the Browse button.

Connections: Select one or more connections whose details will be imported. If a connection to be imported already exists with the same URL, you will be asked to confirm whether you want to overwrite the existing connection details with the details in the imported connection.

5.166 Subversion: Import to Subversion

Use this wizard to import source files into the Subversion repository To go from one step to the next, click Next; to go back to the previous step, click Back.

Destination

Use to identify the Subversion repository, and directory within the repository, where the imported files will be stored.

Repository Connection: The connection for the Subversion repository in which you wish to store the imported files

Path: The directory within the Subversion repository for storing the imported files.

Source

Source Directory: The directory containing the source files that you want to import into Subversion. Initially contains a path based on the item that was selected when you launched the wizard.

Comments: Comment text to accompany the imported files. The comments are recorded with the files in the Subversion repository and will be viewable with the version history of the files. You must enter some comment text; otherwise, an error will occur when you click Finish to attempt to perform the import operation.

Filters

Filters that will be applied to the import operation. If you do not want one or more of the filters to be applied, move them from Selected Filters to Available Filters using the left arrow keys. If necessary, you can use the right arrow keys to move filters from Available Filters to Selected Filters.

New: Displays a dialog box in which you can create a new filter that will be applied to the import operation. New filters are added to the Selected Filters list.

Options

You can configure options specific to the import operation.

Do Not Recurse: If this option is enabled, it prevents files being imported from directories subordinate to the one you identified on the Source page.

Perform Checkout : If this option is enabled, the imported source files will be checked out after import.

Summary

Displays the selected options for the import operation. To make any changes, click Back. To perform the operation, click Finish.

5.167 Subversion: Lock Resources

Use this dialog box to perform a Subversion lock operation on one or more checked out files (working copies).

Files List: Lists the names and physical locations of the files to be locked. You can individually select and deselect files.

Steal Lock: Breaks any existing locks and relocks the files for your use. Causes the --force option to be added to the underlying svn lock command.

Comments: Comments to accompany the action.

5.168 Subversion: Merge

A merge operation copies changes made in one branch to another branch, or copies changes from a branch to the trunk (main line of development). It is typically used to bring another developer's work into your own files, and to merge private development back into the main line of development.

The merge is created by comparing the content of two revisions within the Subversion repository, and applying the differences to a Subversion working copy. If you subsequently wish to use the result of the merge in the main line of development, you commit the working copy to the Subversion repository in the usual way.

Specify the following:

Your selection for Merge Type affects the content of subsequent displays, which can include the following.

From URL and its (start) revision to merge: The resource that is the basis of the comparison. (The resource entered in the To URL box will be compared against the resource entered here.)

HEAD Revision from Repository: Causes the comparison to be made against the most recently committed resources in the Subversion repository.

Use Revision: Causes the comparison to be made against a resource in the Subversion repository with a particular revision number. When selected, the accompanying text box becomes available. You can then enter a revision number into the text box, or click the List Revisions button to select the revision that you require.

To URL and its (end) revision to merge: The resource that will be compared with the base resource selected in the From URL box.

Same as "From" URL: Uses the same base repository location for both elements of the comparison.

Ignore Ancestry: Ignores any relationships between resources in the Subversion repository when comparing the start and end revisions. The effect of this will be to retain resources that have names identical to those they are being compared with, even though the resources have no common ancestry. The alternative is that a resource that predates an identically named one may be deleted and replaced with the later resource.

Dry Run: Causes the comparison to be performed without the changes being applied to the Subversion working copy. The results of the comparison are displayed in the Messages - Log window.

5.169 Subversion: Pending Changes

This window shows files that have been added, modified or removed, either locally or remotely; files whose content conflicts with other versions of the same file; and files that have not been added to source control. This window is opened automatically when you first initiate an action that changes the local source control status of a file. You can also open this window manually.

The window shows any outgoing changes (files that have been added, modified or removed locally, and local files whose content conflicts with remote files), candidates (files that have been created locally but not yet added to source control), and incoming changes (files that have been added, modified or removed at a remote location).

You can restrict the files shown in the Pending Changes window by selecting a scope from the drop-down list. The default scope is Active Application, which will show files from the application currently selected in the navigator.

You can carry out appropriate source control actions on the files listed in the window, using the buttons in the toolbar. Clicking a button will either immediately initiate the operation, or open a dialog box through which you can choose options before proceeding.

5.170 Subversion: Properties

This dialog box is displayed in you right-click a node under a connection in the Versioning navigator and select Properties. It displays properties and property values for the selected object.

5.171 Subversion: Remove from Subversion

Use this dialog box to begin the process of removing the listed files from the Subversion repository.

After you have clicked OK, the listed files will appear on the Outgoing tab of the Pending Changes window. The files will be removed from the Subversion dialog when you next commit the individual files or the working copy that they are part of.

5.172 Subversion: Repository Browser

Use this dialog box to select the location of a Subversion repository when using the branching, merging, and switching facilities. Locations in this dialog are shown as directories and objects. The chosen location is ultimately returned from this dialog as a URL.

Repository Connection: If the required location already exists, select it from the browser tree.

To create a new location, navigate to a parent directory, then select the Create New Remote Directory icon. This opens a dialog box that will show the location of the parent object (in the form of a URL) and let you name a directory beneath that one that will become the new location.

5.173 Subversion: Revert Local Changes

Use this dialog box to revert files to their previous state.

If the contents of a file have been changed, the file will be reverted to its base revision. If a file has been added but not yet committed, it will revert to unadded status. If a file is scheduled for removal (in the Pending Changes window), it will be added back to the navigator and given its previous status

Files List: Lists the names and physical locations of the files that will be reverted.

Recursive: Select if you want the revert operation to recurse into child objects of those selected.

5.174 Subversion: Switch

Use this dialog box to update the current working copy of the specified file from the specified repository and revision.

From URL: Full URL for the repository location associated with the current working copy.

To URL: Full URL for the repository location to use to update the current working copy.

HEAD Revision: Causes the HEAD revision (the latest revision in the repository) to be used for the update operation.

Use Revision: Causes the revision specified in the text box to be used for the update operation. To see a list of revisions from which you can choose, click List Revisions.

5.175 Subversion: Unlock Resources

Use this dialog box to perform a Subversion unlock operation on one or more locked, checked out files (working copies).

Files List: Lists the names and physical locations of the files to be unlocked. You can individually select and deselect files.

Force Unlock: Breaks any existing locks and unlocks the files. Causes the --force option to be added to the underlying svn unlock command.

5.176 Subversion: Update Resources

Use this dialog box to incorporate into your local copies changes that other developers have committed to the Subversion repository.

Files List: Lists the names and physical locations of the files that will be updated with content from the Subversion repository.

Use Revision: Updates the files with content from a particular revision within the Subversion repository. Enter the revision number in the adjacent text box. If not selected, the files will be updated from the HEAD revision.

Ignore Externals: Select if you do not want the update operation to apply to external working copies created as the result of externals definition handling. Externals definitions are used to pull data from multiple repositories. See the Subversion documentation for details.

Recursive: Deselect if you do not want the update operation to recurse into child objects of those selected.

5.177 Subversion: Update Working Copy

Use this dialog box to update individual files with content from the Subversion repository.

Files List: Lists the names and physical locations of the files that will be updated with content from the Subversion repository.

Use Revision: Updates the files with content from a particular revision within the Subversion repository. Enter the revision number in the adjacent text box. If not selected, the files will be updated from the HEAD revision.

Ignore Externals: Select if you do not want the update operation to apply to external working copies created as the result of externals definition handling. Externals definitions are used to pull data from multiple repositories. See the Subversion documentation for details.

Recursive: Deselect if you do not want the update operation to recurse into child objects of those selected.

5.178 Subversion: Versioning Properties

This dialog box displays general and versioning information about the currently selected file or folder.

5.179 Third-Party Database Objects

You have requested help about a type of object in the context of a third-party (non-Oracle) database connection. See the documentation for the third-party database for information about database objects as they apply to that product.

5.180 Unable to Connect

This box informs you that SQL Developer is unable to connect to the Internet. The cause might be that the connection information for the specified HTTP proxy server is invalid or the server in not available.

5.181 Unable to Open File

This box informs you that SQL Developer is unable to perform the export operation to the location and file that you specified. The cause might be that you do not have permission to write to that location.

5.182 Unit Testing: Action Required

This dialog box is displayed if you are creating a repository but the database user for the repository does not have the necessary privileges.

SQL Developer will issue one or more prompts. For each, click Yes, enter the password for the SYS account, and allow the commands to execute.

5.183 Unit Testing: Add Category

Use this dialog box to create a lookup category for unit testing. (Using lookups is explained in Section 3.6, "Using Lookups to Simplify Unit Test Creation".)

5.184 Unit Testing: Add Data Type

Use this dialog box to add a data type to a lookup category for unit testing. (The SQL Developer unit testing feature is explained in Chapter 3.)

5.185 Unit Testing: Add Item to Library

Use this dialog box to add a startup, teardown, or validation specification to the unit testing library, depending on which node you have selected in the Unit Test navigator. After you specify a name and click OK, specify a connection, and then complete the definition. (The SQL Developer unit testing feature is explained in Chapter 3.)

5.186 Unit Testing: Add Test Implementation

Use this dialog box to add an implementation for a unit test. (The SQL Developer unit testing feature is explained in Chapter 3.)

When you create a unit test, a default implementation is created; however, you can specify one or more additional implementations.

5.187 Unit Testing: Add Test Suite

Use this dialog box to create a test suite for unit testing. (The SQL Developer unit testing feature is explained in Chapter 3.)

5.188 Unit Testing: Add Tests or Suites to a Suite

Use this dialog box to add one or more unit tests or suites to a test suite. The Tests or Suites tab is displayed, depending on whether you right-clicked the Tests or Suites node in the Unit Test navigator display and selected Add Test or Add Suite. (The SQL Developer unit testing feature is explained in Chapter 3.)

List of tests or suites: Select one or more tests to be added to the suite.

Run Test Startups: If this option is checked, the startup action defined for each specified test or suite is run when the suite is run.

Run Test Teardowns: If this option is checked, the teardown action defined for each specified test or suite is run when the suite is run.

Any startup and teardown actions for a test are specified when you create the unit test (see Unit Testing: Create Unit Test).

5.189 Unit Testing: Copy or Rename Unit Test

Use this dialog box to copy or rename a unit test. (The SQL Developer unit testing feature is explained in Chapter 3.)

If you specified Copy, a copy of the selected unit test is created and is given the name you specify.

If you specified Rename, the name of the selected unit test is changed to the name you specify.

5.190 Unit Testing: Create Unit Test

Use this wizard to create a unit test. (The SQL Developer unit testing feature is explained in Chapter 3.) To go from one step to the next, click Next; to go back to the previous step, click Back.

Select Operation

Select a database connection on the right, then use the hierarchy tree to select an object to be tested. For example, to test a procedure, expand the Procedures node and select the desired procedure.

Specify Test Name

Test Name: Name for the unit test. Can be the same as the name of the database object (for example, procedure or function) to be tested.

Create with single dummy representation: Creates a single (sometimes called "one-off") test case for which you must specify the input parameter values when you run the test.

Select/create implementation using lookup values: Generates multiple test cases using sets of input parameter values that you will specify.

Specify Startup

Specify the action to perform at the start of the test, before any of the actual test operations are performed. Reasons for using a startup action might include the following:

None: Perform no startup action.

Row Copy: Copy rows from a specified table. You will be asked to specify the table and q query for selecting the rows.

Table Copy: Copy a specified table. You will be asked to specify the table. For example, you might want to copy the EMPLOYEES table to a table named EMPLOYEES_ORIGINAL.

User PL/SQL Code: Run a script. You will be asked to specify the script file.

Specify Parameters

If you are creating a single test case, specify the parameter values in the Input column for each input parameter. For example, for the example award_bonus procedure, you might test the case where employee ID 1001 sold 5000 dollars work of goods or services (EMP_ID Input: 1001, SALES_AMT Input: 5000).

If you are creating multiple test cases, specify the information for each parameter for each test case. For example, to create a test with 5 test cases for the example award_bonus procedure, you need to enter 10 rows on this page (2 input parameters times 5 test cases).

Specify Validations

Specify one or more validation actions to perform after the test case is run, to check whether the desired result was returned. For example, if a test case for a procedure was supposed to increase the salary for employee number 1001 to $2000, specify a Query returning row(s) validation of SELECT salary FROM employees WHERE employee_id = 1001 AND salary = 2000.

For all validation action options except None, you will be prompted to specify the required information.

To add a validation action for a test case, click the Add (+) icon; to delete a validation action for a test case, select it and click the Remove (X) icon.

None: Perform no validation action.

Boolean function: Validation succeeds if Boolean TRUE is returned by the specified function.

Query returning no row(s): Validation succeeds if the specified query returns no rows.

Query returning row(s): Validation succeeds if the specified query returns one or more rows that satisfy the specified query.

Result Set Compare: Validation succeeds if the results returned by the specified query match the values that you specify.

Table Compare: Validation succeeds if the target table (the table modified by the test case) is identical to the specified table.

User PL/SQL Code: Validation succeeds if the specified script runs successfully.

Specify Teardown

Specify the action to perform after the validation action (or the test case in no validation action was specified) is finished. Reasons for using a teardown action might include the following:

None: Perform no teardown action.

Table Drop: Drop (delete) the specified table.

Table Restore: Replace all rows in the specified target table (the table modified by the test case) with the rows in the specified source table. For example, you might want to replace the contents of EMPLOYEES (target) table with a source table named EMPLOYEES_ORIGINAL.

User PL/SQL Code: Run a script. You will be asked to specify the script file.

Summary

Displays the selected options. To make any changes, click Back. To perform the operation, click Finish.

5.191 Unit Testing: Manage Users

Use this dialog box to add or remove database users as unit test users (Users tab) or administrators (Administrators tab). (Managing unit test users and administrators is explained in Section 3.3.1.)

On each tab, use the icons to move selected database users (> or <) or all listed database users (>> or <<) into or out of the column on the right, which identifies the unit testing users or administrators.

With Admin: If this option is checked, the users in the column on the right can grant the specified unit test role (User or Administrator) to other database users.

5.192 Unit Testing: Rename Test Implementation

Use this dialog box to rename an implementation for a unit test. (The SQL Developer unit testing feature is explained in Chapter 3.)

5.193 Unit Testing: Result of Operation

This informational box indicates either that the operation was successful or that an error occurred. If an error occurred, a brief explanation is included.

5.194 Unit Testing: Synchronize Unit Test

Use this wizard to synchronize a unit test, that is, to update the selected unit test to reflect changes in the operation to be tested. For example, you might want the unit test to use a new procedure (perhaps owned by a different database user) instead of the procedure for which the unit test was originally defined. (The SQL Developer unit testing feature is explained in Chapter 3.) To go from one step to the next, click Next; to go back to the previous step, click Back.

Select Operation

Select the database connection (associated with the owner of the object) on the right, then use the hierarchy tree to select an object to be used as the new operation. For example, to specify a different procedure, expand the Procedures node and select the desired procedure.

Current Target Operation: The operation currently associated with this unit test.

Select New Target Operation: Use the hierarchy tree to select the new operation to be associated with this unit test.

Reset: Discards any unsaved specifications and restores the current definition.

Match Parameters by Name/Position: Specify whether to match the new operation's parameters with the current operation's parameters by using the names or positions of the parameters in the format (signature) of the object.

Connection selector: Select the database connection to be used for displaying the hierarchy of objects from which to select the new operation. For example, if the new procedure is in a different database schema, select a connection associated with the database user for that schema.

New Target Operation: Displays the result of your selection.

Summary

Displays information about the current operation, the new operation, and the changes that will be made to the unit test. To make any changes, click Back. To perform the operation, click Finish.

5.195 Export Wizard (Unload Database Objects and Data)

Use this wizard to export database objects and optionally data. For a selected database connection, you can export some or all objects of one or more types of database objects to output files, a SQL Worksheet, or the clipboard. The output may contain SQL data definition language (DDL) statements to create these objects. It may also contain SQL statements to insert data into these objects and other formatted files.

The number of panes and the options available depend on the potential scope of the export operation. For example, if you clicked Tools, then Database Export, all panes are available; however, if you right-clicked a table name and selected Export, fewer panes are required.

In several panes, if you select Proceed to summary, clicking Next takes you to the Export Summary pane.

Source/Destination pane

Contains up to four main areas for specifying the database connection and the DDL, data, and target options. If you specify any options, they override the defaults for Database: Export (Unload) / View DDL Options user preferences.

Connection: Select the database connection with the objects to be exported.

Other options on this pane: See Database: Export (Unload) / View DDL Options for explanations of individual options.

Export DDL: If this option is enabled, DDL statements are included in the export operation. Select the options to apply to the DDL that is generated.

Export Data: If this option is enabled, data is included in the operation. Select the options to apply to the data that is unloaded.

Format: Select the desired output format for the data to be unloaded. Depending on the selected format, other options may appear. For example, for xls (Microsoft Excel file), you can specify worksheet names for the data and the SELECT statement.

To paste unloaded data into a Microsoft Excel file, specify Export Data but not Export DDL, select text for Format, and select Clipboard for Save As; and after completing the unload, paste from the clipboard into Excel.

For exporting date fields in date format instead of timestamp format: If you encounter problems exporting dates, check that the following line is in the sqldeveloper.conf file to have dates unloaded in date format: AddVMOption -Doracle.jdbc.mapDateToTimestamp=false

For exporting large tables to Microsoft Excel files:

Save As: Specifies how or where target statements and data are to be saved:

Types to Export pane

Specify object types to be exported and options for the export operation.

Object Types: Check the types of objects that you want to export. You can click Toggle All to check and uncheck all individual types. You must select at least one object type.

Specify Objects pane

You can limit the types or objects, and the objects within selected types, for the export operation. If you select an object type and do not select specific objects for that type, all objects for that type are unloaded.

Name: Shows only objects that satisfy the filter specification. You can use the standard % wildcard character (for example, EM% to show all objects starting with EM). You can use quotation marks (double quotes) to specify mixed-case searching.

%: If this option is checked, the % wildcard is automatically added to any Name string that you specify.

More and Less: Click More to enable filtering by Schema and Type; click Less to hide the Schema and Type controls.

Schema: Select the database schema in which to search for objects.

Type: Select All Objects for all object types, or a specific type of object to further filter the display of objects.

Lookup: Click Lookup to display a list of objects that meet the selection criteria for the selected connection. Use the arrow keys to move selected objects or all objects from the available objects box to the selected objects box.

Specify Data pane

You can limit the data for the export operation by selecting objects in the same way as as in the Specify Objects pane.

You can select the columns included in the unload by clicking the pencil icon in the Columns cell. (if you click the pencil icon in the Columns cell). (Note: For CLOB columns, only the first 32 KB of any CLOB is exported.)

You can limit the rows in the unload operation by entering a valid WHERE clause for a query on the table, without the WHERE keyword. For example, to restrict the exported data to rows where a column named RATING contains a value greater than 5, specify rating > 5 and click Go to see the results of the query

Export Summary pane

You can review the information that will be used to create the output file, which will contain statements to export database objects and data according to your specifications.

To go back and make any changes, click Back as needed.

To create the output file, click Finish. If a script is created and if Save As was specified as Worksheet, the file is displayed in a SQL Worksheet window, where you can run it as a script and perform other operations.

5.196 Unsupported Database Version

This box is displayed if you try to create a connection to a database release that is not supported by SQL Developer, such as Oracle Database release 8.1. For information about database releases supported by SQL Developer, see Oracle SQL Developer Installation Guide.

5.197 Validate Geometry

This dialog box is displayed if you select a table and specify Validate Geometry Using Tolerance or Validate Geometry Using Dimension Information. Internally, SQL Developer calls the SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT procedure, which examines the specified geometry column to determine if the stored geometries follow the defined rules for geometry objects, and returns context information about any invalid geometries. For information about the specific consistency checks performed, see the documentation for the SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT procedure in Oracle Spatial Developer's Guide.

Table Name: Name of the feature table that has a column of type SDO_GEOMETRY.

Tolerance (for Validate Geometry Using Tolerance): Tolerance value to be used for validating the geometries. For example, you might try larger values to see if more geometries would be invalid, or smaller values to see if fewer geometries would be invalid.

Spatial Column: Name of the column of type SDO_GEOMETRY in the specified feature table.

Identification Columns: Other (non-spatial) columns in the table.

Validation Results: For any invalid geometries, identifies the ROWID value of the row containing the invalid geometry, and an Oracle error message number and the context of the error (the coordinate, edge, or ring that causes the geometry to be invalid). You can then look up the error message for more information about the cause of the failure.

5.198 Windows

This dialog box is displayed if you right-click the tab for a window in the display area of the SQL Developer main window.

Windows: A list of the windows in the display area.

Activate: Makes active (switches focus to) the selected window.

Close: Closes the selected window.

5.199 XMLType Column Properties

This dialog box is displayed if you define a table column as type SYS.XMLTYPE and click the XMLType Properties button. For more information about XMLType storage models, see Oracle XML DB Developer's Guide.

Store As: Binary XML (data stored in compact, post-parse, XML schema-aware format; can be used with XML data that is not based on an XML schema), CLOB (data stored in Character Large Object instances), or Object Relational (data stored as a set of objects).

XML Schema Owner: Owner of the XML schema.

XML Schema: Name of the XML Schema. For example: http://xmlns.oracle.com/xdb/XDBResource.xsd

Element: Select the desired element from the list of elements defined in the XML schema.

Allow Any Schema (only if Store As is Binary XML): Encodes all XML schema-based documents using an encoding based on the XML schema referenced by the document.

Allow Non-Schema (only if Store As is Binary XML): Encodes all XML schema-based documents using an encoding based on the referenced XML schema; encodes all non-schema-based documents using the non-schema-based encoding.