Help Center> Data Warehouse Service (DWS)> Tool Guide (Paris Region)> Data Studio - Integrated Database Development Tool> SQL Terminal> Using SQL Terminals
Updated on 2023-03-17 GMT+08:00
View PDF

Using SQL Terminals

Auto Commit

The Auto Commit option is available in the Preferences pane. For details, see Transaction.

  • If Auto Commit is enabled, the Commit and Rollback functions are disabled. Transactions are committed automatically.
  • If Auto Commit is disabled, Commit and Rollback functions are enabled. Transactions are committed or rolled back manually.
    • For OLAP, the server will open a transaction for all SQL statements, such as SELECT, EXPLAIN SELECT, and SET PARAMETER.
    • For OLTP, the server will open a transaction for only DML statements, such as INSERT, UPDATE, and DELETE.

Reuse Connection

The Reuse Connection option allows you to select the same SQL terminal connection or new connection for the result set. The selection affects the record visibility due to the isolation levels defined in the database server.

  • When Reuse Connection is set to ON, terminal connection will be used to perform operations on data and refresh the Result tab.

For some databases, the temporary tables created or used by the terminal can be edited in the Result tab.

  • When Reuse Connection is set to OFF, new connection will be used to perform operations on data and refresh the Result tab.

For some databases, the temporary tables can be edited in the Result tab.

: displayed when Reuse Connection is set to ON

: displayed when Reuse Connection is set to OFF

: displayed when Reuse Connection is disabled

Perform the following steps to set Reuse Connection to OFF:

  1. Click in the SQL Terminal toolbar.

    Reuse Connection is set to OFF for the terminal. is displayed.

    • The Reuse Connection function is set to ON by default. You can set it to OFF as required. If you enable Auto Commit, Reuse Connection is set to ON automatically.
    • If you disable Auto Commit, Reuse Connection is set to OFF automatically. However, this function is still displayed as ON on the GUI, and the status cannot be modified.

For details about Auto Commit and Reuse Connection, see Table 1.

Executing SQL Queries

Perform the following steps to execute a function/procedure or SQL query:

Enter a function/procedure or SQL statement in the SQL Terminal tab and click , or press Ctrl+Enter, or choose Run > Compile/Execute Statement in the main menu.

Alternatively, you can right-click in the SQL Terminal tab and select Execute Statement.

You can check the status bar to view the status of a query being executed.

After the function/procedure or SQL query is executed, the result is generated and displayed in the Result tab.

If the connection is lost during execution but the database remains connected in Object Browser, the Connection Error dialog box is displayed with the following options:

  • Reconnect: The database is reconnected.
  • Reconnect and Execute: With Auto Commit set to ON, execution will continue from the failed statement. With Auto Commit set to OFF, execution will continue from the position of the cursor.
  • Cancel: The database is disconnected in Object Browser.

If the reconnection fails after three attempts, the database will be disconnected in Object Browser. Connect to the database in Object Browser and try the execution again.

  • For time-consuming queries, the result set can be edited only after the complete results are obtained.
  • Query results can be edited in the following scenarios:
    • The selected objects are in the same table.
    • All or some columns are selected without aliases, aggregate functions, or column expressions.
    • The query contains the WHERE clause.
    • The query contains the ORDER BY clause.
    • The table is an ordinary, partitioned, or temporary table.
  • If an empty row is committed, Null is assigned to all columns.
  • Result sets of queries executed on tables available in Object Browser are editable.
  • Results of queries executed in SQL Terminal can be edited.

You can choose Settings > Preferences to set the column width. For details, see Query Results.

Column Reorder

You can click a column header and drag the column to the desired position.

Multi-Column Sort

This feature allows you to sort table data of some pages by multiple columns, as well as to set the priority of columns to be sorted.

This feature is available for the following tabs:

  • Result Set
  • Edit Table Data
  • View Table Data
  • Batch Drop Result

Perform the following steps to enable Multi-Column Sort:

  1. Click in the toolbar.

    The Multi-Column Sort dialog box is displayed.

  2. Click Add Column. Choose the column to be sorted from the drop-down list.

  3. Set the sort order.
  4. Click Apply.

The Multi-Column Sort dialog box contains the following elements.

Table 1 Elements of the Multi-Column Sort dialog box

Name

UI Element Type

Description/Operation

Priority

Read-only text field

Shows the column priority in Multi-Column Sort

Column Name

Concatenated field, which can be all column names of the table

Shows the name of the column added for sorting

Data Type

Read-only text field

Shows data type of the selected column

Sort Order

Concatenated field, which can be in either ascending or descending order

Shows the sort order of the selected column

Add Column

Button

Adds new columns to a table for multi-column sort

Delete Column

Button

Deletes selected columns from a table for multi-column sort

Up

Button

Moves the selected column up by one step to change the sort priority

Down

Button

Moves the selected column down by one step to change the sort priority

Apply

Button

Applies the sort priority

Data types will be sorted in an alphabetical order, except the following ones:

TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, REAL, DOUBLE, NUMERIC, BIT, BOOLEAN, DATE, TIME, TIME_WITH_TIMEZONE, TIMESTAMP, and TIMESTAMP_WITH_TIMEZONE

The Multi-Column Sort dialog box contains the following icons.

Table 2 Icons of the Multi-Column Sort dialog box

Icon

Description

Operation

Not Sorted

If this icon is displayed in a column header, the column is not sorted. You can click this icon to sort the column in ascending order.

Alternatively, use Alt+Click to select the column header.

Ascending Sort

If this icon is displayed in a column header, the column is sorted in ascending order. You can click this icon to sort the column in descending order.

Alternatively, use Alt+Click to select the column header.

Descending Sort

If this icon is displayed in a column header, the column is sorted in descending order. If you click on this icon the column will be in no sort order.

Alternatively, use Alt+Click to select the column header.

Icons for the sort priority are as follows:

: Icon with three dots indicates the highest priority.

: Icon with two dots indicates the second highest priority.

: Icon with one dot indicates the lowest priority.

Table 3 Toolbar icons

Toolbar Name

Toolbar Icon

Description

Copy

This icon is used to copy selected data from the Result pane to clipboard. The shortcut key is Ctrl+C.

Advanced Copy

This icon is used to copy selected data from the Result pane to clipboard. The copied data includes column headers. See Query Results to set this preference. The shortcut key is Ctrl+Shift+C.

Export all data

This icon is used to export all data to files in Excel (xlsx/xls), CSV, text, or binary format. For details, see Exporting Table Data.

NOTE:
  • The columns involved in the query are automatically populated in the Selected Columns area. The Available Columns area is empty.
  • To export the query results, execute the query again using a new connection. The exported results may be different from the data in the Result pane.
  • This function is not available for queries using the EXPLAIN or ANALYZE statement. To export such queries, select Export current page data.

Export current page data

This icon is used to export current page data to files in Excel (xlsx/xls) or CSV format.

Paste

This icon is used to paste copied information. For details, see Pastes content.

Add

This icon is used to add a row to the result set. For details, see Insert.

Delete

This icon is used to delete a row from the result set. For details, see Delete.

Save

This icon is used to save the changes made in the result set. For details, see Editing Table Data.

Rollback

This icon is used to roll back the changes made in the result set. For details, see Editing Table Data.

Refresh

This icon is used to refresh information in the result set. If multiple result sets are open for the same table, changes made in one result set will take effect in other result sets after refresh. If the table is edited, the result sets will be updated after refresh.

Clear Unique Key selection

This icon is used to clear the previously selected unique key. For details, see Editing Table Data.

Show/Hide Query bar

This icon is used to display or hide the query executed for a specified result set. This is a toggle button.

Show/Hide Search bar

This icon is used to display or hide the Search field. This is a toggle button.

Encoding

Whether you can configure this field depends on the settings in Preferences > Result Management > Query Results > Result Data Encoding. In this drop-down list, you can select the appropriate encoding to view the data accurately. The value defaults to UTF-8. For details about the encoding preference, see Result Data Encoding.

NOTE:

Data editing operations, except data insertion, are restricted after the default encoding is modified.

Multi Sort

This icon is used to display the Multi Sort dialog box.

Clear Sort

This icon is used to reset all sorted columns.

Icons in the Search field are as follows:

Icon Name

Icon

Description

Search

This icon is used to search for result sets according to the criteria defined. The text is case-insensitive.

Clear Search Text

This icon is used to clear the text entered in the Search field.

Right-click options in the Result pane are as follows:

Option

Description

Close

Closes only the active Result pane

Close Others

Closes all other Result panes except the active one

Close Tabs to the Right

Closes all Result panes to the right of the active one

Close All

Closes all Result panes including the active one

Detach

Opens only the active Result pane

Status information displayed in the Result pane is as follows:

  • Query Submit Time: indicates time when a query is submitted
  • The number of rows obtained and the execution time are displayed, as well as the default number of rows. If there are additional rows to be obtained, the icon is displayed with the word more. You can scroll to the bottom of the table to obtain and display all rows.

    When you are viewing table data, Data Studio automatically adjusts the column width for better display. You can adjust the column width as required. If the text length exceeds the column width and you adjust the column width, Data Studio may fail to respond.

  • A new Result pane is opened each time a query is executed. To view the results in the new pane, select the pane.
  • Set the focusOnFirstResult parameter to false to automatically locate the newly opened Result pane. For details, see Installing and Configuring Data Studio.
  • Selected rows, columns, and cells can be copied from the result set.
  • All data can be exported even after the connection is removed.
  • If the text of a column contains spaces, word wrapping is applied to fit the column width. Word wrapping is not applied to columns without space.
  • To copy part of a cell, select the desired part and press Ctrl+C or click .
  • The column size depends on the column with the longest text.
  • You can save preferences to define:
    • Number of records to be obtained
    • Column width
    • Copying options from a result set

      For details, see Query Results.

  • If the lock icon is displayed in any column of the Result Set tab, parameters cannot be edited.

Backing up Unsaved Queries/Functions/Procedures

Data Studio backs up unsaved data in SQL Terminal and PL/SQL Viewer periodically based on the time interval defined in the Preferences pane. Data is encrypted and saved based on the Preference settings. See Query/Function/Procedure Backup to enable or disable the backup function, set time interval of data saving, and encrypt the saved data.

Unsaved changes in SQL Terminal and PL/SQL Viewer are backed up and saved in the DataStudio\UserData\Username\Autosave folder. If these backup files have been saved before Data Studio is shut down unexpectedly, these files will be available upon the next login.

If unsaved data exists in SQL Terminal and PL/SQL Viewer during graceful exit, Data Studio will not be closed until the backup is complete.

Locating Errors

When an error occurs during the execution of queries/functions/procedures, an error locating message will be displayed.

Yes: Click Yes to proceed with the execution.

No: Click No to stop the execution.

You can select Do not display other errors that occur during the execution to hide the error messages and proceed with the current SQL query.

The line number and position of an error message is displayed in the Messages pane. In SQL Terminal or PL/SQL Viewer, the corresponding line is marked with and a red underline at the position of the error. You can hover over to display the error message. For details about why the line number does not match with the error detail, see FAQs.

If a query/function/procedure is modified during execution, the error locator may not display the correct line number and the position of the error.

Searching in the PL/SQL Viewer or SQL Terminal Pane

Perform the following steps to search in the PL/SQL Viewer or SQL Terminal pane:

Press F3 to search for the next line or Shift+F3 to search for the previous line. You can use these shortcut keys after pressing Ctrl+F to search for text and key words. Ctrl+F, F3, and Shift+F3 will be available only when you search for keywords in the current instance.

  1. Choose Edit > Find and Replace from the main menu.

    Alternatively press Ctrl+F.

    The Find and Replace dialog box is displayed.

  2. Enter the text to be searched for in the Find what field, and click the Find Next button.

    The desired text is highlighted.

    You can press F3 for forward search or Shift+F3 for backward search.

    When reaching the last line in a SQL query or PL/SQL statement, select Wrap around to proceed with the search.

Locating a Specific Line in the PL/SQL Viewer or SQL Terminal Pane

Perform the following steps to locate a specific line in the PL/SQL Viewer or SQL Terminal pane:

Perform the following steps to go to a line in PL/SQL Viewer or SQL Terminal:

  1. Choose Edit > Go To Line from the main menu or press Ctrl+G.

    The Go To Line dialog box is displayed, allowing you to skip to a specific line in SQL Terminal.

  2. Enter the desired line number in the Enter the line number field, and click OK.

    The cursor moves to the beginning of the line entered in the Go to Line dialog box.

    You cannot enter the following characters in this field:

    • Non-numeric character
    • Special characters
    • Line numbers that do not exist in the editor
    • Number with more than 10 digits

Commenting or Uncommenting

Data Studio allows you to comment or uncomment lines or blocks.

Perform the following steps to comment or uncomment lines in PL/SQL Viewer or SQL Terminal:

  1. Select the lines to comment or uncomment.
  2. Choose Edit > Comment/Uncomment Lines from the main menu to comment or uncomment each selected line.

    Alternatively, press Ctrl+/ or right-click a line and select Comment/Uncomment Lines.

Perform the following steps to comment or uncomment blocks in PL/SQL Viewer or SQL Terminal:

  1. Select the lines or a block to comment or uncomment.
  2. Choose Edit > Comment/Uncomment Block from the main menu to comment or uncomment each selected line or the entire block.

    Alternatively, press Ctrl+Shift+/ or right-click a line or the entire block and select Comment/Uncomment Block.

Indenting or Un-indenting Lines

You can indent or un-indent lines according to the indent size defined in Preferences.

Perform the following steps to indent lines in PL/SQL Viewer or SQL Terminal:

  1. Select the desired lines.
  2. Press Tab or click .

    Move the selected lines according to the indent size defined in Preferences. For details about modifying the indent size, see Formatter.

Perform the following steps to un-indent lines in PL/SQL Viewer or SQL Terminal:

  1. Select the desired lines.
  2. Press Shift+Tab or click .

    Move the selected lines according to the indent size defined in Preferences. For details about modifying the indent size, see Formatter.

    Only selected lines that have available tab space will be un-indented. For example, if multiple lines are selected and one of the selected lines starts at position 1, pressing Shift+Tab will un-indent all lines except the one starting at position 1.

Inserting Spaces

The Insert Space option is used to replace a tab with spaces according to the indent size defined in Preferences.

Perform the following steps to replace a tab with spaces in PL/SQL Viewer or SQL Terminal:

  1. Select the desired lines.
  2. Press Tab or Shift+Tab.

    A tab is replaced with spaces according to the indent size defined in Preferences. For details about modifying the indent size, see Formatter.

Executing Multiple Functions/Procedures or Queries

Perform the following steps to execute multiple functions/procedures:

Insert a forward slash (/) in a new line under the function/procedure in SQL Terminal.

Add the new function/procedure in the next line.

Perform the following steps to execute multiple SQL queries:

  1. Enter multiple SQL queries in SQL Terminal as follows:

  2. Click in SQL Terminal, or press Ctrl+Enter, or choose Run > Compile/Execute Statement from the main menu.

    • If no query is selected, only the query in the line where the cursor is placed will be executed.
    • If the cursor is placed in an empty line, the next available query statement will be executed.
    • If the cursor is placed in the last empty line, no query will be executed.
    • If a single query is written in multiples lines and the cursor is placed in any line of the query, the query will be executed. Queries are separated using a semicolon (;).

Perform the following steps to execute a SQL query after executing a function/procedure:

Insert a forward slash (/) in a new line under the function/procedure in SQL Terminal. Then add new query or function/procedure statements.

Perform the following steps to execute PL/SQL statements and SQL queries on different connections:

Select the required connection from the Connection drop-down list and click in SQL Terminal.

Renaming a SQL Terminal

Perform the following steps to rename a SQL Terminal:

  1. Right-click in SQL Terminal and select Rename Terminal.

    The Rename Terminal dialog box is displayed prompting you to enter the new terminal name.

  2. Enter the new name and click OK.

    • The terminal name must follow the Windows file naming convention.
    • The Rename Terminal dialog box allows a maximum of 150 characters.
    • The Restore option cannot be used to restore to the default name. You must manually rename a terminal with its default name.
    • Tool tip of the renamed terminal will display the previous terminal name.

SQL Assistant

The SQL Assistant tool provides suggestion or reference for the information entered in SQL Terminal and PL/SQL Viewer. Perform the following steps to open SQL Assistant:

When Data Studio is started, related syntax is displayed in the SQL Assistant panel. After you enter a query in SQL Terminal, related syntax details are displayed, including precautions, examples, and description of syntax, functions, and parameters. Select the text and right-click to copy the selected text or copy and paste it to SQL Terminal.

  • Choose Settings > Preferences > Environment > Session Setting. In the SQL Assistant area displayed on the right, enable or disable the SQL Assistant function permanently. By default, the SQL Assistant function is enabled permanently.
  • After the SQL Assistant function is enabled, you can click the SQL Assistant icon () on the toolbar to open the SQL Assistant window. If the SQL Assistant icon is gray after the SQL Assistant function is enabled, the operation is invalid.

Using Templates

The Templates option of Data Studio allows you to insert frequently used SQL statements in SQL Terminal or PL/SQL Viewer. Some frequently used SQL statements have been saved in Data Studio. You can create, edit, or remove a template. For details, see Adding/Editing/Removing a Template.

The following table lists the default templates.

Name

Description

df

delete from

is

insert into

o

order by

s*

select from

sc

select row count

sf

select from

sl

select

Perform the following steps to use the Templates option:

  1. Enter a template name in SQL Terminal or PL/SQL Viewer.
  2. Press Ctrl+Alt+Space.

    A list of existing template information is displayed. For details, see the following tables.

    Exact Match

    Display

    On

    Displays all entries that start with the input text (case-sensitive).

    For example, if SF is entered in SQL Terminal or PL/SQL Viewer, all entries that start with SF are displayed.

    Off

    Displays all entries that start with the input text (case-insensitive).

    For example, if SF is entered in SQL Terminal or PL/SQL Viewer, all entries that start with SF, Sf, sF, or sf are displayed.

    Text Selection/Cursor Location

    Display

    Text is selected and the shortcut key is used.

    Displays entries that match the text between the leftmost character of the selected text and the space or newline character nearest to the character.

    No text is selected and the shortcut key is used.

    Displays entries that match the text between the cursor position and the space or newline character nearest to that position.
    • If you press Ctrl+Alt+Space without entering text in SQL Terminal or PL/SQL Viewer, all entries in Templates will be displayed.
    • If the text entered in SQL Terminal or PL/SQL Viewer matches with only one entry, this entry will replace the text entered and the template list will not be displayed.

Parent topic: SQL Terminal