3.6 Using the SQL Notebook

SQL Notebook is a powerful feature of the SQL Developer Extension for VS Code that offers an alternative to SQL Worksheets that build up a series of queries with Markdown, similar to user defined reports.

SQL Notebooks use a YAML-based structure, making them both human-readable and machine-parseable. They allow you to create, organize, and run SQL and PL/SQL code in a structured notebook format. You can also create Markdown which help organize reports, group related queries, and add meaningful comments.

3.6.1 Working With SQL Notebooks

SQL Notebooks enable you to run query blocks in a structured notebook format and view the results. You can create a new notebook or edit an existing one.

Open a New SQL Notebook

From the File menu, select New File, and then select Oracle SQL Notebook.
Description of sql_notebook.png follows
Description of the illustration sql_notebook.png

VS Code opens a new SQL Notebook editor with a code cell to enter your query. The default extension of a SQL Notebook is .sqlnb. To run a SQL Notebook, you need to attach it to a connection.
Description of new_sql_notebook_noconnection.png follows
Description of the illustration new_sql_notebook_noconnection.png

Attach a SQL Notebook to a Connection

When a SQL Notebook is opened, it is not attached to any connection by default. You can attach a connection by clicking the connection icon in the lower-right corner of the SQL Notebook editor.
  1. In the right pane, click the No connection attached label.

    The Search box displays the list of available connections.
    Description of sql_notebook_connection_searchbox.png follows
    Description of the illustration sql_notebook_connection_searchbox.png

  2. Select a connection.

    The SQL Notebook is now attached to the selected connection. You see the name of your connection displayed in the lower-right corner.

Alternatively, you can also right-click on the connection name in the left pane and select Open SQL Notebook. In this case, the notebook is attached to the dedicated database connection by default.
Description of open_sql_notebook_connection.png follows
Description of the illustration open_sql_notebook_connection.png

Save the SQL Notebook

From the File menu, select Save As or press Ctrl+Shift+S. Provide a meaningful file name for the SQL Notebook and save it. The notebook will be saved as an .sqlnb file.

Open an Existing SQL Notebook

From the File menu, select Open File or press Ctrl+O . Browse to the file (.sqlnb) and then open it. After the notebook is opened, you need to attach it to a connection again. For more information, see Attach a SQL Notebook to a Connection.

The SQL Notebook enables you to perform the following operations using the buttons available in the editor:
  • Code (Ctrl+Enter) adds a new code cell. It enables you to run one or more SQL statements, PL/SQL statements, and scripts. For more information, see Using the Code Cell.
  • Markdown adds a new Markdown cell. It enables you to enter markdown-formatted text. For more information, see Using the Markdown Cell.
  • Run All runs all the cells in the SQL Notebook sequentially.
  • Clear All Outputs clears all the output.

3.6.1.1 Using the Code Cell

In the code cell, you can enter one or more SQL, PL/SQL statements, or scripts that you intend to run.

You can run multiple queries sequentially in a single code cell, combine DDL and DML operations, and run setup scripts followed by data queries. You can also use bind variables and substitution variables for dynamic query execution. It also displays detailed output of the script execution.

For multiple statements, each SQL statement must be terminated with either a semicolon or (on a new line), and each PL/SQL statement must be terminated with a slash (/) on a new line. You can drag some kinds of objects such as tables or views from the Connections navigator and drop them into the code cell. When you do this, the following options appear: Object Name, Insert, Delete, Update, and Select. If you select SELECT, a SELECT statement is constructed with all columns in the table or view. You can then edit the statement.

To add a new code cell, click Code. Enter the statement that you want to run in the code cell. As you start typing, a context-sensitive pop up window appears to help you auto-complete the code. You can click Esc to close the pop up window.

Click Execute Cell (Ctrl+Alt+Enter).
Description of code_cell.png follows
Description of the illustration code_cell.png

The output is displayed in a grid format:
Description of code_cell_output.png follows
Description of the illustration code_cell_output.png

You can click on column headers to sort the data, drag to resize columns to fit the output, view the full details of a record, and export the output data. The main advantage of SQL Notebooks is that you can run a single code cell, a group of consecutive code cells, or the entire notebook.
Description of code_cell_icons.png follows
Description of the illustration code_cell_icons.png

The following icons are available on a code cell:
  • Execute Above Cells runs all the preceding code cells.
  • Execute Cells and Below runs the current cell and all code cells below it.
  • Split Cell splits the current code cell into two separate cells.
  • Delete Cell deletes the selected code cell.
  • More Actions... provides additional options to perform common tasks. For more information, see Additional Cell Configuration Options.

Viewing Single Record

Right-click on an output row and select Single Record View to view its details.
Description of single_view_record.png follows
Description of the illustration single_view_record.png

The details of the selected row is displayed:
Description of single_view_record_output.png follows
Description of the illustration single_view_record_output.png

You can use the <<, <, >, and >> buttons to navigate to the first, previous, next, and last records, respectively. Clicking Close closes the dialog box.

Exporting Data

Right-click on the grid and select Export to export the output data in multiple formats.
Description of export_data.png follows
Description of the illustration export_data.png

You can choose the desired format for exporting the output by configuring the settings in the Export dialog box. Click Export to export the output.
Description of export_data_dialog.png follows
Description of the illustration export_data_dialog.png

The following configurations are available for exporting the output:
  • Format lists the formats in which the output can be exported. Available options are: CSV, Delimited, Fixed, HTML, Insert, JSON, Text, XML, JSON Formatted, Loader
  • Line Terminator lists the line terminator options for export. Available options are: Unix/Mac LF, Windows CR LF, CR
  • Header displays header in the exported output if this check box is selected.
  • Left Enclosure displays the available left enclosure options, which include: double quote ("), single quote ('), opening parenthesis ((), opening brace ({), and opening bracket ([).
  • Right Enclosure displays the available right enclosure options, which include: double quote ("), single quote ('), closing parenthesis ()), closing brace (}), and closing bracket (]).
  • Export Destination lists the destination for the exported output. Available options are: File, Clipboard
  • File allows you to browse and select the destination file for exporting the output.

3.6.1.2 Using the Markdown Cell

In the Markdown cell, you can insert formatted text, explanations, and documentation in a notebook. This helps improve the clarity and readability of notebooks. For example, you can use Markdown to group queries, add comments, and provide descriptions of the code.

To add a new Markdown cell, click Markdown. Enter the markdown-formatted text and click Stop Editing Cell (Ctrl+Alt+Enter).
Description of mardown_cell_input.png follows
Description of the illustration mardown_cell_input.png

The output is displayed as:
Description of mardown_cell_output.png follows
Description of the illustration mardown_cell_output.png

The following icons are available on a Markdown cell:
  • Run Cells in Section executes all code cells grouped under a specific Markdown section at once. Markdown headers (for example, # Header1, ## Header2) create sections, and the code cells between them are treated as part of that section.
  • Stop Editing Cell (Ctrl+Alt+Enter) stops editing the Markdown cell and renders the formatted output.
  • Split Cell (Ctrl+K Ctrl+Shift+\) splits the current cell into two separate Markdown cells.
  • Delete Cell (Delete) deletes the selected cell.
  • More Actions... provides additional options to perform common tasks. For more information, see Additional Cell Configuration Options.

3.6.1.3 Additional Cell Configuration Options

This section outlines additional actions you can perform on a cell.

Actions for Code and Markdown Cells


Description of cell_more_actions.png follows
Description of the illustration cell_more_actions.png

  • Cut Cell deletes the selected cell and stores its contents for pasting.
  • Copy Cell copies the selected cell.
  • Paste Cell pastes the previously cut or copied cell.
  • Insert Cell inserts a new code or Markdown cell above or below the selected cell.
  • Change Cell to Markdown changes the selected code cell to a Markdown cell. This option is available only for code cells.
  • Join With Previous Cell (Shift+Alt+Windows+j) joins the selected cell with the preceding cell.
  • Join With Next Cell (Alt+Windows+j) joins the selected cell with the next cell.
  • Join Selected Cells joins the selected cells.
  • Copy Cell Down (Shift+Alt+Down Arrow) duplicates the selected cell directly below.
  • Show Cell Line Numbers shows or hides the line numbers in the cell.
  • Toggle Cell Toolbar Position moves the toolbar to either the upper-left or upper-right corner of the cell.