2
Using SQL*Plus

This chapter explains how to start and use SQL*Plus from the command line interface and the graphical user interface, and describes the menu options.

Specific topics discussed are:

Using the Command Line Interface

The command line interface is a standard feature of SQL*Plus for all platforms.

To start the command line interface:

If you are connecting to a remote Oracle database, make sure your SQL*Net/networking software is installed and working properly.
Start an MS-DOS command prompt window.
At the command prompt, enter:

SQLPLUS [username/password][@connectstring]

For Windows NT and Windows 95/98, the rules for interpreting arguments are:

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

Using the Graphical User Interface

The graphical user interface is a feature of SQL*Plus for Windows NT/95.

To start the graphical user interface:

If you are connecting to a remote Oracle database, make sure your SQL*Net/networking software is installed and working properly.
Choose Start > Programs > Oracle for Windows NT [HOME_NAME] (or Oracle for Windows 95/98 [HOME_NAME]) > SQL Plus 8.0.

The Log On dialog box appears.

Enter your user name and password. If you are connecting to a remote Oracle database, enter the SQL*Net connect string.
Click OK.

The SQL*Plus application window appears.

Using the SQL*Plus Application Window

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

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

Using the Mouse Buttons to Copy SQL Commands in the Application Window

You can use the mouse buttons to copy previously entered SQL commands to the current SQL prompt.

To copy a command, highlight it with the left mouse button. While still holding down the left mouse button, click the right mouse button.

SQL*Plus copies the text to the SQL 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

Copy text

Ctrl+V

Paste text

Shift+Del

Clear the screen and the screen buffer

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	Copy text
Ctrl+V	Paste text
Shift+Del	Clear the screen and the screen buffer

Using the SQL*Plus Menus

This section describes the five menus in SQL*Plus.

File Menu

The File menu contains the following commands

File Menu Command Description Command Line Equivalent

Open

The Open command retrieves a previously stored command file.
By default, SQL*Plus looks for a command file with the .SQL extension.

not applicable

Save

The Save command contains three subcommands: 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 filename CREATE

Save Replace replaces the contents of an existing file with the contents of the SQL*Plus buffer. If the file does not exist, SQL*Plus creates the file.

SAVE filename REPLACE

Save Append adds the contents of the SQL*Plus buffer to the end of the file you specify.

After you save a command file, you can do the following:

retrieve the file with the Open command from the File menu

edit the file with the Editor command from the Edit menu

start or run the file with the START or RUN commands from the SQL*Plus command prompt

SAVE filename APPEND

Save As

The Save As command 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 command contains two subcommands: Spool File and Spool Off.

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 command from the Edit menu, and print the file from a Windows NT or Windows 95/98 text editor.

SPOOL filename

Spool Off turns off spooling.

SPOOL OFF

Run

The Run command lists and executes the SQL command or PL/SQL block currently stored in the SQL buffer.

RUN

Cancel

The Cancel command cancels an in-progress operation.
Ctrl+C is the keyboard shortcut for the Cancel command.

not applicable

Exit

The Exit command commits all pending database changes and closes the SQL*Plus application window.

EXIT

:

File Menu Command	Description	Command Line Equivalent
Open	The Open command retrieves a previously stored command file. By default, SQL*Plus looks for a command file with the .SQL extension.	not applicable
Save	The Save command contains three subcommands: Save Create, Save Replace, and Save Append.	SAVE
	Save Create saves the contents of the SQLPlus buffer in a command file. By default, SQLPlus assigns the .SQL extension to command files. You can specify a different extension in the File name text box.	SAVE filename CREATE
	Save Replace replaces the contents of an existing file with the contents of the SQLPlus buffer. If the file does not exist, SQLPlus creates the file.	SAVE filename REPLACE
	Save Append adds the contents of the SQLPlus buffer to the end of the file you specify. After you save a command file, you can do the following: retrieve the file with the Open command from the File menu edit the file with the Editor command from the Edit menu start or run the file with the START or RUN commands from the SQLPlus command prompt	SAVE filename APPEND
Save As	The Save As command saves the contents of the SQLPlus buffer in a command file. By default, SQLPlus assigns the .SQL extension to command files. You can specify a different extension in the File name text box.	SAVE filename
Spool	The Spool command contains two subcommands: Spool File and Spool Off.
	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 command from the Edit menu, and print the file from a Windows NT or Windows 95/98 text editor.	SPOOL filename
	Spool Off turns off spooling.	SPOOL OFF
Run	The Run command lists and executes the SQL command or PL/SQL block currently stored in the SQL buffer.	RUN
Cancel	The Cancel command cancels an in-progress operation. Ctrl+C is the keyboard shortcut for the Cancel command.	not applicable
Exit	The Exit command commits all pending database changes and closes the SQL*Plus application window.	EXIT

Edit Menu

The Edit menu contains the following commands:

Edit Menu Command Description Command Line Equivalent

Copy

The Copy command copies the 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.
Ctrl+C is the keyboard shortcut for the Copy command.

not applicable

Paste

The Paste command pastes the contents of the Clipboard onto the SQL*Plus command line.
Ctrl+V is the keyboard shortcut for the Paste command.
Note: A maximum of 3625 characters can be pasted from the Clipboard onto the SQL*Plus command line during a single paste operation.

not applicable

Clear

The Clear command clears the screen of the SQL*Plus application window and the screen buffer.
Shift+Del is the keyboard shortcut for the Clear command.

CLEAR SCREEN

Editor

The Editor command contains two subcommands: 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.

EDIT

Define Editor defines the editor that is invoked.

not applicable

Edit Menu Command	Description	Command Line Equivalent
Copy	The Copy command copies the 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. Ctrl+C is the keyboard shortcut for the Copy command.	not applicable
Paste	The Paste command pastes the contents of the Clipboard onto the SQLPlus command line. Ctrl+V is the keyboard shortcut for the Paste command. Note:* A maximum of 3625 characters can be pasted from the Clipboard onto the SQL*Plus command line during a single paste operation.	not applicable
Clear	The Clear command clears the screen of the SQL*Plus application window and the screen buffer. Shift+Del is the keyboard shortcut for the Clear command.	CLEAR SCREEN
Editor	The Editor command contains two subcommands: Invoke Editor and Define Editor.
	Invoke Editor loads the contents of the SQLPlus buffer into an editor. By default, SQLPlus saves the file to AFIEDT.BUF. You can specify a different file name in the editor.	EDIT
	Define Editor defines the editor that is invoked.	not applicable

Search Menu

The Search menu contains the following commands:

Search Menu Command Description Command Line Equivalent

Find
The Find command searches for a character, a word, or a group of characters or words in the SQL*Plus application window. When you choose the Find command, SQL*Plus begins the search at the top of the displayed screen.
Note: When SQL*Plus reaches the end of the displayed screen, it will not automatically continue searching from the top of the SQL*Plus screen buffer.
ALT+F3 is the keyboard shortcut for the Clear command.

not applicable

Find Next
The Find Next command finds the next occurrence of the search text.
F3 is the keyboard shortcut for the Clear command.

not applicable

Search Menu Command	Description	Command Line Equivalent
Find	The Find command searches for a character, a word, or a group of characters or words in the SQLPlus application window. When you choose the Find command, SQLPlus begins the search at the top of the displayed screen. Note: When SQLPlus reaches the end of the displayed screen, it will not automatically continue searching from the top of the SQLPlus screen buffer. ALT+F3 is the keyboard shortcut for the Clear command.	not applicable
Find Next	The Find Next command finds the next occurrence of the search text. F3 is the keyboard shortcut for the Clear command.	not applicable

Options Menu

The Options menu contains the following command:

Options Menu Command Description Command Line Equivalent

Environment

The Environment command allows you to set system variables to alter the SQL*Plus environment for your current session.
The Environment dialog box contains two areas: Set Options and Screen Buffer.

SET variable value

Set Options
This area contains a list of variables you can choose in order to establish aspects of the SQL*Plus environment for your current session, such as the following:

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 per page

You can make multiple changes to options before clicking OK to commit the changes.

To establish an aspect of the SQL*Plus environment:

In the Environment dialog box, select a variable from the Set Options box.

Select the appropriate option buttons and, if required, enter text in the text box.

Click OK.

Screen Buffer
When you change the Screen Buffer option, SQL*Plus displays a dialog box to alert you that if you shorten the size of your screen buffer, some data may not be displayed on your screen. Click OK if you want to proceed.

The Screen Buffer area consists of two text boxes: Buffer Width and Buffer Length.

In the Buffer Width text box, you can 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 can 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.

Help Menu

The SQL*Plus application does not contain online help. However, the CD-ROM contains the following documentation in both HTML and PDF format:

SQL*Plus User's Guide and Reference
SQL*Plus Quick Reference
SQL*Plus Getting Started Release 8.0.5 for Windows NT and Windows 95/98 (this guide)

The Help menu does contain one command:

Help Menu Command Description Command Line Equivalent

About SQL*Plus

Displays the version number and copyright information.

not applicable

Help Menu Command	Description	Command Line Equivalent
About SQL*Plus	Displays the version number and copyright information.	not applicable

Using SQLPLUS.EXE

SQLPLUS.EXE is used to spawn any SQL*Plus executable that is defined in the PLUS_DFLT parameter located in the registry.

SQLPLUS.EXE allows you to run any specified version and mode of SQL*Plus when it is started. You can modify the value of PLUS_DFLT, thereby having SQLPLUS.EXE spawn the desired version and mode of SQL*Plus.

By default, PLUS_DFLT is set to the last version of the command line interface of SQL*Plus installed.

Options Menu Command	Description	Command Line Equivalent
Environment	The Environment command allows you to set system variables to alter the SQLPlus environment for your current session. The Environment* dialog box contains two areas: Set Options and Screen Buffer.	SET variable value
	Set Options This area contains a list of variables you can choose in order to establish aspects of the SQL*Plus environment for your current session, such as the following: 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 per page You can make multiple changes to options before clicking OK to commit the changes.
	To establish an aspect of the SQLPlus environment: In the Environment* dialog box, select a variable from the Set Options box. Select the appropriate option buttons and, if required, enter text in the text box. Click OK.
	Screen Buffer When you change the Screen Buffer option, SQL*Plus displays a dialog box to alert you that if you shorten the size of your screen buffer, some data may not be displayed on your screen. Click OK if you want to proceed.
	The Screen Buffer area consists of two text boxes: Buffer Width and Buffer Length. In the Buffer Width text box, you can set the number of characters that SQLPlus displays on one line. If you enter a number smaller than the length of output data, SQLPlus 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 can set the number of lines that SQLPlus displays on the screen. If SQLPlus 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.

2 Using SQL*Plus