All Products
Search
Document Center

SQL window

Last Updated: Jul 01, 2022

Overview

On the Database Management page of OceanBase Developer Center (ODC), click Workspace in the top navigation bar and click SQL Window in the drop-down list that appears.

The SQL window provides an SQL editing area for editing scripts, an execution record tab, and a result tab for displaying the execution results. The SQL window also supports executing PL statements.

SQL window

SQL editing area

The SQL editing area provides many features for you.

Feature

Description

Auto-complete

The SQL editing area provides the auto-complete feature to improve your SQL statement editing efficiency.

For example, when you edit SQL statements, the table name is auto-completed for cross-database queries.

Intelligent identification of keywords and code

Database keywords are highlighted in different colors, and PL/SQL statements are identified.

Right-click of object names in the SELECT statement

When you right-click the name of a table, view, or function in a SELECT statement, some common operations are displayed. When you place the pointer over the name of a table, the field information is displayed.

Object drag-and-drop

You can directly drag an object from the object list and drop it into an SQL statement in the SQL editing area to fill in the object name in the statement.

  • When you drag a table or view object and drop it into the SQL editing area, you can select an SQL statement to be generated in the Fast Generation dialog box. To set the default type of statements generated when you drag and drop table or view objects, choose Personal Settings > Type of Statements Generated by Dragging and Dropping Object.

  • For objects other than tables and views, you can directly drag and drop them into the SQL/PL window.

    SQL window - Drag and drop table objects to quickly generate different types

Independent sessions

You can click + to add multiple SQL windows in one connection.

Identification of abnormal special symbols

Abnormal special symbols can be identified and marked with yellow wavy underlines in the editing area of the SQL window.

Note

  • In ODC, multiple identical connections share one single session. The auto-commit feature of ODC in Oracle mode is enabled by default. If you want to manually commit transactions, you can choose Settings > Session Variable in the SQL window or go to the Session Properties page to modify the value of the autocommit variable. For more information, see Session management.

  • In manual-commit mode, make sure to set ob_trx_idle_timeout to a value greater than the timeout value of SQL queries. Otherwise, if two SQL statements in one transaction are executed at an interval longer than the value of ob_trx_idle_timeout, the connection will be terminated. However, if the ob_trx_idle_timeout parameter is set to an excessively large value, the session cannot be released in time, resulting in unnecessary memory consumption. Therefore, you must set this parameter to a proper value.

In addition to the preceding features, the toolbar of the editing area provides buttons that implement the following features.

Feature

Description

Run

Click this button to execute all the SQL statements in the current window. This button is disabled if an SQL statement is being executed in another window of the same session.

Run the current statement

Click this button to execute all the selected SQL statements or the SQL statement in the line where the pointer is located. This button is disabled if an SQL statement is being executed in another window of the same session.

Commit

  • The autocommit property is set to OFF by default to disable auto-commit in Oracle mode, so you need to click this button to commit the current transaction. After you click this button, a dialog box appears, indicating that the current connection uses a shared session and the commit will apply to all windows. Click Yes to commit the transaction.

  • In MySQL mode, the autocommit property is set to ON by default to enable auto-commit, so this button is not displayed. This button is disabled if an SQL statement is being executed in another window of the same session.

    Note

    You can choose Settings > Session Variable in the SQL window or Tool > Session Properties in the top navigation bar to modify the autocommit property.

Roll back

  • The autocommit property is set to OFF by default to disable auto-commit in Oracle mode, so you need to click this button to roll back the current transaction. After you click this button, a dialog box appears, indicating that the current connection uses a shared session and the rollback will apply to all windows. Click Yes to roll back the transaction.

  • In MySQL mode, the autocommit property is set to ON by default to enable auto-commit, so this button is not displayed. This button is disabled if an SQL statement is being executed in another window of the same session.

Abort

Click this button to abort the statement that is being executed.

Format

Click this button to apply formatting, such as indentation, line break, and keyword highlighting, to the selected SQL statements or all the SQL statements in the current SQL window.

Find and replace

You can enter text in the search field to find the specific content and enter text in the replacement field to replace the content found.

Undo

Click this button to undo the last operation.

Redo

Click this button to reverse an Undo operation.

Case sensitivity

The system supports three configurations: All Uppercase, All Lowercase, and Capitalize First Letter. Click the corresponding button to convert the selected statements in the script to the corresponding capitalization format.

Indent

You can add indents to or delete indents from the selected statements in the script.

Comment

You can click Add Comments to convert the statements that you select into comments or click Delete Comment to convert comments to SQL statements.

IN value conversion

Click this button to convert the copied rows or columns into the specified format during queries.
After you paste the copied data to the SQL editing area, select the copied data and click IN Value Conversion to convert it into the in('A','B') format.
  • Column values are separated with line breaks.

  • Row values are separated with spaces or tabs.

Settings

  • Delimiter: Select the sign that you want to use as the delimiter from the drop-down list. Five types of delimiters are supported: semi-colons (;), slashes (/), double slashes (//), dollar signs ($), and double dollar signs ($$).

  • Query Result Limit: allows you to set the maximum number of lines that a query can return. Default value: 1000.

  • Session Variable: allows you to edit, refresh, and search for variables.

Save

Click this button to save the script in the current window. You can open a saved script when you enter the workspace again. You can view only the SQL scripts saved by yourself. The script names must be unique. After you open a saved script, you can continue to edit it.

Execution plan

Click this button to view the execution plan for the SQL statement that you select or on which the pointer is located. This execution plan is estimated by the system before execution and is the result of the EXPLAINPLAN operation. Therefore, the execution data displayed may differ from the actual execution data. You can use this feature to assess SQL statements.

Click Execution Plan. On the Plan Details tab, click View Formatting Info to go to the format page.

Snippets

Click this button to view and reference built-in and custom code snippets. For more information, see Snippets.

Execute a PL statement

You can edit a PL statement in the SQL window. Note that you must end the PL statement with the specified delimiter.

At present, you can use the following methods to specify a delimiter in ODC:

  • Click Settings in the toolbar of the SQL editing area. In the window that appears, select the required delimiter in the Delimiter field.

  • On the Personal Settings page, select a required delimiter in Delimiter Settings.

    Notice

    • In ODC V2.4.1 and later versions, you can use the DELIMITER statement in the editing area to define a delimiter. In Settings, the Delimiter field displays the delimiter that you specified.

    • You can directly use forward slashes (/) to separate PL statements. However, if a PL statement contains any forward slashes, you still need to set a custom delimiter.

After you execute the PL statement in the SQL window, the result tab displays the database management system (DBMS) output.

Execute a PL statement

Record tab

User Guide  / / /> SQL window > Execution Record tab

On the Record tab, you can view the history of SQL statements executed in the current connection. It includes Status, Time, SQL Statement, Results, TRACE ID, Time Spent, and Execution Details. Execution Details is the execution plan.

Column

Description

Check box

You can select the check box Check box in front of the record list and click Delete in the upper-right corner of the list to delete multiple execution records at a time.

TRACE ID

The ID of the execution record.
Notice

If TRACE ID is empty, make sure that the system parameter enable_sql_audit and the variable ob_enable_trace_log are both set to ON when the statement is executed. You can take the following steps to turn on the variables:

  1. Choose Session > Session Properties > Session Variable and search for enable_sql_audit and ob_enable_trace_log separately.

  2. Click the edit icon on the Session Variable tab. In the dialog box that appears, change the value OFF to ON and then click OK.

  3. Enable the system parameter:

    obclient> alter system set enable_sql_audit='True';
    Query OK, 0 rows affected (0.06 sec)

Time Spent

Time Spent of an SQL statement in ODC is divided into the following three parts:

  • Network time spent: the time consumed to transmit the SQL statement over the network.

  • ODC time spent: the time consumed to process the SQL statement by ODC.

  • Database time spent: the time consumed to process the SQL statement by the database.

You can move the pointer over the icon next to Time Spent. The time spent on executing an SQL statement is displayed in three sections in the tooltip.

Execution Details

By default, the execution plan is displayed in text.

In the Execution Details column, click View. The Execution Details page displays the basic information, time spent, I/O statistics, and executed SQL statements.

Logs tab

You can view the execution records of the current SQL window on the Logs tab.

Results tab

You can view the execution result of the current SQL statement on the Results tab. The result set on the Results tab can be used in the following interactive operations to facilitate daily development work.

Operation

Description

Select required data

You can select the required data of a result set and copy the data to an external file by using hotkeys. You can also click a field name or row number to select an entire row or column.

You can also click Export in the toolbar to export data of a result set to a local file.

Select rows and columns

  • You can select consecutive rows or columns while holding the Shift key.

  • You can select non-consecutive rows or columns while holding the Ctrl or Command key.

Right-click a cell

You can right-click a cell and choose Copy Row, Copy, or Export to Clipboard to directly export the data into an external file.

Right-click a row ID

You can right-click a row ID and choose Copy Row, Freeze this row, or Unlock all frozen rows.

  • Copy Row: copies the selected row.

  • Freeze this row: freezes the selected row and pins it to the top. You can freeze multiple rows. The rows under the table header and frozen rows can be vertically scrolled.

  • Unlock all frozen rows: unfreezes all frozen rows.

Zoom in

If the data in a cell is too long to be displayed in full, you can perform the following operations to view the data in full:

  1. Place the pointer over the cell.

  2. Click the zoom-in button Zoom-in button that appears to the right of the cell. In the LOB dialog box that appears, you can view all the data in the cell.

    Note

    LOBs are displayed in text by default. You can convert a LOB into the hexadecimal format and download the LOB to a local device.

View BLOBs

The SQL window supports BLOB, CLOB, and RAW data types.

Right-click the tab name of a result tab

For example, you can right-click the tab name of the Results 1 tab and click Pin in the context menu to pin the tab, so that it remains displayed. In this way, when you execute a new query, a new result tab appears but does not overwrite the pinned result tab. This allows you to compare the query results.

Click Unpin to unpin a result tab.

Filter, sort, and search for data

Each field name in the result set is provided with a filter icon, a sort icon, and a search icon. You can use them to filter, sort, and search for data in a single column.

Drag the column name

You can adjust the order of fields by dragging column names in a result set.

Results tab

The toolbar in the navigation bar of the Results tab also provides buttons with the following features.

Feature

Description

Column mode

Click this button to display the selected data row in the form of a table. On the Column Mode page, you can switch to the previous or next row. Column mode makes it easier to view data in a row that has many columns. To view data in column mode, perform the following steps:

  1. Select the target data and click the column mode button Column mode button.

  2. On the Column Mode page, click the left and right arrow buttons to view the values of rows and columns.

  3. Move the pointer over a value field and click the zoom-in button Zoom-in button to view the details.

  4. In the list of the Column Mode page, you can view the comments for a column in the Remarks column. To modify the comments, perform the following steps:

    1. In the left-side navigation pane, right-click the target table and select View Table Structure to go to the table attributes tab.

    2. Click the Column tab in the left-side navigation pane of the table attributes tab.

    3. Select the field name, and click the edit icon. The Edit Field dialog box appears.

    4. In the Comment field, edit the comments, and click OK.

    5. In the SQL Confirmation dialog box, click Execute.

    6. Then, the modified comments are displayed on the Column Mode page.

Columns

Click this button to select the columns to be displayed on the page.

Back to start

Click this button to go to the first page.

Previous

Click this button to go to the previous page.

Next

Click this button to go to the next page.

Jump to bottom

Click this button to go to the last page.

Edit

Click Edit to enable editing mode for the current result set. The editing mode supports the following operations: Add, Copy Current Row, Delete, Cancel, Confirm Modification, which commits a transaction when auto-commit is enabled, and Modify and Submit, which is displayed when auto-commit is disabled. In the editing mode, you can either double-click target data to directly modify it or click the preceding buttons for convenient operations.

When you edit a cell, you can right-click the cell and choose Copy or Set to Null from the context menu to operate on the cell.

Note

The ResultMetaData is obtained during the execution of an SQL statement to indicate whether the result set can be edited.
  • If the result set cannot be edited, the Edit button is hidden and a prompt is displayed indicating that the result set cannot be edited.

  • If the result set can be edited, the Edit button is available and you can click it to go to the result set editing page. You can edit result sets in single-table and single-view queries.

  • However, result sets that involve the SET and ENUM fields cannot be edited.

Export

You can export query results to files in the CSV or SQL format.

  • To export an SQL file, specify the File Name, File Format, Encoding Standard, and Row Number Limit of the file. The file encoding format can be GBK or UTF8.

  • To export a CSV file, specify the File Name, File Format, Encoding Standard, Row Number Limit, and CSV Settings of the file. The file encoding format can be GBK or UTF8.

  • To export an EXCEL file, specify the file encoding format, which can be GBK, UTF8, UTF16, GB2312, or BIG5.

Plan

Click this button to view the actual resource consumption and execution plan of an executed SQL statement. This allows you to evaluate the performance of the statement.