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

E38414-07
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 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.4 Associate Repository

This dialog box is displayed if you click Tools, then Migration, then Repository Management, then Associate Repository.

Associate 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.5 Cart Error (Objects Not Available)

This box, with the text Some objects included in the cart are not available. Review cart contents., is displayed when you attempt to perform a Cart operation when one or more of the database objects in the selected cart are no longer available. For example, if you attempt to export some tables that include TABLE_1, but TABLE_1 was dropped or the database connection was deleted after that table was added to the cart, then this error box is displayed.

To fix the problem, ensure that you are attempting to perform the Cart operation only on database objects that exist and are available.

5.6 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.7 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.8 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.9 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.10 Clone Pluggable Database

Use this dialog box to clone a PDB. It creates a new PDB from (using basic specifications of) a specified PDB.

Database Name: Name of the new PDB.

Source PDB: Source PDB for the cloning operation.

Database Link: If the source PDB is in a remote CDB, specify the name of the database link to use for connecting to the remote CDB.

Storage: You can specify storage limits for the PDB total size or temporary tablespace usage, or both; or unlimited storage for either or both.

File Name Conversions: Determines how the database generates the names of files (such as data files and wallet files) for the PDB.

  • None: The database first attempts to use Oracle Managed Files to generate file names. If you are not using Oracle Managed Files, then the database uses the PDB_FILE_NAME_CONVERT initialization parameter to generate file names.

  • Custom Names: Select a Source Files/Target Files pair.

  • Custom Expressions: Specify one or more Source File Expression/Target File Expression pairs. Each pair item is a string found in names of files associated with the seed (when creating a PDB by using the seed), associated with the source PDB (when cloning a PDB), or listed in the XML file (when plugging a PDB into a CDB).

5.11 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.12 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.11.

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

5.13 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.20.9, "File Types").

5.14 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.15 Copy Objects

This dialog box is displayed if you click the Copy Objects icon in the Cart window, which is explained in Section 1.14, "Using the Cart".

Open Configuration icon: Opens a previously saved XML configuration file, to use its settings as defaults for this use of the dialog box (see Save or Open Cart Tool Configuration).

Save Configuration icon: Saves the current settings in the dialog box to an XML file, which you can later open to use for Cart operations of this type (see Save or Open Cart Tool Configuration).

Destination Connection: Database connection into which to copy the objects.

Copy DDL: Copies the object definitions. For destination objects with the same names as source objects of the same type (for example, if both contain a table named EMPLOYEES), specify whether to not perform the copy (that is, do not replace the destination objects) or to perform the copy (that is, replace the existing destination objects with the source objects).

Copy Data: Copies the data for any tables and views that are copied. If you do not select this option, any copied tables or views are empty in the destination connection.

Truncate Destination Data Before Copying: If a table or view of the same name already exists in the destination connection (for example, if an EMPLOYEES table exists in the source and destination), this option deletes any existing data in the destination object before copying the data from the source connection. If this option is not selected, the copied data is appended to the existing data in the destination object.

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

Enter the appropriate information in the dialog box that is displayed (for example, Insert CDATA or Insert Processing Instruction in an XML file).

5.17 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.18 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.19 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.20 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.21 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.22 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.23 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.24 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.25 Create/Edit Chain

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

(To create a chain, SQL Developer internally uses the DBMS_SCHEDULER.CREATE_CHAIN procedure.)

Name: Name of the chain.

Enabled: Enables the chain. (Causes the DBMS_SCHEDULER.ENABLE procedure to be called after the chain is created.)

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

5.26 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.13, "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.

Enabled: If this option is selected, the credential is enabled; if this option is not enabled, the credential is not enabled.

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.27 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.11.

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.28 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 to 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.

Connection Color: Lets you specify a standard or custom color for the border of any SQL Worksheet and other windows associated with the connection. The border can be a helpful visual indicator for preventing confusion; for example, if you use red borders for connections to the production database, it might prevent you from mistakenly dropping a table in the production environment when you intended to drop it in the development environment.

Connection Color does not affect the display of the connection name in the Connections navigator.

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 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 all tnsnames.* files on your system, unless you have set the Database: Advanced preference Tnsnames Directory to identify the location of the tnsnames.ora file to be used.)

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

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.

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.

PostgreSQL tab

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

For PostgreSQL migrations, only online capture and online data move are supported. (DDL can be generated, but the data must be moved using the online method.)

Note that to connect to a PostgreSQL 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 PostgreSQL database.

Port: TCP/IP Port on which PostgreSQL will listen.

Choose Database: Name of the PostgreSQL database.

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.

Use Default Password: If this option is checked, the SQL Server or Sybase default password for new users and for resetting passwords is used.

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

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.29 New/Edit Cloud Connection

Use this dialog box to create or edit a connection to an Oracle Cloud Database Service instance. You will need the following information from the Welcome to Oracle Cloud email you received when the Cloud service with database schema information was activated:

  • Service Home: the Cloud service URL

  • User: the SFTP user for this Cloud service

  • Secure FTP Site: the SFTP site URL

Connection Name: A name for this connection to a specified Cloud Database Service instance. Can be any name that you choose.

Database

Username: Username required during sign in when launching the Cloud service. (You cannot specify the password here. When you attempt to connect to the Cloud connection, an Authentication dialog box is displayed, prompting you for the user name and password.)

URL: Service Home URL from the Welcome to Oracle Cloud email under Service Details. May be in the following form:

https://database-<identity-group>.db.<data-center>.<host-name>/<server-path>/

Advanced: Displays the Advanced Connection Information dialog box.

SFTP

Username: Secure FTP user name that you received in the Welcome to Oracle Cloud email under Service Details. (You cannot specify the password here. You will be prompted for the password when necessary.)

Hostname: Secure FTP Site from the Welcome to Oracle Cloud email under Service Details.

Port: Secure FTP port number (for example, 22).

See Also:

Section about using SQL Developer for data loading in the chapter about developing applications in Oracle Public Cloud Using Oracle Database Cloud Service

5.29.1 Advanced Connection Information

This dialog box is displayed if you click Advanced in the New/Edit Cloud Connection dialog box.

HTTP or HTTPS: Select the desired protocol. (HTTPS provides secure communication.)

Hostname: <host-name> part of the URL in the New/Edit Cloud Connection dialog box.

Port: Port number (if any).

Server Path: <server-path> part of the URL in the New/Edit Cloud Connection dialog box. For example: /apex/

Service Name: Service name. For example: _sqldev/

5.30 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.31 Delete Confirmation

This dialog box is displayed in certain situations to confirm whether you want to delete the selected object or objects. To perform the deletion, click Yes; to cancel the deletion request, click No.

5.32 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.33 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.34 Select Connection

Use this dialog box to select a database or other type of connection for use with a specific SQL Developer feature (for example, the SQL worksheet, the Data Miner navigator, 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 connection, click the plus (+) icon; to edit the selected connection, click the pencil icon. In both cases, a dialog box for specifying connection information is displayed (for database connection, see Section 5.28, "Create/Edit/Select Database Connection").

5.35 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.36 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.37 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.38 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.39 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.40 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.41 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.42 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: Utilities: Import 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: Utilities: Import 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:

  • Insert: For new tables, the table will be created and data will be inserted.

  • Insert Script: A script will be generated with DDL statements to crete the new tables and INSERT statements to add the data rows.

  • External Table: External table DDL statements will be generated for accessing the file as a read-only table.

  • Staging External Table: A script will be generated with DDL statements to create new tables and a staging external table for reading the file, and with INSERT statements for inserting the data into the table from the staging external table.

  • SQL*Loader: For new tables, files will be created for running SQL*Loader to load the data; each table can be created during the import, or a script can be generated with DDL statements to create the table.

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.43 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.44 Create/Edit Database Destination

This interface is used for creating or editing an Oracle Scheduler database destination. For more information about job scheduling, see Section 1.13, "Scheduling Jobs Using SQL Developer".

(To create a database destination, SQL Developer internally uses the DBMS_SCHEDULER.CREATE_DATABASE_DESTINATION procedure.)

Name: Name of the database destination. It can optionally be prefixed with a schema name. Cannot be null. It is converted to uppercase unless enclosed in double-quotes.

Enabled: Enables the database destination. (Causes the DBMS_SCHEDULER.ENABLE procedure to be called after the database destination is created.)

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

Connection Info: An Oracle Net connect identifier that is resolved to the Oracle database instance being connected to.

Agent: The external destination name of the Scheduler agent to connect. Equivalent to an agent name.

5.45 Create/Edit Destination Group (Database or External)

This interface is used for creating a new Oracle Scheduler database or external destination group. For more information about job scheduling, see Section 1.13, "Scheduling Jobs Using SQL Developer".

Properties

Name: Name of the destination group. It can optionally be prefixed with a schema name. Cannot be null. It is converted to uppercase unless enclosed in double-quotes.

Enabled: Enables the destination group. (Causes the DBMS_SCHEDULER.ENABLE procedure to be called after the destination group is created.)

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

Members

Select members for the group, and specify whether to use the default credential for all members.

5.46 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.47 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.12, "Indexes". For detailed information about all index-related options, see the CREATE INDEX reference section in Oracle Database SQL Language Reference.

Schema: Database in which to create the index (often the same as the user creating the index).

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

Definition

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

Table: Name of the table associated with the index.

Index Type: The type of Oracle index. 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; Domain lets you define an index for a specialized domain (for example, for a spatial index: Indextype Schema = MDSYS, Indextype = SPATIAL_INDEX).

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

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

Key Compression: You can enable 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 Degree and Degree: If you enable parallel creation of the index, specify Default for the default degree (the optimum degree of parallelism is automatically calculated) or Select to specify an integer value for 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.)

Storage

Storage Options: Lets you specify Storage Options for the index.

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.

DDL

Read-only display of the DDL statement or statements to create or edit the index using the current specifications.

Save: Click to save the DDL statement or statements to a ,sql file.

5.48 Create File Watcher

This interface is used for creating a new Oracle Scheduler file watcher. For more information about job scheduling, see Section 1.13, "Scheduling Jobs Using SQL Developer".

(To create a file watcher, SQL Developer internally uses the DBMS_SCHEDULER.CREATE_FILE_WATCHER procedure.)

Name: Name of the file watcher. It can optionally be prefixed with a schema name. Cannot be null. It is converted to uppercase unless enclosed in double-quotes.

Enabled: Enables the file watcher. (Causes the DBMS_SCHEDULER.ENABLE procedure to be called after the file watcher is created.)

Description: Optional descriptive text.

Destination: Name of an external destination. You create an external destination by registering a remote Scheduler agent with the database.The view ALL_SCHEDULER_EXTERNAL_DESTS lists valid external destination names. If this parameter is null, the file watcher is created on the local host.

Directory Path: Directory in which the file is expected to arrive. The single wildcard '?' at the beginning of the path denotes the Oracle home path. For example, '?/rdbms/log' denotes the rdbms/log subdirectory of the Oracle home directory.

File Name: Name of the file to look for. Two wildcards are permitted anywhere in the file name: '?' denotes any single character, and '*' denotes zero or more characters.

Credential Name: Name of a valid credential object. The file watcher uses the credential to authenticate itself with the host operating system to access the watched-for file. The file watcher owner must have EXECUTE privileges on the credential.

Min File Size: Minimum size in bytes that the file must be before the file watcher considers the file found.

Steady State Duration: Minimum time interval that the file must remain unchanged before the file watcher considers the file found. Cannot exceed one hour. If null, an internal value is used. The minimum value is 10 seconds. Oracle recommends similar steady state duration values for all file watchers for efficient file watcher job operation. Also, the repeat interval of the file watcher schedule must be equal or greater than the steady state duration value.

5.49 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.50 Create/Edit Job

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

(To create a job, SQL Developer internally uses the DBMS_SCHEDULER.CREATE_JOB procedure, which is documented in Oracle Database PL/SQL Packages and Types Reference.)

Job Details

Job Name: Name of the job.

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

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

Job Class: Name of the job class to which this job belongs.

Type of Job: Type of object to be executed by the job: PL/SQL Block, Chain, Stored Procedure, Named Program, or Executable.

When to Execute Job: When to execute the job: Immediate (immediately on creation, and once only), Once (once, at a specified time), Repeating, Queue, File Watcher, Schedule (using a named schedule object). If you specify anything other than Immediate, you are prompted for additional information.

For Repeating, you can click the pencil icon to specify a Repeat Interval with details. If you check Advanced, you can use the predefined interval scheme or a user-defined one, or a combination.

Destination

Local (local system), Remote (the database destination for a remote database job, or external destination for a remote external job), or Multiple (the job runs on all destinations).

Depending on what destination you selected for the job, select the local credential, the remote credential and destination, or the multiple group.

Job Arguments

If the job expects arguments to be passed, the Name and Data Type of each argument are listed. For each input or input/output argument, specify the appropriate Value.

Notification

Select Job Events for which to send notification email messages. For each message, you can specify recipient email addresses and the sender (or no sender), and you can modify the body of the message.

Properties

Auto Drop: Determines whether the job is to be automatically dropped after it has completed or has been automatically disabled.

Restartable: Determines whether the job can be restarted in case of failure.

Stop on Window Close: If the schedule of a job is a window or a window group, TRUE causes the job to stop once the associated window is closed, and fALSE causes the job to continue after the window closes. (Note that if the job is allowed to continue, its resource allocation will probably change because closing a window generally also implies a change in resource plans.)

Follow Default Time Zone: Determines whether if the job start date is null, then when the default time zone scheduler attribute is changed, the Scheduler recomputes the next run date and time for this job so that it is in accordance with the new time zone.

Parallel Instances: For an event-based job, determined what happens if an event is raised and the event-based job that processes that event is already running. FALSE causes the new event to be ignored. TRUE causes an instance of the job to be started for every instance of the event, and each job instance is a lightweight job so multiple instances of the same event-based job can run in parallel.

Job Style: Style of the job being created: REGULAR (regular job) or LIGHTWEIGHT (lightweight job). A lightweight must reference a program object. Use lightweight jobs when you have many short-duration jobs that run frequently. Under certain circumstances, using lightweight jobs can deliver a small performance gain.

Job Priority: The priority of this job relative to other jobs in the same class as this job. If multiple jobs within a class are scheduled to be executed at the same time, the job priority determines the order in which jobs from that class are picked up for execution by the job coordinator. It can be a value from 1 through 5, with 1 being the first to be picked up for job execution.

Job Weight: (Do not change the value. Shown only for connections to Oracle Database releases before 11.2.)

Logging Level: Determines how much information is logged: DBMS_SCHEDULER.LOGGING_OFF (no logging), DBMS_SCHEDULER.LOGGING_FAILED_RUNS (only jobs that failed, with the reason for failure), DBMS_SCHEDULER.LOGGING_RUNS (all runs of each job in this class), or DBMS_SCHEDULER.LOGGING_FULL (all operations performed on all jobs).

However, if the job class has a higher (more detailed) logging level than the level specified for the job, the job class logging level is used.

Max Runs: The maximum number of consecutive scheduled runs of the job.

Max Failures: The number of times a job can fail on consecutive scheduled runs before it is automatically disabled.

Instance ID: In an Oracle Real Application Clusters environment., the instance ID of the instance that the job must run on.

Raise Events: Determines at what stages of the job execution to raise events. (See the DBMS_SCHEDULER.SET_ATTRIBUTE documentation in Oracle Database PL/SQL Packages and Types Reference for details.)

Max Run Duration: Maximum amount of time that the job should be allowed to run. Its data type is INTERVAL DAY TO SECOND. If this attribute is set to a nonzero and non-null value, and job duration exceeds this value, the Scheduler raises an event of type JOB_OVER_MAX_DUR. It is then up to your event handler to decide whether or not to allow the job to continue.

Schedule Limit: Maximum delay time between scheduled and actual job start before a program run is canceled.

Store Output: If set to TRUE, then for job runs that are logged, all job output and error messages are stored in the *_JOB_RUN_DETAILS views. If set to FALSE, then the output and messages are not stored.

Reset to Defaults: Resets all properties to their default values.

Summary/SQL

May include a summary in hierarchical form or the PL/SQL statement that will be used to implement your specifications, or both.

5.51 Create/Edit Job Class

This interface 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.13, "Scheduling Jobs Using SQL Developer".

(To create a job class, SQL Developer internally uses the DBMS_SCHEDULER.CREATE_JOB_CLASS procedure, which is documented in Oracle Database PL/SQL Packages and Types Reference.)

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

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.

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.

5.52 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.17, "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.53 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.54 Create Pluggable Database

Use this dialog box to create a PDB.

Database Name: Name of the PDB.

Admin Name: Administrative user who has or can be granted the privileges required to perform administrative tasks on the PDB.

Admin Password: Password for the Admin Name database user.

File Name Conversions: Determines how the database generates the names of files (such as data files and wallet files) for the PDB.

  • None: The database first attempts to use Oracle Managed Files to generate file names. If you are not using Oracle Managed Files, then the database uses the PDB_FILE_NAME_CONVERT initialization parameter to generate file names.

  • Custom Names: Select a Source Files/Target Files pair.

  • Custom Expressions: Specify one or more Source File Expression/Target File Expression pairs. Each pair item is a string found in names of files associated with the seed (when creating a PDB by using the seed), associated with the source PDB (when cloning a PDB), or listed in the XML file (when plugging a PDB into a CDB).

Storage: You can specify storage limits for the PDB total size or temporary tablespace usage, or both; or unlimited storage for either or both.

5.55 Create/Edit Program

This dialog box is used for creating or editing an 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.13, "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.

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.

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

Type of program:

  • PL/SQL Block: 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: 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.

  • Remote Stored Procedure: The program is a remote stored procedure. You specify the procedure name and any arguments.

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

  • Script: The program is a SQL Script (SQL*Plus statements), Backup Script (RMAN commands), or External Script (operating system commands). Enter or paste the script text in the box.

Properties tab

Enables you to set program properties. For most properties the default is null, but you can check the box to specify a value.

Detached: TRUE if the program is a detached job; otherwise, FALSE. Use a detached job to start a script or application that runs in a separate process, independently and asynchronously to the Scheduler. A detached job typically starts another process and then exits. Upon exit (when the job action is completed) a detached job remains in the running state. The running state indicates that the asynchronous process that the job started is still active. When the asynchronous process finishes its work, it must connect to the database and call DBMS_SCHEDULER.END_DETACHED_JOB_RUN, which ends the job.

Max Runs: Maximum number of runs before the program is marked as completed.

Max Failures: Maximum number of failures tolerated before the program is marked as broken.

Max Run Duration: Maximum run duration of the program.

Schedule Limit: Maximum delay time between scheduled and actual job start before a program run is canceled.

5.56 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 and in the chapter about configuring privilege and role authorization in Oracle Database Security 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.57 Create/Edit Schedule

This dialog box is used for creating or editing an Oracle Scheduler schedule. For more information about job scheduling, see Section 1.13, "Scheduling Jobs Using SQL Developer".

(To create a schedule, SQL Developer internally uses the DBMS_SCHEDULER.CREATE_SCHEDULE procedure.)

Properties

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

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

When: Specify when jobs that use this schedule are to run: Repeating (specify the repeat interval, start date, and end date), Queue (specify the queue name, agent, condition, start date, and end date), or File Watcher (specify the file watcher object name, condition, start date, and end date).

SQL

Displays the DBMS_SCHEDULER.CREATE_SCHEDULE procedure that will be used to create the schedule according to your specifications.

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

Cache and Cache size: Cache causes sequence values are preallocated in cache, which can improve application performance; Cache size indicates the number of sequence values preallocated in cache. No Cache causes sequence values not to be preallocated in cache.

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.

Order: Indicates whether sequence numbers are generated in the order in which they are requested. If No Order 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.59 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.60 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.61 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.62 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 Constraint. 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.

Table 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 and Graph 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 Constraint) cannot be null.

Comment: Optional descriptive comment about the column.

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

Constraints

You can click the plus sign (+) icon to add one or more constraints. For each, specify the type of constraint: Primary Key, Unique, Foreign Key, or Check. (See the information for each type.)

Primary Key Constraint

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

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 Constraints

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

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. (Note, however, that this display does not include any indexes related to primary key and unique key constraints.) 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).

Storage Pane

Lets you specify Storage Options for the table.

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.

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:

  • CACHE: For data that is accessed frequently, indicates that 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 attribute is useful for small lookup tables.

  • NOCACHE: For data that is not accessed frequently, indicates that the blocks retrieved for this table are placed at the least recently used end of the LRU list in the buffer cache when a full table scan is performed. NOCACHE is the default for LOB storage.

  • CACHE READS: LOB values are brought into the buffer cache only during read operations but not during write operations.

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.63 Storage Options

When you create or edit a table or an index, you can 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.64 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 SecureFiles 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 Directory: Name of the directory or folder for the data file. If not specified, the default location for tablespace data files is used.

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.65 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.66 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.67 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 and in the chapter about managing security for Oracle Database users in Oracle Database Security 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.68 Create/Edit User Defined Report

The following information applies to a user-defined report. You can create a simple report based on a SQL query or, if you click Advanced, a report with specialized features such as a child report. For information about how to create a user-defined report, as well as examples of creating such reports, see Section 1.19.15, "User Defined reports".

Advanced: If this option is not checked, the box contains basic information and the Property subtab. If this option is checked, the box also contains subtabs for SQL Query, Child Reports, Binds, Frill Down, and PDF.

Database connection dropdown: Select the database connection to use for the report.

Style: The style or type of report:

You are encouraged to experiment with different style options to see which best meets your needs.

Description: Optional description of the report.

Tool Tip: Popup text that will be displayed when you hover over the report name in the Reports navigator.

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

SQL: Area for entering the SQL query for the report. You can use the folder icon to select a file with the SQL text, and the eraser icon to erase the contents of the SQL box.

SQL Query (advanced)

Area for entering the SQL query for the report. You can use the folder icon to select a file with the SQL text, and the eraser icon to erase the contents of the SQL box.

SQL Variants: Lets you add and remove Minimum Version and Maximum Version information.

Child Reports (advanced)

Lets you add and remove child reports to a master report.

For an example of creating a child report (where you can click a department ID in the top part to display employees in that department in the bottom part), go to the Start Page (Help > Start Page). On the Get Started tab under Featured Online Demonstrations, and click Reporting Features. On the displayed page, find the title containing Adding a Child Report.

Add Child: Lets you specify the name of the child report, after which a node is added for that child report, along with SQL Query, Property, and PDF subtabs for the child report.

Binds (advanced)

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.

Drill Down (advanced)

You can click Add Report to specify one or more reports to be enabled for drill-down display. To remove a report from drill-down, select it and click Remove Report.

Property

Displays options relevant to the Style (report type) that you selected. (Relevant for Table, Chart, and Gauge styles.)

PDF (advanced)

Lets you specify layout and formatting options for when the report is exported as PDF. For example, you can specify a header and footer; security options including password-protection, encryption, and restrictions on printing and copying; and page layout options such as margin sizes and portrait or landscape orientation.

5.69 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.19.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.70 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

  • Read Only: Prevents the view from being used to add, delete, or change data in the underlying table or tables.

  • Check Option: If this option is checked, it prohibits any changes to the underlying table or tables that would produce rows that are not included in this view.

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:

  • Complete Refresh: Executes the defining query of the materialized view, even if a fast refresh is possible.

  • Fast Refresh: Uses the incremental refresh method, which performs the refresh according to the changes that have occurred to the master tables. The changes for conventional DML changes are stored in the materialized view log associated with the master table.The changes for direct-path INSERT operations are stored in the direct loader log.

  • Force Refresh: Performs a fast refresh if one is possible; otherwise, performs a complete refresh.

  • Never: Do not perform refresh operations.

When: The type of refresh operation to be performed:

  • On Demand: Performs a refresh when one of the DBMS_MVIEW refresh procedures is called.

  • On Commit: Performs a fast refresh whenever the database commits a transaction that operates on a master table of the materialized view. This may increase the time taken to complete the commit, because the database performs the refresh operation as part of the commit process.

  • Specify: Performs refresh operations according to what you specify in the Start on and Next fields.

  • Never: Does not perform a refresh operation.

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

  • Primary Key: Creates a primary key materialized view, which allows materialized view master tables to be reorganized without affecting the eligibility of the materialized view for fast refresh.

  • Row ID: Creates a rowid materialized view, which is useful if the materialized view does not include all primary key columns of the master tables.

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.71 Create Window

This interface is used for creating a new Oracle Scheduler window. For more information about job scheduling, see Section 1.13, "Scheduling Jobs Using SQL Developer".

(To create a window, SQL Developer internally uses the DBMS_SCHEDULER.CREATE_WINDOW procedure.)

Name: Name of the window. It can optionally be prefixed with a schema name. Cannot be null. It is converted to uppercase unless enclosed in double-quotes.

Enabled: Enables the window. (Causes the DBMS_SCHEDULER.ENABLE procedure to be called after the window is created.)

Resource Plan: The resource plan that automatically activates when the window opens. When the window closes, the system switches to the appropriate resource plan, which is usually the plan that was in effect before the window opened, but can also be the plan of a different window. Only one resource plan can be associated with a window. It null, the resource plan in effect when the window opens stays in effect for the duration of the window. If an empty string, the resource manager is disabled for the duration of the window. If the window is open and the resource plan is dropped, then the resource allocation for the duration of the window is not affected.

Duration: The length of time that the window stays open. Can range from one minute to 99 days.

Priority: Relevant when two windows overlap. Because only one window can be in effect at one time, the window priority determines which window opens. The two possible values for this attribute are HIGH and LOW. A high priority window has precedence over a low priority window, therefore, the low priority window does not open if it overlaps a high priority window.

Description: Optional descriptive text.

When to Open: Repeating or Schedule:

  • For Repeating, specify the Repeat Interval and optionally the Start Date and End Date.

  • For Schedule, select the name of the Schedule to be used.

5.72 Create Window Group

This interface is used for creating a new Oracle Scheduler window group. For more information about job scheduling, see Section 1.13, "Scheduling Jobs Using SQL Developer".

(To create a window group, SQL Developer internally uses the DBMS_SCHEDULER.CREATE_GROUP procedure.)

Name: Name of the window group. It can optionally be prefixed with a schema name. Cannot be null. It is converted to uppercase unless enclosed in double-quotes.

Enabled: Enables the window group. (Causes the DBMS_SCHEDULER.ENABLE procedure to be called after the window group is created.)

Comments: Optional comment text.

Members

Available Members: Lists all Scheduler Windows.

Selected Members: List windows to be added to the window group. Use the arrow icons to move selected windows or all windows from one list to the other.

5.73 Create XML Document from XML Schema

Use to specify the filename and the directory in which the XML file from an XML Schema is created, and options for the creation.

File Location

XML File: Name of the XML file to be created.

Directory: Directory for the file. You can click Browse to select a directory.

Use Registered Schemas: Select to use the schemas registered with SQL Developer. To view all the currently registered XML schemas, add new schemas to support additional namespaces and elements, remove existing schemas, and unload schemas from memory, select Tools > Preferences, XML Schemas.

Use File System Schema: Enter or browse to select the directory for the XML schema.

Options

Use to specify the default XML namespace and other options in creating the instance document.

Target Namespace: Select the XML namespace (xmlns attribute). Available namespaces are either schemas already registered with SQL Developer or specified in the file system from the previous wizard page.

Root Element: Select the root element. SQL Developer will automatically provide the root element from the schema selected as the target namespace.

Depth: Number of levels to display in the XML tree structure.

Encoding: Select the encoding that the XML parser will use to understand foreign characters.

Generate Only Required Elements: Select to generate XML elements with a required attribute in the instance document.

5.74 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.75 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.76 Customize Toolbars

This dialog box is displayed if you click View, Show Toolbars, Customize Main Toolbar, then Customize.

To add a command icon to the main toolbar, drag its icon from the dialog box to the desired location on the main toolbar.

To remove a command icon from the main toolbar, drag its icon from the main toolbar to the dialog box.

New Toolbar: Lets you add a "placeholder" in the main toolbar, into which you can drag icons.

Reset Toolbars: Undoes any main toolbar customizations, restoring it to the content when SQL Developer was originally installed. (To reset the entire user interface to its original form, see Section 1.2.2, "Restoring the Original "Look and Feel"".)

5.77 Data Pump Export

This wizard is displayed if you expand a connection in the DBA navigator, right-click Export Jobs, and select Data Pump Export Wizard. (For information about using the DBA navigator, see Section 1.12, "Using DBA Features in SQL Developer".)

To use the Data Pump Export wizard, you must understand the concepts and techniques for Oracle Data Pump, as documented in Oracle Database Utilities.

Source page

Connection: Database connection to use to export the specified information for the specified types of objects.

Data or DDL: Specify whether to export only the data (applies to tables and views), only the object definitions (the "DDL)), or both the data and the object definitions.

Types to be Exported: Specify whether to export the database or just one or more tablespaces, schemas, or tables. (Your selection will affect the display of some remaining wizard pages.)

Tablespaces page

If you specified Tablespaces for Types to be Exported, select one or more tablespaces to be exported, and use the arrow icons to move them to the Selected Source Tablespaces box.

Schemas page

If you specified Schemas for Types to be Exported, select one or more schemas to be exported, and use the arrow icons to move them to the Selected Source Schemas box.

Tables page

If you specified Tables for Types to be Exported, select one or more tables to be exported, and use the arrow icons to move them to the Selected Source Schemas box.

Tables tab: To select one or more tables, see Specify Objects under Shared Wizard Pages.

Include/Exclude Filter tab: Optionally, filter the objects to be included or excluded, using the same interface as for the Filter page.

Filter page

Lets you specify data and metadata filtering to limit the type of information that is exported. You can specify multiple filters by adding rows.

If you specify any Include or Exclude rows, you must put any object names in single quotes. Example: Filter Type = TABLE, and Expression = not in ('EMPLOYEES')

Table Data page

See Data under Shared Wizard Pages.

Options page

Lets you specify Data Pump Export options for the job.

Output Files page

Lets you specify one of more output (dump) files and certain options, including whether to delete existing dump files before creating new ones and whether to include the timestamp in dump file names.

Job Schedule page

Lets you specify scheduling options for the Data Pump Export job. The options include whether to start the job immediately or later, and any repeat options.

Summary page

See Summary under Shared Wizard Pages.

On the Summary page, you can click the PL/SQL tab to see the PL/SQL procedure that will perform the job. For information about the Data Pump PL/SQL API, see Oracle Database Utilities.

5.78 Data Pump Import

This wizard is displayed if you expand a connection in the DBA navigator, right-click Import Jobs, and select Data Pump Import Wizard. (For information about using the DBA navigator, see Section 1.12, "Using DBA Features in SQL Developer".)

To use the Data Pump Import wizard, you must understand the concepts and techniques for Oracle Data Pump, as documented in Oracle Database Utilities.

Type page

Connection: Database connection to use to import the specified information for the specified types of objects.

Job Name: Name to be given to this import job.

Data or DDL: Specify whether to import only the data (applies to tables and views), only the object definitions (the "DDL)), or both the data and the object definitions.

Types to be Imported: Specify whether to import the database from the dump file or files, or just one or more tablespaces, schemas, or tables. (Your selection will affect the display of some remaining wizard pages.)

Choose Import Files: Specify one or more dump files (that were created using Data Pump Export) from which to import the objects. To add files, click Add Row.

Tablespaces page

If you specified Tablespaces for Types to be Imported, select one or more tablespaces to be imported, and use the arrow icons to move them to the Selected Source Tablespaces box.

Schemas page

If you specified Schemas for Types to be Imported, select one or more schemas to be imported, and use the arrow icons to move them to the Selected Source Schemas box.

Tables page

If you specified Tables for Types to be Imported, select one or more tables to be imported, and use the arrow icons to move them to the Selected Source Schemas box.

Tables tab: To select one or more tables, see Specify Objects under Shared Wizard Pages.

Include/Exclude Filter tab: Optionally, filter the objects to be included or excluded, using the same interface as for the Filter page.

Filter page

Lets you specify data and metadata filtering to limit the type of information that is imported. You can specify multiple filters by adding rows.

Table Data page

See Data under Shared Wizard Pages.

Ramapping page

Lets you specify options for the relevant Data Pump Import "remapping" parameters (REMAP_DATAFILE, REMAP_SCHEMA, REMAP_TABLESPACE).

Options page

Lets you specify Data Pump Import options for the job.

Job Schedule page

Lets you specify scheduling options for the Data Pump Import job. The options include whether to start the job immediately or later, and any repeat options.

Summary page

See Summary under Shared Wizard Pages.

On the Summary page, you can click the PL/SQL tab to see the PL/SQL procedure that will perform the job. For information about the Data Pump PL/SQL API, see Oracle Database Utilities.

5.79 Database Copy

This wizard is displayed if you click Tools, then Database Copy. You can copy objects, schemas, or a tablespace from a source connection to a destination connection. The specific options depend on what you are copying.

Some pages include Proceed to Summary, which lets you accept the default values for remaining pages and go directly to the Summary page by clicking Next.

Source/Destination page

Source Connection: Database connection from which to copy the objects, schemas, or tablespace.

Destination Connection: Database connection into which to copy the objects, schemas, or tablespace.

Note: For Schemas Copy or Tablespace Copy, the Source Connection user and the Destination Connection user must have DBA privileges. (DBA privileges are not required for Objects Copy.)

Copy Options: Specify whether to copy database objects, schemas, or tablespaces.

Objects Copy: Copies types of database objects and specific objects of those types.

Schemas Copy: Copies one or more schemas. If you select multiple schemas from the source connection, you can maintain the individual schemas in the destination connection or consolidate them all into one schema in the destination connection.

Tablespace Copy: Copies the objects from a source tablespace to a destination tablespace.

Copy DDL: Copies the object definitions. For destination objects with the same names as source objects of the same type (for example, if both contain a table named EMPLOYEES), specify whether to not perform the copy (that is, do not replace the destination objects) or to perform the copy (that is, replace the existing destination objects with the source objects).

Copy Data: Copies the data for any tables and views that are copied. If you do not select this option, any copied tables or views are empty in the destination connection.

Truncate Destination Data Before Copying: If a table or view of the same name already exists in the destination connection (for example, if an EMPLOYEES table exists in the source and destination), this option deletes any existing data in the destination object before copying the data from the source connection. If this option is not selected, the copied data is appended to the existing data in the destination object.

Object Types page

See Types under Shared Wizard Pages.

Select Schemas page

If you specified Schemas Copy, select one or more schemas from the Available Source Schemas list and click the right-arrow to move them to Selected Source Schemas.

Select Tablespace page

If you specified Tablespace Copy, select the Source Tablespace and Destination Tablespace. Objects in the source tablespace are copied into the destination tablespace.

Specify Objects page

See Specify Objects under Shared Wizard Pages.

Specify Data page

See Data under Shared Wizard Pages.

Summary page

See Summary under Shared Wizard Pages.

5.80 Database Differences

This wizard 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) accessible from two different connections, and optionally generate DDL to update the objects in the destination schema to reflect the differences in the source schema.

Use the Source/Destination page 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.

Some pages include Proceed to Summary, which lets you accept the default values for remaining pages and go directly to the Summary page by clicking Next.

Source/Destination page

Source Connection: Database connection that has access to the source objects to be compared.

Destination Connection: Database connection that has access to the destination objects to be compared.

DDL Generation Options: Check the options or features to be included if you later generate a script to update the objects in the destination schema to reflect differences in the source schema: comments, schema name (prefixed to schema objects), constraints, password values, constraint indexes, referential constraints. Options that are not checked are ignored or not performed during the script generation.

Schema: This option, if you also select Consolidate Schema under DDL Comparison Options, causes object names to be prefixed with the target schema name in the generated script (for example, CREATE TABLE "HR"."SKILLS" instead of CREATE TABLE "SKILLS"). If you select Maintain Schema under DDL Comparison Options, this Schema option is ignored and object names are prefixed with the source schema name in the generated script.

DDL Comparison Options: Check various options, including differences to be ignored, when objects in the source and destination schemas are compared.

Note:

The DDL Generation Options apply to how the DDL will be generated, whereas the DDL Comparison Options apply to how the comparison is made between the two objects.

Consolidate Schema and Maintain Schema enable you either to compare the source objects with objects owned by the target connection user (schema) or with any object accessible by the target connection. The resulting behavior is largely based on which option you select and which objects your connection has access to.

  • Consolidate: Compares the source objects with those owned by the target schema; in other words the selected objects are "consolidated" into the target schema.

    If the target connection does not own an object by that name (that is, target-schema.object-type.object-name), the object will not be found and a comparison cannot be done. If the target connection does own object target-schema.object-type.object-name, source-schema.objectname will compared with target-schema.object-type.object-name.

  • Maintain: Maintains the schema on the source objects and applies it in the target object processing.

    If the target connection does not have access to those schema objects, they will not be found and a comparison cannot be done. If the target connection does has access to those schema objects, source-schema.object-type.object-name will be compared with target-schema.object-type.object-name.

Match Constraints By: Controls whether constraints are matched for comparison by the names (Name) or definitions (Definition).

Ignore Segment Attributes: Causes differences in segment specification to be ignored.

Ignore Table Column Position: Causes differences in the positions of table columns to be ignored when objects are compared. For example, if this option is enabled (checked), two tables would not be considered different in the only difference is that Column1 and Column2 appear first and second in one table but second and first in the other table.

Ignore Storage: Causes differences in storage specification to be ignored.

Ignore High Values: Causes differences in partition high values for tables to be ignored.

Ignore Tablespace: Causes differences in the tablespace specified for the object to be ignored.

Logical Compare: Causes meaningless SQL formatting differences in definitions of objects (such packages, package bodies, procedures, and functions) to be ignored.

Ignore Partitioning: Causes differences in partitioning specifications for the object to be ignored

Report Constraint Name Difference: If Match constraints by Definition is selected, causes the constraint name to be displayed when differences are reported.

Types to Diff page

See Types under Shared Wizard Pages.

Specify Objects page

See Specify Objects under Shared Wizard Pages.

Summary page

See Summary under Shared Wizard Pages.

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. In the Generate SQL dialog box you can specify the Save As destination or type (Single File, Separate Files, Type Files, Separate Directories, Worksheet, Clipboard), the location, and the encoding.

To display only objects with differences that have the same name in both connections, select Show Equal Objects. To display all objects that have differences or that exist in one connection but not the other, select Show New Objects.

5.81 Database Export (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.

SQL Worksheet "Hints" as Alternative to Wizard:

For some needs you can use special SQL Developer worksheet "hints" instead of this wizard to generate output in desired formats, as explained in Section 1.7.10, "SQL Worksheet "Hints" for Formatting Output".

The number of pages and the options available depend on the potential scope of the export operation. For example, if you clicked Tools, then Database Export, all pages are available; however, if you right-clicked a table name and selected Export, fewer pages are required.

In several pages, if you select Proceed to summary, clicking Next takes you to the Export Summary page.

See Also:

Cart Versus Database Export Wizard to consider whether you may want to use the Cart instead of the Database Export wizard

Source/Destination page

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: Utilities: Export user preferences.

Connection: Select the database connection with the objects to be exported.

Other options on this page: See Database: Utilities: Export 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.

Note:

An Export Format Error is displayed if you attempt to export both DDL and Data when Format is loader.

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.

For CLOB data, exporting is supported only if the format is loader (SQL*Loader) or pdf (PDF). Some export types export only a subset of the string followed by an ellipsis (...).

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\sqldeveloper\bin\sqldeveloper.conf file to have dates unloaded in date format: AddVMOption -Doracle.jdbc.mapDateToTimestamp=false

For exporting large tables to Microsoft Excel files:

  • If you encounter problems exporting to an .xls file, try exporting instead to XLSX (.xlsx file) format. However, if you must export to an .xls file, try adding the following line to the sqldeveloper\sqldeveloper\bin\sqldeveloper.conf file to increase heap size and then restarting SQL Developer: AddVMOption -Xmx1024M

  • If the number of table rows exceeds 65,536, SQL Developer writes the rows to multiple worksheets within the spreadsheet file.

Save As: Specifies how or where target statements and data are to be saved:

  • Single File: A single file contains both DDL and data. When you are unloading DDL, only Insert format can be specified for data.

  • Separate Files: Each object is saved to a separate file in the specified directory.

  • Type Files: Objects of the same type are saved to a single file in the specified directory.

  • Separate Directories: A directory for each object type being exported is created. Files are created in the appropriate directory.

  • Worksheet: Statements are sent to a SQL Worksheet window.

  • Clipboard: Statements are copied to the clipboard.

Types to Export page

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 page

See Specify Objects under Shared Wizard Pages.

Specify Data page

See Data under Shared Wizard Pages.

Export Summary page

See Summary under Shared Wizard Pages.

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.82 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.83 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 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.84 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.85 Deploy Objects to Cloud

This dialog box is displayed when you click the Deploy Cloud icon in the Cart window (described in Section 1.14, "Using the Cart").

You can deploy objects to the Cloud, except as noted in Section 5.85.1, "Unsupported Cart Object for Cloud Deployment".

Cloud

Connection: Name of a previously created Cloud connection.

Title: A short descriptive string identifying this deployment.

Server: Server for the Cloud service (for example, a WebLogic Server system).

Port: Port to be used for the deployment. For example: 2222

Authorization

SFTP User: Secure FTP user name that you received in email when you signed up for the Cloud service.

SFTP Password: Password for the SFTP user.

Transfer

File: .zip file to contain the files to be used by SQL Developer for the deployment.

Deploy DDL

Specify whether not to replace or to replace existing destination objects that have the same schema name and object name combination.

Deploy Data

Truncate destination data: If you specified not to replace existing destination objects, then for each existing destination table, this controls whether any existing data is truncated (deleted) before the source table data is appended.

Disable constraints before moving data: Controls whether to disable referential integrity constraints before deploying data to destination tables. (You may want to disable constraints to expedite the deployment, and then reapply the constraints after the deployment.)

Format

Delimiter: Identifies the character used to separate the data into columns. The delimiter character is not included in the data loaded. If the preview page does not show the data separated into columns, the correct delimiter is probably not specified. Examine the data in the preview area to determine the correct delimiter.

Line Terminator: Identifies the terminator for each line. The line terminator is not included in the data loaded. If the preview page shows the data in one single row, the correct terminator is not specified.

Left Enclosure and Right Enclosure: Enclosures are used for character data and are optional. Enclosures are not included in the data loaded.

5.85.1 Unsupported Cart Object for Cloud Deployment

An error box is displayed if any of the following conditions apply on an attempt to deploy objects from the Cart to the Cloud:

  • The objects include any of the following object types: database link, directory, external table, materialized view, materialized view log, or synonym.

  • The objects include any with a data type that is not supported for Cloud deployment (such as SDO_GEOMETRY).

  • The objects are from a non-Oracle database connection.

5.85.2 Other Errors (Invalid or Missing Entries)

An error box is displayed if any entries are invalid or any required entries are omitted. Check the error text and the dialog box entries, ensure that all required information is entered and all entries are valid, and click Apply again.

5.86 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.87 Diff Objects

This dialog box is displayed if you click the Diff Objects icon in the Cart window, which is explained in Section 1.14, "Using the Cart".

Open Configuration icon: Opens a previously saved XML configuration file, to use its settings as defaults for this use of the dialog box (see Save or Open Cart Tool Configuration).

Save Configuration icon: Saves the current settings in the dialog box to an XML file, which you can later open to use for Cart operations of this type (see Save or Open Cart Tool Configuration).

Destination: Destination with objects to be compared with the source objects: Cart for another currently open cart tab, or Connection for a database connection that has access to the destination objects to be compared.

DDL Generation Options: Check the options or features to be included if you later generate a script to update the objects in the destination schema to reflect differences in the source schema: comments, schema name (prefixed to schema objects), constraints, password values, constraint indexes, referential constraints. Options that are not checked are ignored or not performed during the script generation.

Schema: This option, if you also select Schema: Consolidate under DDL Comparison Options, causes object names to be prefixed with the target schema name in the generated script (for example, CREATE TABLE "HR"."SKILLS" instead of CREATE TABLE "SKILLS"). If you select Schema: Maintain under DDL Comparison Options, this Schema option is ignored and object names are prefixed with the source schema name in the generated script.

DDL Comparison Options: Check various options, including differences to be ignored, when objects in the source and destination schemas are compared.

Note:

The DDL Generation Options apply to how the DDL will be generated, whereas the DDL Comparison Options apply to how the comparison is made between the two objects.

Schema: Consolidate and Schema: Maintain enable you either to compare the source objects with objects owned by the target connection user (schema) or with any object accessible by the target connection. The resulting behavior is largely based on which option you select and which objects your connection has access to.

  • Consolidate: Compares the source objects with those owned by the target schema; in other words the selected objects are "consolidated" into the target schema.

    If the target connection does not own an object by that name (that is, target-schema.object-type.object-name), the object will not be found and a comparison cannot be done. If the target connection does own object target-schema.object-type.object-name, source-schema.objectname will compared with target-schema.object-type.object-name.

  • Maintain: Maintains the schema on the source objects and applies it in the target object processing.

    If the target connection does not have access to those schema objects, they will not be found and a comparison cannot be done. If the target connection does has access to those schema objects, source-schema.object-type.object-name will be compared with target-schema.object-type.object-name.

Match Constraints By: Controls whether constraints are matched for comparison by the names (Name) or definitions (Definition).

Ignore Segment Attributes: Causes differences in segment specification to be ignored.

Ignore Table Column Position: Causes differences in the positions of table columns to be ignored when objects are compared. For example, if this option is enabled (checked), two tables would not be considered different in the only difference is that Column1 and Column2 appear first and second in one table but second and first in the other table.

Ignore Storage: Causes differences in storage specification to be ignored.

Ignore High Values: Causes differences in partition high values for tables to be ignored.

Ignore Tablespace: Causes differences in the tablespace specified for the object to be ignored.

Logical Compare: Causes meaningless SQL formatting differences in definitions of objects (such packages, package bodies, procedures, and functions) to be ignored.

Ignore Partitioning: Causes differences in partitioning specifications for the object to be ignored

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. In the Generate SQL dialog box you can specify the Save As destination or type (Single File, Separate Files, Type Files, Separate Directories, Worksheet, Clipboard), the location, and the encoding.

To display only objects with differences that have the same name in both connections, select Show Equal Objects. To display all objects that have differences or that exist in one connection but not the other, select Show New Objects.

5.88 Drop Pluggable Database

Use this dialog box to drop a PDB.

Database Name: Name of the PDB.

Datafile Action: Determines whether to retain or delete the data files associated with the PDB after the PDB is dropped.

  • Keep: Retains the data files associated with the PDB after the PDB is dropped. The temp file for the PDB is deleted because it is no longer needed. Keeping data files may be useful in scenarios where a PDB that is unplugged from one CDB is plugged into another CDB, with both CDBs sharing storage devices.

  • Including: Deletes the data files associated with the PDB being dropped. The temp file for the PDB is also deleted. (Including must be specified if the PDB was created with the SNAPSHOT COPY clause)

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

  • Load a file containing data (such as an image file)

  • Use the external editor associated with the MIME type and file extension (see the preferences for External Editor)

  • Save the result of any edits

For date-based values (such as for CREATED as the filter criterion), you can specify a date, time, and time zone, or click Now to set the values in the dialog box to the current (as of when you click the Now button) date and time.

5.90 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.91 Erase from Disk

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

5.92 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.93 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:

  • The database connection. For Connection, select the database connection from which the objects will be exported.

  • The name of the output file. Look at the Options tab, and be sure that you specified a file.

  • One or more objects or types of objects. Look at the Objects tab, and be sure that you selected (checked) at least one object or type of object.

5.94 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.95 Export Objects

This dialog box is displayed when you click the Export icon in the Cart window (described in Section 1.14, "Using the Cart").

Open Configuration icon: Opens a previously saved XML configuration file, to use its settings as defaults for this use of the dialog box (see Save or Open Cart Tool Configuration).

Save Configuration icon: Saves the current settings in the dialog box to an XML file, which you can later open to use for Cart operations of this type (see Save or Open Cart Tool Configuration).

Export DDL: If this option is checked, the data definition language (DDL) statements for the database objects to be deployed are included in the output file, and the other options in this group affect the content and format of the DDL statements.

For explanations of specific options, see the Export DDL options under Database: Utilities: Export.

Export Data: If this option is checked, the output file or files contain appropriate statements or data for inserting the data for deployed tables and views. If this option is not checked, the data for deployed tables and views is not included; that is, only the DDL statements are included.

Save As: Specifies how or where the statements and data are to be saved:

  • Single File: A single file contains both DDL and data. When you are unloading DDL, only Insert format can be specified for data.

  • Separate Files: Each object is saved to a separate file in the specified directory.

  • Type Files: Objects of the same type are saved to a single file in the specified directory.

  • Separate Directories: A directory for each object type being exported is created. Files are created in the appropriate directory.

  • Worksheet: Statements are sent to a SQL Worksheet window.

  • Clipboard: Statements are copied to the clipboard.

Compressed: If this option is checked, data segments are compressed to reduce disk use.

Encoding: Character set for the statements and data.

File: For exports to one or more files, the .zip file to contain the statements to perform the operation.

5.96 Export: Unique Names Warning

If a cart to be exported contains multiple objects with the same name and different owners (schemas), and if the Show Schema option is not enabled, you are warned that some objects in the cart are not unique and that all objects must be uniquely identified by object type and name. (The Show Schema option causes the schema name to included in CREATE statements: for example, CREATE TABLE SMITH.BOOKS instead of CREATE TABLE BOOKS.)

For example, if the cart contains the tables SMITH.BOOKS and JONES.BOOKS, and if the Show Schema option is not enabled, you cannot include both tables in the export operation.

To perform the export operation, either enable the Show Schema option or select only one of the objects with the same name. If you click Yes to continue, the Show Schema option is selected in the Export Objects dialog box and cannot be deselected.

5.97 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.98 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.99, "Create/Edit External Tool").

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

5.99 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.98, "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.129, "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.129, "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.100 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.101 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.102 Edit Header or Footer (PDF Formatting)

In the user preferences for PDF Format: Header and Footer under Database: Utilities: Export: Formats (CSV, Delimited, Excel, Fixed, HTML, PDF, SQL*Loader, Text, XML), 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.103 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.104 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.105 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.106 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.107 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:

  • Criterion name (list always includes NAME; other criteria depend on the object type)

  • Operator (for example, LIKE)

  • Value for comparison (for example EM%)

  • Case-sensitive option for character data comparison

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.108 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.109 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.110 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.111 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.112 Git: Add

Adds the selected file to the Git staging area. Once a file is in the staging area, it is ready for you to commit to the repository.

If the file is open (that is, not yet saved), you are prompted to save the file before adding it.

Name and Location: Lists the name and location of the file to be added to the staging area.

5.113 Git: Add All

Adds all files not yet added to the Git staging area. Once added to the staging area, these files are ready to be committed to the Git repository.

If any files have not yet been saved, you are prompted to save them.

Name and Location: Lists the names and locations of the files to be added to Git.

5.114 Git: Add to .gitignore File

Lets you mark a file, or a pattern that identifies common file names, as content that Git should ignore.

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

5.115 Git: Checkout Revision

Checks out files from a Git repository.

When you check out files from the Git repository, you can check out a specific revision and branch. Optionally, you can select from existing tags and check out a tagged revision. You can also check out to a specific commit; by default, the most recent commit is used at checkout

Name and Location: Name and location of the local repository to which you are checking out files.

Branch: The branch you are using for this checkout.If you know the name, type the branch name. Otherwise, click Select Branch to browse available branches. If you are creating a new branch, click Create Branch. If you check out a remote branch without specifying a new local branch to track the changes, the changes will become disassociated from the original branch.

Tag: You can enter a tag to help you select the desired revision for checkout. If you know the name of the tag you want to use, type the tag name. Otherwise, click Select Tag to browse the list of available tags

Use Commit ID: The ID for the commit for which this checkout is to be used. Click Select Commit to browse from available commit options.

Create Branch: Creates a new branch to use for this checkout.

5.116 Git: Clone from Git

Clones a Git repository into a newly created directory, creates remote-tracking branches for each branch in the cloned repository, and creates and checks out an initial branch that is forked from the cloned repository's currently active branch.

Based on the information that you enter on these pages, SQL Developer executes the Git clone command. For detailed information, see the Git Reference Manual at http://www.git-scm.com/documentation and the git-clone(1) manual page at https://www.kernel.org/pub/software/scm/git/docs/git-clone.html.

Remote Repository

Specify the information required to log in to the remote Git repository for accessing the files for the operation.

Remote Name: Identifier that you will use when referring to the remote repository. Enter a descriptive, unique name for the clone you are creating.

Repository URL: The URL of the file system at which the Git repository resides. Your Git administrator should be able to prove you with this information.

User Name: If the remote repository does not allow anonymous read access, enter your user name for the server in this field. If your repository permits anonymous access, you can skip the remaining fields on this screen and click Next to continue.

Password: If your repository requires a login with user name and password, select this option, then enter your password in the field. You can then click Next to continue.

Private Key File: If your repository connection uses a private key file, select this option, then enter the path to the private key file. You can click Browse to select the file from a standard directory browser.

Passphrase: If your private key file requires a passphrase, enter the passphrase.

Remote Branch

Specify the branches that you want to include in your clone of the remote repository. Each branch in the repository is represented on the right side. To select a branch, check the box next to the branch name.

Destination

Specify all information for the destination in your local system to which you want to copy your Git repository content.

Destination: Pathname on your local system to which you want the repository to be cloned. You can either type the pathname in the field, or click Browse to select the file.

Clone Name: Name for the clone you are creating.

Checkout Branch: Branch to use for the clone you are creating.

Summary

Displays the selected options for the operation. To make any changes, click Back. To perform the operation, click Finish.

5.117 Git: Commit

Commits a file (located in the staging area) to your Git repository. If any files have not yet been saved, you are prompted to save the files before this dialog box is displayed.

You can commit an individual file or selected multiple files. If you want to commit all uncommitted files (as opposed to selecting from all the available files) in one operation, select Team > Git > Commit All.

Name and Location: Name and physical locations of the file that will be committed to the repository.

Commit Non-Staged Files: Lets you commit a file that you have not yet staged (that is, files not yet on a staged index list).

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. You can make general changes and additions to the comment templates by clicking the link to comment templates.

5.118 Git: Commit All

Saves and commits to the Git repository all open and uncommitted files at the same time.

If you want to commit multiple files, but not necessarily all files not yet committed, you can select them (using Shift-click) from the Applications pane, then select Team > Git > Commit.

Name and Location: Name and physical locations of the file that will be committed to the repository.

Commit Non-Staged Files: Lets you commit a file that you have not yet staged (that is, files not yet on a staged index list).

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. You can make general changes and additions to the comment templates by clicking the link to comment templates.

5.119 Git: Create Branch

Creates a branch, beginning with an existing branch. Note that you can also create a new branch by checking out files from your repository and giving the checked-out files a new branch name.

Name: Name of the branch to be created.

Branch: Branch against which you intend to use this tag. You can type the branch name into the field, or you can click Select Branch to browse available branches.

Tag: You can add a tag to this branch when you create the branch. If you know the name of the tag you want to use, type the tag name. Otherwise, click Select Tag to browse the list of available tags.

Use Commit ID: The ID for the commit for which this branch will be created. Click Select Commit to browse from available commit options.

5.120 Git: Create Tag

Creates a tag, selecting from available branches and existing tags.

Name: Name of the tag to be created.

Comments: A comment about this tag, such as the release it is intended to support, a bug it is meant to fix, or some other identifying information that will help you select it properly in future.

Branch: Branch against which you intend to use this tag. You can type the branch name into the field, or you can click Select Branch to browse available branches.

Tag: You create a tag which is a subset of an existing tag. If you know the name of the tag from which you want to create a subset, type the tag name.Otherwise, click Select Tag to browse the list of available tags.

Use Commit ID: The ID for the commit to which this tag will be applied. Click Select Commit to browse from available commit options. The default commit ID is the most recent one created.

5.121 Git: Export Committed Changes

Creates a file containing the changes in all committed files. The file is displayed in the SQL Developer editor. You can specify the following conditions pertaining to the export.

Name and Location: Names and locations of files for which you are exporting changes.

Export File: Name of the file to which the changes will be exported.

Branch: Branch name to use for this export. To select from a list of available branches, click Select Branch.

Tag: Click Select Tag to choose from a list of available tags to use for this export.

Use Commit ID: The commit ID to use for this export (optional). To select from a list of available commit IDs, click Browse.

5.122 Git: Export Uncommitted Changes

Exports changes that you have not yet committed to a text file, which will be viewed in SQL Developer. You can save this file if you want to share it among team members or make other use of the list of changes.

The files from which these changes are taken must reside in the staging area: that is, they must have been added with the Add or Add All commands, but not yet committed. Any files that you have edited but not yet added to the staging area will be ignored.

Name and Location: Names and locations of files for which you are exporting changes.

Select: Check this box to include the file in the export.

Export File: Name of the file to which the changes will be exported.

5.123 Git: Fetch from Git

Fetches remote sources into your local Git repository.

Fetching a repository copies changes from the remote repository into your local system, without modifying any of your current branches. Once you have fetched the changes, you can merge them into your branches or simply view them.

Remote Repository

Specify the information for the remote repository from which you want to fetch changes.

Remote Name: Identifier that you will use when referring to the remote repository. Enter a descriptive, unique name.

Repository URL: The URL of the Git repository from which you are fetching files. Your Git administrator should be able to prove you with this information.

User Name: If the remote repository does not allow anonymous read access, enter your user name for the server in this field. If your repository permits anonymous access, you can skip the remaining fields on this screen and click Next to continue.

Password: If your repository requires a login with user name and password, select this option, then enter your password in the field. You can then click Next to continue.

Private Key File: If your repository connection uses a private key file, select this option, then enter the path to the private key file. You can click Browse to select the file from a standard directory browser.

Passphrase: If your private key file requires a passphrase, enter the passphrase.

Remote Branch

Specify the branches to include when you fetch changes from the remote repository. Each branch in the repository is represented on the right side. To select a branch, check the box next to the branch name.

Note that the changes you fetch will not affect any branches in your local repository. You can review the changes, then either ignore them or merge them into your local repository.

Summary

Displays the selected options for the operation. To make any changes, click Back. To perform the operation, click Finish.

5.124 Git: Initialize Repository

Creates a Git repository.

Repository Path: Location for the new repository. You can click Browse to select the location.

5.125 Git: Merge

Merges changes, optionally specifying the branch, tag, and commit IDs to use for the merge.

Name and Location: Names and locations of files to be merged.

Branch: Branch you want to merge with the files in your local system. To select from a list of available branches, click Select Branch.

Tag: You can sort the target of the merge by tags, if you have set up tags to keep track of your projects. Click Select Tag to view a list of available tags.

Use Commit ID: The ID for the commit for which this branch will be merged. To select from a list of available commit IDs, click Browse.

5.126 Git: Pull from Git

Pulls remote sources into your local Git repository.

Pull automatically tries to merge the files you are pulling with any files that are already in your local repository. If you are concerned that this will cause merge conflicts and issues, a safer method is to use Fetch, and then Merge the files into your local repository.

Remote Repository

Specify the remote repository used for pulling source files into your Git repository.

Remote Name: Identifier that you will use when referring to the remote repository. Enter a descriptive, unique name, or select from the list if you have more than one available.

Repository URL: The URL of the file system at which the Git repository resides. Your Git administrator should be able to prove you with this information, or select from the list if you have more than one repository available.

User Name: If the remote repository does not allow anonymous read access, enter your user name for the server in this field. If your repository permits anonymous access, you can skip the remaining fields on this screen and click Next to continue.

Password: If your repository requires a login with user name and password, select this option, then enter your password in the field. You can then click Next to continue.

Private Key File: If your repository connection uses a private key file, select this option, then enter the path to the private key file. You can click Browse to select the file from a standard directory browser.

Passphrase: If your private key file requires a passphrase, enter the passphrase.

Remote Branch

Specify the remote branches to pull into your local repository.

Include: Check the branch you want to pull from.

From: Branch, in the remote repository, from which to pull files.

To: Branches that will be part of the pull operation.

Summary

Displays the selected options for the operation. To make any changes, click Back. To perform the operation, click Finish.

5.127 Git: Push to Git

Copies files from your local system to the remote Git repository.

When preparing to push changes, it is recommended that you fetch and merge the latest changes from the remote repository. If there is any merge conflict on the push, the operation will fail.

Remote Repository

Specify the remote repository for the push operation.

Remote Name: Identifier that you will use when referring to the remote repository. Enter a descriptive, unique name, or select from the list if you have more than one available.

Repository URL: The URL of the file system at which the Git repository resides. Your Git administrator should be able to prove you with this information, or select from the list if you have more than one repository available.

User Name: If the remote repository does not allow anonymous read access, enter your user name for the server in this field. If your repository permits anonymous access, you can skip the remaining fields on this screen and click Next to continue.

Password: If your repository requires a login with user name and password, select this option, then enter your password in the field. You can then click Next to continue.

Private Key File: If your repository connection uses a private key file, select this option, then enter the path to the private key file. You can click Browse to select the file from a standard directory browser.

Passphrase: If your private key file requires a passphrase, enter the passphrase.

Local Branch

Specify the branch in your local file system that you will push to the remote repository.

Include: Check all local branches that you want to include in the push operation.

From: Local branch you want to push to the repository.

To: The name that will be used in the remote repository after the push is completed.

Status: The status for this push: Update (if the push will result in updating remote files) or Create (if the push will result in creating new files in the remote repository).

Summary

Displays the selected options for the operation. To make any changes, click Back. To perform the operation, click Finish.

5.128 Git: Revert

Undoes changes made since the last checkout of the file.

Name and Location: Lists the names and physical locations of the file that you are about to revert to the last version stored in the repository.

5.129 Insert Macro

This dialog box is displayed when you click Insert when specifying external program options (see Section 5.99, "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.130 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:

  • To discard changes in the Worksheet and replace the Worksheet with the file currently on disk, click File, then Replace With, then File on Disk.

  • To replace the file on disk with the contents of the Worksheet, click File, then Save.

5.131 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.132 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.133 Format Properties (Advanced Formatting)

The Format Properties box is displayed if you right-click and select Advanced Format in the SQL Worksheet or 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. You can test several output types on a statement in the SQL Worksheet (pasting or reentering the statement each time). For example, if the original statement is entered as:

select 1 from dual

The result with the following output types is:

  • SQL: SELECT 1 FROM dual

  • Java StringBuffer: SQL.append("SELECT 1 FROM dual");

Enclosed In: Not Enclosed (for example, SELECT 1 FROM dual), " (for example, when embedded in Java: SQL.append("SELECT 1 FROM dual");) or, ' (for example, in MSSQL string: N'SELECT 1 FROM dual')).

Escaped By: A character that can be used for "escaping" the Enclosed In character. When Enclosed In is " or ', that character can be escaped by itself or by the Escaped By character. For example, if Escaped By is \ and the input is SQL.append("SELECT 1 \"one\" FROM dual");, it will be formatted as SELECT 1 "one" FROM dual.

5.134 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.135 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.136 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.137 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.138 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.139 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.140 Install SQL Translator

This dialog box is displayed if you right-click SQL Translators under SQL Translator FrameWork for a connection in the DBA navigator (see Using DBA Features in SQL Developer) and select Install SQL Translator.

SQL Translator: SQL translator to be installed for the desired third-party SQL to be translated (for example, Sybase SQL Translator for Sybase).

Mode: online lets you perform the installation interactively in SQL Developer; offline creates a .zip file containing several files, including a Linux .sh file that you can run to perform the installation.

Create New Profile: If this option is checked, you can also create a new SQL translator profile associated with the translator being created. In this case, specify a name for the profile (for example, sybase_profile) and an existing Oracle database schema to own the profile. Whether you check this option or not, you can also create new profiles separately.

Click Apply to start the installation of the SQL translator. (This installation involves several steps, and it may take a long time.)

5.140.1 Installing a Translator and Creating a Profile: Usage Notes

Installing a SQL translator is an Oracle schema enables you to run third-party statements and procedures, have SQL Developer automatically translate them to Oracle syntax, and see the results you would expect from Oracle.

Install the SQL translator in an Oracle schema where the user has been granted the usual privileges for connecting and creating database objects, as well as the CREATE SQL TRANSACTION PROFILE privilege. For example:

GRANT CREATE SQL TRANSACTION PROFILE to USER3;

After you click Apply, SQL Developer checks if the database user has the necessary privileges; and if it does not, it prompts you to log in as the SYS user and then confirm the granting of certain privileges.

The actual translator installation can take a long time, during which the Online SQL Translator Install progress box is displayed. If you want to continue working in SQL Developer during the installation, you can click Run in Background.

After the installation, grant execute access on the translator to the database user. For example:

GRANT EXECUTE on SYBASE_TSQL_TRANSLATOR to USER3;

You can now right-click a profile that is associated with the translator and select Open SQL Worksheet with Profile. In the SQL worksheet that is displayed, you can run SQL statements and procedures in that third-party SQL. For example, in the worksheet for a profile associated with the SYBASE_TSQL_TRANSLATOR translator, you could enter the following Sybase syntax that is not recognized by native Oracle Database:

select top 5 * from my_table;

If you double-click the profile name in the DBA navigator and then select the SQL Translations tab, you can see the SQL text and the translated text for each statement executed. If you want, and if you are sufficiently knowledgeable, you can modify the TRANSLATED_TEXT cells for selected statements to change the way they are interpreted and executed by Oracle.

5.141 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.142 Load Extension

If preferences for a feature area are not displayed, click the Load Extension button to enable the relevant preferences.

Some SQL Developer features are implemented as Oracle-supplied extensions. Some of these are not loaded initially by default, but only after you use the feature in the SQL Developer interface or after you click Load Extension. For example, Migration preferences become available if you open the Migration Projects window (View > Migration Projects) or if you click the Load Extension button in the Migration preferences pane (if the button is visible).

5.143 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.144 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.145 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 and Graph 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 and Graph Developer's Guide.)

5.146 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.147 Manage Features and Updates

This dialog box is displayed when the click Tools, then Features. It lets you enable or disable features in SQL Developer.

Check for Updates: Lets you check for any available updates now and specify whether to check for updates automatically when SQL Developer is started.

Search icon: Enter text to filter the Available Features field.

Features tab

Clear Cache: Removes previously loaded features from the cache.

Available Features: Displays the features available in SQL Developer. Uncheck a feature to disable it. Click Expand All to expand all nodes and Collapse All to collapse all nodes in the tree.

Installed Updates tab

Lets you see and remove installed updates. Click Expand All to expand all nodes and Collapse All to collapse all nodes in the tree.

5.148 Map Connection

Use this dialog box to change the Connection value for one or more objects in the currently selected cart.

Map Connection <connection-name> to Connection: Select the database connection to be used.

Apply Mapping to: Changes the Connection mapping for one of more objects in the currently selected cart.

  • Selected Object Only: Changes the Connection value only for the selected object.

  • All Objects for Connection <connection-name>: Changes the Connection value for all objects (and only those objects) with the specified connection name in the currently selected cart. If the cart contains objects from multiple connections, only the objects associated with the specified connection are affected.

  • All Objects for All Connections: Changes the Connection value for all objects (regardless of their current Connection values) in the currently selected cart.

5.149 Merge Migration Projects

This dialog box is displayed when you right-click a migration project and select Merge into Project.

The operation merges the catalog or catalogs of the selected project into the specified destination project.

  • For databases with multiple catalogs (such as Microsoft SQL Server and Sybase), it merges the catalogs.

  • For databases that do not use multiple catalogs (such as IBM DB2, Teradata, and MySQL), it moves schemas from the single source to the destination.

After the operation, a refresh of the source and destination projects may be required.

5.150 Modify Pluggable State

Use this dialog box to modify the state of a pluggable database (PDB).

Database Name: Name of the PDB.

New State: Contains OPEN if the PDB is closed, or CLOSE if the PDB is open.

State Option: List of options relevant to the New State value: Read Write, Read Only, or Restricted for OPEN; Normal or Immediate for CLOSE.

  • Read Write: Allows queries and user transactions to proceed and allows users to generate redo logs.

  • Read Only: Allows queries but does not allow user changes.

  • Restricted: The PDB is accessible only to users with the RESTRICTED SESSION privilege in the PDB.

  • Normal: Is the PDB equivalent of the SQL*Plus SHUTDOWN command with the normal mode (waits for users to disconnect from the database).

  • Immediate: Is the PDB equivalent of the SQL*Plus SHUTDOWN command with the immediate mode (does not wait for current calls to complete or users to disconnect from the database).

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

  • For a primitive value, you can enter a new value.

  • For a reference pointer, you can enter the memory address of an existing object or array. To set a reference pointer to null, enter 0 as a memory address.

  • For a string, you can enter either a new string value or the memory address of an existing string.

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.152 Data Move Details

This dialog box 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.153 New/Edit Change Set

In a source control system, a change set lets you group files together in ways that help you group multiple files related to the same change. For example, if a bug fix involves edits to three files, you can create a new change set and add all these files to the change set. Then, as you work through the bug fix, the change set will help you track all related files for the same bug.

Name: Name for this change set.

Use Title as Comment: Causes the title of the change set to be used as the contents of the Comments box.

Comments: You can modify or replace any existing comments for this change set, for example, a reference to the bug tracking number for which the files in this change set apply.

Template or Previous Comments: Select from available comment templates, if your version control system uses comment templates.

5.154 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.155 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.156 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.157 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.158 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.159 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.160 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.161 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.162 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.163 Oracle Usage Tracking

This dialog box asks for your consent to usage reporting. If you consent, automated reports can occasionally be sent to Oracle describing the product features in use. No personally identifiable information will be sent and the report will not affect performance. You can review Oracle's privacy policy by clicking the privacy policy link.

Allow automated usage reporting to Oracle: Determines whether you consent to usage reporting.

5.164 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.165 Perforce: Add Files to Perforce

Use to begin the process of bringing a new file under Perforce control.

Files list: Lists the files to be added to Perforce.

Name: The names of the files.

Location: The physical location of the files in the Name column.

Use Changelist: Select a changelist to use when adding these files.

5.166 Perforce: Connection

Use to connect to the Perforce server.

Username: Enter the name by which you are known to Perforce.

Password: Enter your Perforce password, if Perforce has been set up to require passwords.

Port: Enter the port number set up for the Perforce server. If the Perforce server is on another machine, prefix the port number with the machine name and a colon.

Client: Click on the Browse button, then navigate to select your preferred client.

Connection name: The preferred Perforce connection (if you have more than one) for this session.

Test Connection: Click if you want to test that a connection can be made to the Perforce server. The results are displayed in the text area below.

5.167 Perforce: Create Changelist

Use to create a changelist, to submit changed files to the Perforce depot.

Connection: The preferred Perforce connection (if you have more than one) for this session.

Description: Describe the changelist you are creating. The description you create will be visible in the Pending Changelists window. From there you can submit the changelist to the Perforce depot.

Files: The name and path of the files to be selected for the changelist. Place a check in the box beside each file name.

Select All: Selects all files for addition to the changelist (places checks beside all files).

Deselect All: Selects no files for addition to the changelist (clears checks from all files).

You can also make annotations to files in your Perforce changelist. Perforce will store annotations as a comment linked to every file in the revision. When you modify these files later in Perforce, you can view the sequence of revisions or changelists to these files.

5.168 Perforce: Delete Files

Use to delete files immediately from the Perforce client workspace. Files that are deleted from Perforce are indicated by a black diagonal cross. If you want to retrieve files deleted from the client workspace, use the Perforce client. To do this, select Team > Perforce > Launch Perforce Client.

Files list: Lists the files that will be deleted.

Name: The names of the files.

Location: The physical location of the files in the Name column.

5.169 Perforce: Login

Use to log in to the Perforce server.

Server: Name of the computer that acts as your Perforce server.(Read-only field.)

Username: Your username on the Perforce server named in the previous field. (Read-only field.)

Password: Your password for the Perforce server.

If you do not have or cannot remember your username or password for the Perforce server, contact the member of your team who administers the server. The username and password for your Perforce server may or may not be the same as any other username/password combination you have. Make sure you are entering the correct combination.

5.170 Perforce: Open Files for Edit

Use to open a file or files to be edited, with the option of placing the file on the default changelist or a specified changelist.

Name: The names of the files to be edited.

Location: The drive and path of the files in the Name column.

Open on Changelist: Select the changelist from which you want to open the file. Automatically opens on the default changelist.

Sync files to head position before opening: Select if you want to bring the selected files up to date with the controlled latest version in the Perforce depot.

Lock Files: Select if you want to prevent other users from working on the file until you have finished with it.

5.171 Perforce: Revert Files

Use to revert files to their immediately preceding state.

Files list: Lists the files that will be reverted.

Name: The names of the files.

Location: The physical location of the files in the Name column.

5.172 Perforce: Sync Files

Use to synchronize files in your Perforce client workspace with the controlled versions in the Perforce depot.

Connection: The preferred Perforce connection (if you have more than one) for this session.

Files list: Lists the files that will be synchronized.

Name: The names of the files.

Location: The physical location of the files in the Name column.

Sync: Select the revision against which the files in the files list will be synchronized.

Head Revision: Select to synchronize to the latest controlled version in the Perforce depot.

Revision Number: Select to synchronize to a particular revision number in the Perforce depot, then enter the revision number in the accompanying Target box.

Changelist: Select to synchronize to a particular Perforce changelist, then enter the changelist name in the accompanying Target box.

Label Name: Select to synchronize to a particular label name, then enter the name of the label in the accompanying Target box.

Date: Select to synchronize to a particular date or date-and-time combination, then enter the date or date-and-time combination in the accompanying Target box. The format is the same as used by the Perforce client application, which is displayed in the dialog.

Force sync: Select to reinstate files from the Perforce depot, even when this will overwrite or remove files in your Perforce client workspace.

Preview only: Select to report the changes that would be made during synchronization, without actually synchronizing.

5.173 Perforce: Submit Changelist

Use to submit changelists containing edited files to the Perforce depot.

Description: Enter any descriptive comments to identify the changes you have made to the files you are submitting.

Files to submit: Lists the files that will be submitted to Perforce. Select and deselect individual files to include or exclude them.

Comments: Use to add comments to accompany the submit action. You will later be able to see these comments when viewing the history of the files.

Template or Previous Comments: Select from available templates, or from previous comments you have added to other files.

Select All: Selects all the files currently displayed in the Files list.

Deselect All: Deselects all files in the list.

5.174 Plug In Pluggable Database

Use this dialog box to plug in a PDB. Plugging in consists of creating a PDB based on specifications in the XML file from when the original PDB was unplugged.

Database Name: Name of the PDB.

AS CLONE: Specify only if the target CDB already contains a PDB that was created using the same set of data files. The source files remain as an unplugged PDB and can be used again. Specifying AS CLONE also ensures that Oracle Database generates new identifiers, such as DBID and GUID, for the new PDB. If you specify AS CLONE, then you cannot specify the MOVE or NOCOPY clauses.

XML File Name: Specify the XML file containing the metadata for the PDB to be plugged in.

Source File Name Conversions: Determines how the database generates the names of files (such as data files and wallet files) for the PDB.

  • None: The database first attempts to use Oracle Managed Files to generate file names. If you are not using Oracle Managed Files, then the database uses the PDB_FILE_NAME_CONVERT initialization parameter to generate file names.

  • Custom Names: Select a Source Files/Target Files pair.

  • Custom Expressions: Specify one or more Source File Expression/Target File Expression pairs. Each pair item is a string found in names of files associated with the seed (when creating a PDB by using the seed), associated with the source PDB (when cloning a PDB), or listed in the XML file (when plugging a PDB into a CDB).

Copy Action: Determines how the database generates the names of files (such as data files and wallet files) for the PDB.

  • Nocopy: The files for the PDB remain in their current locations. Use this option if there is no need to copy or move the files required to plug in the PDB.

  • Copy: The files listed in the XML file are copied to the new location and used for the new PDB. You can also specify Source File Names Conversions to use pattern replacement in the new file names.

  • Move: The files listed in the XML file are moved, rather than copied, to the new location and used for the new PDB. You can also specify Source File Names Conversions to use pattern replacement in the new file names.

Storage: You can specify storage limits for the PDB total size or temporary tablespace usage, or both; or unlimited storage for either or both.

5.175 Print Preview

This dialog box is displayed if you click File, then Print Preview. It displays an image of the page or pages to be printed. You can click Page Setup and Print Options to specify options for printing, or click Print to select the printer and perform the printing.

Page Setup: Displays a dialog box where you can specify the following for any diagram print operations: media Size (Letter, Legal, or other predefined size) and Source (Automatically Select or a specified paper source), Orientation (Portrait, Landscape, Reverse Portrait, Reverse Landscape), and Margins (left, right, top, bottom).

Print Options: Displays a dialog box where you can specify options for the border, header and footer, text, and zoom.

5.176 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.177 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.178 Delete or Truncate Repository

The Delete Repository dialog box is displayed if you click Tools, then Migration, then Repository Management, then Delete Repository or 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.178.1 Error Truncating Repository

If an error occurs during an attempt to truncate the migration repository, do either of the following:

  • Delete the repository and create it again in the same schema.

  • Change the schema: create a new schema for the repository, and delete the old repository.

5.179 Capture Microsoft Access 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.

5.180 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.181 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.182 Rename Procedure

This dialog box is displayed if you try to rename a procedure. Specify a unique new name for the procedure.

5.183 REST Data Services Connection

Use this dialog box to specify properties for an Oracle REST Data Services connection.

Connection Name: Name for the Oracle REST Data Services connection.

Username: Oracle REST Data Services user name. (For example, might be your Application Express user name.)

http or https: Protocol to use: http or https (secure socket layer)

Hostname: Host where the REST Data Services is running.

Port: Port number on which the Oracle REST Data Services is listening.

Server Path: Context root where the REST Data Services is deployed. Example: /ords

5.184 REST Data Services Connection: Remove Connection

Use this dialog box to remove (delete) an Oracle REST Data Services connection.

Connection: Name of the Oracle REST Data Services connection to be removed.

5.185 RESTful Services Wizard and Object Dialog Boxes

The RESTful Services wizard lets you create the resource module, resource template, and resource handles for a RESTful service; individual dialog boxes let you edit the properties of objects associated with a RESTful service. The wizard is displayed when you right-click the Module node in the RESTful Services Administration Pane pane and select New Module; dialog boxes are displayed when you right-click an object and select Edt.

5.185.1 Resource Module

Specify properties of the resource module.

Module Name: Name of the RESTful service module. Case sensitive. Must be unique.

URI Prefix: Base of the URI that is used to access this RESTful service. Example: hr/ means that all URIs starting with hr/ will be serviced by this resource module. (The displayed example changes to include what you enter.)

Publish: Makes the RESTful service publicly available for use.

Pagination Size: Default pagination for a resource handler HTTP operation GET method, that is, the number of rows to return on each page of a JSON format result set based on a database query.

Origins Allowed: Origins that are allowed to access the resource templates. (Click the plus (+) to add each origin.) For example:

http://example1.org
https://*.example2.com

Note: Uploading a Resource Module:

After creating a resource module, you can upload it, which updates the RESTFUL Services definition on the server.

5.185.2 Resource Template

Specify properties of the resource template.

URI Template: URI for the resource template. Example: employees/ means that all URIs starting with employees/ will be serviced by this resource template. (The displayed example changes to include what you enter.)

Priority: Priority for the order of how the resource template should be evaluated (low through high).

HTTP Entity Tag (ETag): Identifies the type of entity tag to be used by the resource template. An entity tag is an HTTP Header that acts as a version identifier for a resource. Use entity tag headers to avoid retrieving previously retrieved resources and to perform optimistic locking when updating resources. Options include:

  • Secure HASH (default): The contents of the returned resource representation are hashed using a secure digest function to provide a unique fingerprint for a given resource version.

  • Query: Manually define a query that uniquely identifies a resource version. A manually defined query can often generate an entity tag more efficiently than hashing the entire resource representation.

  • None: Do not generate an entity tag.

5.185.3 Resource Handler

Specify the properties of the resource handler. The specific options depend on the method type.

Method: HTTP request method for this handler: GET (retrieves a representation of a resource), POST (creates a new resource or adds a resource to a collection), PUT (updates an existing resource), or DELETE (deletes an existing resource). Only one handler for each HTTP method is permitted.

Requires Secure Access: Indicates whether the resource should be accessed through a secure channel such as HTTPS.

Source Type: Source implementation for the selected HTTP method:

  • Feed: Executes a SQL query and transforms the results into a JSON Feed representation. Each item in the feed contains a summary of a resource and a hyperlink to a full representation of the resource. The first column in each row in the result set must be a unique identifier for the row and is used to form a hyperlink of the form: path/to/feed/{id}, with the value of the first column being used as the value for {id}. The other columns in the row are assumed to summarize the resource and are included in the feed. A separate resource template for the full representation of the resource should also be defined. Result Format: JSON

  • Media Resource: Executes a SQL Query conforming to a specific format and turns the result set into a binary representation with an accompanying HTTP Content-Type header identifying the Internet media type of the representation. Result Format: Binary

  • PL/SQL: Executes an anonymous PL/SQL block and transforms any OUT or IN/OUT parameters into a JSON representation. Available only when the HTTP method is DELETE, PUT, or POST. Result Format: JSON

  • Query: Executes a SQL Query and transforms the result set into either a JavaScript Object Notation (JSON) or CSV representation, depending on the format selected. Available when the HTTP method is GET. Result Format: JSON or CSV

  • Query One Row: Executes a SQL Query returning one row of data into a JSON representation. Available when the HTTP method is GET. Result Format: JSON

Data Format (if the Source Type is Query Source): JSON or CSV.

Pagination Size (GET handler): Default pagination size for the module's resource templates: size of the pagination window, or the number of rows to return for a database query.

MIME Types (PUT and POST handlers): Content types.

Resource Handler Editing Pane

When you edit a resource handler, the following tabs are available:

  • SQL Worksheet: You can specify the query or PL/SQL code in the SQL Worksheet for the resource handler. The SQL Worksheet enables you to connect to the schema, execute the query or PL/SQL code, and view the results.

  • Parameters: Any parameters for the query or the PL/SQL code.

  • Details: You can edit the security and other attributes for the Resource Handler.

5.185.4 RESTful Summary

Displays a summary of the RESTful service properties that you have specified. To make any changes, click Back as needed to return to the desired page or pages.

To create the resource module, resource template, and resource handler as specified, click Finish.

5.186 RESTful Services Connection

Use this dialog box to specify properties for a RESTful Services connection.

Connection Name: Name for the RESTful service connection.

Username: Application Express user name.

http or https: Protocol to use: http or https (secure socket layer)

Hostname: Host where the REST Data Services is running.

Port: Port number on which the Oracle REST Data Services is listening.

Server Path: Context root where the REST Data Services is deployed. Example: /ords

Workspace: Application Express workspace to which the user is assigned. Example: /restful

5.187 RESTful Services Privilege

Use this dialog box to specify properties for a RESTful Services privilege. You can create a RESTful Services privilege to restrict access to specified users for one or more RESTful Services modules. A RESTful Services privilege can include users belonging to one or more user groups.

You can specify roles and protected modules to be associated with the privilege by using the arrow and double-arrow keys to move items between Roles and Selected Roles and between Modules and Protected Modules.

Name: Name for the RESTful service privilege.

Title: Name for the RESTful service privilege.

Description: Description for the RESTful service privilege.

Roles: Available roles that can be associated with this privilege.

Selected Roles: Roles to be associated with this privilege.

Modules: RESTful Services modules to which the privilege can be assigned.

Protected Modules: Modules to which this privilege is assigned.

5.188 Select Current Repository

You can use this dialog box to reconnect to a migration repository after you have disconnected. 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.189 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 Tools, then Migration, then Repository Management, then Associate 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.190 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.191 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.192 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.193 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.194 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.195 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.196 Save or Open Cart Tool Configuration

In dialog boxes for performing operations on the contents of a tab in the Cart window (such as export objects, "diff" objects, and copy objects), you can save an XML file that reflects the current settings in the dialog box, and you can later open that XML file to have its settings be the default values in the dialog box.

For example, if you have settings in the Export Objects dialog box that are different from the SQL Developer default values, but you want to be able to use your customized settings as defaults for certain Cart export operations, you can save the customized settings in a Cart export configuration file, and later open that file for Cart export operations.

File (for Save Tool Configuration): Location and name of the XML file to contain the configuration settings. The default file name reflects the type of operation, such as export_cart.xml, diff_cart.xml, or copy_cart.xml.

Encoding (for Save Tool Configuration): Character set used for encoding of the XML data.

Open Cart (for Open Tool Configuration): Location and name of the XML file containing settings to be used as default values for this use of the dialog box.

5.197 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.198 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.199 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.200 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.80, "Database Differences".

5.201 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.202 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.203 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.204 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.205 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.206 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.207 Shared Wizard Pages

These pages are shared by two or more wizards, such as Database Diff, Database Copy, Database Export, Data Pump Export, and Data Pump Import. The page titles may vary slightly depending on the wizard.

5.207.1 Types

(The page title is Types to Diff, Types to Export, or Object Types.)

Check the types of objects that you want to be included in the operation. You can click Toggle All to check and uncheck all individual types.

You must select at least one object type.

5.207.2 Specify Objects

You can limit the types or objects, and the objects within selected types, for the 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 that satisfy the filter.

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.

5.207.3 Data

(The page title is Specify Data or Table Data.)

You can limit the data for the operation by selecting objects in the same way as on the Specify Objects page.

You can select the columns included in the operation by clicking the pencil icon in the Columns cell. (Note: For an export operation involving CLOB columns, only the first 32 KB of any CLOB is exported.)

You can limit the rows in the operation by entering a valid WHERE clause for a query on the table, without the WHERE keyword. For example, to restrict the 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

5.207.4 Summary

(The page title is Summary, Copy Summary, Diff Summary, or Export Summary.)

You can review a hierarchical display of your specifications for the operation.

To go back and make any changes, click Back.

To perform the operation, click Finish.

5.208 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.209 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.210 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.211 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.212 Show SQL

This displays SQL statements reflecting the current context and content (if applicable).

5.213 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.13, "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.214 Subversion: Add Property

Use this dialog box to add a versioning property for the currently selected file or folder.

5.215 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.216 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.217 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.218 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.219 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.220 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.221 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.222 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.223 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.11.

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

  • Native: The file system type being used by the operating system.

  • Berkeley DB: Causes a Berkeley DB database to be used as the data storage system.

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.224 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.11.

Repository URL: Full, valid URL for the location of the Subversion repository. The following are URL schemas and the access methods they map to:

  • file:/// -- Direct repository access (on local disk)

  • http://-- Access via WebDAV protocol to Subversion-aware Apache server

  • https:// -- Same as http://, but with SSL encryption

  • svn:// -- Access via custom protocol to an svnserve server

  • svn+ssh:// -- Same as svn://, but through an SSH tunnel

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.225 Subversion: Delete Resources

Use this dialog box to delete the selected directory (folder) in the repository.

Comment: Comment explaining the deletion.

5.226 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.227 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.228 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.229 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.230 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.231 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 want 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.232 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.233 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 want 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:

  • Merge Type: Merge Selected Revision Range, Reintegrate a Branch, or Merge Two Different Trees.

  • Merge Resource

  • Merge Options

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.234 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.235 Subversion: Properties

This dialog box is displayed if 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.236 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.237 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.238 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.239 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.240 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.241 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.242 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.243 Subversion: Versioning Properties

This dialog box displays general and versioning information about the currently selected file or folder.

5.244 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.245 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.246 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.247 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.248 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.249 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.250 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.251 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.252 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.253 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.254 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.255 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:

  • You want to create copies of the entire table or specific rows that will be modified by the test, so that you can restore the original values later during teardown.

  • You want to perform some special operations before the test is run.

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:

  • You want to restore a table to its original values, effectively "rolling back" any changes that were made by the test case.

  • You want to perform some special operations after the test is run.

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.256 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.257 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.258 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.259 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.260 Unplug Pluggable Database

Use this dialog box to unplug a PDB from a CDB. When you unplug a PDB, Oracle stores metadata for the PDB in an XML file. You can use this XML file to subsequently plug the PDB into a CDB.

Database Name: Name of the PDB.

XML File Name: The full path of the XML file in which to store the metadata for the unplugged PDB.

5.261 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.262 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 and Graph 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.263 View Whole Value

This dialog box is displayed if, during debugging, you right-click an item (node) of type string, byte array, char array, short array, int array, or long array in the Data tab display and select View Whole Value.

View as String: Displays the item's value as a text string, as opposed to hexadecimal values.

Wrap Text: Enables the wrapping of long lines in the box in which the value is displayed.

5.264 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.265 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). Note that effective with Oracle Database Release 12.1, Store As CLOB for XMLType is deprecated.

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.