1. Using Guide for Oracle Cloud
  2. Development
  3. The SQL and Data Modeler Pages
  4. The SQL Page

The SQL Page

The SQL page enables you to enter and execute SQL and PL/SQL statements, and create database objects.

To navigate to the SQL page, do either of the following:

  • In the Launchpad page, select the Development tab and click SQL.

  • Click Selectorselector iconto display the navigation menu. Under Development, select SQL.

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.

The SQL 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:

Object Navigator and Files

The Navigator tab in the left pane displays saved objects for the selected schema. The Files tab enables you to view and open files saved in your browser or local system.

The following figure shows the various elements in the left pane.

Figure 2-1 Left Pane in SQL

Description of worksheet_left_pane.jpg follows
Description of the illustration worksheet_left_pane.jpg

Navigator Tab

Displays saved objects for the selected schema.

  • Schema and Object Type selector: Use the drop-down lists to select the schema and filter the results by object type.

  • Search: Searches 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 wild card characters.

  • Context menu: Options in the context menu are:

    • Open to browse properties and data relevant to the object type (tables and views).

      The Data pane displays the data for a table, view, or materialized view.

      To edit an entry, double-click a cell to make edits. You can also click view value_icon and enter the value. When you make an edit, the border of the gutter cell in that row changes to blue.

      The icons available in the Data pane are:

      • Create Row: Insert a new row into the database table. When you insert a row using the Create Row icon, the row is committed into the database.

      • Delete Selected: Mark the selected row for deletion. When you mark a row for deletion, the border of the row changes to red.

      • Commit: Commit all changes made to the database.

      • Undo All: Revert all changes that are marked for commit.

      Use the Filter icon at the top right corner to filter the column data. You can also right-click a cell to access the context menu to count rows, view a single record, export or copy the cell text to clipboard.

      To view a Binary Large Object data type (BLOB), click the pencil icon for a BLOB data type. In the View Value dialog:
      • The Image tab displays the loaded image, if the loaded BLOB type is an image.
      • The Text tab displays the text file, if the loaded BLOB type is text .
      • The Information tab displays the details and allows you to perform the following actions:
        • Download: To download the image/text file of BLOB data type .
        • Load: To insert an image/text of BLOB data type.
        • Set NULL: To delete the object and set the value as NULL.
      Description of blob-edit-options.png follows
      Description of the illustration blob-edit-options.png

    • Edit edits the properties of an existing object.

    • Add creates an object based on the object type selected.

    • Use as Template creates an object by using the properties of an existing object as the template.

    • REST

    • Data Loading loads data from local files into a table.

    • Quick DDL generates Data Definition Language statements for the object.

  • Refresh Refresh icon: Refreshes the objects or worksheets listed in the left pane.

  • Object Submenu Object submenu icon: Opens the Create Object dialog to create a new object based on the type selected in the drop-down list.

  • Help Help icon: Provides contextual help documentation.

Files Tab

Enables you to open files from your browser or local device.

Note:

The DEVICE category in the left pane is displayed only when using a Chromium-based browser in a secure context (HTTPS).
Description of file_navigator.png follows
Description of the illustration file_navigator.png

All files: Use the drop-down list to filter files by browser or device.

The context menu options for a file are Open and Delete. In Device, the corresponding option for deleting a file is Forget. In this case, the file is not deleted, instead the reference to the file is removed.

Drag and Drop Objects and Files into the Worksheet

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.

    Figure 2-2 Insert Select Query

    Description of Figure 2-2 follows
    Description of "Figure 2-2 Insert Select Query"

    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.

Executing SQL Statements in the Code Editor

The code editor in the SQL page enables you to run SQL statements, PL/SQL scripts, and JavaScript code. The main features include in-context code completion, syntax highlighting, and error debugging.

You can enter SQL and PL/SQL statements 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

The PL/SQL editor is triggered 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 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 syntax in Oracle Database release 21c. For more details, see Support for Multilingual Engine.

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

Use the Consumer Group drop-down list to select the consumer group to run your SQL or PL/SQL code. The values in the drop-down list match the database services available when connecting to the database. This feature is available only if you have the EXECUTE privilege on the CS_SESSION package.

Description of consumer_group_selector.png follows
Description of the illustration consumer_group_selector.png

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.

Description of autocomplete_code.png follows
Description of the illustration autocomplete_code.png

The editor 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.

Description of error_sql_worksheet.png follows
Description of the illustration error_sql_worksheet.png

You can set code editor preferences using the Preferences option available in the top-right user drop-down list. 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.

The SQL toolbar contains icons for the following operations:

  • Worksheet drop-down list

    • Open: Opens a file from the browser or device.
    • Open Recent: Displays 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: Saves a file to the browser or device.
  • New File
    • Select Worksheet to create a worksheet.
    • Select PL/SQL to create a PL/SQL object type. The editor switches to a PL/SQL mode.
    • Select JavaScript to create a JavaScript file. The editor switches to a JavaScript mode and (JS) is added to the file name.
  • Open opens a file from your browser or device. 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

    • 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 Statement Without Pagination executes the selected statements without wrapping it inside a ROW_NUMBER()OVER analytic function to implement paging. This means the query is run 'as is' in the worksheet, and only fetches the first 256 rows.

  • Run Script

    • Run as SQL 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.

    • Run as JavaScript executes the code as a JavaScript file. This option is used only with JavaScript code. If the JavaScript code is added to a PL/SQL block, then select "Run as SQL Script" to execute the script.

  • Compile (for PL/SQL toolbar) performs a PL/SQL compilation of the subprogram.
  • Create Chart creates a chart for the corresponding SQL statement entered in the editor. In contrast, you can partially highlight a subquery and create a chart. A slider window is displayed for entering the chart parameters. For a description of the fields, see Creating or Editing a Chart.

    If the SQL statement is syntactically incorrect or incomplete, an error/warning notification is displayed.

  • 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. See Viewing the SQL Output

  • Autotrace runs the statement and collects run time 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.

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

About Session State in Database Actions

In Database Actions, a connection to the database is stateless.

In a stateless environment, each HTTPS request from a client maps to a new database session. Therefore, a session begins and ends with every SQL statement or script execution.

As the session state is not maintained, session attributes do not persist and commands such as ROLLBACK and COMMIT do not apply. If a SQL statement or script executes successfully, an implicit commit is performed. If it executes with an error, an implicit rollback is performed.

Therefore, when needed, include the ROLLBACK and COMMIT commands or session attributes in the PL/SQL code block that is sent to the database for a session.

The only configuration commands that persist during a session in Database Actions are:

  • SET DEF[INE] <ON|OFF|prefix_character>
  • SET ESC[APE] <ON|OFF|escape_character>
  • SET TIMI[NG] <ON|OFF>

Keyboard Shortcuts

This section lists the keyboard shortcuts for various commands in the SQL page.

Table 2-1 Keyboard Shortcuts

Windows MacOS Description

Ctrl + Enter

Cmd + Enter

Runs the code as query.

Alt + Tab

Option + Tab

Focus next element.

Ctrl+ Esc / Escape

Cmd + Esc / Escape

Remove focus from editor.

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 + 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 Fn + F1 Opens the help topic.

Shift + Esc

Shift + Esc

Focus previous element.

F5

Fn + F5

Runs code as script.

F6

Fn + F6

Shows Autotrace.

F10

Fn + F10

Shows Explain Plan.

F11

Fn + F11

Creates a chart.

Ctrl + B

Cmd + B

Opens the "Convert Case" drop-down list.

Ctrl + F7

Cmd + Fn + F7

Formats code in the editor.

Ctrl + Space

Ctrl + Space

Autocompletes code (shows hints).

The following table lists the keyboard shortcuts for commands in the Command Palette.

Table 2-2 Command Palette Keyboard Shortcuts

Windows MacOS Description
Ctrl + Alt + Up Cmd + Option + Up Add Cursor Above
Ctrl + Alt + Down Cmd + Option + Down Add Cursor Below
Shift + Alt + I Shift + Option + I Add Cursors to Line Ends
Ctrl + K Ctrl + C Cmd + K Cmd + C Add Line Comment
Ctrl + D Cmd + D Add Selection To Next Find Match
Shift + Alt + Down Shift + Option + Down Copy Line Down
Shift + Alt + Up Shift + Option + Up Copy Line Up
Ctrl + U Cmd + U Cursor Undo
- Cmd + Backspace Delete All Left
- Ctrl + K Delete All Right
Ctrl + Shift + K Shift + Cmd + K Delete Line
Shift + Alt + Right Shift + Ctrl + Cmd + Right Expand Selection
Ctrl + F Cmd + F Find
Enter Enter Find Next
Ctrl + F3 Cmd + Fn + F3 Find Next Selection
Shift + Enter Shift + Enter Find Previous
Ctrl + Shift + F3 Shift + Cmd + Fn + F3 Find Previous Selection
- Cmd + E Find With Selection
Ctrl + Shift + [ Option + Cmd + [ Fold
Ctrl + K Ctrl + 0 Cmd + K Cmd + 0 Fold All
Ctrl + K Ctrl + / Cmd + K Cmd + / Fold All Block Comments
Ctrl + K Ctrl + 8 Cmd + K Cmd + 8 Fold All Regions
Ctrl + K Ctrl + 1 Cmd + K Cmd + 1 Fold Level 1
Ctrl + K Ctrl + 2 Cmd + K Cmd + 2 Fold Level 2
Ctrl + K Ctrl + 3 Cmd + K Cmd + 3 Fold Level 3
Ctrl + K Ctrl + 4 Cmd + K Cmd + 4 Fold Level 4
Ctrl + K Ctrl + 5 Cmd + K Cmd + 5 Fold Level 5
Ctrl + K Ctrl + 6 Cmd + K Cmd + 6 Fold Level 6
Ctrl + K Ctrl + 7 Cmd + K Cmd + 7 Fold Level 7
Ctrl + K Ctrl + [ Cmd + K Cmd + [ Fold Recursively
Ctrl + Shift + \ Shift + Cmd + \ Go to Bracket
Ctrl + G Ctrl + G Go to Line...
Alt + F8 Option + Fn + F8 Go to Next Problem(Error, Warning, Info)
F8 Fn + F8 Go to Next Problem in Files (Error, Warning, Info)
F7 Fn + F7 Go to Next Symbol Highlight
Shift + Alt + F8 Shift + Option + Fn + F8 Go to Previous Problem (Error, Warning, Info)
Shift + F8 Shift + Fn + F8 Go to Previous Problem in Files (Error, Warning, Info)
Shift + F7 Shift + Fn + F7 Go to Previous Symbol Highlight
Ctrl + ] Cmd + ] Indent Line
Ctrl + Shift + Enter Shift + Cmd + Enter Insert Line Above
- Ctrl + J Join Lines
Ctrl + K Ctrl + D Cmd + K Cmd + D Move Last Selection To Next Find Match
Alt + Down Option + Down Move Line Down
Alt + Up Option + Up Move Line up

F1 (All browsers)

Ctrl + Shift + P (Google Chrome only)

 Fn + F1 Open Command palette
Ctrl + [ Cmd + [ Outdent Line
Ctrl + K Ctrl + U Cmd + K Cmd + U Remove Line Comment
Ctrl + H Option + Cmd + F Replace
Ctrl + Shift + . Shift + Cmd + . Replace with Next Value
Ctrl + Shift + , Shift + Cmd + , Replace with Previous Value
Ctrl + Shift + L Shift + Cmd + L Select All Occurrences of Find Match
Alt + F1 Option + Fn + F1 Show Accessibility Help
Shift + F10 Shift + Fn + F10 Show Editor Context Menu
Ctrl + K Ctrl + I Cmd + K Cmd + I Show Hover
Shift + Alt + Left Shift + Ctrl + Cmd + Left Shrink Selection
Shift + Alt + A Shift + Option + A Toggle Block Comment
Ctrl + K Ctrl + L Cmd + K Cmd + L Toggle Fold
Ctrl + / Cmd + / Toggle Line Comment
Ctrl + M Shift + Ctrl + M Toggle Tab Key Moves Focus
- Shift + Ctrl + T Transpose Letters
Ctrl + Space Ctrl + Space Trigger Suggest
Ctrl + K Ctrl + X Cmd + K Cmd + X Trim Trailing Whitespace
Ctrl + Shift + ] Option + Cmd + ] Unfold
Ctrl + K Ctrl + J Cmd + K Cmd + J Unfold All
Ctrl + K Ctrl + 9 Cmd + K Cmd + 9 Unfold All regions
Ctrl + K Ctrl + ] Cmd + K Cmd + ] Unfold Recursively

Support for Multilingual Engine

Database Actions provides support for Multilingual Engine (MLE) by enabling you to run JavaScript code in the worksheet.

Prerequisites

For the availability of MLE features in Database Actions, you need the:

  • DBMS_MLE package in Oracle Database Release 21c and later versions. For more information, see DBMS_MLE in Oracle Database PL/SQL Packages and Types Reference.

  • EXECUTE DYNAMIC MLE and EXECUTE ON JAVASCRIPT privileges assigned to you.

You can work with JavaScript code in the worksheet in the following ways:

  • Create a JavaScript worksheet
  • Execute JavaScript code in a standard worksheet
  • Execute JavaScript code as a PL/SQL block

Create a JavaScript Worksheet

You can open the worksheet in JavaScript mode. You will also see the toolbar icons and output tabs change to reflect the JavaScript mode.

To create and save a JavaScript worksheet:

  1. In the toolbar, expand the New File icon and select JavaScript.
  2. When you enter code in the worksheet, the JavaScript code is automatically highlighted.
  3. Execute the JavaScript code in the worksheet using Run Script.

  4. Click Save.

    In the Files pane, (JS) is added to the JavaScript file name. This enables you to quickly identify the JavaScript file.

Description of jscript_worksheet.png follows
Description of the illustration jscript_worksheet.png

Execute JavaScript code in a standard worksheet

In a standard worksheet (when the worksheet is not in JavaScript mode):

  1. Select the JavaScript code to execute.
  2. In the worksheet toolbar, expand the Run Script icon and select Run as JavaScript.

    Note:

    Selecting Run as SQL script results in an error.
Description of jscript_execute.png follows
Description of the illustration jscript_execute.png

Execute JavaScript code as a PL/SQL block

You can use a PL/SQL code block to execute JavaScript code.

From the worksheet toolbar, expand the Run Script icon and select Run as SQL script (F5).

Description of javascript_plsql_execute.png follows
Description of the illustration javascript_plsql_execute.png

Viewing the SQL Output

The lower right pane in SQL displays the output of the operation executed in the SQL editor.

The following figure shows the output pane in the SQL page.

Figure 2-3 Output Pane

Description of Figure 2-3 follows
Description of "Figure 2-3 Output Pane"

The output pane has the following tabs:

  • 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 the 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. The default view is the diagram view. For more information, see Using the Explain Plan Diagram.

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

    Note:

    The executed statements are saved in the database, and are therefore available across all devices. For a read-only database, the SQL statements are saved on your browser.

  • Data Loading: Displays a report of the total rows loaded and failed for all visible tables (including tables from other schemas).

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.

Figure 2-4 Context Menu for Row Header

Description of Figure 2-4 follows
Description of "Figure 2-4 Context Menu for Row Header"

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.

    • Format: Select the format to export from the drop-down list.

    • Line Terminator: Identifies the terminator for each line. The line terminator is not included in the data exported. If the preview page shows the data in one single row, the correct terminator is not specified.

    • Header: Controls whether the first row is a header row or the first row of data.

    • Left and Right Enclosure: Enclosures are used for character data and are optional. Enclosures are not included in the data exported.

    Note:

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

  • Copy copies data from a cell or a row or a range of rows.

Figure 2-5 Context Menu

Description of Figure 2-5 follows
Description of "Figure 2-5 Context Menu"

Using the Explain Plan Diagram

The Explain Plain diagram view is a graphical representation of the contents of PLAN_TABLE, which is the default table for results of the EXPLAIN PLAN statement. The hierarchical nature of the steps in the execution plan is depicted in the diagram.

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

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

Description of explain_plan_diagram.jpg follows
Description of the illustration explain_plan_diagram.jpg

The icons in the toolbar are:

  • Advanced View/Diagram View: Switches between Advanced and Diagram views. The Advanced View displays data from PLAN_TABLE in a mixed tabular/tree view. The Diagram View displays the data in a flowchart format.

  • Chart View/Diagram View: Switches between Chart and Diagram views. The Chart view displays data from PLAN_TABLE as a flame graph.

  • Print Diagram: Prints the diagram.

  • Save to SVG: Saves the diagram to file in SVG 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.

  • Open in New Tab: Opens the diagram view in a new tab for better viewing and navigation. The diagram is limited to the initial SELECT statement.

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

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

  • Plan Notes: Displays the Explain Plan notes.

Description of explain_plan_tooltip.jpg follows
Description of the illustration explain_plan_tooltip.jpg

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.

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

    Description of explain_plan_navigator.jpg follows
    Description of the illustration explain_plan_navigator.jpg

    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.

Loading Data

In the SQL page, you can load data from one or more local files into one or more tables.

The file formats that you can load are CSV, XLS, XLSX, TSV, TXT, XML, JSON, NDJSON and AVRO. For XML, JSON, NDJSON and AVRO files, see Format Specifications for JSON, AVRO, and XML Files.

Loading Data from a Local File to a New Table

To load data from a local file to a new table:

  1. You can start in one of the following ways:

    • In the Navigator tab, in the left pane, click Object submenuObject submenu icon, select Data Loading, and then select Upload Data into New Table.

      Figure 2-6 Upload Data to New Table Option

      Description of Figure 2-6 follows
      Description of "Figure 2-6 Upload Data to New Table Option"

    • In the Navigator tab, drag and drop the local file into the left pane. When you drag a file into the pane, the following message is displayed Drop the file here to start.

    The Upload Data into New Table is displayed. A preview of the data is displayed in a grid format.

  2. Click Show/Hide options Show/Hide options icon to display options that you can modify for data preview:

    • Column names: Select Get from file to display column headers in the first row.

    • Encoding: An option to select the encoding type is visible when the loaded file is in plain text format (CSV, TSV, or TXT). The default encoding type is UTF-8.

    • Text enclosure and Field delimiter: These options are visible only when the selected file is in plain text format (CSV, TSV, or TXT). Select or enter the character used in the source file for text enclosure and field delimiter.

    • Rows to skip: Enter or use the up and down arrows to select the number of rows to skip.

    • Preview size: Enter or use the up and down arrows to select the number of rows to preview.

    • Limit rows to upload: If you select this option, you need to specify the rows to load. Use the up and down arrows to select the number of rows to load.

    To remove the options selected and the data preview, click Clear.

    After selecting the required options, click Apply, and then click Next.

  3. In Table Definition, do the following:

    • In the Table Name field, enter a name for the target table.

    • Select the check box at the beginning of a row to add the column to the target table.

    • Select or enter values for column attributes such as Column Name, Column Type, Precision, Scale, Default, Primary Key and Nullable.

    • The Format Mask column appears for date, timestamp and numeric types of data. For date and timestamp types, you must select a value from the drop-down list or type the value in the Format Mask field. For numeric types, the format mask is optional.

      For a date and timestamp column, you need to supply a compatible format mask that describes the data being uploaded. For example, if the date data looks like 12-FEB-2021 12.21.30, you need to supply a date mask of DD-MON-YYYY HH.MI.SS. The format mask is automatically determined based on the data in the file. You need to review the suggested format mask and if needed, modify it by entering the format directly into the target cell.

    Figure 2-7 Table Definition Step in Upload Data into New Table

    Description of Figure 2-7 follows
    Description of "Figure 2-7 Table Definition Step in Upload Data into New Table"

    Click Next.

  4. Review the generated DDL code based on the selections made in the previous screens. The mapping of the source to target columns are also displayed.

    Click Finish. After the data is successfully loaded, the new table is displayed in the Navigator tab.

  5. For a detailed report of the total rows loaded and failed, do one of the following:

    • Right-click the table in the Navigator tab, select Data Loading, and then select History. This displays the report for a specific table.

    • In the Navigator tab, select Object submenu Object submenu, select Data Loading, and then select History. This displays the report for all tables in the schema that is selected in the Navigator tab.

    • In the worksheet output pane, select the Data Loading tab. This displays the report for all visible tables (including tables from other schemas).

    A summary of the data loaded is displayed in the History dialog. If any data failed to load, you can view the number of failed rows in the Failed Rows column. Click the failed rows column to open a dialog showing the failed rows.

    In the History dialog, you can also search for files loaded by schema name, table name, or file name. To remove the loaded files, click Remove all history Remove all history.

    You can also load data from a file to a new table using the steps in Loading Data from Multiple Local Files into Mutiple Tables.

Loading Data from a Local File to an Existing Table

To load data from a local file to an existing table:

  1. In the Navigator tab, in the left pane, right-click the table that you want to load data into, select Data Loading, and then select Upload Data.

    Figure 2-8 Upload Data Option for an Existing Table

    Description of Figure 2-8 follows
    Description of "Figure 2-8 Upload Data Option for an Existing Table"

    The Import data dialog is displayed.

  2. Drag and drop the file from your system into the dialog, or click Select Files to browse for the file and open it.

    A preview of the data is displayed in a grid format.

  3. Click Show/Hide options Show/Hide options icon to display options that you can modify for data preview:
    • Column names: Select Get from file to display column headers in the first row.
    • Encoding: An option to select the encoding type is visible when the loaded file is in plain text format (CSV, TSV, or TXT). The default encoding type is UTF-8.
    • Text enclosure and Field delimiter: These options are visible only when the selected file is in plain text format (CSV, TSV, or TXT). Select or enter the character used in the source file for text enclosure and field delimiter.
    • Rows to skip: Enter or use the up and down arrows to select the number of rows to skip.
    • Rows to load: Enter or use the up and down arrows to select the number of rows to load.
    • Preview size: Enter or use the up and down arrows to select the number of rows to preview.

    To remove the options selected and the data preview, click Clear.

    After selecting the required options, click Apply, and then click Next.

  4. In Data mapping, match the data in the file to the appropriate columns in the target table. By default, the matching is done using column name.

    Figure 2-9 Data Mapping

    Description of Figure 2-9 follows
    Description of "Figure 2-9 Data Mapping"

    To modify, click Show/Hide options Show/Hide options icon. In Match columns by:

    • Select Name to match columns based on the name of the column in the target table.
    • Select Position if you want to match columns based on the position of the column in the target table.
    • Select None to remove the current selections and to select the target column for each source column from the drop-down list.

    Note:

    Based on the data in the file, attempts are made to automatically retrieve the correct format mask of date-based columns. If this is incorrect, you can change the suggested format by entering it directly into the target cell.

    If there are any issues to resolve, you see a notification such as icon on the top right of the dialog.

    Click Next.

  5. A summary of the previous screens is displayed. Click Finish.

    The data will start uploading to the target table. After it is completed, an entry is added to the Log with the status of the operation. To view the Log, click the timestamp notification at the bottom of the page. If the operation is successful, a Data Import Completed notification is displayed.

  6. For a detailed summary of the upload process, right-click the table in the Navigator tab, select Data Loading, and then select History. A summary of the data loaded is displayed in the Data Loading History dialog.

    If any data failed to load, you can view the number of rows in the Failed Rows column. Click the column to open a dialog showing the failed rows.

    In the Data Loading History dialog, you can also search for files loaded by schema name, table name, or file name. To remove the loaded files, click Remove all history Remove all history.

    You can also load data from a file to an existing table using the steps in Loading Data from Multiple Local Files into Mutiple Tables.

Loading Data from Multiple Local Files into Mutiple Tables

To load data into multiple tables from multiple files:
  1. In the SQL page, on the top right, click Data Load.

    The Data Loading slider appears.

  2. Click Add File to browse for one or more files and add them concurrently. Alternatively, you can drag and drop the files into the Data Loading slider.

    The added files are displayed as cards.

  3. To open a file, click the name of the file or click actions icon at the top right corner of the card and select Details.
    In the Details page, a preview of the file is displayed. Click Next. To go to the previous page, click All Files.

    Note:

    A preview of the entire file is shown the first time the file is loaded. Subsequently if any changes are made to the settings, the preview is set at a maximum of 10 rows. For a description of the settings, see step 2 in Loading Data from a Local File to a New Table.
  4. In the Target Details page, the Actions field contains the following two options:
    • Create New Table to create a table. By default, this option is selected and the Table Name field is prefilled.
    • Append to Existing Table to add the file to an existing table. The data loader attempts to match the file name to an existing table name. Expand Table Rows Preview to preview the existing table.

    For a description of the mapping options, see step 3 in Loading Data from a Local File to a New Table and step 4 in Loading Data from a Local File to an Existing Table.

    Click All Files to return to the initial Data Loading page.

  5. To load data into a table, at the top right of the card, click Actions icon and select Run.

    To load data into multiple tables simultaneously, click Run all icon Run All.

    The files that are successfully uploaded are displayed with a green colour icon and a check mark and the status UPLOADED.

    Some other possible statuses are:

    • UPLOADED WITH WARNING in yellow.
    • NOT UPLOADED BECAUSE OF ERRORS in red.
    • UPLOADED WITH ERRORS where you can see the number of failed rows.
    • CONTAINS WARNING(S) indicates that something in the settings is wrong. This file will not be loaded until the warnings are resolved.
    Description of upload_file_status.png follows
    Description of the illustration upload_file_status.png
  6. After a file is loaded, go to the Details slider to see the third step named Results.

    The Results step shows the total number of rows loaded along with the number of failed rows.

    To view all previous files that have been loaded along with their loading statuses, click Open History. You can filter by schema and table name.

Format Specifications for JSON, AVRO, and XML Files

Data has to be stored in a particular format for JSON, AVRO, and XML files to load them successfully into a table.

The format specifications are described in the following sections.

JSON and AVRO Files

For JSON and AVRO files, the conversion for primitive types to table columns is supported only for top-level data. Nested objects are saved as JSON strings such as VARCHAR2 (JSON) or CLOB (JSON).

Note:

JSON check constraints are available only for Oracle Database 12c and later releases.

Consider the following JSON file as an example:

[
  {
    "ItemNumber": 1,
    "Description": "One Magic Christmas",
    "Part": {
      "UnitPrice": 19.95,
      "UPCCode": 13131092899
    },
    "Quantity": 9,
    "Total": 179.55
  },
  {
    "ItemNumber": 2,
    "Description": "Lethal Weapon",
    "Part": {
      "UnitPrice": 17.95,
      "UPCCode": 85391628927
    },
    "Quantity": 5,
    "Total": 89.75
  }
]

The AVRO schema for this file:

{
     "type": "array",
     "items": {
          "type": "record",
          "fields": [
               {
                    "name": "ItemNumber",
                    "type": "int"
               },
               {
                    "name": "Description",
                    "type": "string"
               },
               {
                    "name": "Part",
                    "type": {
                         "type": "record",
                         "fields": [
                              {
                                   "name": "UnitPrice",
                                   "type": "float"
                              },
                              {
                                   "name": "UPCCode",
                                   "type": "float"
                              }
                         ]
                    }
               },
               {
                    "name": "Quantity",
                    "type": "int"
               },
               {
                    "name": "Total",
                    "type": "float"
               }
          ]
     }
}

Load the JSON file using "Upload Data" in the SQL page, and it is converted to the following table with two rows. part is a nested object that is assigned the column type CLOB (JSON) during data mapping.

Description of json_data_loader.png follows
Description of the illustration json_data_loader.png
XML Files

This section lists the specifications for loading XML files.

  • Attributes will have their own columns

    If the XML data is structured as:

    <?xml version="1.0"?>
<catalog>
  <book id="bk102">
    <author>Ralls, Kim</author>
    <title>Midnight Rain</title>
    <genre>Fantasy</genre>
    <publisher>John Doe</publisher>
   </book>
</catalog>

    The generated columns in the table are id, author, title, genre, and publisher.

  • Two or more levels of nesting are needed to parse the data

    In the following example, the data that needs to be parsed will not be located because it has only one level of nesting (catalog).

    <?xml version="1.0"?>
<catalog>
  <author>Ralls, Kim</author>
  <title>Midnight Rain</title>
  <genre>Fantasy</genre>
  <publisher>John Doe</publisher>
</catalog>

    However, the following examples will work:

    <?xml version="1.0"?>
<catalog>
  <book id="bk102">
    <author>Ralls, Kim</author>
    <title>Midnight Rain</title>
    <genre>Fantasy</genre>
    <publisher>John Doe</publisher>
  </book>
</catalog>

    or

    <?xml version="1.0"?>
<catalog>
  <bookstore>
    <book id="bk102">
      <author>Ralls, Kim</author>
      <title>Midnight Rain</title>
      <genre>Fantasy</genre>
      <publisher>John Doe</publisher>
    </book>
  </bookstore>
</catalog>

    or

    <?xml version="1.0"?>
<catalog>
  <bookstore>
    <shelf>
      <book id="bk102">
        <author>Ralls, Kim</author>
        <title>Midnight Rain</title>
        <genre>Fantasy</genre>
        <publisher>John Doe</publisher>
      </book>
    </shelf>
  </bookstore>
</catalog>

  • Special characters such as hyphen (-) and period (.) in tag names are replaced by underscore (_) in the column name

    XML tag names can contain hyphens and periods. Since the parser is converting XML to JSON, these characters become invalid object keys.

    <?xml version="1.0"?>
<catalog>
  <book id="bk102">
    <author-name>Ralls, Kim</author-name>
    <title.1>Midnight Rain</title.1>
    <genre>Fantasy</genre>
    <publisher>John Doe</publisher>
   </book>
</catalog>

    The generated columns are id, author_name, title_1, genre, and publisher.

  • First-level only-text tags are ignored

    <?xml version="1.0"?>
<catalog>
  <library> New Age Library </library>
  <book id="bk102">
    <author>Ralls, Kim</author>
    <title>Midnight Rain</title>
    <genre>Fantasy</genre>
    <publisher>John Doe</publisher>
  </book>
</catalog>

    The <library> tag is ignored and only the content of the <book> tag is taken into account. The generated columns are id, author, title, genre, and publisher.

  • First-level repetitive data is interpreted as an array of values

    <?xml version="1.0" encoding="UTF-8"?>
<items id="orders">
    <item_number>1</item_number>
    <description>One Magic Christmas</description>
    <part>
      <unit_price>19.95</unit_price>
      <upccode>13131092899</upccode>
    </part>
    <quantity>9</quantity>
    <total>179.55</total>
    <item_number>2</item_number>
    <description>Lethal Weapon</description>
    <part>
      <unit_price>17.95</unit_price>
      <upccode>85391628927</upccode>
    </part>
    <quantity>5</quantity>
    <total>89.75</total>
</items>

    The generated columns include item_number, description, part, and each column will have only one row with the following values respectively ([1,2], ["One Magic Christmas","Lethal Weapon"], [{" unit_price":19.95,"upccode":13131092899},{"unit_price":17.95,"upccode":85391628927}] and so on for the remaining columns.

  • Tags containing values and attributes are converted to object

    <?xml version="1.0"?>
<catalog>
  <book id="bk102">
    <author country="ca">Ralls, Kim</author>
    <title>Midnight Rain</title>
    <genre>Fantasy</genre>
    <publisher>John Doe</publisher>
  </book>
</catalog>

    The <author> tag is converted to a column and will have an object as value that is structured as: 

    {
      "_":"Ralls, Kim",
      "country":"ca"
    }

    Note that the value of the tag has underscore ("_") as key and the attributes as "attribute_name": "attribute_value".