Skip Headers
Oracle® Application Express SQL Workshop and Utilities Guide
Release 3.2

Part Number E12511-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

3 Using SQL Commands

This section provides information on how to use SQL Commands to create, edit, view, run, and delete SQL commands.

This section contains the following topics:

See Also:

What is SQL Commands?

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

When using SQL Commands, remember the following:

Accessing SQL Commands

To access SQL Commands:

  1. Log in to the Workspace home page.

    The Workspace home page appears.

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

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

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

    Description of sql_commands.gif follows
    Description of the illustration sql_commands.gif

    Note:

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

About the SQL Commands Home Page

The SQL Commands home page is divided into two sections: a command editor and a display pane. You use the command editor to execute SQL commands and the display pane to view output, saved command lists, and history lists.

Description of sql_com_top.gif follows
Description of the illustration sql_com_top.gif

The top of the SQL Commands home page features a command editor and the following controls:

Selecting a Schema

A schema is a logical container for database objects. To access objects in another schema, make a selection from the Schema list in the upper right side of the page.

Switching to Another SQL Workshop Component

You can navigate to another SQL Workshop component by making a selection from the Component list located on the upper right side of the page:

About the Display Pane

A display pane displays at the bottom of the SQL Commands home page.

Description of sql_com_bottom.gif follows
Description of the illustration sql_com_bottom.gif

The display pane features five tabs:

Using the Command Editor

You use the command editor in SQL Commands to execute SQL commands within Application Express.

Topics in this section include:

Running a SQL Command

To execute a SQL Command:

  1. On the Workspace home page, click SQL Workshop and then SQL Commands.

    The SQL Commands page appears.

  2. Enter the SQL command you want to run in the command editor.

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

    Tip:

    To execute a specific statement, select the statement you want to run and click Run.

    The results appear in the Results pane.

  4. To export the resulting report as a comma-delimited file (.csv) file, click the Download link.

About Transactions in SQL Commands

To disable transactional SQL commands in SQL Commands, 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. Oracle Application Express 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:

About Unsupported SQL*Plus Commands

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

About Command Termination

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

SELECT * from emp;
SELECT * from emp
/
SELECT * from emp

The first example demonstrates the use of a semicolon (;), the second example demonstrates using a 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 Application Express, 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 SQL Commands.

To save a SQL command:

  1. On the Workspace home page, click SQL Workshop and then SQL Commands.

    The SQL Commands page appears.

  2. Enter the command in the command editor.

  3. Click Save to save the command.

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

  4. 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. On the Workspace home page, click SQL Workshop and then SQL Commands.

    The SQL Commands page appears.

  2. Click the Saved SQL tab.

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

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

  4. Click Save to save the command.

  5. Enter a 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

To access saved SQL commands:

  1. On the Workspace home page, click SQL Workshop and then SQL Commands.

    The SQL Commands page appears.

  2. Click the Saved SQL tab.

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

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

    The command appears in the editor.

  4. Click Run to execute the command.

About the Saved SQL Pane

The Saved SQL pane displays a list of all commands saved under the current workspace. The list displays commands saved from SQL Commands and SQL commands saved from Query Builder. Saved SQL commands must have unique names in the current workspace. The same name cannot be used in the Query Builder and SQL Commands.

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.

Description of proc_savedsql.gif follows
Description of the illustration proc_savedsql.gif

On the Saved SQL pane you can:

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 SQL Commands.

Topics in this section include:

Accessing a Command from Command History

To access history commands:

  1. On the Workspace home page, click SQL Workshop and then SQL Commands.

    The SQL Commands page appears.

  2. Click the History tab.

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

  3. 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.

Description of proc_history.gif follows
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:

Viewing 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 SQL Commands.

Topics in this section include:

Accessing the Results Pane

To display SQL command results:

  1. On the Workspace home page, click SQL Workshop and then SQL Commands.

    The SQL Commands page appears.

  2. Click the Results tab.

    Description of proc_results.gif follows
    Description of the illustration proc_results.gif

    The HTML formatted results appear in the display pane.

  3. 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 after the output, and the time taken. DBMS output appears as plain text after the HTML formatted results.

On the Results pane you can:

Using Explain Plan

You can view the explain plan the Oracle Optimizer uses to run your SQL command. It is not necessary to execute the command to view the explain plan.

Description of explaintab.gif follows
Description of the illustration explaintab.gif

Topics in this section include:

Viewing an Explain Plan

To view the Explain Plan:

  1. On the Workspace home page, click SQL Workshop and then SQL Commands.

    The SQL Commands page appears.

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

  3. Click the Explain tab.

    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: