You can run SQL statements, PL/SQL scripts, and JavaScript code in the SQL Worksheet editor. Some salient features of the editor are in-context code completion, syntax highlighting, and error debugging.

You can enter SQL statements or PL/SQL code blocks to specify actions such as creating a table, inserting data, selecting data, or deleting data from a table. SQL keywords are automatically highlighted. For multiple statements, you must terminate:

• Each non-PL/SQL statement with either a semicolon (;) or a slash (/) on a new line
• Each PL/SQL statement with a slash (/) on a new line

For SQL*Plus and SQLcl statements supported in the worksheet, see Supported SQL*Plus and SQLcl Commands in SQL Worksheet.

The PL/SQL editor mode is triggered in the worksheet when opening the following object types: Functions, Procedures, Packages and Types. This editor helps you detect errors in your PL/SQL code during compilation. The output includes error details such as the specific line and column where the error is detected, along with a link to go to the relevant position in the code block.

The JavaScript worksheet mode supports the Multilingual Engine feature introduced in Oracle Database release 21c. For more information, see Support for Multilingual Engine.

Code Editor Assistance

If you press Ctrl+Space, the editor 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.

The editor also offers a comprehensive list of commands available through the Command Palette. To open the Command Palette, press Ctrl+Shift+P. For a list of keyboard shortcut keys, see Keyboard Shortcuts.

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

Drag and Drop Objects to Automatically Generate SQL Statements

You can drag objects from the left pane 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 PL/SQL code, you can enter the parameters before inserting the code into the worksheet.

Set Editor Preferences

You can set code editor preferences using the Preferences option available in the left sidebar. Some of the code editor options that you can customize are Theme (Light, Dark and High contrast dark), Font size and family, Tab size, Word wrap, Ruler, Line numbers and so on.

Toolbar Icons

The SQL Worksheet editor toolbar contains icons for the following operations:

• Worksheet drop-down list

  • Open: Open a file from the browser, device or object storage.
  • Open Recent: Display the recently accessed files. If there are more than five files in the recent list, then a More link is displayed for viewing the additonal files.
  • Save As: Save a file to the browser, device or object storage.
• New File
  • Select Worksheet: Create a worksheet.
  • Select PL/SQL: Create a PL/SQL object type. The editor switches to a PL/SQL mode.
• Open: Open a file from your browser, device or object storage. To open a file from your device, in the Open File slider, click Open File and browse to select the file, or drag and drop the file into the slider.

• Run Statement: Execute 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. A dialog box is displayed for entering variable values.

• Run Script: Execute all statements in the worksheet editor using the Script Runner. The SQL statements can include bind variables (but not substitution variables) of type VARCHAR2. A dialog box is displayed for entering bind variable values.

• Compile (for PL/SQL toolbar): Perform a PL/SQL compilation of the subprogram.

  Note
  
  The Compile icon is not available for MySQL database services.

• Explain Plan: Generate the execution plan for the statement. The output is displayed in the Explain Plan tab in the output pane. See Using the Explain Plan Diagram

  Note
  
  The Explain Plan icon is not available for MySQL database services.

• Autotrace: Run the statement and show the SQL statement execution statistics and the actual execution plan. The output is displayed in the Autotrace tab in the output pane.

  Note
  
  The Autotrace icon is not available for MySQL database services.

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

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

• Clear: Remove the statements from the editor.

• Open in Fullscreen: Open the editor in full screen mode.

• Data Load: Open the Data Loading wizard. See Loading Data.

  Note
  
  The Data Load icon is not available for MySQL database services.