Basic commands of SQL*PLUS

The SQL*Plus utility issues a command line prompt (usually “SQL> “), after which you can enter commands. Commands consist of one or more words separated by an arbitrary number of spaces and/or tabulation characters. The character register in commands is usually irrelevant. When you press the Enter key, the command (if certain conditions are fulfilled upon its completion) is executed. Once the command is executed, the invitation is issued again.

Some commands, such as PL/SQL blocks, may take several lines. Then SQL*Plus issues additional invitations with the line number, for example, “2> “.

In SQL*Plus command line you can enter three types of commands:

• SQL statements for working with information in a database;
• PL/SQL blocks for working with the information in the database;
• SQL*Plus commands for formatting query results, setting options, editing commands, etc.

The way the command is continued on the next line, its completion and execution depends on its type.

Execution of commands

SQL Buffer
The last command entered (SQL statement or PL/SQL block) is stored by SQL*Plus in an area called SQL buffer . The operator or block remains in this buffer until a new one is entered. The SQL buffer does not contain a semicolon or a slash, closing operator or block respectively. The contents of the SQL buffer can be edited and executed again.

The RUN command or a slash (/) command is used to re-execute the SQL buffer content. The RUN command outputs the buffer content to a standard output stream and then executes it. The slash simply executes the command from the buffer.

Executing SQL operators
There are three ways to complete an SQL statement:

• semicolon (;)
• a string containing only a slash (/)
• blank line
• GO

The semicolon at the end of the line indicates the execution of the operator. The SQL*Plus utility executes an operator and memorizes it in the SQL buffer. This character is a sign of command completion and you cannot enter a comment after it.

A slash as the first and only character of the next line means that the operator must be executed. The SQL*Plus utility executes an operator and remembers it in the SQL buffer.

A blank line in an SQL statement or SQL*Plus script usually means that the command is complete, but you don’t need to execute it yet (this behavior can be changed with SQL*Plus SET SQLBLANKLINES). The command is placed in the SQL buffer and remains there until another command is entered.

Executing PL/SQL blocks

To enter PL/SQL blocks, you must work in PL/SQL mode. The SQL*Plus utility switches to this mode if:

• keyword DECLARE or BEGIN is entered in the command line;
• one of the SQL commands is entered which creates a stored program unit, e.g. CREATE PROCEDURE.

The SQL*Plus utility handles PL/SQL blocks in the same way as SQL operators, except for semicolons and blank lines. In the PL/SQL mode, entering them does not cause the command to be executed or terminated. To complete and execute a PL/SQL block, you must enter a single character string with a slash (/). To finish entering a block and simply place it in the SQL buffer without executing it, enter a single character string with a dot (.).

Executing SQL*Plus commands

SQL*Plus commands are executed immediately and do not hit the SQL buffer. You can finish SQL*Plus commands with a semicolon, but not necessarily. Most SQL*Plus commands can be shortened to one or more first letters, sufficient for unambiguous command definition.

If you need to enter a long SQL*Plus command, you can move it to the next line. To do this, you need to enter a hyphen (-) at the end of the line and only then press the Enter key. An invitation will be issued and you can continue entering the command.

The hyphen is interpreted in the same way in SQL statements – if it is the last non-space character in a string, it will not get into the buffer and is considered to be just a sign of continuing the command on the next line.

Termination of the command in the course of work

To stop a long running command while working, you should enter the interrupt symbol, usually Ctrl+C. In this case, SQL*Plus will stop issuing the results of the command and issue a prompt.

In this way, you cannot stop a command outputting the results to a file using the SPOOL command. You will have to stop the SQL*Plus utility where the command was issued using the operating system.

Getting information about the command execution time

To collect and output data on computing resources used to execute one or more commands or blocks, the command TIMING is designed. It has the following syntax:

<command TIMING> ::=
TIMI[NG] <command timer>.
< timer command> ::=
START [<timer name>] / SHOW / STOP

The purpose of the timer commands is described in Table 3.

Table 3 Timer SQL*Plus commands

Team Appointment
START Starts the timer and gives it the specified name. You can use multiple active timers by running additional ones with the START command before stopping the original timer. The last timer running becomes the current timer.
SHOW Specifies the name and data (running time) of the current timer.
STOP Sets the name and data (running time) of the current timer, and then stops and deletes the timer. If several timers are active, the next one, the penultimate one, becomes the current timer.
The TIMING command gives the number of active timers without parameters.

Let us consider an example of using the TIMING command:

The CLEAR TIMING command is used to delete all timers.

It is also possible to automatically give the running time of each command by setting SET AUTOTRACE. For details, refer to “Configuring the SQL*Plus Environment” below.