| Oracle® Database SQL Developer User's Guide Release 1.5 Part Number E12152-06 |
|
|
View PDF |
| Oracle® Database SQL Developer User's Guide Release 1.5 Part Number E12152-06 |
|
|
View PDF |
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 are not presented here in any rigorous order, because the help for each box is an independent piece of information and is normally seen when you click Help or press F1 in that box.
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"
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.
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.
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.
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.
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.
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.
Download: If you selected any updates to download, this page displays the progress of the download operation.
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.
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.
Use this dialog box to check out modules from a Subversion repository.
Repository Connection: Name of the connection to the repository.
Path: Path to the module to be checked out.
Destination: Directory or folder into which to place the checked out files.
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.
Recursive: If this option is checked, the folder hierarchy is searched recursively from the top down. If this option is not checked, only the top folder is searched.
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.
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.
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.
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.)
Specify the type of object to create. After you click OK, the dialog box for creating that type of object is displayed.
Search: Enables you to restrict the types of objects based on a string that you specify. For example, to display only the item for creating a table, specify table.
Categories: A hierarchical display of types of objects. To see all types of objects that you can create, select All Items.
Items: Types of objects that you can create within the selected category, and as limited by any search string that you specified.
Show All Descriptions: If this option is checked, full descriptions of all displayed items are shown.
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.
The database connection dialog box displays any existing connections. Depending on the context, you can select a connection to connect to the database, edit the information about existing connections, or specify information while creating a new connection. (See Creating and Editing Connections.)
Connection Name: An alias for a connection to the database using the information that you enter. (The connection name is not stored in the database, and the connection is not a database object.) Suggestion: Include the database name (SID) and user name in the connection name. Example: personnel_herman for connecting to the personnel database as user Herman.
Username: Name of the database user for the connection. This user must have sufficient privileges to perform the tasks that you want perform while connected to the database, such as creating, editing, and deleting tables, views, and other objects.
Password: Password associated with the specified database user.
Save Password: If this option is checked, the password is saved with the connection information, and you will not be prompted for the password on subsequent attempts to connect using this connection.
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), or Advanced. (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 and Reference.
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 Parameters. For information about configuring Kerberos authentication, see Oracle Database Advanced Security Administrator's Guide.
Proxy Connection: If this option is checked, proxy authentication will be used, as explained in Section 1.4.5, "Connections with Proxy Authentication". Displays the Oracle Proxy Authentication dialog box.
Basic connection type
Host Name: Host system for the Oracle database.
Port: Listener port.
SID: Database name.
Service Name: Network service name of the database (for a remote database connection over a secure connection).
TNS connection type
Network Alias: Oracle Net alias for the database. (The list for selecting a network alias is initially filled from the tnsnames.ora file on your system, if that file exists.)
Connect Identifier: Oracle Net connect identifier.
LDAP connection type
Enterprise users are authenticated with the Lightweight Directory Access Protocol (LDAP) server. The user login information must be configured in the LDAP server and mapped to a schema in the database. Support for LDAP-compliant directory servers provides a centralized vehicle for managing and configuring a distributed Oracle network. The directory server can replace client-side and server-side localized tnsnames.ora files.
LDAP Server: Select from the list (from <DIRECTORY_SERVER> entries in the ldap.ora file, or enter a new directory server.
Context: LDAP administrative context. The contexts available in the selected server are listed.
DB Service: Database connection information. If a connection uses the OCI/Thick driver (see the Use OCI/Thick preference under Database: Advanced Parameters), 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. To load the information, click Load.
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.
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 the Oracle TimesTen In-Memory Database API Reference Guide for information about the attributes.)
Oracle Password (for Cache): The password for the TimesTen user account on the Oracle Database. (See the Oracle TimesTen In-Memory Database Cache Connect Guide for more information.)
For more information about SQL Developer support for TimesTen, see Section 1.15.
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/technology/products/timesten/
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, as follows:
Open the database (.mdb) file in Microsoft Access.
Click Tools, then Options, and on the View tab ensure that System Objects are shown.
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.
Save changes and close the Access database file.
Create and test the connection in SQL Developer.
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.
The following information applies to a connection to a Microsoft SQL Server or Sybase Adaptive Server database.
Note that to connect to a Microsoft SQL Server or Sybase Adaptive Server database, you must first download the appropriate connection driver, and then click Tools, then Preferences, and use the SQL Developer user preference pane for Database: Third Party JDBC Drivers to add the driver.
Host Name: Host system for the Microsoft SQL Server or Sybase Adaptive Server database.
Port: TCP/IP Port on which Microsoft SQL Server or Sybase Adaptive Server will listen.
Retrieve Database: Name of the Microsoft SQL Server or Sybase Adaptive Server database.
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.
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.
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.
Use this dialog box to select a database connection for use with a specific SQL Developer feature (for example, the SQL worksheet or the Reports navigator). After you click OK, the interface for the component is displayed , with the current user the same as the one specified in the connection.
To create a new database connection, click the plus (+) icon; to edit the selected database connection, click the pencil icon. In both cases, a dialog box for specifying connection information is displayed (see Section 4.12, "Create/Edit/Select Database Connection").
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.
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.
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.
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".
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.
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.
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.
This dialog box is displayed when you right-click the Tables node or a table name in the Connections navigator, select Import Data, and specify the .xls or .csv file from which to import data. It enables you to create a table and import data into it from an Microsoft Excel file, or to import Microsoft Excel data into an existing table.
Data Preview
Worksheet: Name of a worksheet in the Microsoft Excel file.
Header row?: If this option is checked, the first row in the selected Microsoft Excel worksheet is considered a row with text for the column headings. If this option is not checked, the first row is considered to contain worksheet data.
Locale: Language for any text data in the worksheet.
Choose Columns
Available Columns: Lists the Microsoft Excel worksheet columns from which you can select for import into columns in the table. To select one or more worksheet 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 the name of the database table and information about the columns in that table.
Table Name: Name of the database table into which to import the Excel data.
Source Data Columns and Target Table Columns: You can select a source (Excel) 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: You must verify the import parameters. If any test fails, the Information column contains a brief explanation, and you must go back and fix any errors before you can click Finish.
Send to Worksheet: Does not immediately perform the import operation, but instead opens a SQL Worksheet with statements that will be used after you click the Run Script icon in the worksheet.
To perform the import operation, or to send the statements to a SQL Worksheet if you so specified, click Finish.
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.
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.3, "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.
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(s) of the table or cluster and provides direct, fast access to rows, as explained in Section 1.3.6, "Indexes".
Advanced: If this option is checked, the dialog box changes to enable you to set advanced properties (select Advanced on the left side).
Schema: Database schema that owns the table associated with the index.
Table: Name of the table associated with the index.
Name: Name of the index. Must be unique within a schema.
Index Type: Normal for a standard Oracle index, in which case you also specify non-unique, unique, or bitmap, as well as one or more index expressions; or Text for an Oracle Text index (created with INDEXTYPE IS CTXSYS.CONTEXT), in which case you specify the column to be indexed.
Non-unique means that the index can contain multiple identical values; Unique means that no duplicate values are permitted; Bitmap stores rowids associated with a key value as a bitmap.
Index: A list of index expressions, that is, the table columns or column expressions in the index. To add an index expression, click the Add Column Expression (+) icon; this adds a column name here and in Column Expression, where you can edit it. To delete an index expression, click the Remove Column Expression (X) icon; to move an index expression up or down in the list, click the Move Column Up and Move Column Down icons. An index must have at least one index expression.
For example, to create an index on the AUTHOR_LAST_NAME column of the BOOKS table from the tutorial (see Section 3.1, "Create a Table (BOOKS)"), click the + icon, and select AUTHOR_LAST_NAME in Column Name or Expression (next field), which changes BOOKS to AUTHOR_LAST_NAME in the Index field.
Column Name or Expression: A column name or column expression. A column expression is an expression built from columns, constants, SQL functions, and user-defined functions. When you specify a column expression, you create a function-based index.
Order: ASC for an ascending index (index values sorted in ascending order); DESC for a descending index (index values sorted in descending order).
Properties
Enables you to specify index properties such as compression, parallelism, and storage options.
Compress: If this option is checked, key compression is enabled, which eliminates repeated occurrence of key column values and may substantially reduce storage. If this option is checked, you can enter an integer to specify the prefix length (number of prefix columns to compress).
Parallel: If this option is checked, parallel creation of the index is enabled. You can also enter an integer in the text box to specify the degree of parallelism, which is the number of parallel threads used in the parallel operation. (Each parallel thread may use one or two parallel execution servers.) If you specify Parallel without entering an integer, the optimum degree of parallelism is automatically calculated.
Storage Options: Enables you to specify storage options for the index. Displays the Storage Options dialog box.
This dialog box is displayed when you click New to add a user-defined exclusion filter when importing files into a CVS 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.
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.9, "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
If you are editing an existing materialized view log or if you have only partially created a materialized view log, this tab contains a read-only display of a SQL statement that reflects the current definition of the materialized view log.
To save the SQL statement to a script file, click Save and specify the location and file name.
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.
Use this dialog box to create a PL/SQL subprogram (function or procedure). A function returns a value; a procedure does not return a value.
Specify the information for the package and for each parameter, then click OK to create the subprogram and have it displayed in the Editor window, where you can enter the details.
Schema: Database schema in which to create the PL/SQL subprogram.
Name: Name of the subprogram. Must be unique within a schema.
Add New Source in Lowercase: If this option is checked, new text is entered in lowercase regardless of the case in which you type it. This option affects only the appearance of the code, because PL/SQL is not case-sensitive in its execution.
Parameters tab
For each parameter in the procedure to be created, specify the following information.
Name: Name of the parameter.
Type: Data type of the parameter.
Mode: IN for input only, OUT for output only, or IN OUT for input and output (that is, the output is stored in the parameter overwriting its initial input value).
Default Value: Optionally, the default value if the parameter is omitted or specified as null when the subprogram is called.
To add a parameter, click the Add (+) icon; to delete a parameter, click the Remove (X) icon; to move a parameter up or down in the list, click the up-arrow or down-arrow icon.
DDL tab
This tab contains a read-only display of a SQL statement that reflects the current definition of the subprogram.
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.
The following information applies to a sequence, which is an object from which multiple users may generate unique integers. You can use sequences to automatically generate primary key values.
Schema: Database schema in which to create the sequence.
Name: Name of the sequence. Must be unique within a schema.
Increment: Interval between successive numbers in a sequence.
Start with: Starting value of the sequence.
Min value: Lowest possible value for the sequence. The default is 1 for an ascending sequence and -(10^26) for a descending sequence.
Max value: Highest possible value for the sequence. The default is 10^27 for an ascending sequence and -1 for a descending sequence.
Cycle: Indicates whether the sequence "wraps around" to reuse numbers after reaching its maximum value (for an ascending sequence) or its minimum value (for a descending sequence). If cycling of values is not enabled, the sequence cannot generate more values after reaching its maximum or minimum value.
Cache and Cache size: If Cache is checked, sequence values are preallocated in cache, which can improve application performance; Cache size indicates the number of sequence values preallocated in cache. If Cache is not checked, sequence values are not preallocated in cache.
Order: Indicates whether sequence numbers are generated in the order in which they are requested. If no ordering is specified, sequence numbers are not guaranteed to be in the order in which they were requested.
DDL tab
You can review the SQL statement that SQL Developer will use to create a new sequence or that reflects any changes you have made to the sequence properties.
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.
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 Connection: Attempts to establish a connection to the Subversion repository.
Status: Displays the result of the test (success or an error message).
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.
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.
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.
The table dialog box is used for creating a new table or editing an existing table. The table properties are grouped under several tabs.
To create a new table, the only things you must do are specify the schema and the table name, add the necessary columns, and click OK. Although it is not required, you should also specify a primary key using the Primary Key pane. For other table-related features, use the appropriate tabs; the order in which you visit tabs usually does not matter, although you might find it convenient to visit them in the sequence in this topic. If you are editing an existing table, you can visit the tabs in any order.
If you click OK before you are finished creating or editing the table, right-click the table name in the Connections navigator, select Edit, and continue creating or editing the table.
Schema: Database schema in which to create the table.
Name: Name of the table. Must be unique within a schema.
Type: The type of table:
Normal: A regular database table. It can be partitioned (see Partitioning pane, Subpartition Templates pane, and Partition Definitions pane).
External: An external table (see External Table Properties pane).
Index Organized: An index-organized table (see Index Organized Properties pane).
Temporary Table: A temporary table, which is not stored permanently in the database. The temporary table definition persists in the same way as the definition of a regular table, but the table segment and any data in the temporary table persist only for the duration of either the transaction (Transaction option) or the session (Session option).
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.
Note:
After you add a column, to add another column, click the Add Column (+) icon.Name: Name of the column. Must be unique within the table. Suggestion: For a new column, replace any default name, such as COLUMN1.
Datatype: Simple indicates a simple (non-object) data type; Complex indicates an object type. For a complex type, you must specify the schema and the type name (for example, MDSYS and SDO_GEOMETRY for the Oracle Spatial geometry type).
Type: Name of the data type. Most of the remaining information depends on the specific type.
Precision: For numeric data, the precision (total number of significant digits that can be represented) of the column data.
Scale: For numeric data, the scale (number of digits after the decimal point) of the column data.
Size: For character data, the maximum size of the column data.
Units: For character data, the units represented by the Size: BYTE for bytes or CHAR for characters. This attribute is important if the database can contain data in Unicode format, with multiple bytes for each character.
Default: For relevant types, the default value inserted into the column if no value is specified when a row is inserted.
Cannot be NULL: If this option is checked, the column must contain data; you cannot specify no value or an explicit null value for this column when you insert a row. If this option is not checked, the column can contain either data or no data. A primary key column (see Primary Key pane) cannot be null.
Comment: Optional descriptive comment about the column.
To add another column, click the Add Column (+) icon.
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 3.3, "Create a Table (TRANSACTIONS)".
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.
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.
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.
Specifies properties for each index on the table.
Indexes: Lists the indexes currently defined on the table. To add an index, click the Add Index (+) icon; to delete an index, select it and click the Remove Index (X) icon.
Note:
After you add an index, to add another index, click the Add Index (+) icon.Name: Name of the index. Must be unique within the schema.
Index: A list of index expressions, that is, the table columns or column expressions in the index. To add an index expression, click the Add Column Expression (+) icon; this adds a column name here and in Column Expression, where you can edit it. To delete an index expression, click the Remove Column Expression (X) icon; to move an index expression up or down in the list, click the Move Column Up and Move Column Down icons. An index must have at least one index expression.
For example, to create an index on the AUTHOR_LAST_NAME column of the BOOKS table from the tutorial (see Create a Table (BOOKS)), click the + icon, and select AUTHOR_LAST_NAME in Column Name or Expression (next field), which changes BOOKS to AUTHOR_LAST_NAME in the Index field.
Column Name or Expression: A column name or column expression. A column expression is an expression built from columns, constants, SQL functions, and user-defined functions. When you specify a column expression, you create a function-based index.
Order: ASC for an ascending index (index values sorted in ascending order); DESC for a descending index (index values sorted in descending order).
Enables you to specify sequences and before-insert triggers to be used in populating a column with values. This approach is especially convenient for automatically populating primary key column values with unique values.
Column: Name of the column for which a sequence and a trigger are to be used to insert unique values. The data type of the column must be numeric.
Sequence: None causes no sequence to be used; Existing Sequence uses the sequence that you specify; New Sequence creates a new sequence with a default or specified name.
Trigger: Before-insert trigger that automatically inserts the next value of the specified sequence into the column when a new row is inserted.
Table Properties pane
Enables you to specify table properties such as compression, parallelism, and storage options.
Compress (heap-organized tables only): If this option is checked, data segments are compressed to reduce disk use. This clause is especially useful in environments such as data warehouses, where the amount of insert and update operations is small, and in OLTP environments.
Parallel: If this option is checked, parallel creation of the table is enabled, and the default degree of parallelism is set for queries and the DML INSERT, UPDATE, DELETE, and MERGE statements after table creation. You can also enter an integer in the text box to specify the degree of parallelism, which is the number of parallel threads used in the parallel operation. (Each parallel thread may use one or two parallel execution servers.) If you specify Parallel without entering an integer, the optimum degree of parallelism is automatically calculated.
Storage Options: Enables you to specify storage options for the table. Displays the Storage Options dialog box.
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.
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.
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.
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.
This dialog box is displayed if you click Storage Options in the Properties pane when creating or editing a table or an index. It enables you to override the default storage options.
Tablespace: Name of the tablespace for the table or index.
Pct Free: Percentage of space in each of the data blocks of the table or index reserved for future updates. You can enter a value from 0 through 99.
Pct Used: Minimum percentage of used space that Oracle maintains for each data block. A block becomes a candidate for row insertions when its used space falls below the Pct Used value. You can enter a value from 1 through 99.
Logging: <DEFAULT> means to use the Oracle Database default. ON means that the table creation and any subsequent direct loader (SQL*Loader) and direct-path INSERT operations against the table, partition, or LOB storage are logged in the redo log file. OFF means that these operations are not logged in the redo log file.
Ini Trans: Number of update transaction entries for which space is initially reserved in the data block header.
Max Trans: Number of transaction entries that could concurrently use data in a data block. This parameter has been deprecated. Oracle Database now automatically allows up to 255 concurrent update transactions for any data block, depending on the available space in the block.
Extents - Initial: Size of the first extent of the table or index. Specify K (kilobytes) or M (megabytes) for the unit associated with the number.
Extents - Next: Size of the next extent to be allocated to the table or index. Specify K (kilobytes) or M (megabytes) for the unit associated with the number.
Extents - Min: Minimum number of extents allocated when the table or index is created.
Extents - Max: Maximum number of extents allocated when the table or index is created. Unlimited (if checked) means that there is no maximum (and any specified maximum is ignored).
Pct Increase: Percentage that each extent grows over the previous extent.
Buffer Pool: <DEFAULT> means to use the Oracle Database default. KEEP means to put blocks from the segment into the Keep buffer pool; maintaining an appropriately sized Keep buffer pool lets Oracle retain the database object in memory to avoid I/O operations. RECYCLE means to put blocks from the segment into the Recycle pool; an appropriately sized Recycle pool reduces the number of objects whose default pool is the Recycle pool from taking up unnecessary cache space.
Free Lists: Number of free lists for each of the free list groups for the table or index. The default and minimum value for this parameter is 1, meaning that each free list group contains one free list.
Free List Groups: Number of groups of free lists for the table or index. The default and minimum value for this parameter is 1. Oracle uses the instance number of Real Application Clusters instances to map each instance to one free list group.
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
This tab contains a read-only display of a SQL statement that reflects the current definition of the trigger.
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.
The user dialog box is used for creating a new database user or editing an existing database user. The user properties are grouped under several tabs.
To create or edit a database user, the user associated with your database connection must have the DBA role. You should also be familiar with the main concepts and techniques documented in Oracle Database Administrator's Guide.
User tab
Specifies general properties for the database user.
User Name: The user name string. For an existing user, this field is read-only; to change the name, you must drop the user and create a new user with the desired name.
New Password: Password string for the new user, or new password for an existing user. You must also type the same password string for Confirm Password.
Password Expired: If this option is checked, the password is marked as expired, and the user must change the password before being permitted to connect to the database.
Account Locked: If this option is checked, the user will not be permitted to connect to the database until a DBA user unlocks the account associated with this user.
Roles tab
Specifies roles to be granted to the user. For each role, you can check Granted to grant the role, Admin to permit the user to grant the role to other users, and Default to use the default settings for Granted and Admin.
For convenience, you can click buttons to affect all settings (Grant All, Revoke All, Admin All, Admin None, Default All, Default None); then, you can specify other settings for individual roles.
System Privileges tab
Specifies privileges to be granted to the user. For each privilege, you can check Granted to grant the privilege, and Admin 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.
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.
The following information applies to a user-defined report. For information about how to create a user-defined report, as well as examples of creating such reports, see Section 1.12.15, "User Defined reports".
Details tab
Name: Name of the user-defined report.
Style: Report style: Table (default), Code (formats the code in the output), Chart (bar or pie chart; see Section 1.12.15.1, "User-Defined Report Example: Chart" for an example), Gauge (dial or status meter; see Section 1.7.8, "Gauges: In the SQL Worksheet and User-Defined Reports"), plsql-dbms_output (dynamic HTML; see Section 1.12.15.2, "User-Defined Report Example: Dynamic HTML" for an example), or Script (executable script).
Description: Optional description of the report.
ToolTip: Optional tooltip text to be displayed when the mouse pointer stays briefly over the report name in the Reports navigator display.
SQL Statement: The complete SQL statement for retrieving the information to be displayed in the user-defined report. As a trivial example, the statement SELECT user "Current User" FROM DUAL displays Current User as the heading and the name of the user associated with the current database connection.
Suggestion: Look at the SQL statements for various SQL Developer-supplied reports; check the Messages - Log pane below the report results, or click the SQL icon under the Report Results tab.
Add Child: Add a child report (subreport) of this report.
Test: Tests the report definition by running it in a separate window. This feature enables you to test the report before creating it.
Columns tab
Name: Name of the column.
Format: Format of the column. If specified, must use the Java MessageFormat formatting syntax. For detailed information and examples, see the Sun Javadoc information for the MessageFormat class in the java.text package.
hAlign: Horizontal alignment: Left or Right
vAlign: Vertical alignment: Bottom, Center, or Top
Add Column: Adds a new column.
Remove column: Removes the selected column.
Binds tab
Name: Name of the bind variable.
Prompt: String displayed when the user is prompted to enter a value. Example: Table name
Default: Default value if the user does not enter a value at the prompt. To accept the Oracle SQL value, specify NULL_VALUE.
ToolTip: Optional tooltip text to be displayed when the mouse pointer stays briefly over the bind variable name.
Chart Details tab
Available if the report type is Chart.
Chart Type: Bar chart with horizontal or vertical bars, or pie chart.
3D Graph: True for a three-dimensional appearance; False for a two-dimensional appearance.
Gradient Effect: True for a gradient effect; False for no gradient effect.
Chart Style: Thematic name for the overall appearance of the chart.
Show Grid: True to show the grid lines; False to hide the grid lines.
Show Legend: True to show the chart legend; False to hide the chart legend.
Gauge Details tab
Available if the report type is Gauge.
Gauge Type: Dial (like a fuel gauge in an automobile) or Status meter (bar representation).
Query Based: True if the minimum, maximum, low, and high values are specified in the SQL query; False to specify the minimum, maximum, low, and high values in the remaining fields.
Min: Minimum value displayed on the gauge.
Max: Maximum value displayed on the gauge.
Low: "Low" value; usually greater than Min and less than High.
High: "High" value; usually greater than Low and less than Max.
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.12.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.
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.
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.
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.
Specifies the tables and views that you can use in the FROM clause of the view definition.
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.
Alias: Alias for the table or view.
For the example in DDL tab or pane, select the EMP table.
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.
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
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.
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.
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
If you are editing an existing object or if you have only partially created an object, this tab contains a read-only display of a SQL statement that reflects the current definition of the object.
To save the SQL statement to a script file, click Save and specify the location and file name.
This dialog box enables you to specify the URL of an XML schema that can be associated with XML document instances.
Schema: Name of the schema in which to create the XML schema object.
Name: URL of the XML schema.
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.
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.13.8, "File Types").
This dialog box is displayed if you right-click and select Customize Filters in the History tab for 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.
This dialog box is displayed if you click Tools, then Database Copy. Specify the type of operation, and the connections for the source and destination schemas. All database objects are copied from the source schema to the destination schema, subject to any restrictions depending on the type of operation, which determines the behavior if objects of the same name exist in the destination schema.
Source/Destination pane
Source Connection: Database connection for the schema from which to copy the objects.
Destination Connection: Database connection for the schema to which to copy the objects
Create Objects: Copies the objects to new objects in the destination schema only if an existing object of that type with the same name does not already exist.
Truncate Objects: Deletes existing rows in any existing table with the same name, and then loads rows from the source.
Drop Objects: Drops any existing table with the same name, and then creates and loads it from the source.
Copy Summary pane
You can review the SQL statements that will be used to perform the copy operation according to your specifications.
To go back and make any changes, click Back.
To perform the copy operation, click Finish. After the copy operation completes, a log file is displayed.
This interface is displayed if you click Tools, then Database Diff. You can find differences between objects of the same type and name (for example, tables named CUSTOMERS) in two different schemas, and optionally update the objects in one schema (destination) to reflect differences in the other schema (source).
Use the Source/Destination pane to specify the source and destination database connections. Database objects in the schemas associated with these connections will be compared. The schemas for the source and destination connections can be in the same database or different databases.
Source/Destination pane
Source Connection: Database connection for the source schema (the schema in which selected objects are to be compared with objects in the destination schema).
Destination Connection: Database connection for the database that contains the destination schema (the schema containing one or more objects of the same type and name as those selected in the source schema). The selected connection can be the same as, or different from, the connection for the source schema.
Diff Objects: Check the types of objects that you want to be compared in the source and destination connections. You can click Toggle All to check and uncheck all individual types. You must select at least one object type.
Proceed to Summary: If this option is checked, clicking Next takes you directly to the Diff Summary pane.
Specify Objects pane
You can limit the types or objects, and the objects within selected types, for the comparison operation.
Object Type: Select All for all object types, or a specific type of object.
Go: Click Go to display a list of objects that meet the selection criteria for the selected connection. Use the arrow keys to move selected objects or all objects from the available objects box to the selected objects box.
Diff Summary pane
You can review the information that will be used to compare the source and destination connections, according to your specifications.
To go back and make any changes, click Back as needed.
To perform the comparison, click Finish. The results are displayed in a Diff Report window, where you can see the DDL statements to update the objects in the destination schema to reflect differences in the source schema. To create a file containing these DDL statements, click the Generate Script icon in that window. To toggle the display between all objects and only those objects with differences, click Show Equal Objects.
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.
This dialog box is displayed when you right-click a database connection name and select Remote Debug. Use this dialog box if you are using the Sun Microsystem's Java Platform Debugger Architecture (JPDA) and you would like the debugger to listen so that a debuggee can attach to the debugger. For more information about remote debugging, see Section 1.6.2, "Remote Debugging".
Host: Name or IP address of the remote host on which SQL Developer should listen for the database to connect.
Port: Listening port number on the remote host. You can choose any valid port number that is not in use by another process.
Timeout: The number of seconds that SQL Developer will wait for the remote database to make a debugging connection.
Don't Show Dialog Box Before Connecting: If this option is checked, this dialog box will not be displayed before future connections for remote debugging.
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.
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.
This dialog box enables you to edit data in a cell in the table Data grid (that is, edit the value of a single column within a row). You can change the data value and then click OK.
The specific options available depend on the data type of the column associated with that cell in the grid.
If you are not permitted to modify the data, the Value display is read-only.
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.
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 Data dialog box and click Apply.
This interface is displayed when you click Tools and then Database Export 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 a file containing SQL data definition language (DDL) statements to create these objects. To specify options for the export operation, use the Types to Export pane. To specify the objects or types of objects to export, use the Specify Objects pane.
In several panes, if you select Proceed to summary, clicking Next takes you to the Export Summary pane.
Source/Destination pane
Specify the output file, the database connection, and options that affect the content (DDL statements) of the output file.
File: Specify the name of the script file to contain the DDL statements for creating the objects to be exported and the INSERT statements if you will also be exporting data (for example, my_tables.sql). You can click Browse to select a directory for this file. (The default file path for export operations is specified in the SQL Developer user preferences for Database.)
Connection: Select the database connection with the objects to be exported.
DDL Options: Options that affect the DDL statements in the output file:
Show Schema: If this option is checked, the schema name is included in CREATE statements. If this option is not checked, the schema name is not included in CREATE statements, which is convenient if you want to re-create the exported objects under a schema that has a different name.
Storage: If this option is checked, any STORAGE clauses in definitions of the database objects are preserved in the exported DDL statements. If you do not want to use the current storage definitions (for example, if you will re-create the objects in a different system environment), uncheck this option.
Terminator: If this option is checked, a line terminator character is inserted at the end of each line.
Pretty Print: If this option is checked, the statements are attractively formatted in the output file, and the size of the file will be larger than it would otherwise be.
Include BYTE Keyword: If this option is checked, column length specifications refer to bytes; if this option is not checked, column length specifications refer to characters.
Add Force to Views: If this option is checked, the FORCE option is added to any CREATE VIEW statements, causing each view to be created even if it contains errors.
Include Drop Statement: If this option is checked, a DROP statements is included before each CREATE statement, to delete any existing objects with the same names. However, you may want to uncheck this option, and create a separate drop script that can be run to remove an older version of your objects before creation. This avoids the chance of accidentally removing an object you did not intend to drop.
Include Grants: If this option is checked, GRANT statements are included for any grant objects on the exported objects. (However, grants on objects owned by the SYS schema are never exported.)
Automatically Include Dependent Objects: If this option is checked, objects that are dependent on the objects specified for export are also exported. For nonprivileged users, only dependent objects in their schema are exported; for privileged users, all dependent objects are exported.
Types to Export pane
Specify object types to be exported and options for the export operation.
Object Types: Check the types of objects that you want to export. You can click Toggle All to check and uncheck all individual types. You must select at least one object type. Note also the following:
Dependencies (under Tables): If this option is checked, constraints for each table are defined as inline constraints in the CREATE TABLE statement; and if any indexes or triggers exist for a table, they are also included in the CREATE TABLE statement.
Constraints: If this option is checked, any constraints for each table are defined in separate ALTER TABLE statements instead of in the CREATE TABLE statement.
Data: If this option is checked, statements are included to insert the data for an exported table or view. If this option is not checked, statements are not included to insert the data for an exported table or view; that is, only the DDL statements are included. If you check Data, statements are included to insert all data in all tables in the selected schema, unless you use the Filter Data tab to limit the data to be migrated.
Specify Objects pane
You can limit the types or objects, and the objects within selected types, for the export operation.
Object Type: Select All for all object types, or a specific type of object.
Go: Click Go to display a list of objects that meet the selection criteria for the selected connection. Use the arrow keys to move selected objects or all objects from the available objects box to the selected objects box.
Specify Data pane
You can limit the data for the export operation.
Go: Click Go to display a list of available tables, and use the arrow keys to move selected tables or all tables from the available box to the selected box.
Then, select a table, enter the filter text (a WHERE clause without the WHERE keyword), and click Apply Filter.
Export Summary pane
You can review the information that will be used to create the output file, which will contain statements to export database objects and data according to your specifications.
To go back and make any changes, click Back as needed.
To create the output file, click Finish. The file is also displayed in a SQL Worksheet window, where you can run it as a script and perform other operations.
This dialog box is displayed when you click Advanced in the Export (Database Objects and Data) dialog box.
Schema: Select the name of the schema to see the available objects on which you can specify a filter.
Filter: A WHERE clause specifying the condition or conditions for filtering data from the selected object.
Apply Filter: Click to apply the specified filter.
When you are finished applying any filters, click Apply.
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.
This dialog box is displayed when you right-click a table name, a table data display, a SQL Worksheet result set, or report output, and select Export and then an export format. You can export some or all of the data to a file or to the system clipboard. To restrict the output to specified columns, use the Columns tab. To restrict the output based on a WHERE clause condition, use the Where tab.
Format tab
Format: Determines the format of entries written in the specified output file: Insert for SQL INSERT statements, XML for XML tags and data, SQL LOADER for a SQL*Loader control file, or CSV for comma-separated values including a header row for column identifiers.
Output: File writes the output to a file that you specify; Clipboard places the output on the system clipboard, so that you can paste it into a file, a command line, or other location appropriate for the format.
File: If the output is to a file, click Browse to select the directory or folder and to specify the file name and extension. The file path is then placed in the File box. (The default file path for export operations is specified in the SQL Developer user preferences for Database.) Standard file extensions are .sql for Insert format, .xml for XML format, .ctl for SQL LOADER format, and .csv for CSV format.
Columns tab
You can specify whether the output should include data from all columns or just from the checked columns. (Note: For CLOB columns, only the first 32 KB of any CLOB is exported.)
Where tab
You can restrict the output by entering a valid WHERE clause for a query on the table, without the WHERE keyword. For example, to restrict the exported data to rows where a column named RATING contains a value greater than 5, specify: rating > 5
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.)
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 4.65, "Create/Edit External Tool").
Edit: Displays a dialog box for editing the selected external tool (see Section 4.65, "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 4.64, "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 4.69, "Insert Macro").
Run Directory: Directory in which to run the program. You can click Insert to insert a macro for the directory (see Section 4.69, "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.
This dialog box is displayed when you click Migration, then MySQL, SQL Server, and Sybase Offline Capture, then Create Database Capture Scripts. It specifies options for creating an offline capture properties (.ocp) file, which you can later load and run by clicking Migration, then MySQL, SQL Server, and Sybase Offline Capture, then Load Database Capture Script Output.
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 migrations, 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 MySQL, Microsoft SQL Server, or Sybase Adaptive Server version for which to generate the scripts.
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. If you have a license for the feature on all databases on which you plan to use the feature now and in the future, you can enable Skip This Message Next Time and click Yes.
To purchase any required license, contact your Oracle sales representative or authorized Oracle Reseller, or go to the Oracle Store to buy online.
This dialog box is displayed when you right-click 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 (for example, OBJECT_NAME for a table)
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: OBJECT_NAME LIKE EM% (with the percent sign as a wildcard character)
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.
This dialog box is displayed when you click Insert when specifying external program options (see Section 4.65, "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.
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 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.
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.
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.
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.
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.
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.
This dialog box is displayed when you click Migration, then Script Generation, then Generate Oracle DDL It specifies the converted model for which to generate Oracle DDL (data definition language) statements. The operation produces a SQL*Plus script file that you use for offline generation: that is, you can run the script to create the appropriate objects in the Oracle database.
When the operation is in progress, a box displays object types and the number of objects of each type for which DDL statements are being generated.
After the operation finishes, a box displays the directory in which two files have been created: a .sql file containing DDL statements (such as CREATE TABLE and CREATE OR REPLACE VIEW) that create the migrated schema objects in the Oracle database, and a .ctl file containing the SQL*Plus @ statement to invoke the .sql file.
Converted Models: Converted model containing objects for which to generate Oracle DDL statements.
This dialog box is displayed when you click Migration, then Script Generation, then Generate Data Move Scripts. It specifies the converted model and the destination directory if you are performing offline data migration, which is explained in Section 2.9.1.
Converted Model: Converted model containing tables whose data is to be moved to the corresponding Oracle database tables.
Directory: Path in which to create files containing the data and the SQL*Loader specifications.
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.
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.
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.
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.
This dialog box is displayed when you click Load Preset when specifying accelerator 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 reset the accelerator key mappings to the SQL Developer defaults for your system.
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.
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
This dialog box is displayed when you click Migration, then Migrate Data. It specifies the source and target information for online data migration, which is explained in Section 2.9.
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.
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.
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.
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.
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.
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.
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.
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.
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.
The Query Builder box is displayed when you right-click in the SQL Worksheet and select Query Builder. You can use this box to create a SELECT statement by dragging and dropping table and view names and by graphically specifying columns and other elements of the query. When you finish building the query, the resulting SELECT statement is inserted into the SQL Worksheet.
The Query Builder capabilities are grouped under the following tabs.
Select Columns
Use the Select Columns tab to select tables and views, then columns within them, to be used in the query. Use the connections tree on the left to find the desired tables and views under the appropriate schema or schemas, and double-click each desired table and view.
Within each selected table or view, click to select the desired columns (all or specific ones) to include in the query.
Create Where Clause
Use the Create Where Clause tab to select, for each column in the WHERE clause, the column name, operator, and value. For example, you might want to select only rows where AUTHOR_LAST_NAME contains Melville or where RATING > 5.
Show SQL
Use the Show SQL tab to see a read-only display of the query reflecting what you have specified so far.
View Results
Use the View tab to test the query in its current form. Click the Execute Statement icon to execute the query.
Refresh: Specifies the refresh interval: the number of seconds between each time the query is automatically re-executed and the results display is updated. A value of zero (0) means that the query is not automatically re-executed after the initial execution.
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).
This dialog box is displayed if you click Migration, then Repository Management, then Create Repository.
Create Repository: Name of the database connection to use to create a migration repository. The objects associated with the migration repository are created in the schema of the user associated with the selected connection.
The Delete Repository dialog box is displayed if you click Migration, then Repository Management, then Delete Repository; the Truncate Repository dialog box is displayed if you click Migration, then Repository Management, then Truncate Repository.
Deleting a repository removes all schema objects that are used for the migration repository. Truncating a repository deletes all data from schema objects that are used for the migration repository, but does not delete the schema objects themselves, effectively leaving you with an empty repository.
Repository: Name of the database connection in which to delete or truncate the migration repository.
This dialog box is displayed if you click Migration, then Capture Exporter XML.
File Path: File path to the .xml file that was produced when you ran the appropriate version of the exporter tool for Microsoft Access (when you clicked Migrations, then Microsoft Access Exporter, then the appropriate version for your version of Microsoft Access).
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.
This dialog box is displayed if you try to rename a procedure. Specify a unique new name for the procedure.
This dialog box is displayed if you click Migration, then Repository Management, then Select Current Repository. You can use this dialog box to reconnect to a migration repository after you have disconnected (using Migration, then Repository Management, then Disconnect Migration Repository). In addition, if you have multiple migration repositories, and you can use this dialog box to switch from one to another.
Select Current Repository: Name of the database connection with the migration repository to be used for all operations relating to migrating third-party databases to Oracle.
This dialog box is displayed if you try to capture a third-party database before establishing and connecting to a current migration repository.
If no migration repository exists, create one by clicking Migration, then Repository Management, then Create Repository.
To make an existing migration repository the current one, right-click its connection in the Connections navigator and select Associate Migration Repository.
To open a connection to the migration repository, expand the node for its connection in the Connections navigator.
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 new password, confirm the password, and click OK.
This dialog box is displayed if you click List Revisions in the Branch/Tag dialog box. It contains a list of revisions in the repository.
Select the desired revision to use, and click OK.
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".
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.
This is a standard box for saving information to a file: use Location to navigate to (double-clicking) the folder in which to save the file, then specify the file name (including any extension) and, if necessary, the file type.
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).
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.
This dialog box is displayed when you click Save As in the Code Editor: 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.
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 4.51, "Database Schema Differences".
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.
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.6.2, "Offline Capture".
Click OK to close the error box. Later, run the controlling script to generate output containing the converted model.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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 Database SQL Developer Installation Guide.
|
 Copyright © 2006, 2009, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|