Using the Worksheet

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 the Dashboard.

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

Related Topics

Oracle SQL Developer User's Guide

1.5.1 Navigating Objects and Worksheets

The Navigator tab in the left pane displays the saved objects for the selected schema. You can select the schema and object type, for which you want to view the objects, from the drop-down lists.

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 available in any other browser.

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 Refresh icon next to the Search field. To display the help topic for the left pane, click the Help 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 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.

The context (right-click) menu for a worksheet consists of options such as Open and Delete.

The context menu for any object in the Navigator consists of:

Quick DDL: Saves the DDL statements for creating the object, to a worksheet or to a text file.
Run: Inserts the code for a selected function, procedure, or package into the worksheet. This command is available only for functions, procedures, and packages.

The context menu that is specific to object types consists of the following:

For a table:

New: To create a new table for a specific schema.
Edit: To edit an existing table for a specific schema.
Use as Template: To create a new table by using an existing table for the initial content.
Add Index: To create an index for a table.

See Creating or Editing a Table.

For a sequence:

Add Sequence: To create a sequence for a specific schema.
Edit Sequence: To edit a sequence for a specific schema.

See Creating or Editing a Sequence.

For an index:

Edit: To edit an index for a specific table.

See Creating or Editing an Index.

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 auto-complete code that you are editing. This list is based on the code context at the insertion point.

Description of autocomplete_wrksheet.png follows

Description of the illustration autocomplete_wrksheet.png

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

Description of error_wrksheet.png follows

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.

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

Related Topics

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

Schema: Database schema in which to create the table.

Name: Name of the table.

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

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, select the row that is added to the table below, 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 of 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.
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.

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

DDL pane

You can review and save the SQL statements that SQL Developer Web 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 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 have options to save to a text file or to clear the output.

Related Topics

Oracle Database SQL Language Reference

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, to reflect any changes you have made to the object’s properties. 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 have options to save to a text file or to 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 options to save to a text file or to 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.

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 contmenu_rowheader.png follows

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

Related Topics