Oracle Enterprise Manager Concepts Guide

CHAPTER 13. Using the SQL Worksheet

This chapter describes how to use the SQL Worksheet, which is part of Oracle Enterprise Manager. It explains how to:

Overview of the SQL Worksheet

Executing Commands in the SQL Worksheet

Using the Command History

Saving your work

Running Scripts from the SQL Worksheet

Overview of the SQL Worksheet

Using the SQL Worksheet, you can enter, edit, and execute SQL and PL/SQL code. You can also execute the Server Manager DBA commands described in Appendix B, "DBA Command Reference." You can also run scripts from the SQL Worksheet. The SQL Worksheet maintains a history of the commands you have issued, so you can easily retrieve and re-execute previous commands.

Starting a SQL Worksheet

You can start a SQL Worksheet from the Enterprise Manager Console window by choosing SQL Worksheet from the Tools menu. The new worksheet is connected to the database you have selected in the Navigator tree list or in the Map window.

If you start the SQL Worksheet from the Windows Program Manager, a database Login dialog box appears.

Note: If you connect as INTERNAL to a database that is currently shut down, the following error message appears in the SQL Worksheet output pane upon startup: Cannot obtain ORACLE version number. This message is caused by a minor system interface discrepancy and will not affect SQL Worksheet operation or the database connection.

Using the SQL Worksheet

A SQL Worksheet window consists of the following elements:

Input pane

Output pane

Split bar

Input pane toolbar

New Worksheet

Open worksheet

Save worksheet as

Execute

Command history

Previous command

Next command

Output pane toolbar

Worksheet pull-down menu

The following figure illustrates a SQL Worksheet window.

Figure 13 - 1. SQL Worksheet Window

The SQL Worksheet window is described below:

Output Pane Upper window pane. Displays recently executed commands and output. SQL Worksheet stores the last 32 KB of output for display in the output pane. The actual amount stored varies depending upon the amount of system memory available.
Input Pane Lower window pane. Enter and edit commands in the input pane.
Split Bar Adjusts the relative sizes of the panes. Drag the split bar vertically to resize the panes.
Output Pane Toolbar Icon provides access to the Save Output As menu option.
Input Pane Toolbar Provides access to the following operations:
Creates a new worksheet.
Imports the contents of an existing SQL script.
Saves the contents of the input pane to a file
Executes the command in the input pane. See "Executing Commands in the SQL Worksheet" for information about executing commands in a SQL Worksheet.
Displays the Command History dialog box, which displays previously executed commands. See "Using the Command History" for information about using the command history.

The File Menu

The File menu items are described below:

Change Database Connection Displays the Connect dialog box to connect to an instance. To connect to multiple databases concurrently, you need to launch the application multiple times from the console. See Entering Login Information.
New Clears the contents of the input pane. You are prompted to save your existing work.
Open Displays the Open Worksheet dialog box to allows you to open an existing .SQL or ascii text file. The contents of the file appear in the input pane.
Save Saves the contents of the input pane to the current file.
Save As Saves the contents of the input pane to a file other than the one that is currently open.
Print Prints the contents of the input pane.
Save Output As Saves the contents of the output pane to a file.
Print Output Prints the contents of the output pane.
Print Setup Displays the Print Setup dialog box allowing you to set print parameters and choose a local printer.
Exit Quits the SQL Worksheet.

The Edit Menu

The Edit menu items are described below:.

Cut Cuts selected text.
Copy Copies selected text.
Paste Pastes selected text at the location of the cursor.
Delete Deletes selected text and selected objects in lists.
Select All Selects all the text in a document or selects all objects in a list.

The Worksheet Menu

The Worksheet menu items are described below:

Execute Executes the commands in the input pane. Equivalent to the Execute button or the Enter key.
See "Executing Commands from the SQL Worksheet" for information about executing commands in a SQL Worksheet.
Run Script Allows you to select and execute a script. See "Running Scripts from the SQL Worksheet" for information about running scripts from a SQL Worksheet.
Spool Writes output to a specified file. See "Saving Your Work" for information about spooling the output of a SQL Worksheet.
Command History Displays the Command History dialog box, which displays previously executed commands. Equivalent to the Command History button. See "Using the Command History" for information about using the command history.
Previous Command Retrieves the previous command from the command history and enters it in the input pane. The retrieved command replaces the entire contents of the input pane.
You can execute Previous Command repeatedly to work back through the command history. Previous Command is unavailable if you have currently retrieved the oldest command from the command history.
Next Command Retrieves the next command from the command history and enters it in the input pane. The retrieved command replaces the entire contents of the input pane.
Next Command is unavailable if you have currently retrieved the most recent command from the command history.

Suggestion: Use the keyboard shortcuts to quickly access and execute commands from the command history.

Executing Commands in the SQL Worksheet

Enterprise Manager executes commands you have entered in the input pane of a SQL Worksheet.

Entering Commands in the Input Pane

You can enter SQL, PL/SQL, and Server Manager DBA commands in the input pane of a SQL Worksheet. A command entered in the input pane can contain tabs or carriage returns.

All standard text editing capabilities are available in the input pane of a SQL Worksheet. You can edit text in the input pane using the mouse, keyboard functions, and the Edit menu. In addition, you can select text from the output pane and paste it into the input pane.

Executing Commands in the Input Pane

To execute the command you have entered in the input pane, click the Execute icon in the input pane toolbar. You can also execute the command by choosing Execute from the Worksheet menu.

When you execute a command, the command and its output appear in the output pane. However, if you set the parameter ECHO to off, only the output of the command is displayed. For information about setting SQL Worksheet parameters, see the SET command in Appendix B, "DBA Command Reference."

The following figure illustrates executing a SQL statement in a worksheet.

Figure 13 - 2. Executing a Command in the SQL Worksheet

After you execute a command, the command remains in the input pane, but is selected, as shown in Figure 13 - 2. You can then type in the next command, and it replaces the previous command.

Executing Server Manager DBA Commands

In a SQL Worksheet you can execute the Server Manager DBA commands described in Appendix B, "DBA Command Reference." Some of these commands behave differently when executed in a SQL Worksheet than when executed in line mode. These commands are summarized below:

CONNECT Displays the Connect dialog box when you issue the CONNECT command without specifying complete connect information. For information about connecting to an instance of a database, see "Connecting to an Instance" .
RECOVER If recovery is needed, displays the Recover dialog box when you issue the RECOVER command without arguments. Otherwise, an error message is generated.
EXIT Closes the worksheet.

Note: The line mode command HOST is not available in the SQL Worksheet.

Connecting from a Worksheet

When you start a SQL Worksheet from the Enterprise Manager console, you are automatically connected to the same database as the console. You can change this connection by issuing the CONNECT command from the input pane of the worksheet. The title of the SQL Worksheet window also changes to reflect your new connection.

If you execute the CONNECT command from the input pane without any arguments or select Change Database Connection from the File menu, the Connect dialog box appears. You can use the Connect dialog box to specify a new connection for that worksheet. For information about connecting to an instance of a database, see "Connecting to an Instance" . You can also specify the connection using arguments in the CONNECT command. For a description of the CONNECT command and its syntax, see Appendix B, "DBA Command Reference."

Performing Recovery from the SQL Worksheet

When you issue the RECOVER command from a SQL Worksheet, the Recovery dialog box appears, if recovery is needed. The following figure illustrates the Recovery dialog box.

Figure 13 - 3. Recovery Dialog Box

From the Recovery dialog box, you can apply the next log file, apply all the log files, or cancel recovery. You can specify the name of the log file to apply or apply the default.

Using the Command History

A SQL Worksheet maintains a history of the last 20 command executions you have issued in that worksheet. To bring up the Command History dialog box, click Command History or choose Command History from the Worksheet menu.

Note: Command executions larger than 100K are not recorded in the Command History.

The Command History dialog box contains a scrolling list of the commands in the worksheet's command history. The following figure illustrates the Command History dialog box.

Figure 13 - 4. Command History Dialog Box

The Command History dialog box is described below:

Command List Displays an abbreviated list of the last 20 command exectutions. Explicit SQL commands associated with each command execution are displayed in the Command Text pane.
Command Text Pane Displays all SQL commands associated with the selected entry in the Command List.
Close Closes the dialog box without retrieving any text.
Get Retrieves the selected item in the Command List command. See "Retrieving a Selection from the Command History" for more information about retrieving commands.
Help Displays help information for the Command History dialog box.

Retrieving a Selection from the Command History

To retrieve a command from the Command History dialog box, select the desired command execution entry in the Command List. All SQL commands associated with this entry are displayed in the Command Text pane. Click Get. The contents of the Command Text pane appear in the input pane replacing any existing text.

You can also drag and drop commands directly into the input pane by selecting an entry in the Command List and dropping it into the input pane. The text associated with the entry appears in the input pane.

To retrieve only a portion of a command execution from the Command Text pane, select the text you wish to retrieve and press Ctrl-C (copy). Move the cursor to the worksheet's input pane, place the cursor at the position where you want the text inseerted and press Ctrl-V (paste).

If no text is selected in the input pane, the copied text is inserted at the cursor position. If you have selected text in the input pane, then the retrieved text replaces the selected text.

After you have retrieved text from the command history, you can edit or execute the command, as desired.

Retrieving a Command Using the Worksheet Menu

You can also retrieve command executions without using the Command History dialog box. The Previous Command and Next Command menu items and input toolbar icons retrieve commands from the command history and enter them in the input pane, completely replacing the existing contents of the input pane. For more information on the Previous Command and Next Command menu items, see "The Worksheet Menu"

Saving Your Work

The Spool menu item, Save Output As icon, and Save Worksheet As icon allow you to save the work you have performed in a SQL Worksheet. Each of these Worksheet menu commands displays the standard file selection dialog box, which you can use to specify the file in which to save your work.

The Spool menu item allows you to dynamically save all output of your worksheet session to a specified file. SQL Worksheet continues to save the output of your worksheet until you choose Stop Spooling from the menu or when you exit the SQL Worksheet.

The Save Output As allows you to write the current contents of your worksheet's output pane to a specified file. The output pane normally contains the last 32 KB of output and the last 100 lines of text. The actual amount may vary according to the amount of available system memory. Any text that has scrolled out of the output pane is also written to the file.

The Save Worksheet As menu item and icon (located in the input pane toolbar) writes the contents of the worksheet's input pane to a specified file. .

Note: The SQL Worksheet appends a file extension, such as .log or .txt, to the name of each file created by the Spool, Save Output As, and Save Worksheet As commands.

Running Scripts from the SQL Worksheet

From a SQL Worksheet, you can run any script that contains SQL, PL/SQL, or Server Manager DBA commands. There are four ways to run scripts from a SQL Worksheet:

Clicking the Open Worksheet Icon from the input pane toolbar

Choosing Run Script from the Worksheet menu

Draging and dropping a SQL script from the Windows NT file manager into the worksheet's input pane.

Executing the Server Manager DBA @ command from the input pane

Clicking the Open Worksheet icon in the input pane toolbar or selecting the Open menu item displays the standard file selection dialog box. After you select the file and click OK, the content of the SQL script appears in the worksheet's input pane. Click the Execute icon or select the Execute menu item to run the script.

Selecting the Run Script menu item also displays the Run Script dialog box. Select the desired script and click OK. The Server Manager DBA @ command appears with the full path and filename of the selected script in the input pane. The script is run automatically.

Figure 13 - 5. Running a Script from the Input Pane

To drag and drop scripts from the Windows file manager, select the desired script in the file manager and drop it into the input pane. The content of the script appears in the input pane. Click the Execute icon to run the script.

You can also run a script from the input pane of a worksheet by entering an @ command followed by the script name. The following figure illustrates running a script from the input pane of a worksheet.

Note: In a script you can run other scripts. In the original script you can include the line @@second_scriptname to run another script called second_scriptname. The @@ command indicates that the second script is in the same directory as the original script.

<Oracle Enterprise Manager Concepts GuideOracle Enterprise Manager Concepts Guide

Output Pane	Upper window pane. Displays recently executed commands and output. SQL Worksheet stores the last 32 KB of output for display in the output pane. The actual amount stored varies depending upon the amount of system memory available.
Input Pane	Lower window pane. Enter and edit commands in the input pane.
Split Bar	Adjusts the relative sizes of the panes. Drag the split bar vertically to resize the panes.
Output Pane Toolbar	Icon provides access to the Save Output As menu option.
Input Pane Toolbar	Provides access to the following operations:
	Creates a new worksheet.
	Imports the contents of an existing SQL script.
	Saves the contents of the input pane to a file
	Executes the command in the input pane. See "Executing Commands in the SQL Worksheet" for information about executing commands in a SQL Worksheet.
	Displays the Command History dialog box, which displays previously executed commands. See "Using the Command History" for information about using the command history.

Change Database Connection	Displays the Connect dialog box to connect to an instance. To connect to multiple databases concurrently, you need to launch the application multiple times from the console. See Entering Login Information.
New	Clears the contents of the input pane. You are prompted to save your existing work.
Open	Displays the Open Worksheet dialog box to allows you to open an existing .SQL or ascii text file. The contents of the file appear in the input pane.
Save	Saves the contents of the input pane to the current file.
Save As	Saves the contents of the input pane to a file other than the one that is currently open.
Print	Prints the contents of the input pane.
Save Output As	Saves the contents of the output pane to a file.
Print Output	Prints the contents of the output pane.
Print Setup	Displays the Print Setup dialog box allowing you to set print parameters and choose a local printer.
Exit	Quits the SQL Worksheet.

Cut	Cuts selected text.
Copy	Copies selected text.
Paste	Pastes selected text at the location of the cursor.
Delete	Deletes selected text and selected objects in lists.
Select All	Selects all the text in a document or selects all objects in a list.

Execute	Executes the commands in the input pane. Equivalent to the Execute button or the Enter key.
	See "Executing Commands from the SQL Worksheet" for information about executing commands in a SQL Worksheet.
Run Script	Allows you to select and execute a script. See "Running Scripts from the SQL Worksheet" for information about running scripts from a SQL Worksheet.
Spool	Writes output to a specified file. See "Saving Your Work" for information about spooling the output of a SQL Worksheet.
Command History	Displays the Command History dialog box, which displays previously executed commands. Equivalent to the Command History button. See "Using the Command History" for information about using the command history.
Previous Command	Retrieves the previous command from the command history and enters it in the input pane. The retrieved command replaces the entire contents of the input pane.
	You can execute Previous Command repeatedly to work back through the command history. Previous Command is unavailable if you have currently retrieved the oldest command from the command history.
Next Command	Retrieves the next command from the command history and enters it in the input pane. The retrieved command replaces the entire contents of the input pane.
	Next Command is unavailable if you have currently retrieved the most recent command from the command history.

CONNECT	Displays the Connect dialog box when you issue the CONNECT command without specifying complete connect information. For information about connecting to an instance of a database, see "Connecting to an Instance" .
RECOVER	If recovery is needed, displays the Recover dialog box when you issue the RECOVER command without arguments. Otherwise, an error message is generated.
EXIT	Closes the worksheet.

Command List	Displays an abbreviated list of the last 20 command exectutions. Explicit SQL commands associated with each command execution are displayed in the Command Text pane.
Command Text Pane	Displays all SQL commands associated with the selected entry in the Command List.
Close	Closes the dialog box without retrieving any text.
Get	Retrieves the selected item in the Command List command. See "Retrieving a Selection from the Command History" for more information about retrieving commands.
Help	Displays help information for the Command History dialog box.