20 Using SQL Command Processor

This section provides information on how to use the SQL Command Processor.

This section contains the following topics:

• About the SQL Command Processor

• Accessing the SQL Command Processor

• Executing a SQL Command

• Saving a SQL Command

• Copying a Command

• Using Saved Commands

• Using SQL Command History

• Displaying Results

• Using Explain Plan

About the SQL Command Processor

You can use the SQL Command Processor to create, edit, view, run, and delete SQL commands. A SQL command can contain SQL statements or PL/SQL blocks.

SQL commands created in the Query Builder can be accessed in the SQL Command Processor. Saved SQL commands must have names unique in the Workspace.

There is no interaction between the SQL Command Processor and the SQL Script Repository. You can cut and paste a SQL command from the SQL Command Processor to run in the SQL Script Editor.

Accessing the SQL Command Processor

To access the SQL Command Processor:

1. Log in to Oracle HTML DB.

   The Workspace home page appears.

2. To view the SQL Command Processor home page you can either:

   • Click the SQL Workshop icon and then the SQL Commands icon to drill-down to the SQL Command Processor home page.

   • Click the down arrow on the right side of the SQL Workshop icon to view a pull-down menu. Then select the SQL Commands menu option.

   Figure 20-1 SQL Workshop Pull-down Menu

   
   Description of the illustration sqlwrkshpmenu.gif
   
   
   
   

   Note: For the purposes of consistency, this document uses the primary navigation path (or drill-down approach) when explaining navigation.

   
   

About the SQL Command Processor Home Page

The SQL Command Processor home page contains a command editor and a display pane. You enter and edit SQL commands in the editor and view output, saved command lists, and history lists in the display pane.

Figure 20-2 SQL Command Processor Home Page

Description of the illustration proc_home.gif

On the SQL Commands home page you can:

• Choose the schema. Make a selection from the Schema list to specify the schema in which to execute the command.

• Disable transactional commands. If available, click the Autocommit check box to enable autocommit and disable transactional commands. The Autocommit check box is only available if transactional SQL commands are enabled for this HTML DB instance.

• Set the Number of Output Rows. Make a selection from the Display list to specify the number of rows of output to display at one time up to a maximum of 100,000. All rows of DBMS Output are displayed regardless of the Display list setting.

• Save a SQL command. Click Save to save the contents of the editor, or the currently highlighted content to a file. You are prompted to enter a name and an optional description. The new command appears in the Saved SQL list.

• Execute a SQL command. Click Run (Ctrl+Enter) to run the command in the editor, or the currently highlighted command in the editor.

• Highlight an individual statement for execution. Select an individual statement in the editor and click Run or press Ctrl+Enter to execute only the highlighted statement.

See Also: "About Transactions in the SQL Command Processor" and "Configuring SQL Workshop" for information about enabling the Autocommit check box

About the Results Tab

Click the Results tab to see the results from the last successfully executed SQL command. Click DBMS Output at the bottom of the displayed results to display lines of DBMS output. This control only appears when there is DBMS output to display. Click CSV Export to export results to a comma separated file on your local file system.

About the Explain Tab

Click the Explain tab to examine the execution plan used by the optimizer for statements that make changes to the database. Objects in the output are linked to the Object Browser. Click the linked object to view its properties in the Object Browser.

See Also: "Top SQL" for more information about the execution plan

About the Describe tab

Enter Describe object_name and click Run to display column definitions for a table or view, or specifications for a function or procedure in the Describe tab. Select links in the Describe results to write that information into the command editor. For example, click a table name to add owner.table, click a column name to add the column name, click a procedure or function name to add the object call with parameters, or click a package name to add the package call.

About the Saved SQL tab

Click the Saved SQL tab to display a list of all SQL commands saved in the current workspace. Click the command title to load it into the command editor.

About the History tab

Click the History tab to list your recently executed commands. Your last 200 executed commands are saved.

Executing a SQL Command

You use the SQL Command Processor to run SQL commands on any Oracle database schema for which you have privileges.

To execute a SQL Command:

1. Click the SQL Workshop icon on the Workspace home page.

2. Click the SQL Commands icon.

3. Enter the SQL command you want to run in the SQL editor pane.

4. Make a selection from the Schema list to specify the Database schema in which to execute the SQL command.

5. Click Run (Ctrl+Enter) to execute the command.

   The results appear in the display pane.

About Transactions in the SQL Command Processor

If transactional SQL commands are enabled for the HTML DB instance, an Autocommit check box appears in the SQL Command Processor home page.

To disable transactional SQL commands in the SQL Command Processor, check the Autocommit check box. Attempting to use any transactional SQL commands such as COMMIT or ROLLBACK when transactional mode is disabled returns an error message.

To enable transactional SQL commands, clear the Autocommit check box. HTML DB verifies that the necessary system resources are available before entering the transactional mode. If resources are unavailable, an error message is displayed.

Transactional mode is a stateful transaction mode where you can, for example, perform an update, select data for review, and COMMIT or ROLLBACK changes. It is implemented using DBMS_JOBS.

Consider the following behavior in transactional mode:

• Actions are not committed to the database until you enter an explicit COMMIT command.

• Exiting the SQL Command Processor terminates and rolls back the current transaction.

• A session time out terminates and rolls back the current transaction. The system preference, SQL_COMMAND_MAX_INACTIVITY, sets the time before an inactive session times out. The default timeout is 60 minutes.

• The CSV Export option is not available.

See Also: "Using the SQL Script Repository" for information on running scripts and "Configuring SQL Workshop" for information about setting the session timeout and enabling transactional SQL commands

About Unsupported SQL*Plus Commands

The SQL Command Processor does not support SQL*Plus commands. If you attempt to enter a SQL*Plus command such as SET ECHO or DEFINE in the SQL Command Processor, an error message displays.

About Command Termination

You can terminate a command in the SQL Command Processor using a semicolon (;), a forward slash (/), or with nothing. Consider the following valid alternatives:

SELECT * from emp;

or

SELECT * from emp
/

or

SELECT * from emp

The first example demonstrates the use of a semicolon (;), the second example demonstrates the use of forward slash (/), and the final example demonstrates a command with no termination.

Using Bind Variables

Bind variables are supported. You are prompted to enter values for bind variables during command execution. Bind variables are prefixed with a colon.

For example

SELECT * from emp where deptno = :dept

In earlier versions of Oracle HTML DB, you could check your Workspace ID by running the command:

select :WORKSPACE_ID from dual

In this release, run the following SQL command to check your Workspace ID:

select v('WORKSPACE_ID') from dual

Saving a SQL Command

You can save commands you enter in the SQL Command Processor.

To save a SQL command:

1. Click the SQL Workshop icon on the Workspace home page.

2. Click the SQL Commands icon.

3. Enter the command in the command editor.

4. Click Save to save the command.

   You are prompted to enter a name and description for the command.

5. Click Save, or click Cancel to return to the command editor without saving.

   The saved command is listed in the display area.

Copying a Command

To copy a SQL command:

1. Click the SQL Workshop icon on the Workspace home page.

2. Click the SQL Commands icon.

3. Click the Saved SQL tab, located between the command editor and the display pane.

   The Saved SQL list of commands appears in the display pane.

4. Click the title of the command to load it into the command editor

5. Click Save to save the command.

6. Enter a new name for the command in the Name field and click Save.

   The command is copied to the new name.

Using Saved Commands

You can access the commands you save and commands saved by other users in the same Workspace. You can also access SQL commands you and other users of the same Workspace saved from the Query Builder.

Topics in this section include:

• Accessing Saved Commands

• About the Saved SQL Pane

Accessing Saved Commands

To access saved SQL commands:

1. Click the SQL Workshop icon on the Workspace home page.

2. Click the SQL Commands icon.

3. Click the Saved SQL tab which is located between the command editor and the display pane.

   The Saved SQL list of commands appears in the display pane.

4. Click the title of the command to load it into the command editor.

   The command appears in the editor.

5. Click Run to execute the command.

About the Saved SQL Pane

The saved SQL pane displays a list of all commands saved in this Workspace. The list displays commands saved from the SQL Command Processor and SQL commands saved from the Query Builder. Saved SQL commands must have unique names in the Workspace. The same name cannot be used in the Query Builder and the SQL Command Processor

Each command entry shows the owner name, the command name, the first characters of the SQL command, a description if it exists, who last updated the command and when.

Figure 20-3 Saved SQL Pane

Description of the illustration proc_savedsql.gif

On the Saved SQL pane you can:

• Show commands by owner. Make a selection from the Owner list to specify the user whose commands you want to display. To view all scripts select -All Users-.

• Search for a command. Enter a command name or partial name, or enter a code snippet in the Find field and click Go. To view all scripts, leave the Find field blank and click Go. You control how many rows display by making a selection from the Rows list.

• Set the Number of Output Rows. Make a selection from the Display list to specify the number of Saved SQL commands to display at one time.

• Delete a command. Click the check box associated with each command you want to delete, and click Delete Checked.

• Sort commands. Click a column heading to sort the listed commands by that column.

Using SQL Command History

Commands you have executed are stored in the command history regardless of whether you explicitly save them. You use SQL Command History to access commands you have executed in this Workspace.

Topics in this section include:

• Accessing a Command from Command History

• About the History Pane

Accessing a Command from Command History

To access history commands:

1. Click the SQL Workshop icon on the Workspace home page.

2. Click the SQL Commands icon.

3. Click the History tab, located between the command editor and the display pane.

   The list of commands in History appears in the display pane.

4. Click the partial command displayed in the SQL column.

   The command appears in the editor.

About the History Pane

The History pane displays a list of commands you have executed.

Figure 20-4 History Pane

Description of the illustration proc_history.gif

Each history entry shows the time the command was last executed, the first characters of the command, and the schema in which it was executed.

On the History pane you can:

• Load a command. Click the partial command displayed in the SQL column to load the command into the command editor. When the command loads, it also sets the schema in which it was last executed.

• Sort by time. Click the Time column heading to sort the command history by least recent or most recent.

Displaying Results

When you execute a SQL command, the results are displayed. The results of the last executed command are available until you execute another SQL command, or leave the SQL Command Processor.

Figure 20-5 Results Pane

Description of the illustration proc_results.gif

To display SQL command results:

1. Click the SQL Workshop icon on the Workspace home page.

2. Click the SQL Commands icon.

3. Click the Results tab which is located between the command editor and the display pane.

   The HTML formatted results appear in the display pane.

4. Click DBMS Output to display plain text DBMS output results.

   The DBMS Output control only appears if there is DBMS output in addition to HTML formatted results. It does not appear if there is only DBMS output, or if there is only HTML formatted output.

About the Results Pane

The Results pane displays SQL command results as HTML formatted table. The number of rows returned appears at the end of the output, and the time taken. DBMS output appears as plain text after the HTML formatted results.

On the Results pane you can:

• Display DBMS output. Click DBMS Output at the bottom of the displayed results to display lines of DBMS output. This control only appears when there is DBMS output to display.

• Export results. Click CSV Export to export results to a comma separated file on your local file system. You are prompted to enter a name and directory for the file.

Using Explain Plan

You can view the explain plan the Oracle Optimizer uses to run your SQL command. You do not need to execute the command to view the explain plan.

Figure 20-6 Explain Plan Pane

Description of the illustration explaintab.gif

Topics in this section include:

• Viewing an Explain Plan

• About Explain Plan Pane

Viewing an Explain Plan

To view the Explain Plan:

1. Click the SQL Workshop icon on the Workspace home page.

2. Click the SQL Commands icon.

3. Enter or load the command whose plan you want to view.

4. Click Explain which is located between the command editor and the display pane.

   The explain plan used by the optimizer appears in the display pane.

About Explain Plan Pane

The Explain Plan pane shows the plan used by the Oracle Optimizer to run your SQL command. It typically displays the Query Plan, Index Columns and Table Columns used.

On the Explain Plan pane you can:

• View object definitions. Click the object name in Query Plan to display the object definition in the Object Browser.

• View index definitions. Click the index name in Table Columns to display the index definition in the Object Browser.