1.5 Using the Worksheet

In SQL Developer Web, the worksheet is the code editor that is used to enter and execute SQL and PL/SQL statements.

You can use SQL and PL/SQL statements in the worksheet to create a table, insert data, create and edit a trigger, select data from a table, and save that data to a file. Some other features are syntax highlighting and error detection.

Database administrators can also access the worksheet from the Quick Links pane in Dashboard.

The Worksheet page consists of the left pane for navigating worksheets and objects, the editor for executing SQL statements, and the output pane for viewing the results. These panes are described in the following sections:

• Navigating Objects and Worksheets

• Executing SQL Statements in the Worksheet Editor

  • Shortcut Keys in the Worksheet

  • Creating or Editing a Table

  • Creating or Editing an Index

  • Creating or Editing a Sequence

• Viewing the Worksheet Output

1.5.1 Navigating Objects and Worksheets

The Navigator tab in the left pane displays the saved objects for the selected schema. The drop-down menus enable you to select the schema for which you want to see the objects, and filter the results by object type.

The Worksheets tab displays the worksheets that are saved in the browser. The worksheets displayed are dependent on the browser used (Internet Explorer, Mozilla Firefox, or Google Chrome) for the SQL Developer Web session at the time of saving the worksheet, and they will not be accessible from any other browser. You can open or delete an existing worksheet by using the context (right-click) menu.

You can also search the contents of a saved worksheet or search for objects in the Navigator tab by name. The search functionality is not case-sensitive, retrieves all matching entries and does not require the use of wildcard characters.

To refresh the objects or worksheets list, click the icon. To create a new object, click the icon.

For context-related help, click the icon.

You can drag objects from the Navigator and drop them into the worksheet editor in the right pane:

• If you drag and drop a table or view, you are prompted to select one of the following SQL statements: Insert, Update, Select, or Delete. For example, if you choose Select, a Select statement is constructed with all columns in the table or view. You can then edit the statement, for example, modifying the column list or adding a WHERE clause.

  If you choose Object Name, the name of the object prefixed by the schema name is added to the worksheet.

• If you drag and drop a function or procedure, you can choose to insert the name or the PL/SQL code of the function or procedure in the worksheet. If you select the PL/SQL code, you can enter the parameters before inserting the code into the worksheet.

1.5.1.1 Database Objects

You can create and edit objects for some object types by using the context menu in the Navigator tab. If you do not have any existing objects for the object type (such as tables), you can create objects by using the New Object icon, after first selecting the object type from the drop-down list. For all objects, the context menu includes Quick DDL, which saves the DDL statements for creating the object to a worksheet or to a text file. The various object types and the corresponding options are:

• Tables: Tables are used to hold data. Each table has multiple columns that describe the attributes of the database entity associated with the table, and each column has an associated data type.

  You can create new tables, edit existing tables, and add indexes by using the options available in the context menu. Or you can select Use as Template, which displays the dialog box with information for the selected object filled in. You should then change the object name and any other aspects of the definition, as needed, to create a new object of that type. For a description of the fields, see Creating or Editing a Table.

• Views: Views are virtual tables that select data from one or more underlying tables. For information about creating and editing a view, see Creating or Editing a View.

• Indexes: An index contains an entry for each value that appears in the indexed column of the table and provides direct, fast access to rows. For information about creating and editing an index for a table, see Creating or Editing an Index.

• Packages, Functions, Procedures: A package contains subprograms, which are programming objects that can be stored and executed in the database server, and called from other programming objects or applications. A package can contain functions or procedures, or both. Select Run from the context menu to insert the code for a selected function, procedure, or package into the worksheet.

• Triggers: Triggers are stored PL/SQL blocks associated with a table, a schema, the database, or anonymous PL/SQL blocks or calls to a procedure implemented in PL/SQL or Java.

• Types: A data type associates a fixed set of properties with the values that can be used in a column of a table or in an argument of a function or procedure.

• Sequences: Sequences are used to generate unique integers. You can use sequences to automatically generate primary key values. For information about creating and editing a sequence, see Creating or Editing a Sequence.

• Materialized View: A materialized view is a database object that contains the results of a query. The FROM clause of the query can name tables, views, and other materialized views. For information about creating and editing a materialized view, see Materialized View.

• Materialized Views Logs: A materialized view log is a table associated with the master table of a materialized view. When DML changes are made to master table data, Oracle Database stores rows describing those changes in the materialized view log and then uses the materialized view log to refresh materialized views based on the master table.

• Synonyms, Public Synonyms: Synonyms provide alternative names for tables, views, sequences, procedures, stored functions, packages, materialized views, Java class database objects, user-defined object types, or other synonyms.

• Database Links: A database link is a database object in one database that enables you to access objects on another database. After you have created a database link, you can use it to refer to tables and views in the other database.

1.5.2 Executing SQL Statements in the Worksheet Editor

The worksheet editor in the right pane enables you to enter the SQL statements that you intend to execute. You can use SQL and PL/SQL statements to specify actions such as creating a table, inserting data, selecting data or deleting data from a table. For multiple statements, each non-PL/SQL statement must be terminated with either a semicolon or (on a new line) a slash (/), and each PL/SQL statement must be terminated with a slash (/) on a new line. SQL keywords are automatically highlighted.

For a list of the SQL*Plus statements that are supported by the SQL Worksheet, see Supported SQL*Plus Statements.

If you press Ctrl+Space, the worksheet provides you with a list of possible completions at the insertion point that you can use to autocomplete code that you are editing. This list is based on the code context at the insertion point. Also, you can select multiple options in the list using Ctrl+Click.

Description of the illustration autocomplete_wrksheet.png

An error in the code is signified by a red dot or squiggle line. When you hover over it, you see a pop-up displaying possible fixes for resolving the error.

Description of the illustration error_wrksheet.png

The SQL Worksheet toolbar contains icons for the following operations:

• Worksheet enables you to create a worksheet, open or delete an existing worksheet, and save a worksheet.

  Note:

  The worksheets are saved in the browser. Therefore, if you do sensitive work on a computer in a public network, remember to clear the browser cache before you leave. Additionally, the saved worksheets will only be displayed in the browser that was used at the time of creating the worksheet.

• Run Statement executes the selected statements or the statement at the mouse pointer in the worksheet editor. The SQL statements can include bind variables and substitution variables of type VARCHAR2 (although in most cases, VARCHAR2 is automatically converted internally to NUMBER if necessary). A dialog box is displayed for entering variable values.

• Run Script executes all statements in the worksheet editor using the Script Runner. The SQL statements can include bind variables (but not substitution variables) of type VARCHAR2 (although in most cases, VARCHAR2 is automatically converted internally to NUMBER if necessary). A dialog box is displayed for entering bind variable values.

• Explain Plan generates the execution plan for the statement (internally executing the EXPLAIN PLAN statement). The execution plan is automatically displayed in the Explain Plan tab in the worksheet output pane.

• Autotrace runs the statement and collects runtime statistics and the actual execution plan. The Autotrace output is displayed in the Autotrace tab in the worksheet output pane. Currently, there are no preferences available.

• Download Editor Content downloads the content of the worksheet as a SQL file to the local system.

• Format formats the SQL statement in the editor, such as capitalizing the names of statements, clauses, keywords, and adding line breaks and indentation.

• Clear removes the statements from the editor.

• Tour provides a guided tour of the worksheet highlighting salient features and providing information that is useful if you are new to the interface.

• Help provides context-related help and provides a link to the help documentation.

For a list of shortcuts used in the worksheet, see Shortcut Keys in the Worksheet.

1.5.2.1 Shortcut Keys in the Worksheet

The following table lists the shortcut keys for various user actions in the worksheet.

Table 1-1 Shortcut Keys for User Actions in the Worksheet

Shortcut	Action
Ctrl+Enter/ Cmd+Enter	Runs the code as query.
Ctrl+Down Arrow/ Cmd+Down Arrow	Moves to the next SQL code from history.
Ctrl+Up Arrow/ Cmd+Up Arrow	Moves to the previous SQL code from history.
Ctrl+D/ Cmd+D	Clears the editor.
Ctrl+S/ Cmd+S	Saves the current worksheet.
Ctrl+O/ Cmd+O	Opens the worksheet browser dialog.
Ctrl+I/ Cmd+I	Downloads the content of the editor.
F1	Opens the help topic.
F5	Runs code as script.
F6	Shows Autotrace.
F10	Shows Explain Plan.
Ctrl+F7/ Cmd+F7	Formats code in the editor.
Ctrl+Space/ Cmd+Space	Autocompletes code (shows hints).
Windows+Esc/ Cmd+Esc	Focuses outside the editor and navigates to the rest of the application using the Tab key.

1.5.2.2 Creating or Editing a Table

The Table Properties Dialog is displayed when you create a table, edit an existing table, or create a table using an existing one as a template. To create or edit a table, right-click a table object in the Navigator tab. The table options are also available when you right-click a table object in the Data Modeler Navigator tab. The table properties are grouped in several panes.

If you are editing an existing table, you can visit the panes in any order. If you click Create before you are finished creating the table, right-click the table name, select Edit, and continue creating the table.

Note:

Editing a partitioned table is not recommended. For a partitioned table, when you right-click and select Edit, a warning message is displayed. This is how you can identify whether the table is partitioned or not.

Schema: Database schema in which to create the table. By default, a new table is created in the existing schema or the schema that you are logged into.

Name: Name for the table.

The different panes in the dialog are described in the following sections:

• Columns pane

• Primary Key pane

• Unique Keys pane

• Indexes pane

• Foreign Keys pane

• Table Constraints pane

• Comments pane

• Storage pane

• External Table Properties pane

• Materialized View

• DDL pane

• Output pane

Columns pane

Specifies properties for each column in the table.

General tab

Lists the columns currently in the table. To add a column, click the Add Column (+) icon, from the table below, select the column whose properties you want to add, and enter the details for each column in the fields at the bottom. To delete a column, select the row and click the Remove Column (-) icon; to move a column up or down in the table definition, select it and use the up-arrrow and down-arrow buttons.

• Name: Name for the column.

• Datatype: Data type for the column.

• Default: If no value is specified, the default value inserted into the column when a row is inserted.

• Default on NULL: Applicable for Oracle Database 12c and later releases. If this option is selected, when a row is inserted into the table and the value specified for the column is NULL, the default value is inserted into the column.

• Expression: Expression for computing the value in the column.

• Comments: Optional descriptive comments about the column. Use this field to provide descriptions for the attributes.

In the table:

• PK: If this option is selected, the column becomes the primary key.

• Identity Column: If this option is selected, the column becomes an identity column. This is applicable only for Oracle Database 12c and later releases. For more details, see the Identity Column tab.

Constraints tab

Displays the Not Null and Check Constraints for a column. A check constraint requires values in a column to comply with a specified condition.

• Not Null Constraint: Name: Name for the Not Null constraint.

• Not Null Constraint: Not Null: If this option is selected, 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 cannot be null.

• Check Constraint: Name: Name for the check constraint definition.

• Check Constraint: Constraint: Condition that must be met for a column to fulfill the check constraint. You can use 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.

• Enabled: If this option is selected, the constraint is checked when data is entered or updated in the column.

• Deferrable: If this option is selected, you can defer checking the validity of the constraint until the end of a transaction.

• Initially Immediate: If this option is selected, the constraint is checked whenever you add, update, or delete data from the column.

• Validate: If this option is selected, the existing data is checked to see if it conforms to the constraint.

Identity Column tab

Applicable for Oracle Database 12c and later releases. The Identity Column tab lists the properties of the identity column. This tab becomes available only after the Identity Column checkbox is selected for the column in the General tab. An identity column is an autoincrement column that can be used to identify a table row. Only one identity column can be specified for a table.

• Generate: Always means that values cannot be explicitly included for the identity column in INSERT OR UPDATE statements, By Default means values for the identity column are generated automatically if no values are specified explicitly, By Default on Null means values are generated for the column only when a NULL value is supplied.

• Start with: Starting value of the sequence.

• Increment: Interval between successive numbers in a sequence.

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

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

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

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

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

Primary Key pane

Specifies the primary key for the table. The primary key is the column, or set of columns, that uniquely identifies each row in the table. If the Primary Key checkbox is selected for a column in the General tab, the corresponding fields are automatically populated in the Primary Key pane. You can make changes to the properties as required.

An index is automatically created on the primary key.

• Name: Name of the constraint to be associated with the primary key definition.

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

• Index: Name of the index to which the primary key refers.

• Tablespace: Name of the tablespace associated with the index.

• Available Columns: Lists the columns that are available to be added to the primary key definition. You can select multiple attributes, if required, for the primary key.

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

Unique Keys pane

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

To add a unique constraint, click the Add button; to delete a unique constraint, select it and click the Remove button.

• Name: Name of the unique constraint.

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

• Rely: If this option is selected, the constraint in NOVALIDATE mode is taken into account during query rewrite.

• Deferrable: If this option is selected, in subsequent transactions, constraint checking can be deferred until the end of the transaction using the SET CONSTRAINT(S) statement.

• Initially Immediate: If this option is selected, the constraint is checked at the end of each subsequent SQL statement.

• Validate: If the option is selected, the existing data is checked to see if it conforms to the constraint.

• Index: Name of the index to which the unique key refers.

• Tablespace: Name of the tablespace associated with the index.

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

Indexes pane

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

• Name: Name of the index.

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

• Tablespace: Name of the tablespace for the index.

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

• Available Columns and Selected Columns: Columns selected for the index. To select a column, click the column in the Available Columns box, and then click the click the Add Selected Columns icon to move it to the Selected Columns box.

Foreign Keys pane

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

• Name: Name of the foreign key definition.

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

• Rely, Deferrable, Initially Immediate, Validate: See the description of these fields in the Unique Keys pane.

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

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

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

• Referenced Constraint: On Delete: Action to take automatically when a row in the referenced table is deleted and rows with that value exist in the table containing this foreign key: NO ACTION (shown by a crossing line in diagrams) performs no action on these rows; CASCADE (shown by an "X") deletes these rows; SET NULL (shown by a small circle) sets null all columns in those rows that can be set to a null value.

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

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

Table Constraints pane

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

• Name: Name of the check constraint definition.

• Check Condition: Condition that must be met for a row to fulfil the check constraint. You can use 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.

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

Comments pane

Optional descriptive comments about the table.

Storage pane

Enables you to specify storage options for the table. When you create or edit a table or an index, you can override the default storage options.

• Organization: Specifies that the table is stored and organized with (Index) or without an index (Heap) or as an external table (External).

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

• Logging: ON means that the table creation and any subsequent INSERT operations against the table are logged in the redo log file. OFF means that these operations are not logged in the redo log file.

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.

External Table

• Access Driver Type: Specifies the type of external table.

  • ORACLE_LOADER: Extracts data from text data files. This is the default access driver, which loads data from external tables to internal tables.

  • ORACLE_DATAPUMP: Extracts data from binary dump files. This access driver can perform both loads and unloads.

  • ORACLE_BIGDATA: Extracts data from Oracle Big Data Appliance.

  • ORACLE_HDFS: Extracts data stored in a Hadoop Distributed File System (HDFS).

  • ORACLE_HIVE: Extracts data stored in Apache HIVE.

• Default Directory: Specifies the default directory to use for all input and output files that do not explicitly name a directory object. The location is specified with a directory object, not a directory path.

• Access Params: Assigns values to the parameters of the specific access driver for the external table. Access parameters are optional.

  • OPAQUE_FORMAT_SPEC: The opaque_format_spec specifies all access parameters for the ORACLE_LOADER, ORACLE_DATAPUMP, ORACLE_HDFS, and ORACLE_HIVE access drivers. For descriptions of the access parameters, see Oracle Database Utilities. Field names specified in the opaque_format_spec must match columns in the table definition, else Oracle Database ignores them.

  • USING CLOB: Enables you to derive the parameters and their values through a subquery. The subquery cannot contain any set operators or an ORDER BY clause. It must return one row containing a single item of data type CLOB.

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

• 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 raises an error, such as a data type conversion error, the row is rejected even if that column was not referenced in the select list of the query.

  • REFERENCED: Processes only those columns in the select list of the query.

  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.

• Location: Specifies the data files for the external table. Use the Add (+) icon to add each location specification.

  • For ORACLE_LOADER and ORACLE_DATAPUMP, the files are named in the form directory:file. The directory portion is optional. If it is missing, then the default directory is used as the directory for the file. If you are using the ORACLE_LOADER access driver, then you can use wildcards in the file name. An asterisk (*) signifies multiple characters and a question mark (?) signifies a single character.

  • For ORACLE_HDFS, LOCATION  is a list of Uniform Resource Identifiers (URIs) for a directory or for a file. There is no directory object associated with a URI.

  • For ORACLE_HIVE, LOCATION  is not used. Instead, the Hadoop HCatalog table is read to obtain information about the location of the data source (which could be a file or another database).

Opaque Format Spec

Specifies all access parameters for the ORACLE_LOADER, ORACLE_DATAPUMP, ORACLE_HDFS, and ORACLE_HIVE access drivers.

For example:

RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
    TERRITORY AMERICAN
    BADFILE log_file_dir:'ext_1v3.bad'
    LOGFILE log_file_dir:'ext_1v3.log'
    FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '^' LDRTRIM
    ( PROD_ID,
      CUST_ID ,
      TIME_ID DATE(10) "YYYY-MM-DD",
      CHANNEL_ID ,
      PROMO_ID ,
      QUANTITY_SOLD ,
      AMOUNT_SOLD ,
      UNIT_COST ,
      UNIT_PRICE
    )

and the full statement:

CREATE TABLE SH.SALES_TRANSACTIONS_EXT
    (
      PROD_ID NUMBER ,
      CUST_ID NUMBER , 
      TIME_ID DATE ,
      CHANNEL_ID NUMBER ,
      PROMO_ID NUMBER ,
      QUANTITY_SOLD NUMBER , 
     AMOUNT_SOLD NUMBER (10,2) , 
     UNIT_COST NUMBER (10,2) , 
     UNIT_PRICE NUMBER (10,2)
     )
     ORGANIZATION EXTERNAL
     (
     TYPE ORACLE_LOADER
     DEFAULT DIRECTORY DATA_FILE_DIR
     ACCESS PARAMETERS
     (
     RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
    TERRITORY AMERICAN
    BADFILE log_file_dir:'ext_1v3.bad'
    LOGFILE log_file_dir:'ext_1v3.log'
    FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '^' LDRTRIM
    ( PROD_ID ,
      CUST_ID ,
      TIME_ID DATE(10) "YYYY-MM-DD",
      CHANNEL_ID ,
      PROMO_ID ,
      QUANTITY_SOLD ,
      AMOUNT_SOLD ,
      UNIT_COST ,
      UNIT_PRICE
    )
    )
     LOCATION ( "DATA_FILE_DIR":'sale1v3.dat')
     )
     REJECT LIMIT 100
 ;

CLOB Subquery

Type or copy and paste the query.

Note:

For more information about the external table fields, see Database Utilities and SQL Language Reference.

Materialized View

Query: Contains the SQL code for the query part of the view definition. Type or copy and paste the query.

General

• On Pre-built Table: If Yes, 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: Yes 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 the subquery. If No , the precision of the table or materialized view columns must exactly match the precision returned by the subquery, or the create operation will fail.

• For Update: Select Yes to allow a subquery, primary key, object, or rowid materialized view to be updated. When used in conjunction with Advanced Replication, these updates will be propagated to the master.

• Real Time MV: Select Yes to create a real-time materialized view or a regular view. A real-time materialized view provides fresh data to user queries even when the materialized view is not in sync with its base tables due to data changes. Instead of modifying the materialized view, the optimizer writes a query that combines the existing rows in the materialized view with changes recorded in log files (either materialized view logs or the direct loader logs). This is called on-query computation.

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

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

• Use Index: If Yes, a default index is created and used to speed up incremental (fast) refresh of the materialized view. If No, 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.)

• Index Tablespace: Specifies the tablespace in which the materialized view is to be created. If a tablespace is not selected, the materialized view is created in the default tablespace of the schema containing the materialized view.

• Cache: If Yes, 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 No, the blocks are placed at the least recently used end of the LRU list.

Refresh Clause

• Refresh: Select Yes to enable refresh operations.

• Refresh Type: 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.

• Action: The type of refresh operation to be performed:

  • On Demand: Performs a refresh when one of the DBMS_MVIEW refresh procedures are 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.

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

• Next Date: 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.

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

• Default Storage: If Yes, DEFAULT specifies that Oracle Database will choose automatically which rollback segment to use. If you specify DEFAULT, you cannot specify the rollback_segment. DEFAULT is most useful when modifying, rather than creating, a materialized view.

• Storage Type: MASTER specifies the remote rollback segment to be used at the remote master site for the individual materialized view. LOCAL specifies the remote rollback segment to be used for the local refresh group that contains the materialized view. This is the default.

• Rollback Segment: Enter the name of the rollback segment.

• Using Constraint: 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.

DDL pane

You can review and save the SQL statements that SQL Developer Web will use to create or edit the object. If you want to make any changes, go back to the relevant panes and make the changes there.

For a new table, click CREATE to view the generated DDL statements. When you edit table properties, click UPDATE to view the generated ALTER statements. For a new table, the UPDATE tab will not be available.

When you are finished, click Apply.

Output pane

Displays the results of the DDL commands. If there are any errors, go to the appropriate pane, fix the errors, and run the commands again. You can save to a text file or clear the output.

1.5.2.3 Creating or Editing an Index

The Index Properties dialog box is displayed when you create or edit an index. You can create and edit in three ways:

• Right-click a table in the worksheet Navigator tab, and select Add Index. To edit, right-click an index in the Navigator tab and select Edit.

• Right-click a table object in the Data Modeler Navigator tab, and select Add Index.

• Create and edit an index in the Table Properties Dialog (See Creating or Editing a Table).

Definition pane

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

• Table: Name of the table associated with the index.

• Schema: Database in which to create the index.

• Tablespace: Tablespace for the index.

• Name: Name of the index.

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

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

• Available Columns: Columns available in the table.

• Selected Columns: Columns selected for the index. Click the Add Selected Columns icon to move columns from the Available Columns list.

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

DDL pane

You can review and save the SQL statements that SQL Developer Web will use to create or edit the index. If you want to make any changes, go back to the Definition pane and make the changes there. For a new index, click CREATE to view the generated DDL statements. When you edit index properties, click UPDATE to view the generated ALTER statements. For a new index, the UPDATE tab will not be available. When you are finished, click Apply.

Output pane

Displays the results of the DDL commands. If there are any errors, go to the Definition pane, fix the errors, and run the commands again. You can save to a text file or clear the output.

1.5.2.4 Creating or Editing a Sequence

This Sequence Properties Dialog is displayed when you right-click a sequence object in the worksheet Navigator tab and select Add Sequence or Edit Sequence. These options are also available when you right-click a table object in the Data Modeler Navigator tab.

A sequence is an object from which multiple users may generate unique integers. You can use sequences to automatically generate primary key values.

Properties pane

• Schema: Database schema in which to create the sequence.

• Name: Name of the sequence.

• Start with: Starting value of the sequence.

• Increment: Interval between successive numbers in a sequence.

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

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

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

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

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

DDL pane

You can review and save the SQL statements that SQL Developer Web will use to create or edit the sequence. If you want to make any changes, go back to the Properties pane and make the changes there. For a new sequence, click CREATE to view the generated DDL statements. When you edit a sequence, click UPDATE to view the generated ALTER statements. For a new sequence, the UPDATE tab will not be available. When you are finished, click Apply.

Output pane

Displays the results of the DDL commands. If there are any errors, go to the Properties pane, fix the errors, and run the commands again. You have save to a text file or clear the output.

1.5.2.5 Creating or Editing a View

The View Properties Dialog is displayed when you create or edit a view. To create or edit, right-click a view in the worksheet Navigator tab, and select New or Edit.

Schema: Database schema in which to create the view.

Name: Name of the view.

The different panes in the dialog are described in the following sections:

SQL Query pane

Enter or copy and paste the SQL query for the view, using the SELECT and FROM keywords along with the syntax needed to retrieve the desired information. A semicolon is not required after the query.

Columns pane

Click the Refresh Columns icon to automatically populate the columns in this pane. You can edit the columns by selecting the required row and making changes in the Header Alias and Comments fields.

Storage pane

Force on Create: Select Yes to create the view regardless of whether the base tables of the view or the referenced object types exist or the owner of the schema containing the view has privileges on them. These conditions must be true before any SELECT, INSERT, UPDATE, or DELETE statements can be issued against the view. If the view definition contains any constraints, CREATE VIEW ... FORCE fails if the base table does not exist or the referenced object type does not exist. CREATE VIEW ... FORCE also fails if the view definition names a constraint that does not exist.

Query Restriction: Read Only prevents the view from being used to add, delete, or change data in the underlying table. Check Option prohibits any changes to the underlying table that would produce rows that are not included in this view.

Use the Primary Key, Unique Keys, Foreign Keys, and Comments panes to add or edit properties as required.

DDL

Based on the inputs provided, the DDL statements are generated. You can review and save the SQL statements. If you want to make any changes, go back to the relevant pane and make the changes there. For a new view, click CREATE to view the generated DDL statements. When you edit a view, click UPDATE to view the generated ALTER statements. For a new view, the UPDATE tab will not be available. When you are finished, click Apply.

Output

Displays the results of the DDL commands. If there are any errors, go to the respective pane, fix the errors, and run the commands again. You can save to a text file or clear the output.

1.5.3 Viewing the Worksheet Output

The bottom right pane of the Worksheet screen has tabs that display the following panes:

• Query Result: Displays the results of the most recent Run Statement operation in a display table.

• Script Output: Displays the text output from your statements executed as a script using SQL Developer Web's script engine.

• DBMS Output: Displays the output of DBMS_OUTPUT package statements.

• Explain Plan: Displays the plan for your query using the Explain Plan command.

• Autotrace: Displays the session statistics and execution plan from v$sql_plan when executing a SQL statement using the Autotrace feature. Displays the output if you clicked the Autotrace icon.

• SQL History: Displays the SQL statements and scripts that you have executed. To re-enter a previously executed query in the worksheet, double-click the query in the history list. You can search for specific statements by clicking the Search icon. The Search functionality is case-sensitive, retrieves all entries that contain the search text, and does not require wildcard characters.

The icons in this pane are:

• Clear output: Clears the output.

• Show info: Displays the SQL statement for which the output is displayed.

• Open in new tab: Opens the query result or explain plan in a new window.

• Download: This is applicable only for Query Result. Enables you to download the query result to your local computer in CSV, JSON, XML, or TEXT (.tsv) format.

In the Query Result tab, in the display table, the context menu (right-click) for the row header consists of the following:

• Columns enables you to select columns to hide.

• Sort displays a dialog box for selecting columns to sort by. For each column, you can specify ascending or descending order, and you can specify that null values be displayed first.

Description of the illustration contmenu_rowheader.png

The context menu for the rest of the display table consists of the following commands:

• Count Rows displays the number of rows in the result set for your query.

• Single Record View enables you to view data for a table or view, one record at a time.

• Export generates the file for download based on the format selected, which can be XML, CSV (comma-separated values including a header row for column identifiers), Insert , Delimited, Fixed, HTML, JSON, or TEXT.

  Note:

  If a popup blocker is enabled, it will prevent the file from downloading.

  
  Description of the illustration contmenu_table.png