SQL*Plus Tutorial

SQL*Plus SET Commands

To specify options for working with SQL * Plus it is use the command SET. The command SET has two syntax forms:

- one of them is used as a Help to the user;

- the other is used for setting system variables.

A variable is a field reserved for the system in which a value is retained by the Oracle system, value indicating a status of the system. To display the values of all environment variables at one time it gives the SHOW ALL command.

SET system_var value

System_var parameter can take any of the values that appear in the command SHOW ALL.

The most important SQL * Plus SET Commands

- SET RECSEP {WR [APPED] | SHE [CH] | OFF} controls the print record delimiters. The three possible values have the following meanings:
WRAPPED prints a separator after each line of a record (even if it spans multiple lines);
EACH doing the same thing, but only at the end of a record;
OFF cancels print a separator.

- SET RECSEPCHAR {_ | c} sets the character that separates the records;

- SPACE {1 | n} sets the number of spaces between the columns.

- SET WRA [P] {OFF | ON} specifies how to display records (truncated or on a line).

- SET NULL text specifies the text displayed in place of NULL value.

- HEADS [EP] {| | c | OFF | ON} specifies the characters that separate the column names of records.

- SET UND [ERLINE] {_ | c | OFF | ON the specific character used} for underlining the names of the columns.

- SET NEWP [AGE] {1 | n | NONE} sets the number of blank lines left at top of every page.

- SET TERM [OUT] {OFF | ON the display of output controls } generated commands executed from a script.

- SET PAGES [IZE] {24 | n} specifies the number of lines displayed per page (default is 24; may be set to a value of 0 to suppress the display of column names, the title of a report).

- SET FEED [BACK] {6 | n | OFF | ON the display number} controls the record supplied as a result of a SELECT command.

- ECHO {OFF | Controls the display ON} commands from a script launched with the START command SQL, as they are executed.

The SPOOL command allows saving the results of a query to a file specified as a parameter. She has the following syntax: