Using the Command Editor

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

• Running a SQL Command
  Enter the SQL command you want to run in the command editor and click Run.
• About Transactions in SQL Commands
  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.
• Disabling Transactional SQL Commands
  Check the Autocommit check box to disable transactional SQL commands.
• Enabling Transactional SQL Commands
  Uncheck the Autocommit check box to enable the transactional SQL commands.
• About Unsupported SQL*Plus Commands
  SQL Commands does not support SQL*Plus commands.
• Terminating a Command
  You can terminate a command in SQL Commands using a semicolon (;), a slash (/), or with nothing.
• Using Bind Variables
  Enter values for bind variables during command execution.
• Using the Find Tables Button
  Use the Find Tables button to view tables within the currently selected schema.

See Also:

"About the SQL Commands Home Page"

Running a SQL Command

Enter the SQL command you want to run in the command editor and click Run.

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), click the Download link.

About Transactions in SQL Commands

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 SQL Commands terminates and rolls back the current transaction.

• A session timeout terminates and rolls back the current transaction.

  Note that the Environment Setting, SQL Commands Maximum Inactivity in minutes, sets the time before an inactive session times out. The default timeout is 60 minutes.

• The CSV Export option is not available.

See Also:

"Configuring SQL Workshop"in Oracle Application Express Administration Guide

Disabling Transactional SQL Commands

Check the Autocommit check box to disable transactional 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.

Enabling Transactional SQL Commands

Uncheck the Autocommit check box to enable the transactional SQL commands.

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.

About Unsupported SQL*Plus Commands

SQL Commands does not support SQL*Plus commands.

If you attempt to enter an SQL Command Line command such as SET ECHO or DEFINE in SQL Commands, an error message displays.

Terminating a Command

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

Enter values for bind variables during command execution.

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

Using the Find Tables Button

Use the Find Tables button to view tables within the currently selected schema.

To view tables within the current schema:

1. Navigate to SQL Commands.
2. From the Schema list, select a schema (optional).
3. Click Find Tables.

   The Table Finder appears. A search bar displaying the selected schema displays at the top of the page and contains the following controls:

   • Search. Search for a table name. Enter case insensitive keywords in the Search field and click Go. To view all tables, leave the Search field blank and click Go.

   • Views. Select the Views check box and click Go to include views in the resulting report.

   • Rows. Determine how many rows display in the resulting report. To change the number of rows that display, make a selection from the list and click Go.

   The Table Finder report appears displaying the table name, the number of rows, last analyzed date, and the object type.

4. Select a table name.

   The Table Finder report appears.

   This report displays the column names, data type, length, precision, scale and the SQL necessary to re-create the table that appears at the bottom of the page.

5. Click Append to append SQL from the page to the existing command in the command editor.
6. Click Replace to replace existing command in the command editor with the SQL from the page.