3
Using SQL*Plus

This chapter explains how to start and use SQL*Plus from both the command-line interface and the graphical user interface, and describes the graphical user interface menu options. It also describes how to use and configure the iSQL*Plus Extension for Windows.

Specific topics discussed are:

• Using the Command-line Interface
  
  
• Using the Graphical User Interface
  
  
• Exiting SQL*Plus
  
  
• iSQL*Plus Extension for Windows

Using the Command-line Interface

The SQL*Plus command-line interface is standard on all operating systems.

If you are connecting to a remote Oracle database, make sure your Oracle Net software is installed and working properly. For more information, see the Oracle9i Net Services Administrator's Guide and the Oracle9i Database Administrator's Guide for Windows.

To start the SQL*Plus command-line interface:

1. Select Command Prompt from Programs (Programs/Accessories in Windows XP/2000) in the Start menu. A Command Prompt window appears.

   

   Text description of the illustration prompt.gif

2. Start SQL*Plus by entering
   
   

   C:\> SQLPLUS
   

   You can optionally include your login username and password separated by a slash (/), and a remote database name to connect to, for example:

   C:\> SQLPLUS username/password@connect_identifier
   

   Otherwise you will be prompted to enter your username and password.

Interpreting Arguments in Windows

The rules for interpreting arguments in Windows commands are:

• Arguments are delimited by white space.
  
  
• A string surrounded by double quotation marks, for example, "this string", is interpreted as a single argument.
  
  
• A double quotation mark preceded by a backslash is interpreted as a literal double quotation mark.

Example

The following SET MARKUP command illustrates white space to separate arguments, the single string argument for HEAD surrounded by single quotes, and the escaped double quotes around Employees so that the characters are printed and not interpreted as string delimiters.

SET MARKUP HTML HEAD '<TITLE>Annual Report "Employees"</TITLE>'

For more information about SQL*Plus command-line arguments, see the SQL*Plus User's Guide and Reference.

Changing the Command-line Font and Font Size

You can use the Windows Command Prompt Properties dialog to set the font and font size used in the SQL*Plus command-line interface.

To Change the Command-line Interface Font and Font Size

1. Right click in the command-line interface title bar.
   
   
2. Click Properties. The Window Preview box displays the current window's relative size on your monitor based on your font and font size selections. The Selected Font: box displays a sample of the current font.
   
   
3. Click the Font tab.
   
   
4. Select the font size to use from the Size box. Raster font sizes are shown as width by height in pixels. TrueType font sizes are shown as height in pixels.
   
   
5. Select the font to use from the Font box.
   
   
6. Select the Bold Fonts check box if you want to use a bold version of the font.

For more information about changing Windows Command Prompt properties, see Windows Help or click Help in the Command Prompt Properties dialog. For information about changing font face and size in the Windows Graphical User Interface (GUI), see "Changing the GUI Font and Font Size" later in this chapter.

Using a Special Character

To check if a font contains a particular character, for example, the Euro sign, enter the character's decimal number equivalent in the SQL*Plus command-line interface. For example, the decimal number equivalent for the Euro sign is 128, so you would enter Alt+0128 (hold Alt while pressing 0, 1, 2 and 8) to display it. If it appears correctly, the font contains the Euro sign, otherwise you need to try another font.

You can use the Windows Character Map utility to view the characters available in a font. Character Map also shows the decimal number equivalent for extended ASCII characters. You access the Character Map utility by selecting Start, Programs, Accessories and then clicking Character Map.

Using the Graphical User Interface

While the command-line interface is a standard feature of SQL*Plus on all operating system platforms, the graphical user interface is a feature of SQL*Plus only available in Windows. The Windows Graphical User Interface will be obsoleted in favor of the iSQL*Plus browser-based user interface in future releases of SQL*Plus.

If you are connecting to a remote Oracle database, make sure your Oracle Net software is installed and working properly. For more information, see the Oracle9i Net Services Administrator's Guide and the Oracle9i Database Administrator's Guide for Windows.

The graphical user interface can be started from the Windows menus, or from a Windows command prompt.

Starting the GUI from the Windows Menu

1. Select Programs in the Start menu. Then select Oracle - ORACLE_HOME, then Application Development, and click SQL Plus.

   The SQL*Plus window appears displaying the Log On dialog.

   

   Text description of the illustration logon.gif

   Enter a valid user name and password. If you are connecting to a remote Oracle database, enter the Oracle Net connect_identifier in the Host String field. To connect to the default database, leave the Host String field blank. See the Oracle9i Net Services Administrator's Guide for more information about configuring and using Oracle Net connect_identifiers.

2. Click OK.

Starting the GUI from the Windows Command Prompt

1. Select Command Prompt from Programs (Programs/Accessories in Windows XP/2000) in the Start menu. A Command Prompt window appears.
   
   
2. Enter
   
   

   C:\> SQLPLUSW
   

   The SQL*Plus graphical user interface starts. The -LOGON option is not supported in the Windows GUI. You can optionally include your login username and password separated by a slash (/), and a remote database to connect to, for example:

   C:\> SQLPLUSW username/password@connect_identifier
   

   Otherwise enter the required information in the login dialog as described earlier. The Oracle SQL*Plus application window appears.

   

   Text description of the illustration 34.gif

Using the SQL*Plus Application Window

SQL*Plus displays the SQL command prompt in the application window.

To enter SQL and SQL*Plus commands, type each command at the SQL*Plus command prompt and press Enter.

Using the Mouse Buttons to Copy Text to the Command Prompt

You can use the mouse buttons to copy text from anywhere in the SQL*Plus interface to the SQL*Plus prompt in the same interface.

To copy text, select the text by clicking and dragging with the left mouse button. While still holding down the left mouse button, click the right mouse button. SQL*Plus copies the selected text to the SQL*Plus prompt.

Using the Command Keys

The following command keys have special functions in SQL*Plus:

Key	Function
Home	Top of screen buffer
End	Bottom of screen buffer
Page Up	Previous screen page
Page Down	Next screen page
Ctrl+Page Up	Show page on left of current screen page
Ctrl+Page Down	Show page on right of current screen page
Alt+F3	Find
F3	Find next
Ctrl+C	Cancels an operation running in SQL*Plus.
Ctrl+C	Copies text - when no operations are running in SQL*Plus.
Ctrl+V	Paste text
Shift+Del	Clear the screen and the screen buffer

:

Using the SQL*Plus Menus

This section describes menus in the SQL*Plus graphical user interface (GUI). Bracketed entries show keyboard shortcuts to that File Menu command. The rightmost column shows the equivalent command-line interface command where applicable.

File Menu

The File menu has the following options:

Option	Description of File Menu Option	Command-line
Open	The Open option retrieves a previously stored command file. If you supply no file extension, SQL*Plus looks for command files with the .SQL extension. The command file should contain a single SQL or PL/SQL statement. It should not contain multiple statements or SQL*Plus commands.	GET filename
Save	The Save option has three alternatives: Save Create, Save Replace, and Save Append.	SAVE
	Save Create saves the contents of the SQL*Plus buffer in a command file. By default, SQL*Plus assigns the .SQL extension to command files. You can specify a different extension in the File name text box. Save Replace replaces the contents of an existing file with the contents of the SQL*Plus buffer. SQL*Plus creates the file if it does not exist. Save Append adds the contents of the SQL*Plus buffer to the end of the file you specify.	SAVE filename CREATE SAVE filename REPLACE SAVE filename APPEND
	After you save a command file, you can: Retrieve the file using the Open option on the File menu. Edit the file using the Editor option on the Edit menu. Run the file using the START or RUN commands from the SQL*Plus command prompt.
Save As	The Save As option saves the contents of the SQL*Plus buffer in a command file. By default, SQL*Plus assigns the .SQL extension to command files. You can specify a different extension in the File name text box.	SAVE filename
Spool	The Spool option has two alternatives: Spool File and Spool Off. SQL*Plus for Windows does not support the SPOOL OUT clause.
	Spool File stores query results in a file. By default, SQL*Plus assigns the .LST extension to spool files. You can specify a different extension in the File name text box. You can edit the results with the Editor option on the Edit menu, and print the file from a Windows text editor. Spool Off turns off spooling.	SPOOL filename SPOOL OFF
Run	The Run option lists and executes the SQL command or PL/SQL block currently stored in the SQL buffer.	RUN
Cancel (Ctrl+C)	The Cancel option cancels an in-progress operation. The Cancel keyboard shortcut is only available when a SQL*Plus operation is running in the SQL*Plus session. When no SQL*Plus operation is running, Ctrl+C copies selected text.	Ctrl-C
Exit	The Exit option commits all pending database changes and closes the SQL*Plus application window.	EXIT

Edit Menu

The Edit menu has the following options:

Option	Description of Edit Menu Option	Command-line
Copy (Ctrl+C)	The Copy option copies selected text to the Clipboard. After you copy text to the Clipboard, you can paste the text into other Windows applications, such as Microsoft Excel and Microsoft Word. The Copy keyboard shortcut is only available when no SQL*Plus operations are running in the SQL*Plus session. When a SQL*Plus operation is running, Ctrl+C cancels the running operation.	not applicable
Paste (Ctrl+V)	The Paste option pastes the contents of the Clipboard to the SQL*Plus command-line. Note: A maximum of 3625 characters can be pasted from the Clipboard to the SQL*Plus command-line during a single paste operation.	not applicable
Clear (Shift+Del)	The Clear option clears the screen buffer and the screen of the SQL*Plus application window.	CLEAR SCREEN
Editor	The Editor option has two alternatives: Invoke Editor and Define Editor.
	Invoke Editor loads the contents of the SQL*Plus buffer into an editor. By default, SQL*Plus saves the file to AFIEDT.BUF. You can specify a different file name in the editor. Define Editor defines the editor that is invoked.	EDIT DEFINE _EDITOR = editor name

Search Menu

The Search menu has the following options:

Option	Description of Search Menu Option	Command-line
Find (Alt+F3)	The Find option searches for a character, a word, or a group of characters or words in the SQL*Plus application window. Find begins the search at the top of the displayed screen. Note: When Find reaches the end of the displayed screen, it does not wrap and continue searching from the top of the screen buffer.	not applicable
Find Next (F3)	The Find Next option finds the next occurrence of the search text.	not applicable

Options Menu

The Options menu has the following options:

Option	Description of Options Menu Option	Command-line
Environment	The Environment option enables you to set system variables to alter the SQL*Plus environment for your current session. This dialog has three areas: Set Options, Value, and Screen Buffer. Note: See "Setting Options and Values Using the Environment Dialog" for examples of how these controls interact.
	Set Options This area has a list of variables you can select to establish aspects of the SQL*Plus environment for your current session, such as: Setting the display width for NUMBER data. Setting the display width for LONG data. Enabling or disabling the printing of column headings. Setting the number of lines in each page. See the "Command Reference" chapter in the SQL*Plus User's Guide and Reference for descriptions of each system variable in the SET command.	SET variable value
	Value The Value area has four options: Default, Custom, On, and Off. Note: When Custom is selected, the On and Off buttons and the text field may or may not be enabled for user selection. The availability of these fields depends on the item selected in the Set Option.	SET variable value
	Screen Buffer This area has two text boxes: Buffer Width and Buffer Length.	SET variable value
	In the Buffer Width text box, you set the number of characters that SQL*Plus displays on one line. If you enter a number smaller than the length of output data, SQL*Plus truncates the data to match the buffer width you specified. The default value of the Buffer Width parameter is 100 characters. You can specify from 80 to 1000 characters on one line. In the Buffer Length text box, you set the number of lines that SQL*Plus displays on the screen. If SQL*Plus displays more lines of data than you specify, the remaining lines of data will "wrap around" to the top of the screen buffer. The default value of the Buffer Length parameter is 1000 lines. You can specify from 100 to 2000 lines on one screen.
	Notes: When you change the Screen Buffer option, SQL*Plus displays a dialog to alert you that if you shorten the size of your screen buffer, some data may not be displayed on your screen. Click OK to proceed.
	If you use SET MARKUP to send output to a HTML table, the number of lines specified in the Buffer Length variable specifies the number of HTML table rows. Each HTML table row may contain more than one text line.

Help Menu

The Help menu has the following option:

Option	Description of Help Menu Option	Command-line
About SQL*Plus	Displays the SQL*Plus version number and copyright information. You access SQL*Plus help from the SQL*Plus prompt. See "Accessing SQL*Plus Help".	not applicable

Setting Options and Values Using the Environment Dialog

Choose Environment from the Options menu to display the Environment dialog which you can use to create a SQL environment statement for the current session.

Choose an item from the Set Options list to begin. You can use the default settings, or you can customize the settings by using the other dialog controls. The available controls vary with the options you choose. You can make multiple changes to options and values. When the text box is available, you can enter appropriate text or appropriate numeric values. Click OK to commit your settings.

Example 3-1

The ARRAYSIZE is set to 15, the default value.

Text description of the illustration 32.gif

Example 3-2

Changing the GUI Font and Font Size

There are three registry entries that set the font and font size used in the SQL*Plus Windows GUI. SQLPLUS_FONT sets the font face, and SQLPLUS_FONT_SIZE sets the font size in pixels. For Windows NT/98, SQLPLUS_FONT_CHARSET sets the font subset to use. If not set, the default font subset is used. It may be important to set the SQLPLUS_FONT_CHARSET registry entry particularly when specifying a font to support a national language. Not all font subsets may contain the characters you expect or want to make available.

You use the Windows Registry Editor to create these three registry entries and define values for them. Ensure that you create the correct entries in uppercase, and that the values (font names, sizes, subset names) you enter are correct. If you enter an incorrect name, the default font Fixedsys, and size 16, are used. If you enter an incorrect font subset name, the default subset is used.

You can choose any fixed-pitch TrueType font available in your Windows system such as Courier New or Lucida Console. If you choose a proportional pitch font such as Arial or Times New Roman, or if you enter an unavailable font, the registry entry is ignored and the default font and size, Fixedsys 16, are used. If you choose an unavailable font size, the default font size, 16, is used. If you choose an unavailable font subset, the default subset for the selected font is used.

If you do not create the SQLPLUS_FONT registry entry, or if you do not specify a value for SQLPLUS_FONT, the default font and size, Fixedsys 16, are used.

If you want to use particular characters, such as the Euro sign, you should make sure that the fixed pitch font you choose contains those characters.

To Change the Windows GUI Font and Font Size

1. Select Run from the Start menu and then enter regedit in the Open field.
   
   
2. Click OK to start the Registry Editor. The Registry Editor is displayed.
   
   
3. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0.

   Note: If you have more than one Oracle installation, you must select the HOME entry associated with the Windows GUI you want to change. HOME0 is the registry entry for an Oracle installation. A subsequent Oracle installation will have the registry entry HOME1 and the next HOME2 and so on. Changes only affect a SQL*Plus Windows GUI started from the associated Oracle installation, so you can use different settings for each Oracle installation.

4. Click New String Value in the Edit menu. A new string value, with the default name, NewValue #1 is created at the bottom of the right pane of the Registry Editor. The default name of the new string value is selected ready for you to replace with the name you want.
   
   
5. Enter SQLPLUS_FONT as the name of the new font face string value. If you miskey the name or inadvertently enter it in mixed or lower case, you can edit the name by selecting Rename from the Edit menu.

   or

   Enter SQLPLUS_FONT_SIZE as the name of the new font size string value. If you miskey the name or inadvertently enter it in mixed or lower case, you can edit the name by selecting Rename from the Edit menu.

   or

   Enter SQLPLUS_FONT_CHARSET as the name of the new font subset string value. If you miskey the name or inadvertently enter it in mixed or lower case, you can edit the name by selecting Rename from the Edit menu.

6. Click Modify from the Edit menu or press Enter again to display the Edit String dialog.
   
   
7. Enter the font name you want to use, such as Courier New, in the Value Data: field. SQL*Plus will use the new font the next time you start the SQL*Plus Windows GUI. The font be a True Type fixed pitch font such as Courier New or Lucida Console.

   or

   Enter the font size you want to use in pixels, such as 14, in the Value Data: field. SQL*Plus will use the new font size the next time you start a SQL*Plus Windows GUI. The size must be a size that exists on the client machine for the specified font)

   or

   For Windows NT/98, enter the font subset you want to use, such as TURKISH, in the Value Data: field. SQL*Plus will use the new font subset the next time you start a SQL*Plus Windows GUI. While there may be other font subsets available if you have installed additional fonts, the following valid values for SQLPLUS_FONT_CHARSET are available:

   DEFAULT
   SYMBOL
   SHIFTJIS
   HANGEUL
   GB2312
   CHINESEBIG5
   OEM
   JOHAB
   HEBREW
   ARABIC
   GREEK
   TURKISH
   VIETNAMESE
   THAI
   EASTEUROPE
   RUSSIAN
   MAC
   BALTIC

   Note: You should not change the font face, font size or font subset while any SQL*Plus Windows GUI is active. You should exit all SQL*Plus Windows GUI sessions, make font face, font size and font subset changes in the registry, exit the Registry Editor and then restart the SQL*Plus Windows GUI to see the changes.

Using a Special Character

To check if a font contains a particular character such as the Euro sign, enter the character's decimal number equivalent in the SQL*Plus Windows GUI. For example, the decimal number equivalent for the Euro sign is 128, so you would enter Alt+0128 (hold Alt while pressing 0, 1, 2 and 8) to display it. If it appears correctly, the font contains the Euro sign, otherwise you need to try another font.

You can also use the Windows Character Map accessory to view the characters available in a font. Character Map also shows the decimal number equivalent for extended ASCII characters. You access the Character Map accessory by selecting Start, Programs, Accessories and then Character Map.

Exiting SQL*Plus

You enter EXIT or QUIT at the SQL*Plus prompt to exit SQL*Plus from the command-line interface or from the GUI.

You return to the Windows Command Prompt when you exit either the command-line interface, or the GUI if you started it from the command-line with SQLPLUSW.

Click Exit from the File menu to exit from the GUI. The GUI closes and you return to Windows when you exit SQL*Plus from the GUI.

iSQL*Plus Extension for Windows

The iSQL*Plus Extension for Windows provides a context menu to load or execute local SQL scripts in iSQL*Plus. The iSQL*Plus Extension for Windows works with .SQL files and is activated by right clicking a .SQL file in Windows Explorer.

The utility is installed during Oracle9i Database and Oracle9i Client for Windows installations. After installation, you can configure access to one or more iSQL*Plus Servers. For more information about the iSQL*Plus User Interface, see the SQL*Plus User's Guide and Reference.

Context Menu

After you have configured an iSQL*Plus Server, you can use the context menu to:

• Run or load local scripts accessible from your workstation
  
  
• Select the server to use from a list of configured iSQL*Plus Servers
  
  
• Choose the double click file association for .SQL files
  
  
• Choose the language to use from a list of configured languages

The following shows a typical view of the context menu with iSQL*Plus Servers configured.

Text description of the illustration isenew.gif

The highlighted iSQL*Plus Server is configured to start iSQL*Plus in a browser and load MIS_Reports.sql into the Input area of the iSQL*Plus Server named Employee_LA.

Configuring the iSQL*Plus Extension

The iSQL*Plus Extension for Windows is accessed through extended menus in Windows platforms. The iSQL*Plus Extension for Windows extended menu is accessed by right clicking files with the .SQL file extension.

If you have installed the Oracle9i Server, iSQL*Plus Server entries for your Oracle HTTP Server are created by default. If you have installed the Oracle9i Client, no iSQL*Plus Server entries are created. You can use the Configuration dialog to configure the available iSQL*Plus Servers that you want.

The Configuration Dialog

Open the Configuration dialog by right clicking a .SQL file in Windows Explorer, then select iSQL*Plus Servers, and then Configure. The Configure iSQL*Plus Servers dialog is displayed.

When the column is narrower than the field it contains, tool tips show the full text in the field, but to edit or copy a field, make sure that the column width is wide enough to display the full field. You can widen the column by selecting and dragging the heading column separator.

You can sort the list of iSQL*Plus Servers in ascending or descending field name order by clicking the field name column heading. You can also manually sort the server definitions by using the up and down arrow buttons on the right side of the list of iSQL*Plus Servers.

Text description of the illustration iseconf.gif

URL

Enter a valid iSQL*Plus Server URL in the form:

http://host.domain/isqlplus

This field is mandatory.

Name

Enter a name for this iSQL*Plus Server. The name, if entered, is used in the context menu, otherwise the full iSQL*Plus Server URL is used.

Connect String

Enter a connect string to specify your username and password and the database you want to connect to. If you omit a username and password you are prompted for them when iSQL*Plus starts. If you omit a database connection identifier or Oracle Net alias, you are connected to the default database.

Execute

Clear the Execute checkbox to change the action performed on the selected file. The default action is Execute, the alternative is Load. Execute runs the selected file in the iSQL*Plus Server and displays the results in your browser. Load opens the iSQL*Plus Server and loads the selected file into the Input area.

Add

Click Add to add a new iSQL*Plus Server.

Remove

Click Remove to delete a selected iSQL*Plus Server definition.

File Association:

Enter a path and arguments for the application you want to associate with the .SQL file extension when you double click. The default application is Windows Notepad. This definition does not affect the iSQL*Plus Server definitions.

Browse

Click Browse to find an application you want to associate with the .SQL file extension when you double click.

Language:

Select a language you want to use in the iSQL*Plus Extension from the Language box:

Brazilian Portuguese
English
French/Canadian French
German
Italian
Japanese
Korean
Simplified Chinese
Spanish/LA Spanish
Traditional Chinese

This language setting only affects the iSQL*Plus Extension, not iSQL*Plus.

OK

Click OK to save your changes and close the Configure iSQL*Plus Servers dialog.

Cancel

Click Cancel to quit your changes and close the Configure iSQL*Plus Servers dialog. You are prompted once more before your changes are lost.

When you first open the Configure iSQL*Plus Servers dialog, the OK button is unavailable and the Cancel button is labelled Close. Once you make changes, OK is enabled and Close is renamed Cancel.

Adding a Server

You must configure at least one iSQL*Plus Server before you can use the iSQL*Plus Extension. To add an iSQL*Plus Server, perform the following steps:

1. Right click a .SQL file in Windows Explorer.
   
   
2. Select iSQL*Plus Servers to display the context menu options. Initially, there are the following options:
   
   
   • No iSQL*Plus Servers configured

     If you have installed Oracle9i Server, there are two iSQL*Plus Servers configured, one for the standard URL and one for the DBA URL.

   • Configure.
     
     
   • About iSQL*Plus Extension.
3. Click Configure. The Configure iSQL*Plus Servers dialog is displayed.
   
   
4. Enter an iSQL*Plus Server definition in the iSQL*Plus Servers: area. When you first open the dialog, the following syntax model is displayed to show you the expected field contents:
   
   

   URL: << http://host.domain/isqlplus >>
   Name: << host >>
   Connect String: << username/password@connect_identifier >> 
   

   You must enter a URL for your iSQL*Plus Server, the other fields are optional. This may be different if iSQL*Plus Servers have been configured during an Oracle9i Server installation.

5. Click OK to save your new server definition.

The new iSQL*Plus Server Name appears in the context menu. When you select a server from the context menu, the full iSQL*Plus Server URL is shown in the status bar.

Modifying a Server

To modify an iSQL*Plus Server, perform the following steps:

1. Right click a .SQL file in Windows Explorer.
   
   
2. Select iSQL*Plus Servers to display the context menu options.
   
   
3. Click Configure. The Configure iSQL*Plus Servers dialog is displayed.
   
   
4. Select the server you wish to modify from the list of available servers.
   
   
5. Edit the server definition directly in the text fields and press Enter when you have finished editing each field. To edit or copy a field when the column is narrower than the field it contains, you need to widen the column by selecting and dragging the heading column separator.
   
   
6. Click OK to save your changes.

The modified iSQL*Plus Server name appears in the context menu.

Removing a Server

Select the server you wish to remove from the list of available servers. The server's details will appear in the following text boxes. Click the Remove button to remove it from the list of available servers.

To remove an iSQL*Plus Server, perform the following steps:

1. Right click a .SQL file in Windows Explorer.
   
   
2. Select iSQL*Plus Servers to display the context menu options.
   
   
3. Click Configure. The Configure iSQL*Plus Servers dialog is displayed.
   
   
4. Select the server you wish to remove from the list of available servers.
   
   
5. Click Remove.
   
   
6. Click OK to save your changes.

The iSQL*Plus Server is removed from the context menu.