Working with the SQL Terminals
In the SQL Terminal, you can
- Auto Commit
- Execute SQL Queries
- Backuping Unsaved Queries/Functions/Procedures
- Locate Error
- Search in PL/SQL Viewer or SQL Terminal
- Go to Line in PL/SQL Viewer or SQL Terminal
- Comment/Uncomment
- Indent/Un-indent Lines
- Insert Space
- Execute Multiple Functions/Procedures or Queries
- Rename SQL Terminal
- SQL Assistant
- Using Templates
Auto Commit
Auto Commit option can be switched on or off based on the Preferences settings. Refer Transaction for further details on how to enable and disable Auto Commit option.
- If Auto Commit option is enabled, Commit and Rollback buttons are disabled. Transactions are committed automatically.
- If Auto Commit option is disabled, Commit and Rollback buttons are enabled. You can use the buttons manually to commit or revert the changes.
- For OLAP, server will open a transaction for all the SQL statements, such as select statement, explain select statement, and set parameter.
- For OLTP, server will open transaction for only DML statements, such as INSERT statement, UPDATE statement, and DELETE statement.
Reuse Connection
It enables the user to choose the same SQL terminal connection or new connection for the result set. The choice affects the record visibility as per the isolation levels defined in the database server.
- When Reuse Connection is ON, terminal connection will be used for data manipulation and refresh of the result window.
For some data base temp tables that are created or used by the terminal can be edited in the result window.
- When Reuse Connection is OFF, new connection will be used for data manipulation and refresh of the result window.
For some database temp tables cannot be edited from the result window.
Icon is displayed when Reuse Connection is ON.
Icon is displayed when Reuse Connection is OFF.
Icon is displayed when Reuse Connection is disabled.
Follow the steps to turn off Reuse Connection:
- Click
on the SQL Terminal toolbar. Reuse Connection is disabled for the terminal.
- Auto Commit ON- Reuse Connection is enabled and ON by default. You can switch it OFF if needed.
- Auto Commit OFF- Reuse Connection is disabled and ON.
Refer to Table 1 for more details about the behavior of Auto Commit and Reuse Connection .
Enter a function/procedure(s) or SQL query(s) in the SQL Terminal tab and click 
in the SQL Terminal tab, or press Ctrl+Enter, or choose Run > Compile/Execute Statement from 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.
The Result tab displays the results after executing the function/procedure(s) or SQL queries along with the query statement executed.
If the connection is lost during execution and the database is still connected in Object Browser, then Connection Error dialog box is displayed:
- Reconnect - The connection is reestablished.
- Reconnect and Execute - With Auto commit on, execution will continue from failure statement. With Auto commit off, execution will continue from position of cursor.
- Cancel - Disconnects database in Object Browser.
Failure to reconnect after three attempts will disconnect the database in Object Browser. Connect to the database in Object Browser and retry execution.
- For long running queries, result set can be edited only after the complete results are fetched.
- Editing of query results are only allowed in following scenarios:
- Selected targets are from a single table
- Either select all columns or subset of columns [No aliases, aggregate functions, expressions on columns]
- All WHERE condition
- All ORDER BY clause
- On regular, partition, and temporary tables.
- Committing an empty row assigns Null to all columns.
- Only result set of queries executed on tables available in Object Browser is editable.
- Editing of query results is allowed only for queries executed in SQL Terminal.
The column width definition can be set using Settings > Preferences option. Refer to Query Results to set this parameter.
Column Reorder
Column reordering can be performed by clicking and dragging the selected column header to the desired position.
Multi-Column Sort
This feature allows the user to sort table data of some pages by multiple columns. In addition, you can set the priority of columns for sorting.
The feature is available for the following pages:
- Result Set Tab
- Edit Table Data Window
- View Table Data Window
- Batch Drop Result Window
Follow the steps below to access Multi-column sort:
- Click
button on the toolbar. Multi-Column Sort pop-up is displayed.
- Click Add Column. Choose the column you want to sort from the drop down.
- Select the required sort order.
- Click on Apply.
Multi-sort pop up has following elements:
| Attribute Name | UI Element Type | Description/Action |
|---|---|---|
| Priority | Read only text field | Shows column priority in multi sort. |
| Column Name | Combo field having all column names of the table as its value set | Column name of the column added for sorting. |
| Data Type | Read only text field | Shows data type of the column selected. |
| Sort Order | Combo field having values {sort_ascending, sort_descending} | Sort order of the column. |
| Add Column | Button | Adds new row to multi-sort table. |
| Delete Column | Button | Deletes selected column from multi-sort table. |
| Up | Button | Moves selected column up by 1 step, thus changing sort priority. |
| Down | Button | Moves selected column down by 1 step, this changing sort priority. |
| Apply | Button | Apply prepared sort configuration. |
Except following data types, all the other data types will be sorted by their string value (Alphabetical order):
TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, REAL, DOUBLE, NUMERIC, BIT, BOOLEAN, DATE, TIME, TIME_WITH_TIMEZONE, TIMESTAMP, TIMESTAMP_WITH_TIMEZONE.
Elements of Multi-Column Pop-up:
| Icon | Description | Action |
|---|---|---|
|
| Not Sorted | This icon in column header indicates that the column is not sorted. If you click on this icon the column will be sorted in ascending order. Alternatively click Alt+column header |
|
| Ascending Sort | This icon in column header indicates that the column is sorted in ascending order. If you click on this icon, the column will be sorted in descending order. Alternatively click Alt+column header |
|
| Descending Sort | This icon in column header indicates that the column is sorted in descending order. If you click on this icon the column will be in no sort order. Alternatively click Alt+column header |
Icons for the sort priority are as follows:
: Icons having three dots have the highest priority.
: Icons having two dots have the second highest priority.
: Icons having three dots have the third and onwards priority.
| Toolbar Name | Toolbar Icon | Description |
|---|---|---|
| Copy |
| This button is used to copy selected content from result window to clipboard. Shortcut key - Ctrl+C. |
| Advanced Copy |
| This button is used to copy content from result window to clipboard. Results can be copied to include column header. Refer to Query Results to set this preference. Shortcut key - Ctrl+Shift+C. |
| Export all data |
| This icon is used to export all data either in excel (xlsx/xls), CSV, Text or Binary format. Refer to Exporting Table Data. NOTE:
|
| Export current page data |
| This button is used to export current page data to excel (xlsx/xls) or CSV. |
| Paste |
| This button is used to paste copied information. Refer to Paste for more information. |
| Add |
| This button is used to add a row to the result set. Refer to Insert for more information. |
| Delete |
| This button is used to delete a row from the result set. Refer to Delete for more information. |
| Save |
| This button is used to save the changes made in the result set. Refer to Editing Table Data for more information. |
| Rollback |
| This button is used to roll back the changes made to the result set. Refer to Editing Table Data for more information. |
| Refresh |
| This button is used to refresh information in the result set. If multiple result sets are open for the same table, then changes made to one result set will reflect on the other post refresh. Similarly if the same table is edited, then the result set will be updated post refresh. |
| Clear Unique Key selection |
| This button is used to clear the previous unique key selection. Refer to Editing Table Data for more information. |
| Show/Hide Query bar |
| This button is used to display/hide the query executed for that particular result set. This is a toggle button. |
| Show/Hide Search bar |
| This button is used to display/hide the search text field. This is a toggle button. |
| Encoding |
| This field will be available based on the settings. This drop-down is used to select the appropriate encoding to view the data accurately. The default encoding is UTF-8. Refer to Result Data Encoding to set the encoding preference. NOTE: Data editing except for data insertion is restricted once the default encoding is modified. |
| Multi Sort |
| This button brings up multi-sort pop up. |
| Clear Sort |
| This button is used to reset all the sorted column. |
Icons in Search field:
| Icon Name | Icon | Description |
|---|---|---|
| Search |
| This icon is used to search the result set based on the criteria defined. Search text are case insensitive. |
| Clear Search Text |
| This icon is used to clear the search text entered in the search field. |
Right-click options in the Result window:
| Option | Description |
|---|---|
| Close | Closes only the active result window. |
| Close Others | Closes all other result windows except for the active result window. |
| Close Tabs to the Right | Closes only the right active result window. |
| Close All | Closes all result windows including the active result window. |
| Detach | Detach from current active result window. |
Status information displayed in the Result window:
- Query Submit Time - Provides the query submitted time.
- Number of rows fetched with execution time is displayed. The default number of rows is displayed. If there are additional rows to be fetched, then it will be denoted with the word "more". You can scroll to the bottom of the table to fetch and display all rows.
When viewing table data, Data Studio automatically adjusts the column widths for a good table view. Users can resize the columns as needed. If the text contents of a cell exceeds the total available display area, then resizing the cell column may cause DS to become unresponsive.
- Each time a query is run in SQL Terminal tab, a new result window opens. To view the results in the new window, you must select the newly opened window.
- Set the focusOnFirstResult configuration parameter to false to automatically set focus to the newly opened Result window. Refer to Installing and Configuring Data Studio for details.
- Each row, column and selected cells can be copied from the result set.
- Export all data operation will be successful even after the connection is removed.
- If the content of the column have spaces between the words, then word wrap is applied to fit the column within the display area. Word wrap is not applied if the content does not have any spaces between the words.
- Select part of cell content and press Ctrl+C or click
to copy selected text from a cell. - The size of the column is determined by the maximum content length column.
- You can save preference to define:
- Number of records to be fetched
- Column width
- Copy option from result set
Refer to Query Results for more information.
- If any column of resultset tab has Lock Image icon in it, then values are not editable.
Backuping Unsaved Queries/Functions/Procedures
Data Studio creates back up of unsaved data in SQL Terminal and PL/SQL Viewer periodically based on the time interval defined in the Preferences tab. The data can be encrypted and saved based on Preference settings.
Refer to Query/Function/Procedure Backup to turn on/off backup, define time interval to save the data, and encrypt the saved data.
Unsaved changes of each SQL Terminal/PL/SQL Viewer are taken as backup and stored in DataStudio\UserData\<user name>\Autosave folder. Backup files saved before unexpected shutdown of Data Studio will be available at next login.
In case there are unsaved data in SQL Terminal/PL/SQL Viewer, during graceful exit, Data Studio will wait for backup to complete before closing.
Error Locator
During execution of query/function/procedure in case of an error the error locator message is displayed.
Yes - Click Yes to continue with the execution.
No - Click No to stop the execution.
Select Do not show additional errors for this execution option to hide the error message popup from displaying while continuing with the current execution.
Line number and position of error displays in Messages tab. The corresponding line number is marked with 
icon along with red underline at the position of the error in the Terminal/PL/SQL Viewer. Hovering over 
displays the error message. Refer to FAQs section to understand in certain scenarios why the line number and error detail does not match.
If the query/function/procedure is modified while execution is in progress, then error locator may not display the correct line and position number.
Search in PL/SQL Viewer or SQL Terminal
Follow the steps below to search in PL/SQL Viewer or SQL Terminal:
F3 key is used to search next word and Shift+F3 key is used to search previous word. These shortcut keys will be enabled only after Ctrl+F is used to search a text. These keys will be active with the current search word until a new word is searched. The value searched using Ctrl+F and F3/Shift+F3 will be applicable only for the current instance.
- Choose Edit > Find and Replace from the main menu.
Alternatively press Ctrl+F.
Find and Replace dialog box is displayed.
- Enter the text to be searched in Find what field, and click the Find Next button.
The searched text is highlighted.
F3 and Shift+F3 key will now be enabled for forward and backward search.
Select Wrap around option to continue the search after reaching the last line in the SQL queries or PL/SQL statements.
Go to Line in PL/SQL Viewer or SQL Terminal
Go to line option is used to skip to a specific line in the terminal.
Follow the steps below to go to a line in PL/SQL Viewer or SQL Terminal:
- Choose Edit > Go To Line from the main menu or
Alternatively press Ctrl+G.
Go to Line dialog box is displayed.
- Enter the desired number in the Enter the line number field, and then click the OK button. The cursor moves to the beginning of the line entered in the Go to Line dialog box.
Below are invalid inputs to this field.
- Non-numeric value
- Special characters
- Line number entered does not exist in the editor.
- More than 10 digits is entered.
Comment/Uncomment
Comment/uncomment option is used to comment/uncomment lines or block of lines.
Follow the steps below to comment/uncomment lines in PL/SQL Viewer or SQL Terminal:
- Select the lines to comment/uncomment.
- Choose Edit option. Choose Comment/Uncomment Lines from the main menu.
Alternatively, press Ctrl+/, or right-click and select Comment/Uncomment Lines, and then the selected lines are commented/uncommented.
Follow the steps below to comment/uncomment block of lines/content in PL/SQL Viewer or SQL Terminal:
- Select the lines/content to comment/uncomment.
- Choose Edit option. Choose Comment/Uncomment Block from the main menu.
Alternatively press Ctrl+Shift+/, or right-click and select Comment/Uncomment Block, and then selected block of lines/content are commented/uncommented.
Indent/Un-indent Lines
The indent/un-indent option is used to shift lines as per the indent size defined in the Preferences tab.
Follow the steps to indent lines in PL/SQL Viewer or SQL Terminal:
- Select the lines to indent.
- Press Tab or click
. Shift the selected line as per the indent size defined in the Preferences tab. Refer to Formatter section to modify the indent size.
Follow the steps to un-indent lines in PL/SQL Viewer or SQL Terminal:
- Select the lines to un-indent.
- Press Shift+Tab or click
. Shifts the selected line as per the indent size defined in the Preferences tab. Refer to Formatter to modify the indent size.
Only selected lines that have available tab space will be un-indented. For example, if multiple lines are selected, and one of the selected line starts at position 1, then pressing Shift+Tab will un-indented all the lines except for the one starting at position 1.
Insert Space
The Insert Space option is used to replace a tab with spaces based on the indent size defined in the Preferences tab.
Follow the steps below to replace a tab with spaces in PL/SQL Viewer or SQL Terminal:
- Select the lines to replace tab with spaces.
- Press Tab or Shift+Tab.
Replaces the tab with spaces as per the indent size defined in the Preferences tab. Refer to Formatter section to modify the indent size.
Execute Multiple Functions/Procedures or Queries
Follow the steps below to execute multiple functions/procedures:
Insert a forward slash (/) in a new line after the function/procedure in the SQL Terminal.
Add the new function/procedure in the next line.
Follow the steps below to execute multiple SQL queries:
- Enter multiple SQL queries in the SQL Terminal tab as follows:
- Click
in the SQL Terminal tab, or press Ctrl+Enter, or choose Run > Compile/Execute Statement from the main menu. - If the queries are not selected for execution, then only the query in the line where cursor is placed will be executed.
- If the cursor is placed next to an empty line, then the next available query statement will be executed.
- If the cursor is placed at the last line which is blank, then no query will be executed.
- If a single query is written in multiples lines and the cursor is placed at any line of the query, then that query is executed. Queries are separated using semicolon (;).
Do as follow to execute an SQL query after a function/procedure:
Insert a forward slash (/) in a new line after the function/procedure and click 
in the SQL Terminal tab.
Do as follow to execute PL/SQL statements and SQL queries on different connections:
In the toolbar, select the required connection from the connection profiles drop-down list and click 
in the SQL Terminal tab.
- In the SQL Terminal tab right-click and select Rename Terminal.
A Rename Terminal dialog box is displayed prompting you to provide the new name for the Terminal.
- Enter the new name and select OK to rename the Terminal.
- Terminal name follows Windows file naming convention.
- Rename Terminal allows a maximum of 150 characters.
- Restore option is not available to revert to the default name. You must manually rename the Terminal to default name.
- Tool tip of the renamed Terminal will display the old name.
SQL Assistant
The SQL Assistant tool provides suggestion or reference for the information entered in the SQL Terminal and PL/SQL Viewer. Follow the steps to open SQL Assistant:
When Data Studio is launched SQL Assistant panel displays with related syntax topics. As you type a query in the SQL Terminal topics related to the query is displayed. It also provides precautions, examples, syntax, function, and parameter description. Select the text and use the right-click option to copy selected information or copy and paste to SQL Terminal.
- You can enable/disable the SQL Assistant tool permanently. Refer to 7.3-SQL Assistant to enable/disable this option.
- SQL Assistant icon (
) from the toolbar can be used to open the SQL Assistant window.
Using Templates
Data Studio provides an option to insert frequently used SQL statements in the SQL Terminal/PL/SQL Viewer using the Templates option. Some of the commonly used SQL statements are saved for ease of use. You can create, modify existing templates or remove templates. Refer to Adding/Modifying Templates section for information on adding, removing, and creating new templates.
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 |
Follow the steps to use the Templates option:
- Enter the name of the template in SQL Terminal/PL/SQL Viewer.
- Press Alt+Ctrl+Space. A list of saved template information is displayed. The list displayed is based on the following criteria:
Exact Match
Display List
On
Displays all entries that match the input text case.
Example: Entering "SF" in SQL Terminal/PL/SQL Viewer displays all entries that start with "SF".
Off
Displays all entries that match the input irrespective of the text case.
Example: Entering "SF" in SQL Terminal/PL/SQL Viewer displays all entries that start with "SF", "Sf", "sF", or "sf".
Text Selection/Cursor Location
Display List
A text is selected and the shortcut key is used
Displays entries that match the text before the selection to the nearest space or new line character.
No text selected and the shortcut key is used
Displays entries that match the text before the cursor to the nearest space or new line character.
- Using the shortcut key without entering text in SQL Terminal/PL/SQL Viewer displays all entries in the Templates.
- If the text entered in SQL Terminal/PL/SQL Viewer has only a single match, then it will be replaced directly in the SQL Terminal/PL/SQL Viewer without listing them out.
- After you click Open SQL Assistant button, the SQL Assistant pane is displayed. If you close the pane, it will be closed for all the terminals unless you click Open SQL Assistant button again.
Last Article: Viewing the Query Execution Plan and Cost Graphically
Next Article: Exporting Query Results
Did this article solve your problem?
Thank you for your score！Your feedback would help us improve the website.