SQL window|V4.1.3|OceanBase Developer Center| docs|Distributed Database

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/SQL statements.

SQL editing area

The SQL editing area provides many features for you.

Operation	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 objects from the object list into an SQL statement in the SQL editing area to fill in an object name in the statement. - After you drag a table or view and drop it into the SQL editing area, you can select the type of the SQL statement to be generated in the Fast Generation dialog box. You can click the username and select Personal Settings from the drop-down list, and set the default statement type in the Type of Statements Generated by Dragging and Dropping Object field of the Edit Personal Settings panel. - If you drag an object other than a table or view, the object name is directly dropped into the SQL editing area.
Shared and independent sessions	The SQL window of ODC supports Shared Session and Independent Session modes. - If you select Shared Session, you can open multiple SQL windows in the same connection, but cannot execute SQL statements in multiple windows in parallel. - If you select Independent Session, you can open multiple SQL windows in the same connection, and execute SQL statements in multiple windows in parallel. To open a new SQL window, click + next to the existing SQL window tab. By default, SQL windows are in Shared Session mode. You can click the username in the upper-right corner of the top navigation bar, select Personal Settings from the drop-down list, and set the session mode to Independent Session on the Edit Personal Settings panel.
Identification of special symbols	Special symbol abnormality can be identified in the editing area of the SQL window, where abnormal symbols are marked with yellow wavy underlines.
Viewing the execution status	When you hover the pointer over an SQL window tab, the window name and execution status are displayed.

Note
In Oracle mode, the autocommit feature of ODC is set to OFF by default. You can change the transaction commit mode by modifying the value of the autocommit variable in the following two ways: click the Settings icon in the upper-right corner of the SQL window and modify the variable in the Session Variables dialog box, or click Session in the top navigation bar and modify the variable on the Session Properties page. Note that the settings in the Set session variable dialog box are valid only for the current SQL window. For more information, see Manage sessions.
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.

4editing area-SQL Check-EN

In addition to the preceding features, the toolbar of the editing area provides the following icons.

Icon	Description
Run	Click this icon to execute all the SQL statements in the current window. This icon is disabled if an SQL statement is being executed in another window of the same session.
Run Current Statement	Click this icon to execute all the selected SQL statements or the SQL statement in the line where the pointer is located. This icon is disabled if an SQL statement is being executed in another window of the same session.
Abort	Click this icon to abort the statement that is being executed.
SQL Check	Click this icon to check SQL syntax.
Execution Plan	Click this icon 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 evaluate SQL statements. Click Execution Plan. On the Plan Details tab, click View Formatting Info to switch to the formatted view.
Find and Replace	Click this icon and 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 icon to undo the last operation.
Redo	Click this icon to reverse an Undo operation.
Format	Click this icon 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.
IN Value Conversion	Click this icon 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.
Case Sensitivity	The system supports three configurations: All Caps, All Lowercase, and Capitalize First Letter. Click this icon to convert the selected statements in the script to the corresponding capitalization format.
Indent	You can add indents to or delete indents from the statements that you selected.
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.
Save script	Click this button/use the shortcut key Ctrl+S to save the script in the current window. Click this icon 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 stored script, you can continue to edit it.
Settings	Delimiter: Select the character 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: Set the maximum number of lines that a query can return. Default value: 1000. Obtain Column Information of Result Set: Before running SQL statements, you can choose whether to enable obtaining column information of result set. If this option is turned off when the number of columns in the table is large, the column comments and editable column information will not be queried to reduce the execution time of SQL statements. Session Variables: You can edit, refresh, and search for variables.
Snippet	Click this icon to view and reference built-in and custom code snippets. For more information, see Snippet.
Commit	- By default, `autocommit` is set to `OFF` in Oracle mode. You need to click this icon to commit the current transaction. After you click this icon, 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, `autocommit` is set to `ON` by default, and this icon is not displayed. This icon is disabled if an SQL statement is being executed in another window of the same session. Note You can change the value of the `autocommit` variable in the following two ways: click the Settings icon in the upper-right corner of the SQL window and set the variable in the Session Variables dialog box, or click Sessions in the top navigation bar and set the variable on the Session Properties page.
Rollback	- In Oracle mode, if the `autocommit` variable in the Session Variables dialog box is set to `OFF`, click this icon to roll back the current transaction. After you click this icon, a dialog box appears, indicating that the current connection uses a shared session and the rollback will apply to all windows. Click Yes to apply the rollback. - In MySQL mode, the `autocommit` variable in the Session Variables dialog box is set to `ON`, and this icon is not displayed. This icon is disabled if an SQL statement is being executed in another window of the same session.

Execute PL/SQL statements

You can edit a PL/SQL statement in the SQL window. Note that you must end the PL/SQL 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 delimiter as needed in the Delimiter field.
On the Personal Settings page, select a required delimiter in Delimiter Settings.
The compilation error information displayed includes the PL/SQL row ID and the position at which the error occurred.
Right-click the PL object in the left navigation bar and select Run, which supports setting the parameter value as DEFAULT, NULL or empty string.

Notice

In ODC V2.4.1 and later, 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 use slashes (/) to separate PL/SQL statements.

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

Execute PL/SQL statements5

Execution Records tab

Execution Records tab6

On the Execution Records tab, you can view the history of SQL statements executed in the current connection. You can view the information in several columns, such as Status, Time, SQL Statement, Results, Trace ID, Time Spent, and Execution Details. The execution details constitute the execution plan.

Column	Description
Check box	You can select the check box in front of one or more records and then click Delete in the upper-right corner of the list to delete the selected records.
TRACE ID	The ID of the execution record. Notice If TRACE ID is empty, make sure that the `enable_sql_audit` and `ob_enable_show_trace` parameters are set to `ON` when the statement is executed. Procedure: 1. Choose Sessions > Session Properties > Session Variables and search for the `enable_sql_audit` and `ob_enable_show_trace` parameters. 2. Click the Edit icon on the Session Variables tab. In the Edit Variable dialog box, change OFF to ON and click OK. 3. Execute the following statement to enable the system parameter: `sql obclient> alter system set enable_sql_audit='True'; Query OK, 0 rows affected (0.06 sec)`
DB Time	You can hover the pointer over the icon next to a value in the DB Time column. In the prompt box that appears, you can view the total execution duration of the SQL statement and verify whether the duration of each phase adds up to the total execution duration.
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, executed SQL statements, plan statistics, and outlines.

Logs tab

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

Log tab

Result tab

You can view the execution result of the current SQL statement on the Result tab. The result set in the Result 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 ID 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 inconsecutive rows or columns while holding the Ctrl or Command key.
Right-click a cell	You can right-click a cell and select 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 select Copy Row, Freeze this row, Unlock all frozen rows, or Export to Clipboard. Copy Row: Click this option to copy the selected row. Freeze this row: Click this option to freeze the selected row and pin 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: Click this option to unfreeze all frozen rows. Export to Clipboard: Click this option to output the entire row of data to the clipboard in SQL/CSV format.
Zoom in	If the data in a cell is too long and cannot be fully displayed, you can hover the pointer over the cell and click the zoom-in icon at the right end of the cell. In the window that appears, view the data in full. Note LOBs are displayed in text by default. You can convert an LOB into the hexadecimal format and download the LOB to a local device.
View BLOBs	In Oracle mode, you can directly edit text data of the CLOB, BLOB, or RAW type, edit hexadecimal data, and upload files (Size limits: 2 MB for text, 200 KB for hexadecimal, and 20 MB for images). In MySQL mode, you can directly edit text data of the XLOB type such as BLOB, MEDIUMBLOB, or TINYBLOB, edit hexadecimal data, and upload files.
Right-click the tab name of a result tab	For example, you can right-click the tab name of the Result 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.
Adjust the column width	You can adjust the column width by dragging the column edge.
The field type and remarks information displayed in the status bar	Click a field in the result set, and the status bar at the bottom supports displaying specific information such as the type and remarks of the field.

The navigation bar of the Result tab also provides the following tools.

Tool	Description
Column Mode	Click this icon to display the selected data row in the form of a table. On the Column Mode tab, you can switch to the previous or next row. **The column mode makes it easier to view data in a row that has many columns. Procedure: 1. Select the data that you want to view and click Column Mode . 2. On the Column Mode tab, click the left or right arrow to switch between the column values of adjacent rows. 3. Hover the pointer over a value field and click the zoom-in icon to view the details. 4. In the table on the Column Mode tab, you can view the comments for a column in the Remarks column. Perform the following steps to modify the comments: a. In the left-side navigation pane, right-click the target table and select View Table Structure to go to the Attribute tab. b. In the left-side navigation pane of the Attribute tab, click the Column tab. c. Select a field and click the Edit icon. The field editing dialog box appears. d. In the Comment field , edit the comments, and click OK. e. In the SQL Confirmation dialog box, click Execute. f. View the modified comments on the Column Mode** tab.
Columns	Click this icon to select the columns to be displayed on the page.
Back to Start	Click this icon to go back to the first page.
Previous	Click this icon to go to the previous page.
Next	Click this icon to go to the next page.
Jump to Bottom	Click this icon to go to the last page.
Search	Enter a keyword in the search box to search for the desired results.
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 submits a transaction when autocommit is enabled, and Modify and Submit, which is displayed when autocommit is disabled. In the editing mode, you can either double-click target data to directly modify it or click the preceding icons for convenient operations. When you edit a cell, you can right-click the cell and select Copy or Set to Null from the context menu to operate on the cell. Note The ResultMetaData information 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 icon is not displayed and you are prompted that the result set cannot be edited. - If the result set can be edited, the Edit icon 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.
Download Data	You can export the query results to a CSV, SQL, or Excel file. - To export the query results to an SQL file: Edit the SQL statements in the SQL Query field, specify Maximum Number of Lines in Result Set, File Name, File Format (SQL), File Encoding, Data Desensitization, and SQL File Settings (Table Name ). - To export the query results to a CSV file: Edit the SQL statements in the SQL Query field, specify Maximum Number of Lines in Result Set, File Name, File Format, File Encoding, and Data Desensitization. In the CSV File Settings section, specify Include the Column Header, Convert the Empty Character String into a Null Value, Field Separator, Text Identifier, and Line Break Symbol. - To export the query results to an EXCEL file: Edit the SQL statements in the SQL Query field, specify Maximum Number of Lines in Result Set, File Name, File Format,File Encoding, and Data Desensitization. In the Excel File Settings section, specify Include the Column Header and Export SQL statements to another sheet. Note - You can desensitize the exported data. You can specify a custom number of rows to export as needed. - If you export data in CSV format, the exported CSV file can be opened by Microsoft Excel. - If you export data in XLS format, you can specify whether to include the column header and whether to export the SQL statements of the query.
Plan	Click this icon to view the actual resource consumption and execution plan of an executed SQL statement. This allows you to evaluate the performance of the statement.

Community Edition

Enterprise Edition

SQL window