| Oracle® Database SQL Developer User's Guide Release 1.1.2 Part Number B31695-02 |
|
|
View PDF |
| Oracle® Database SQL Developer User's Guide Release 1.1.2 Part Number B31695-02 |
|
|
View PDF |
Beta Draft
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.
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.
Updates: If any updates are available from the selected source or sources, select those that you want to download.
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.
Install Location: Select whether to install the extensions for all users or just for the current user.
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.
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.
Specify the type of object to create. After you click OK, the dialog box for creating that type of object is displayed.
Filter By: Available Items displays the types of objects that you can create in the current database connection; All Items displays all types of objects (some of which may not be available for selection).
Categories: A hierarchical display of categories of objects.
Items: Types of objects that you can create within the selected category.
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.
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.
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.
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/tiger@localhost:1521:orcl
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 database.
Note that to connect to a Microsoft SQL Server database, you must first download the appropriate Microsoft SQL Server 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 database.
Port: TCP/IP Port on which Microsoft SQL Server will listen.
Retrieve Database: Name of the Microsoft SQL Server database.
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 as follows: select all tables whose names start with Msys, and give the Admin user at least Read Design and Read Data permission on these tables.
Save changes and close the Access database file.
Create and test the connection in SQL Developer.
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 New, enter the connection information, and click Connect. 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 on the left side, and click Connect. To test the connection before you save changes to it, click Test.
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 New; to edit the selected database connection, click Edit. In both cases, a dialog box for specifying connection information is displayed (see Section 4.5, "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.
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 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 a table name in the Connections navigator and select Import Data, then Excel. It enables you to from an Excel spreadsheet into the table.
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.
Data Preview: Displays a read-only preview of the contents of the worksheet.
Columns tab
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.
Data Types tab
Displays information about the data types of the columns to be imported.
DML tab
This tab contains a read-only display of a SQL statement that will perform the operation.
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.1, "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.4, "Indexes".
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, "Creating 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).
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.6, "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.
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.
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, "Creating 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 Creating 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.
Specifies storage options for the table, enabling you to override the default storage options.
Tablespace: Name of the tablespace for the table.
Pct Free: Percentage of space in each of the data blocks of the table reserved for future updates to the rows of the table. You can enter a value from 0 through 99.
Pct Used: Minimum percentage of used space that Oracle maintains for each data block of the table. 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.
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).
Pct Increase: Percentage that each extent grows over the previous extent.
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.
Free Lists - 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 Lists - 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.
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.
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.
Specifies storage options for LOB (large object) columns, enabling you to override the default storage options.
Column: Name of the LOB column.
Store VARRAY as LOB: If this option is checked: If the maximum varray size is less than 4000 bytes, the database stores the varray as an inline LOB unless you have disabled storage in row; if the maximum varray size is greater than 4000 bytes or if you have disabled storage in row, the database stores in the varray as an out-of-line LOB. If this option is not checked, storage is based on the maximum possible size of the varray rather than on the actual size of a varray column.
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.
Tablespace: Name of the tablespace for the LOB data.
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.
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.
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.
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.
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.
Free Lists - 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 Lists - 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.
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.
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 for the Storage Options pane.
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.
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 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.11.11, "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.11.11.1, "User-Defined Report Example: Chart" for an example), plsql-dbms_output (dynamic HTML; see Section 1.11.11.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.
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.
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.11.11, "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 Creating 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 Entire SQL Query box, and displays the contents of the box before these edits.
Test Syntax: Checks the statement in the Entire 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 Creating 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 view or if you have only partially created a view, this tab contains a read-only display of a SQL statement that reflects the current definition of the view.
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 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.12.8, "File Types").
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.1, "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.
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. (The NULL option is checked by default.)
This dialog box is displayed when you click Tools and then Export. 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 Options tab. To specify the objects or types of objects to export, use the Objects tab.
Options tab
File: Specify the name of the file to contain the DDL statements for creating the objects to be exported (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.)
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.
Constraints as Alters: If this option is checked, constraints for each table are defined in separate ALTER TABLE statements instead of in the CREATE TABLE statement.
Export 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.
Include Drop Statements: If this option is checked, DROP statements are included before the CREATE statements, to delete any existing objects with the same names.
Objects tab
Connection: Select the database connection with the objects to be exported.
Objects: Expand the tree as needed to display the object types and objects that you want to export, and check to select the desired object types and/or objects. You must select at least one object or object type.
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 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.
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 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.40, "Create/Edit External Tool").
Edit: Displays a dialog box for editing the selected external tool (see Section 4.40, "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.39, "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.42, "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.42, "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 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.40, "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 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 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 line number to go to in the selected function or procedure. After you enter the line number and click OK, 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 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.
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.
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.
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 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.
Use this box to specify parameter values for running or debugging a PL/SQL function or procedure. (If you specify a package, select a function or procedure in the package.)
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 watchpoint.
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 dialog box is displayed if you click Tools, then Schema Diff. Use this box to find differences between objects of the same type and name (for example, tables named CUSTOMERS) in two different schemas, and optionally to update the objects in one schema (destination) to reflect differences in the other schema (source).
Use the Source tab to specify one database connection, and use the Destination tab to identify the database containing the schema with objects to be compared with the source schema. The database for the destination schema can be the same as, or different from, the database for the source schema.
Source tab
Connection: Database connection for the source schema (the schema in which selected objects are to be compared with objects in the destination schema).
Objects: Expand the hierarchy, and select one or more types or objects or specific objects that will compared with objects of the same type and name in the destination schema.
Destination tab
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.
Schemas: Select a schema in the database associated with the specified connection. SQL Developer will find differences in objects of the same type and name between this schema and the schema for the source connection.
Results tab
When you click Apply, the Results tab displays SQL statements to modify the destination objects to reflect source object features that are different-- for example, an ALTER TABLE statement to add a column from the source table to the destination table of the same name. However, the statements are not applied to the destination schema objects until you execute them after clicking Save As or Open Editor.
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.66, "Schema Differences".
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 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.9.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.
Use this box, which is displayed by clicking the SQL History button in the SQL Worksheet toolbar, to view SQL statements that you have executed and optionally to select (click) one to have it either replace the statements currently on the SQL Worksheet or be added to the statements currently on the SQL Worksheet.
You can click on a column heading to sort the rows by the values in that column.
The SQL history list will not contain any statement that can include a password. Such statements include (but are not necessarily limited to) CONNECT, ALTER USER, and CREATE DATABASE LINK.
Filter: If you type a string in the text box and click Filter, only SQL statements containing that string are displayed.
Clear: Removes all statements from the SQL history.
Replace: Replaces any statements currently on the SQL Worksheet with the selected statement.
Append: Appends the selected statement to any statements currently on the SQL Worksheet
Use this dialog box (displayed if you click Tools and then SQL*Plus and no SQL*Plus executable location is currently defined in your SQL Developer Preferences for Database) to specify the location of the SQL*Plus executable on the system on which you are running SQL Developer. The SQL*Plus executable is under the Oracle home directory or folder, and its specific location and file name depend on your operating system and Oracle Database installation.
If there is no SQL*Plus executable on the system on which you are running SQL Developer, you cannot invoke SQL*Plus from SQL Developer.
