Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index



Go to previous file in sequence Go to next file in sequence

Command Reference


This chapter contains descriptions of SQL*Plus commands, listed alphabetically. Use this chapter for reference only. Each description contains the following parts:

Purpose

Discusses the basic use(s) of the command.

Syntax

Shows how to enter the command. Refer to Chapter 1 for an explanation of the syntax notation.

Terms and Clauses

Describes the function of each term or clause appearing in the syntax.

Usage Notes

Provides additional information on how the command works and on uses of the command.

Examples

Gives one or more examples of the command.

A summary table that lists and briefly describes SQL*Plus commands precedes the individual command descriptions.

You can continue a long SQL*Plus command by typing a hyphen at the end of the line and pressing [Return]. If you wish, you can type a space before typing the hyphen. SQL*Plus displays a right angle-bracket (>) as a prompt for each additional line.

You do not need to end a SQL*Plus command with a semicolon. When you finish entering the command, you can just press [Return]. If you wish, however, you can enter a semicolon at the end of a SQL*Plus command.

SQL*Plus Command Summary

Command

Description

@

Runs the specified command file.

@@

Runs a command file.

/

Executes the SQL command or PL/SQL block.

ACCEPT

Reads a line of input and stores it in a given user variable.

APPEND

Adds specified text to the end of the current line in the buffer.

ATTRIBUTE

Specifies display characteristics for a given attribute of an Object Type column, and lists the current display characteristics for a single attribute or all attributes.

BREAK

Specifies where and how formatting will change in a report, or lists the current break definition.

BTITLE

Places and formats a specified title at the bottom of each report page, or lists the current BTITLE definition.

CHANGE

Changes text on the current line in the buffer.

CLEAR

Resets or erases the current value or setting for the specified option, such as BREAKS or COLUMNS.

COLUMN

Specifies display characteristics for a given column, or lists the current display characteristics for a single column or for all columns.

COMPUTE

Calculates and prints summary lines, using various standard computations, on subsets of selected rows, or lists all COMPUTE definitions.

CONNECT

Connects a given username to Oracle.

COPY

Copies data from a query to a table in a local or remote database.

DEFINE

Specifies a user variable and assigns it a CHAR value, or lists the value and variable type of a single variable or all variables.

DEL

Deletes one or more lines of the buffer.

DESCRIBE

Lists the column definitions for the specified table, view, or synonym or the specifications for the specified function or procedure.

DISCONNECT

Commits pending changes to the database and logs the current username off Oracle, but does not exit SQL*Plus.

EDIT

Invokes a host operating system text editor on the contents of the specified file or on the contents of the buffer.

EXECUTE

Executes a single PL/SQL statement.

EXIT

Terminates SQL*Plus and returns control to the operating system.

GET

Loads a host operating system file into the SQL buffer.

HELP

Accesses the SQL*Plus help system.

HOST

Executes a host operating system command without leaving SQL*Plus.

INPUT

Adds one or more new lines after the current line in the buffer.

LIST

Lists one or more lines of the SQL buffer.

PASSWORD

Allows a password to be changed without echoing the password on an input device.

PAUSE

Displays an empty line followed by a line containing text, then waits for the user to press [Return], or displays two empty lines and waits for the user's response.

PRINT

Displays the current value of a bind variable.

PROMPT

Sends the specified message or a blank line to the user's screen.

REMARK

Begins a comment in a command file.

REPFOOTER

Places and formats a specified report footer at the bottom of each report, or lists the current REPFOOTER definition.

REPHEADER

Places and formats a specified report header at the top of each report, or lists the current REPHEADER definition.

RUN

Lists and executes the SQL command or PL/SQL block currently stored in the SQL buffer.

SAVE

Saves the contents of the SQL buffer in a host operating system file (a command file).

SET

Sets a system variable to alter the SQL*Plus environment for your current session.

SHOW

Shows the value of a SQL*Plus system variable or the current SQL*Plus environment.

SPOOL

Stores query results in an operating system file and, optionally, sends the file to a printer.

START

Executes the contents of the specified command file.

STORE

Saves attributes of the current SQL*Plus environment in a host operating system file (a command file).

TIMING

Records timing data for an elapsed period of time, lists the current timer's title and timing data, or lists the number of active timers.

TTITLE

Places and formats a specified title at the top of each report page, or lists the current TTITLE definition.

UNDEFINE

Deletes one or more user variables that you defined either explicitly (with the DEFINE command) or implicitly (with an argument to the START command).

VARIABLE

Declares a bind variable that can be referenced in PL/SQL.

WHENEVER OSERROR

Exits SQL*Plus if an operating system command generates an error.

WHENEVER SQLERROR

Exits SQL*Plus if a SQL command or PL/SQL block generates an error.

@ ("at" sign)

Purpose

Runs the specified command file.

Syntax

@ file_name[.ext] [arg...]

Terms and Clauses

Refer to the following list for a description of each term or clause:

file_name[.ext]

Represents the command file 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 in this chapter.

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. Consult the Oracle installation and user's manual(s) provided for your operating system for specific information related to your operating system environment.

arg...

Represent data items you wish to pass to parameters in the command file. If you enter one or more arguments, SQL*Plus substitutes the values into the parameters (&1, &2, and so forth) in the command file. 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 command file 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 "Passing Parameters through the START Command" under "Writing Interactive Commands".

Usage Notes

You can include in a command file any command you would normally enter interactively (typically, SQL, SQL*Plus commands, or PL/SQL blocks).

An EXIT or QUIT command used in a command file terminates SQL*Plus.

The @ command functions similarly to START.

If the START command is disabled (see "Disabling SQL*Plus, SQL, and PL/SQL Commands" in Appendix E), this will also disable the @ command. See START in this chapter for information on the START command.

SQL*Plus removes the SQLTERMINATOR (a semicolon by default) before the @ command is issued. A workaround for this is to add another SQLTERMINATOR. See the SQLTERMINATOR variable of the SET command in this chapter for more information.

Example

To run a command file named PRINTRPT with the extension SQL, enter

SQL> @PRINTRPT

To run a command file named WKRPT with the extension QRY, enter

SQL> @WKRPT.QRY

@@ (double "at" sign)

Purpose

Runs a command file. This command is identical to the @ ("at" sign) command except that it looks for the specified command file in the same path as the command file from which it was called.

Syntax

@@ file_name[.ext]

Terms and Clauses

Refer to the following for a description of the term or clause:

file_name[.ext]

Represents the nested command file 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 in this chapter.

When you enter @@file_name.ext from within a command file, SQL*Plus runs file_name.ext from the same directory as the command file. When you enter @@file_name.ext interactively, SQL*Plus runs file_name.ext from the current working directory. 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. Consult the Oracle installation and user's manual(s) provided for your operating system for specific information related to your operating system environment.

Usage Notes

You can include in a command file any command you would normally enter interactively (typically, SQL or SQL*Plus commands).

An EXIT or QUIT command used in a command file terminates SQL*Plus.

The @@ command functions similarly to START.

If the START command is disabled, this will also disable the @@ command. See START in this chapter for further information on the START command.

SQL*Plus removes the SQLTERMINATOR (a semicolon by default) before the @@ command is issued. A workaround for this is to add another SQLTERMINATOR. See the SQLTERMINATOR variable of the SET command in this chapter for more information.

Example

Suppose that you have the following command file named PRINTRPT:

SELECT * FROM EMP
@EMPRPT
@@ WKRPT

When you START PRINTRPT and it reaches the @ command, it looks for the command file named EMPRPT in the current working directory and runs it. When PRINTRPT reaches the @@ command, it looks for the command file named WKRPT in the same path as PRINTRPT and runs it.

/ (slash)

Purpose

Executes the SQL command or PL/SQL block currently stored in the SQL buffer.

Syntax

/

Usage Notes

You can enter a slash (/) at the command prompt or at a line number prompt of a multi-line command.

The slash command functions similarly to RUN, but does not list the command in the buffer on your screen.

Executing a SQL command or PL/SQL block using the slash command will not cause the current line number in the SQL buffer to change unless the command in the buffer contains an error. In that case, SQL*Plus changes the current line number to the number of the line containing the error.

Example

To see the SQL command(s) you will execute, you can list the contents of the buffer:

SQL> LIST
  1* SELECT ENAME, JOB FROM EMP WHERE ENAME = 'JAMES'

Enter a slash (/) at the command prompt to execute the command in the buffer:

SQL> /

For the above query, SQL*Plus displays the following output:

ENAME      JOB
---------- ---------
JAMES      CLERK

ACCEPT

Purpose

Reads a line of input and stores it in a given user variable.

Syntax

ACC[EPT] variable [NUM[BER]|CHAR|DATE] [FOR[MAT] format] [DEF[AULT] default] [PROMPT text|NOPR[OMPT]] [HIDE]

Terms and Clauses

Refer to the following list for a description of each term or clause:

variable

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

NUM[BER]

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

CHAR

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.

DATE

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.

FOR[MAT]

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 COLUMN 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 Oracle8 Server Administrator's Guide and the Oracle8 Server SQL Reference Guide for information on Oracle date formats.

DEF[AULT]

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

PROMPT text

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

NOPR[OMPT]

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

HIDE

Suppresses the display as you type the reply.

To display or reference variables, use the DEFINE command. See the DEFINE command in this chapter for more information.

Examples

To display the prompt "Password: ", place the reply in a CHAR variable named PSWD, and suppress the display, enter

SQL> ACCEPT pswd CHAR PROMPT 'Password:  ' HIDE

To display the prompt "Enter weekly salary: " and place the reply in a NUMBER variable named SALARY with a default of 000.0, enter

SQL> ACCEPT salary NUMBER FORMAT '999.99' DEFAULT '000.0' -
>       PROMPT 'Enter weekly salary:  '

To display the prompt "Enter date hired: " and place the reply in a DATE variable named HIRED with the format "dd/mm/yy" and a default of "01/01/94", enter

SQL> ACCEPT hired DATE FORMAT 'dd/mm/yy' DEFAULT '01/01/94'-
>       PROMPT 'Enter date hired:  '

To display the prompt "Enter employee lastname: " and place the reply in a CHAR variable named LASTNAME, enter

SQL> ACCEPT lastname CHAR FORMAT 'A20' -
>       PROMPT 'Enter employee lastname:  '

APPEND

Purpose

Adds specified text to the end of the current line in the SQL buffer.

Syntax

A[PPEND] text

Terms and Clauses

Refer to the following for a description of the term or clause:

text

Represents the text you wish to append. If you wish 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).

Examples

To append a space and the column name DEPT to the second line of the buffer, make that line the current line by listing the line as follows:

SQL> 2
  2* FROM EMP,

Now enter APPEND:

SQL> APPEND  DEPT
SQL> 2
  2* FROM EMP, DEPT

Notice the double space between APPEND and DEPT. The first space separates APPEND from the characters to be appended; the second space becomes the first appended character.

To append a semicolon to the line, enter

SQL> APPEND ;;

SQL*Plus appends the first semicolon to the line and interprets the second as the terminator for the APPEND command.

ATTRIBUTE

Purpose

Specifies display characteristics for a given attribute of an Object Type column, such as format for NUMBER data.

Also lists the current display characteristics for a single attribute or all attributes.

Syntax

ATTRIBUTE [type_name.attribute_name [option ...]]

where option represents one of the following clauses:

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

Terms and Clauses

Enter ATTRIBUTE followed by type_name.attribute_name and no other clauses to list the current display characteristics for only the specified attribute. Enter ATTRIBUTE with no clauses to list all current attribute display characteristics.

Refer to the following list for a description of each term or clause:

type_name.attribute_name

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.

ALI[AS] alias

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.

CLE[AR]

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.

FOR[MAT] format

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

LIKE {type_name.attribute_name|alias}

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

ON|OFF

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.

Usage Notes

You can enter any number of ATTRIBUTE commands for one or more attributes. All attribute characteristics set for each attribute remain in effect for the remainder of the session, until you turn the attribute OFF, or until you use the CLEAR COLUMN command. Thus, the ATTRIBUTE commands you enter can control an attribute's display characteristics for multiple SQL SELECT commands.

When you enter multiple ATTRIBUTE commands for the same attribute, SQL*Plus applies their clauses collectively. If several ATTRIBUTE commands apply the same clause to the same attribute, the last one entered will control the output.

Examples

To make the ENAME attribute of the Object Type EMP_TYPE 20 characters wide, enter

SQL> ATTRIBUTE EMP_TYPE.ENAME FORMAT A20

To format the SAL attribute of the Object Type EMP_TYPE so that it shows millions of dollars, rounds to cents, uses commas to separate thousands, and displays $0.00 when a value is zero, enter

SQL> ATTRIBUTE EMP_TYPE.SAL FORMAT $9,999,990.99

BREAK

Purpose

Specifies where and how formatting will change in a report, such as

Also lists the current BREAK definition.

Syntax

BRE[AK] [ON report_element [action [action]]] ...

where:

report_element

Requires the following syntax:

{column|expr|ROW|REPORT}

action

Requires the following syntax:

[SKI[P] n|[SKI[P]] PAGE] [NODUP[LICATES]|DUP[LICATES]]

Terms and Clauses

Refer to the following list for a description of each term or clause:

ON column [action [action]]

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:

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

	SQL> BREAK ON DEPTNO SKIP PAGE ON JOB - 
	>  SKIP 1 ON SAL SKIP 1
the first ON clause represents the outermost break (in this case, ON DEPTNO) and the last ON clause represents the innermost break (in this case, ON SAL). 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 DEPTNO, then JOB, then SAL.

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 SAL toward SKIP PAGE for ON DEPTNO). 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 changes--but the values of DEPTNO and SAL remain the same--SQL*Plus skips two lines before printing the row (one as a result of SKIP 1 in the ON SAL clause and one as a result of SKIP 1 in the ON JOB clause).

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. The following SELECT command produces meaningful results:

	SQL> SELECT DEPTNO, JOB, SAL, ENAME
	  2  FROM EMP
	  3  ORDER BY DEPTNO, JOB, SAL, ENAME;
All rows with the same DEPTNO print together on one page, and within that page all rows with the same JOB print in groups. Within each group of jobs, those jobs with the same SAL print in groups. Breaks in ENAME cause no action because ENAME does not appear in the BREAK command.

ON expr [action [action]]

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.

ON ROW [action [action]]

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.

ON REPORT [action]

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.

Refer to the following list for a description of each action:

SKI[P] n

Skips n lines before printing the row where the break occurred.

[SKI[P]] PAGE

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.

NODUP[LICATES]

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.

DUP[LICATES]

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

Enter BREAK with no clauses to list the current break definition.

Usage Notes

Each new BREAK command you enter replaces the preceding one.

To remove the BREAK command, use CLEAR BREAKS.

Example

To produce a report that prints duplicate job values, prints the average of SAL and inserts one blank line when the value of JOB changes, and additionally prints the sum of SAL and inserts another blank line when the value of DEPTNO changes, you could enter the following commands. (The example selects departments 10 and 30 and the jobs of clerk and salesman only.)

SQL> BREAK ON DEPTNO SKIP 1 ON JOB SKIP 1 DUPLICATES
SQL> COMPUTE SUM OF SAL ON DEPTNO
SQL> COMPUTE AVG OF SAL ON JOB
SQL> SELECT DEPTNO, JOB, ENAME, SAL FROM EMP
  2  WHERE JOB IN ('CLERK', 'SALESMAN')
  3  AND DEPTNO IN (10, 30)
  4  ORDER BY DEPTNO, JOB;

The following output results:

DEPTNO    JOB       ENAME            SAL
--------- --------- ---------- ---------
       10 CLERK     MILLER          1300
          *********            ---------
          avg                       1300

**********                    ----------
sum                                 1300

       30 CLERK     JAMES           1045
          *********           ----------
          avg                       1045

          SALESMAN  ALLEN           1760
          SALESMAN  MARTIN          1375
          SALESMAN  TURNER          1650
          SALESMAN  WARD            1375
          *********           ----------
          avg                       1540

**********                    ----------
sum                                 7205




Go to previous file in sequence Go to next file in sequence
Prev Next
Oracle
Copyright © 1997 Oracle Corporation.
All Rights Reserved.
Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index