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:

Click the SQL Workshop icon on the Workspace home page.
Click the SQL Commands icon.
Enter the SQL command you want to run in the SQL editor pane.
Make a selection from the Schema list to specify the Database schema in which to execute the SQL command.
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.

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;

SELECT * from emp
/

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