At the SQL prompt, you may enter two types of commands:

• SQL commands, for working with data in the database
• SQL*Plus commands, for formatting query results, setting options, and editing and storing SQL commands

SQL*Plus is an ORACLE product that is used in conjunction with the SQL database language to store and retrieve data in ORACLE databases. Through SQL*Plus, you can:

• Enter, edit, store, retrieve, and run SQL commands
• Format, perform calculations on, store, and print query results in the form of reports
• List column definitions for any table
• Access and copy data between SQL databases
• Send messages to and accept responses from an end user

SQL*Plus commands are listed below.

Use HELP to display information about each command. At the SQL prompt, type HELP, a space, all or part of any command name, and then press Enter. Typing only part of a topic (for example, HELP SE) will cause all help topics that match the string (SE) to be displayed on your screen in alphabetical order (for example, HELP SE will retrieve help on the SELECT statement, the SET command, and the Set Operators--in that order). Type HELP COMMANDS to display the list of SQL*Plus commands above, as well as SQL commands.

SQL commands may be divided into separate lines at any points you wish, as long as individual words are not split between lines. A semicolon ends a SQL command.

SQL*Plus commands have a different syntax from SQL commands. You can continue a long SQL*Plus command by typing a hyphen at the end of the line and pressing enter. You do not need to end a SQL*Plus command with a semicolon, although it is allowed.

You may find the following commands useful in preparing and saving reports.

It is not uncommon for the output of an SQL command to return more than one screen of data. When this happens the system scrolls until the bottom of the output is reached. This can be very annoying.

You can use the SQL*Plus SET PAUSE command to pause after each screen of data.

This command tells SQL*Plus to wait until the user presses Enter before it displays the next page of output.

The pagesize default is set to 14 lines of output. This can be altered to any amount you desire. Since most screens display around 25 lines of text you may want to change the pagesize setting. To set the pagesize to twenty use the following command:

SQL*Plus also allows the users to save frequently used queries and run them again at a later date. The contents of the buffer can be saved by writing them to disk through the use of the SAVE command. You may check the contents of the buffer by using the LIST command. When using the SAVE command follow it with a name you want to give the file (query) as follows:

You will receive a message that the file has been created.

This will save the file provided the filename you give does not already exist. If you make changes to the file and you want to save those changes, you need to add the REPLACE command after the filename. This will in effect update your file.

To retrieve your file at a later time and rerun it, use the GET command to bring the file back into the buffer. Then use either the / or the command run to run the query. For example, to create and retrieve the file 'DEPT10CLERKS' and then run it do the following:

The results table will look like this.

While the query is still in the buffer, save it by typing the following command.

You will receive a message that the file has been created. To retrieve the file, type the following command.

1 select empno

2 from employee

3 where deptno = 10

4* and job = 'Clrk'

To run the query, while the file is still in the buffer, type RUN or hit the / key. When the backslash key is used, only the results table is displayed. When the RUN command is used, the query is displayed before the results table. The RUN command will work only if the first line contains an SQL command. It will not work for SQL*Plus scripts.

1 select empno

2 from employee

3 where deptno = 10

4* and job = 'Clrk'

EMPNO

100

To retrieve and run the query in one command, use either of these SQL*Plus commands: START or @.

SQL> START DEPT10CLERKS  

 EMPNO

100

OR

 EMPNO

100

Last revised 11/4/1997 by J. Warren, Kennesaw State University

Department of Computer Science and Information Systems