13
SQL*Plus Command Reference

Specifies the Uniform Resource Locator of a script to run on the specified web server. SQL*Plus supports HTTP and FTP protocols.

Represents the script you wish to run. If you omit ext, SQL*Plus assumes the default command-file extension (normally SQL). For information on changing the default extension, see the SUFFIX variable of the SET command.

When you enter @file_name.ext, SQL*Plus searches for a file with the filename and extension you specify in the current default directory. If SQL*Plus does not find such a file, SQL*Plus will search a system-dependent path to find the file. Some operating systems may not support the path search. See the Oracle installation and user's manual(s) provided for your operating system for specific information related to your operating system environment.

Represent data items you wish to pass to parameters in the script. If you enter one or more arguments, SQL*Plus substitutes the values into the parameters (&1, &2, and so forth) in the script. The first argument replaces each occurrence of &1, the second replaces each occurrence of &2, and so forth.

The @ command DEFINEs the parameters with the values of the arguments; if you run the script again in this session, you can enter new arguments or omit the arguments to use the current values.

For more information on using parameters, refer to the subsection "Substitution Variables in iSQL*Plus" under "Writing Interactive Commands".

Specifies the Uniform Resource Locator of a script to run on the specified web server. SQL*Plus supports HTTP and FTP protocols.

Represents the nested script you wish to run. If you omit ext, SQL*Plus assumes the default command-file extension (normally SQL). For information on changing the default extension, see the SUFFIX variable of the SET command.

When you enter @@file_name.ext from within a script, SQL*Plus runs file_name.ext from the same directory as the script.

When you enter @@file_name.ext interactively, SQL*Plus runs file_name.ext from the current working directory or from the same url as the script from which it was called. If SQL*Plus does not find such a file, SQL*Plus searches a system-dependent path to find the file. Some operating systems may not support the path search. See the Oracle installation and user's manual provided for your operating system for specific information related to your operating system environment.

Represents the name of the variable in which you wish to store a value. If variable does not exist, SQL*Plus creates it.

Makes the datatype of variable the datatype NUMBER. If the reply does not match the datatype, ACCEPT gives an error message and prompts again.

Makes the datatype of variable the datatype CHAR. The maximum CHAR length limit is 240 bytes. If a multi-byte character set is used, one CHAR may be more than one byte in size.

Makes reply a valid DATE format. If the reply is not a valid DATE format, ACCEPT gives an error message and prompts again. The datatype is CHAR.

Specifies the input format for the reply. If the reply does not match the specified format, ACCEPT gives an error message and prompts again for a reply. The format element must be a text constant such as A10 or 9.999. See the CHANGE command in this chapter for a complete list of format elements.

Oracle date formats such as "dd/mm/yy" are valid when the datatype is DATE. DATE without a specified format defaults to the Oracle NLS_DATE_FORMAT of the current session. See the Oracle9i Database Administrator's Guide and the Oracle9i SQL Reference for information on Oracle date formats.

Sets the default value if a reply is not given. The reply must be in the specified format if defined.

Displays text on-screen before accepting the value of variable from the user.

Skips a line and waits for input without displaying a prompt.

Suppresses the display as you type the reply.

Represents the text to append. To separate text from the preceding characters with a space, enter two spaces between APPEND and text.

To APPEND text that ends with a semicolon, end the command with two semicolons (SQL*Plus interprets a single semicolon as an optional command terminator).

Requests a display that shows the range of redo log files to be archived, the current log file group's sequence number, and the current archive destination (specified by either the optional command text or by the initialization parameter LOG_ARCHIVE_DEST).

If you are using both ARCHIVELOG mode and automatic archiving, the display might appear like:

ARCHIVE LOG LIST

Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /vobs/oracle/dbs/arch
Oldest online log sequence     221
Next log sequence to archive   222
Current log sequence           222

Since the log sequence number of the current log group and the next log group to archive are the same, automatic archival has archived all log groups up to the current one.

If you are using ARCHIVELOG but have disabled automatic archiving, the last three lines might look like:

Oldest online log sequence            222
Next log sequence to archive          222
Current log sequence                  225

If you are using NOARCHIVELOG mode, the "next log sequence to archive" line is suppressed.

The log sequence increments every time the Log Writer begins to write to another redo log file group; it does not indicate the number of logs being used. Every time an online redo log file group is reused, the contents are assigned a new log sequence number.

Disables automatic archival. If the instance is still in ARCHIVELOG mode and all redo log file groups fill, database operation is suspended until a redo log file is archived (for example, until you enter the command ARCHIVE LOG NEXT or ARCHIVE LOG ALL).

Enables automatic archiving. Starts the background process ARCH, which performs automatic archiving as required. If ARCH is started and a filename is supplied, the filename becomes the new default archive destination. ARCH automatically starts on instance startup if the initialization parameter LOG_ARCHIVE_START is set to TRUE.

Manually archives the next online redo log file group that has been filled, but not yet archived.

Manually archives all filled, but not yet archived, online redo log file groups.

Causes archival of the online redo log file group with log sequence number n. You can specify any redo log file group that is still online. An error occurs if the log file cannot be found online or the sequence number is not valid. This option can be used to re-archive a log file group.

Specifies the destination device or directory in an operating system. Specification of archive destination devices is installation-specific; see your platform-specific Oracle documentation for examples of specifying archive destinations. On many operating systems, multiple log files can be spooled to the same tape.

If not specified in the command-line, the archive destination is derived from the initialization parameter LOG_ARCHIVE_DEST. The command ARCHIVE LOG START destination causes the specified device or directory to become the new default archive destination for all future automatic or manual archives. A destination specified with any other option is a temporary destination that is in effect only for the current (manual) archive. It does not change the default archive destination for subsequent automatic archives. For information about specifying archive destinations, see your platform-specific Oracle documentation.

ALI[AS] alias
CLE[AR]
FOR[MAT] format
LIKE {type_name.attribute_name|alias}
ON|OFF

Identifies the data item (typically the name of an attribute) within the set of attributes for a given object of Object Type, type_name.

If you select objects of the same Object Type, an ATTRIBUTE command for that type_name.attribute_name will apply to all such objects you reference in that session.

Assigns a specified alias to a type_name.attribute_name, which can be used to refer to the type_name.attribute_name in other ATTRIBUTE commands.

Resets the display characteristics for the attribute_name. The format specification must be a text constant such as A10 or $9,999--not a variable.

Specifies the display format of the column. The format specification must be a text constant such as A10 or $9,999--not a variable.

Copies the display characteristics of another attribute. LIKE copies only characteristics not defined by another clause in the current ATTRIBUTE command.

Controls the status of display characteristics for a column. OFF disables the characteristics for an attribute without affecting the characteristics' definition. ON reinstates the characteristics.

When you include action(s), specifies action(s) for SQL*Plus to take whenever a break occurs in the specified column (called the break column). (column cannot have a table or view appended to it. To achieve this, you can alias the column in the SQL statement.) A break is one of three events, a change in the value of a column or expression, the output of a row, or the end of a report

When you omit action(s), BREAK ON column suppresses printing of duplicate values in column and marks a place in the report where SQL*Plus will perform the computation you specify in a corresponding COMPUTE command.

You can specify ON column one or more times. If you specify multiple ON clauses, as in

BREAK ON DEPARTMENT_ID SKIP PAGE ON JOB_ID - 
SKIP 1 ON SALARY SKIP 1

the first ON clause represents the outermost break (in this case, ON DEPARTMENT_ID) and the last ON clause represents the innermost break (in this case, ON SALARY). SQL*Plus searches each row of output for the specified break(s), starting with the outermost break and proceeding--in the order you enter the clauses--to the innermost. In the example, SQL*Plus searches for a change in the value of DEPARTMENT_ID, then JOB_ID, then SALARY.

Next, SQL*Plus executes actions beginning with the action specified for the innermost break and proceeding in reverse order toward the outermost break (in this case, from SKIP 1 for ON SALARY toward SKIP PAGE for ON DEPARTMENT_ID). SQL*Plus executes each action up to and including the action specified for the first occurring break encountered in the initial search.

If, for example, in a given row the value of JOB_ID changes--but the values of DEPARTMENT_ID and SALARY remain the same--SQL*Plus skips two lines before printing the row (one as a result of SKIP 1 ON SALARY and one as a result of SKIP 1 ON JOB_ID).

Whenever you use ON column, you should also use an ORDER BY clause in the SQL SELECT command. Typically, the columns used in the BREAK command should appear in the same order in the ORDER BY clause (although all columns specified in the ORDER BY clause need not appear in the BREAK command). This prevents breaks from occurring at meaningless points in the report.

If the BREAK command specified earlier in this section is used, the following SELECT command produces meaningful results:

SELECT DEPARTMENT_ID, JOB_ID, SALARY, LAST_NAME
FROM EMP_DETAILS_VIEW
WHERE SALARY > 12000
ORDER BY DEPARTMENT_ID, JOB_ID, SALARY, LAST_NAME;

All rows with the same DEPARTMENT_ID print together on one page, and within that page all rows with the same JOB_ID print in groups. Within each group of jobs, those jobs with the same SALARY print in groups. Breaks in LAST_NAME cause no action because LAST_NAME does not appear in the BREAK command.

When you include action(s), specifies action(s) for SQL*Plus to take when the value of the expression changes.

When you omit action(s), BREAK ON expr suppresses printing of duplicate values of expr and marks a place in the report where SQL*Plus will perform the computation you specify in a corresponding COMPUTE command.

You can use an expression involving one or more table columns or an alias assigned to a report column in a SQL SELECT or SQL*Plus COLUMN command. If you use an expression in a BREAK command, you must enter expr exactly as it appears in the SELECT command. If the expression in the SELECT command is a+b, for example, you cannot use b+a or (a+b) in a BREAK command to refer to the expression in the SELECT command.

The information given above for ON column also applies to ON expr.

When you include action(s), specifies action(s) for SQL*Plus to take when a SQL SELECT command returns a row. The ROW break becomes the innermost break regardless of where you specify it in the BREAK command. You should always specify an action when you BREAK on a row.

Marks a place in the report where SQL*Plus will perform the computation you specify in a corresponding COMPUTE command. Use BREAK ON REPORT in conjunction with COMPUTE to print grand totals or other "grand" computed values.

The REPORT break becomes the outermost break regardless of where you specify it in the BREAK command.

Note that SQL*Plus will not skip a page at the end of a report, so you cannot use BREAK ON REPORT SKIP PAGE.

Skips n lines before printing the row where the break occurred. BREAK SKIP n does not work in SET MARKUP HTML ON mode or in iSQL*Plus unless PREFORMAT is SET ON.

Skips the number of lines that are defined to be a page before printing the row where the break occurred. The number of lines per page can be set via the PAGESIZE clause of the SET command. Note that PAGESIZE only changes the number of lines that SQL*Plus considers to be a page. Therefore, SKIP PAGE may not always cause a physical page break, unless you have also specified NEWPAGE 0. Note also that if there is a break after the last row of data to be printed in a report, SQL*Plus will not skip the page.

Prints blanks rather than the value of a break column when the value is a duplicate of the column's value in the preceding row.

Prints the value of a break column in every selected row.

Represents any non-alphanumeric character such as "/" or "!". Use a sepchar that does not appear in old or new.

Represents the text you wish to change. CHANGE ignores case in searching for old. For example,

CHANGE /aq/aw

will find the first occurrence of "aq", "AQ", "aQ", or "Aq" and change it to "aw". SQL*Plus inserts the new text exactly as you specify it.

If old is prefixed with "...", it matches everything up to and including the first occurrence of old. If it is suffixed with "...", it matches the first occurrence of old and everything that follows on that line. If it contains an embedded "...", it matches everything from the preceding part of old through the following part of old.

Represents the text with which you wish to replace old. If you omit new and, optionally, the second and third sepchars, CHANGE deletes old from the current line of the buffer.

BRE[AKS]
BUFF[ER]
COL[UMNS]
COMP[UTES]
SCR[EEN]
SQL
TIMI[NG]

Removes the break definition set by the BREAK command.

Clears text from the buffer. CLEAR BUFFER has the same effect as CLEAR SQL, unless you are using multiple buffers (see the SET BUFFER command in Appendix C, "Obsolete SQL*Plus Commands").

Resets column display attributes set by the COLUMN command to default settings for all columns. To reset display attributes for a single column, use the CLEAR clause of the COLUMN command. CLEAR COLUMNS also clears the ATTRIBUTEs for that column.

Removes all COMPUTE definitions set by the COMPUTE command.

Clears your screen.

CLEAR SCREEN is not available in iSQL*Plus.

Clears the text from SQL buffer. CLEAR SQL has the same effect as CLEAR BUFFER, unless you are using multiple buffers (see the SET BUFFER command in Appendix C, "Obsolete SQL*Plus Commands").

Deletes all timers created by the TIMING command.

ALI[AS] alias
CLE[AR]
ENTMAP {ON|OFF}
FOLD_A[FTER]
FOLD_B[EFORE]
FOR[MAT] format
HEA[DING] text
JUS[TIFY] {L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]}
LIKE {expr|alias}
NEWL[INE]
NEW_V[ALUE] variable
NOPRI[NT]|PRI[NT]
NUL[L] text
OLD_V[ALUE] variable
ON|OFF
WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]

Identifies the data item (typically, the name of a column) in a SQL SELECT command to which the column command refers. If you use an expression in a COLUMN command, you must enter expr exactly as it appears in the SELECT command. If the expression in the SELECT command is a+b, for example, you cannot use b+a or (a+b) in a COLUMN command to refer to the expression in the SELECT command.

If you select columns with the same name from different tables, a COLUMN command for that column name will apply to both columns. That is, a COLUMN command for the column LAST_NAME applies to all columns named LAST_NAME that you reference in this session. COLUMN ignores table name prefixes in SELECT commands. Also, spaces are ignored unless the name is placed in double quotes.

To format the columns differently, assign a unique alias to each column within the SELECT command itself (do not use the ALIAS clause of the COLUMN command) and enter a COLUMN command for each column's alias.

Assigns a specified alias to a column, which can be used to refer to the column in BREAK, COMPUTE, and other COLUMN commands.

Resets the display attributes for the column to default values.

To reset the attributes for all columns, use the CLEAR COLUMNS command. CLEAR COLUMNS also clears the ATTRIBUTEs for that column.

Allows entity mapping to be turned on or off for selected columns in HTML output. This feature allows you to include, for example, HTML hyperlinks in a column of data, while still mapping entities in other columns of the same report. By turning entity mapping off for a column containing HTML hyperlinks, the HTML anchor tag delimiters, <, >, " and &, are correctly interpreted in the report. Otherwise they would be replaced with their respective entities, &lt;, &gt;, &quot; and &amp;, preventing web browsers from correctly interpreting the HTML.

Entities in the column heading and any COMPUTE labels or output appearing in the column are mapped or not mapped according to the value of ENTMAP for the column.

The default setting for COLUMN ENTMAP is the current setting of the MARKUP HTML ENTMAP option. For more information about the MARKUP HTML ENTMAP option, see SET "MARKUP Options" and SET later this Chapter.

Inserts a carriage return after the column heading and after each row in the column. SQL*Plus does not insert an extra carriage return after the last column in the SELECT list.

Inserts a carriage return before the column heading and before each row of the column. SQL*Plus does not insert an extra carriage return before the first column in the SELECT list.

Specifies the display format of the column. The format specification must be a text constant such as A10 or $9,999--not a variable.

Character Columns The default width of CHAR, NCHAR, VARCHAR2 (VARCHAR) and NVARCHAR2 (NCHAR VARYING) columns is the width of the column in the database. SQL*Plus formats these datatypes left-justified. If a value does not fit within the column width, SQL*Plus wraps or truncates the character string depending on the setting of SET WRAP.

A LONG, CLOB, NCLOB or XMLType column's width defaults to the value of SET LONGCHUNKSIZE or SET LONG, whichever one is smaller.

To change the width of a datatype to n, use FORMAT An. (A stands for alphanumeric.) If you specify a width shorter than the column heading, SQL*Plus truncates the heading.

DATE Columns The default width and format of unformatted DATE columns in SQL*Plus is derived from the NLS parameters in effect. Otherwise, the default width is A9. In Oracle9i, the NLS parameters may be set in your database parameter file or may be environment variables or an equivalent platform-specific mechanism. They may also be specified for each session with the ALTER SESSION command. (See the documentation for Oracle9i for a complete description of the NLS parameters).

You can change the format of any DATE column using the SQL function TO_CHAR in your SQL SELECT statement. You may also wish to use an explicit COLUMN FORMAT command to adjust the column width.

When you use SQL functions like TO_CHAR, Oracle automatically allows for a very wide column.

To change the width of a DATE column to n, use the COLUMN command with FORMAT An. If you specify a width shorter than the column heading, the heading is truncated.

NUMBER Columns To change a NUMBER column's width, use FORMAT followed by an element as specified in Table 13-1.

Table 13-1 Number Formats

9999

0999

9990

$9999

B9999

9999MI

S9999

9999PR

99D99

9G999

C999

L999

9,999

99.99

999V99

9.999EEEE

RN

DATE

The MI and PR format elements can only appear in the last position of a number format model. The S format element can only appear in the first or last position.

If a number format model does not contain the MI, S or PR format elements, negative return values automatically contain a leading negative sign and positive values automatically contain a leading space.

A number format model can contain only a single decimal character (D) or period (.), but it can contain multiple group separators (G) or commas (,). A group separator or comma cannot appear to the right of a decimal character or period in a number format model.

SQL*Plus formats NUMBER data right-justified. A NUMBER column's width equals the width of the heading or the width of the FORMAT plus one space for the sign, whichever is greater. If you do not explicitly use FORMAT, then the column's width will always be at least the value of SET NUMWIDTH.

SQL*Plus may round your NUMBER data to fit your format or field width.

If a value cannot fit within the column width, SQL*Plus indicates overflow by displaying a pound sign (#) in place of each digit the width allows.

If a positive value is extremely large and a numeric overflow occurs when rounding a number, then the infinity sign (~) replaces the value. Likewise, if a negative value is extremely small and a numeric overflow occurs when rounding a number, then the negative infinity sign replaces the value (-~).

Defines a column heading. If you do not use a HEADING clause, the column's heading defaults to column or expr. If text contains blanks or punctuation characters, you must enclose it with single or double quotes. Each occurrence of the HEADSEP character (by default, "|") begins a new line.

For example,

COLUMN LAST_NAME HEADING 'Employee |Name'

would produce a two-line column heading. See the HEADSEP variable of the SET command in this chapter for information on changing the HEADSEP character.

Aligns the heading. If you do not use a JUSTIFY clause, headings for NUMBER columns default to RIGHT and headings for other column types default to LEFT.

Copies the display attributes of another column or expression (whose attributes you have already defined with another COLUMN command). LIKE copies only attributes not defined by another clause in the current COLUMN command.

Starts a new line before displaying the column's value. NEWLINE has the same effect as FOLD_BEFORE.

Specifies a variable to hold a column value. You can reference the variable in TTITLE commands. Use NEW_VALUE to display column values or the date in the top title. You must include the column in a BREAK command with the SKIP PAGE action. The variable name cannot contain a pound sign (#).

NEW_VALUE is useful for master/detail reports in which there is a new master record for each page. For master/detail reporting, you must also include the column in the ORDER BY clause. See the example at the end of this command description.

For information on displaying a column value in the bottom title, see COLUMN OLD_VALUE. For more information on referencing variables in titles, see the TTITLE command later in this chapter. For information on formatting and valid format models, see COLUMN FORMAT command.

Controls the printing of the column (the column heading and all the selected values). NOPRINT turns off the screen output and printing of the column. PRINT turns the printing of the column on.

Controls the text SQL*Plus displays for null values in the given column. The default is a white space. SET NULL controls the text displayed for all null values for all columns, unless overridden for a specific column by the NULL clause of the COLUMN command. When a NULL value is SELECTed, a variable's type will always become CHAR so the SET NULL text can be stored in it.

Specifies a variable to hold a column value. You can reference the variable in BTITLE commands. Use OLD_VALUE to display column values in the bottom title. You must include the column in a BREAK command with the SKIP PAGE action.

OLD_VALUE is useful for master/detail reports in which there is a new master record for each page. For master/detail reporting, you must also include the column in the ORDER BY clause.

For information on displaying a column value in the top title, see COLUMN NEW_VALUE. For more information on referencing variables in titles, see the TTITLE command later in this chapter.

Controls the status of display attributes for a column. OFF disables the attributes for a column without affecting the attributes' definition. ON reinstates the attributes.

Specifies how SQL*Plus will treat a datatype or DATE string that is too wide for a column. WRAPPED wraps the string within the column bounds, beginning new lines when required. When WORD_WRAP is enabled, SQL*Plus left justifies each new line, skipping all leading whitespace (for example, returns, newline characters, tabs and spaces), including embedded newline characters. Embedded whitespace not on a line boundary is not skipped. TRUNCATED truncates the string at the end of the first line of display.

Represents one of the functions listed in Table 13-2. If you specify more than one function, use spaces to separate the functions.

COMPUTE command functions are always executed in the sequence AVG, COUNT, MINIMUM, MAXIMUM, NUMBER, SUM, STD, VARIANCE, regardless of their order in the COMPUTE command.

Table 13-2 COMPUTE Functions

AVG

COU[NT]

MIN[IMUM]

MAX[IMUM]

NUM[BER]

SUM