1. SQL Developer for VS Code
  2. Using SQL Developer for VS Code
  3. Using the SQL Worksheet

3.5 Using the SQL Worksheet

You can use the SQL Worksheet to enter and execute SQL, PL/SQL, and SQLcl statements. You can specify any action that can be processed by the database connection associated with the worksheet, such as creating a table, inserting data, creating and editing a trigger, selecting data from a table, and saving that data to a file.

You can display a SQL Worksheet by right-clicking a connection name in the Connections panel and selecting Open SQL Worksheet.

When SQL Worksheet is open and if you also have existing SQL files open, you can attach a connection by clicking the connection icon in the lower right corner. Once a connection is associated with your editor or file, you see the name of your connection displayed in the lower right corner.

Description of attach_connection.png follows
Description of the illustration attach_connection.png

In the editor, you can enter one or more SQL or statements that you intend to execute. 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.

You can drag some kinds of objects such as tables or views from the Connections navigator and drop them into the editor pane. When you do this, the following options appear: Object Name, Select, Insert, Delete, Update. If you select 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.

When you press Ctrl+Space, a context-sensitive popup window appears. This 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. To exit at any time, press Esc or continue typing.

Description of worksheet.png follows
Description of the illustration worksheet.png

SQL Worksheet has the following icons at the top right corner:

  • Run Statement (Ctrl+Enter) executes the statement at the mouse pointer in the SQL Worksheet. The SQL statements can include bind variables and substitution variables of type VARCHAR2. A pop-up box is displayed for entering variable values. The output is displayed under the Query Result tab in the lower pane.

  • Run Script (F5) executes all statements in the SQL Worksheet using the Script Runner. The SQL statements can include substitution variables (but not bind variables) of type VARCHAR2. A pop-up box is displayed for entering substitution variable values. The output is displayed under the Script Output tab in the lower pane.

  • Run in SQLcl (Ctrl+Shift+Enter) executes the statement or script by opening a SQLcl command line session and displaying the output.

  • Explain Plan (F10) generates the execution plan for the statement (internally executing the EXPLAIN PLAN statement). The results appear under the Explain tab in the lower pane.

3.5.1 SQLcl Commands Supported in the SQL Worksheet

The SQL Worksheet supports SQLcl commands. SQLcl commands must be interpreted by the SQL Worksheet before being passed to the database. Any commands that are not supported by the SQL Worksheet are ignored and not passed to the database.

For information about SQLcl commands, you can enter the help command. For information about a specific command or topic, include it as the parameter (for example, help @, help exit, or help reserved words). If the command is not supported, or if there are restrictions or usage notes, the help display includes this information.

3.5.2 Script Runner

With Script Runner, you have access to commands such as @, @@, CONNECT, EXIT, QUIT, UNDEFINE, WHENEVER, and substitution variables. For example, to run a script named c:\myscripts\mytest.sql, type @c:\myscripts\mytest in the editor, and select the Run Script icon.

The following considerations apply to using the script runner:

  • You cannot use bind variables. (However, you can use bind variables of type VARCHAR2, NUMBER, and DATE.)

  • For substitution variables, the syntax &&variable assigns a permanent variable value, and the syntax &variable assigns a temporary (not stored) variable value.

  • For EXIT and QUIT, commit is the default behavior, but you can specify rollback. In either case, the context is reset, for example, WHENEVER command information and substitution variable values are cleared.

  • DESCRIBE works for most, but not all object types.

3.5.3 Explain Plan

The Execute Explain Plan icon generates the execution plan, which you can see by clicking the Explain Plan tab. The execution plan is the sequence of operations that will be performed to execute the statement.

The Explain Plain diagram view is a graphical representation of the contents of PLAN_TABLE, which is the default table for the results of the EXPLAIN PLAN statement.

By default, three levels of steps are visible in the diagram. You can use the +/- signs at the bottom of each step (available when the step has children) to expand or collapse. To view all steps in the diagram, use expand_all_icon Expand All in the toolbar.

The diagram also provides the following details:

  • Cardinality (number on the arrow to the parent step), which is the number of rows processed
  • Operation and options applied in that step
  • Execution order, which is the sequential number in the order of execution
  • Access predicates CPU cost in percentage (orange bar)
  • Total CPU cost for the step in percentage (blue circle)
  • Estimated I/O Cost, Bytes processed and Cost metrics
Description of explain_plan.png follows
Description of the illustration explain_plan.png

You see a brief description pop-up when you hover over any of the statistics in a step.

The icons in the toolbar are:

  • Text View: Displays the execution steps in a plain text format.

    For the Text View, you can select the level of detail displayed in explain plan by selecting the appropriate option in Using Preferences.

  • Diagram View: Displays the steps in a flowchart format.

  • Zoom In, Zoom Out: If a step is selected in the diagram, clicking the Zoom In icon ensures that it remains at the center of the screen.

  • Fit Screen: Fits the entire diagram in the visible area.

  • Actual Size: Sets the zoom factor to 1.

  • Expand All: Displays all steps in the diagram.

  • Reset Diagram: Resets the diagram to the initial status, that is, only three levels of steps are displayed.

  • Show Info: Shows the SELECT statement used by the Explain Plan functionality.

  • Min Visible Total CPU Cost(%): Defines the threshold to filter steps with total CPU cost equal or more than the provided value.

    Enter a value between 0 and 100. There is no filtering for 0.

  • Plan Notes: Displays the Explain Plan notes.

Properties

Double-click or press Enter on a selected step to open the Properties slider, which provides more information about that step. See PLAN_TABLE in Oracle Database Reference for a description of each property.

The Properties slider shows:

  • All information for that step extracted from PLAN_TABLE in a tabular format. Nulls are excluded.

    You can select JSON to view the properties in JSON format.

  • Information from OTHER_XML column of PLAN_TABLE.

    The information is displayed in JSON format.

Navigation

  • Press the Tab key to move through the steps in the execution order. The selected step has a blue border around it.

    To move in the reverse direction, press the Shift + Tab keys.

    If no step is selected, pressing the Tab key selects the step with execution number 1.

  • Depending on the zoom level, use horizontal and vertical scrollbars to view different parts of diagram.

    Click the left mouse button and hold it to pan the diagram around up and down.

    Use the scroll_icon icon at the bottom right to scroll to the top of the diagram.

Diagram Navigator

The Diagram Navigator is at the top right corner and represents a smaller copy of the diagram. The rectangle border allows zoom-in and zoom-out operations and moves to show different parts of the diagram.

The diagram navigator shows a list with steps having more than 1% CPU cost in descending order. Click a step in the list to navigate to the same step in the diagram, enabling you to see it in the context of the other steps.

3.5.4 SQL History

You can click the SQL History tab in the lower right pane to view a list of SQL statements and scripts that you have executed, and optionally double-click one or more statements to have them either replace the statements currently on the SQL Worksheet or be added to the statements currently on the SQL Worksheet. The list is at first organized by date, and then by connection name.

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.

You can control the maximum number of statements in the history by setting the SQL History Limit in Preferences.

When a SQL statement in the SQL History list is selected, it automatically opens the PL/SQL subprogram in the SQL Worksheet pane.

To sort the statements in the list, double-click the column header that you want to sort on and the statements are sorted in ascending order. Double-click the header again to sort in descending order. To remove sorting applied to the column, double-click the column header again.

To display the Search functionality, in the SQL History pane, press Ctrl+F (Cmd+F for MacOS).

Description of sql_history_n.png follows
Description of the illustration sql_history_n.png

3.5.5 Using Snippets to Insert Code Fragments

Snippets are code fragments, such as SQL functions, Optimizer hints, and miscellaneous PL/SQL programming techniques. Some snippets are just syntax, and others are examples. You can insert and edit snippets when you are using the SQL Worksheet or creating or editing a PL/SQL function or procedure.

Snippets are displayed in the lower part of the Connections panel. Click the Expand icon to view all the available sinppets in each group (such as Aggregate Functions or Character Functions). In most cases, the fragments in each group do not represent all available objects in that logical grouping, or all formats and options of each fragment shown. For complete and detailed information, see the Oracle Database documentation.

Description of snippets.png follows
Description of the illustration snippets.png

To insert a snippet into your code in a SQL Worksheet or in a PL/SQL function or procedure, drag the snippet from the left bottom pane and drop it into the desired place in your code. Edit the syntax so that the SQL function is valid in the current context.

For example, you could type SELECT and then drag CONCAT(char1, char2) from the Character Functions group. Then, edit the CONCAT function syntax and type the rest of the statement, such as in the following:

SELECT CONCAT(title, ' is a book in the library.') FROM books;

3.5.6 Connection Management

Starting with release 24.1.1, opening a new SQL Worksheet creates a new, dedicated connection to the database. This means that long-running queries in your SQL Worksheet do not block other database requests.

You can control this behaviour using the Session per attached worksheet setting in Preferences.

  1. From the File menu, go to Preferences and then select Settings from the sub-menu.
  2. Under User, expand Extensions and then expand Oracle SQL Developer Extension for VS Code.

  3. In the right panel, under Database Connections, in the Session per attached worksheet field, select off from the drop-down list to remove this setting.

    The default setting is on.