SQL*Plus User's Guide and Reference
Release 8.1.7
Part Number A82950-01

Library

Product

Contents

Index

Go to previous pageGo to next page

8
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
@ ("at" sign)
 

Runs the specified command file. 

@@ (double "at" 
sign)
 

Runs the specified command file which is expected to be in the same directory as the script that called it. 

/ (slash)
 

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. 

ARCHIVE LOG
 

Starts or stops the automatic archiving of online redo log files, manually (explicitly) archives specified redo log files, or displays information about redo log files. 

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 clause 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 user to Oracle. 

COPY
 

Copies results 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 user 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 the specified text, then waits for the user to press [Return]. 

PRINT
 

Displays the current value of a bind variable. 

PROMPT
 

Sends the specified message to the user's screen. 

EXIT
 

Terminates SQL*Plus and returns control to the operating system. QUIT is identical to EXIT. 

RECOVER
 

Performs media recovery on one or more tablespaces, one or more datafiles, or the entire database. 

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. 

SHUTDOWN
 

Shuts down a currently running Oracle instance. 

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. 

STARTUP
 

Starts an Oracle instance and optionally mounts and opens a database. 

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]
arg...

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.

Examples

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]

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. For more information, see the START command later in this chapter.

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
NUM[BER]
CHAR
DATE
FOR[MAT]
DEF[AULT]
PROMPT text
NOPR[OMPT]
HIDE

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/99", enter

SQL> ACCEPT hired DATE FORMAT 'dd/mm/yy' DEFAULT '01/01/99'-
>       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

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.

ARCHIVE LOG

Purpose

Starts or stops automatic archiving of online redo log files, manually (explicitly) archives specified redo log files, or displays information about redo log files.

Syntax

ARCHIVE LOG {LIST|STOP}|{START|NEXT|ALL|integer} [TO destination]

Terms and Clauses

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

LIST
STOP
START
NEXT
ALL
integer
destination

Usage Notes

You must be connected to an open Oracle database as SYSOPER, or SYSDBA. For information about connecting to the database, see the CONNECT command.

If an online redo log file group fills and none are available for reuse, database operation is suspended. The condition can be resolved by archiving a log file group.

For information about specifying archive destinations, see your platform-specific Oracle documentation.


Note:
This command applies only to the current instance. To specify archiving for a different instance or for all instances in a Parallel Server, use the SQL command ALTER SYSTEM. For more information about using SQL commands, see the Oracle8i SQL Reference

Examples

To start up the archiver process and begin automatic archiving, using the archive destination specified in LOG_ARCHIVE_DEST, enter

SQL> ARCHIVELOG START

To stop automatic archiving, enter

SQL> ARCHIVELOG STOP

To archive the log file group with the sequence number 1001 to the destination specified, enter

SQL> ARCHIVELOG 1001 '/vobs/oracle/dbs/arch'

'arch' specifies the prefix of the filename on the destination device; the remainder of the filename is dependent on the initialization parameter LOG_ARCHIVE_FORMAT, which specifies the filename format for archived redo log files.

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
ALI[AS] alias
CLE[AR]
FOR[MAT] format
LIKE {type_name.attribute_name|alias}
ON|OFF

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]]
ON expr [action [action]]
ON ROW [action [action]]
ON REPORT [action]

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

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

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             950
           *********            ----------
           avg                         950


    DEPTNO JOB       ENAME             SAL
---------- --------- ---------- ----------
        30 SALESMAN  ALLEN            1600
           SALESMAN  WARD             1250
           SALESMAN  MARTIN           1250
           SALESMAN  TURNER           1500
           *********            ----------
           avg                        1400

**********                      ----------
sum                                   6550


6 rows selected.

BTITLE

Purpose

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

For a description of the old form of BTITLE, see Appendix F.

Syntax

BTI[TLE] [printspec [text|variable] ...] [ON|OFF]

Terms and Clauses

Refer to the TTITLE command for additional information on terms and clauses in the BTITLE command syntax.

Enter BTITLE with no clauses to list the current BTITLE definition.

Usage Notes

If you do not enter a printspec clause before the first occurrence of text, BTITLE left justifies the text. SQL*Plus interprets BTITLE in the new form if a valid printspec clause (LEFT, SKIP, COL, and so on) immediately follows the command name.

Examples

To set a bottom title with CORPORATE PLANNING DEPARTMENT on the left and a date on the right, enter

SQL> BTITLE LEFT 'CORPORATE PLANNING DEPARTMENT' -
> RIGHT '23 Nov 1999'

To set a bottom title with CONFIDENTIAL in column 50, followed by six spaces and a date, enter

SQL> BTITLE COL 50 'CONFIDENTIAL' TAB 6 '23 Nov 1999'

CHANGE

Purpose

Changes the first occurrence of text on the current line in the buffer.

Syntax

C[HANGE] sepchar old [sepchar [new [sepchar]]]

Terms and Clauses

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

sepchar
old
new

Usage Notes

CHANGE changes the first occurrence of the existing specified text on the current line of the buffer to the new specified text. The current line is marked with an asterisk (*) in the LIST output.

You can also use CHANGE to modify a line in the buffer that has generated an Oracle error. SQL*Plus sets the buffer's current line to the line containing the error so that you can make modifications.

To reenter an entire line, you can type the line number followed by the new contents of the line. If you specify a line number larger than the number of lines in the buffer and follow the number with text, SQL*Plus adds the text in a new line at the end of the buffer. If you specify zero ("0") for the line number and follow the zero with text, SQL*Plus inserts the line at the beginning of the buffer (that line becomes line 1).

Examples

Assume the current line of the buffer contains the following text:

4* WHERE JOB IS IN ('CLERK','SECRETARY','RECEPTIONIST')

Enter the following command:

SQL> C /RECEPTIONIST/GUARD/

The text in the buffer changes as follows:

4* WHERE JOB IS IN ('CLERK','SECRETARY','GUARD')

Or enter the following command:

SQL> C /'CLERK',.../'CLERK')/

The original line changes to

4* WHERE JOB IS IN ('CLERK')

Or enter the following command:

SQL> C /(...)/('COOK','BUTLER')/

The original line changes to

4* WHERE JOB IS IN ('COOK','BUTLER')

You can replace the contents of an entire line using the line number. This entry

SQL> 2  FROM EMP e1

causes the second line of the buffer to be replaced with

FROM EMP e1

Note that entering a line number followed by a string will replace the line regardless of what text follows the line number. For example,

SQL> 2  c/old/new/

will change the second line of the buffer to be

2* c/old/new/

CLEAR

Purpose

Resets or erases the current value or setting for the specified option.

Syntax

CL[EAR] option ...

where option represents one of the following clauses:

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

Terms and Clauses

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

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

Example

To clear breaks, enter

SQL> CLEAR BREAKS

To clear column definitions, enter

SQL> CLEAR COLUMNS

COLUMN

Purpose

Specifies display attributes for a given column, such as

Also lists the current display attributes for a single column or all columns.

Syntax

COL[UMN] [{column|expr} [option ...]]

where option represents one of the following clauses:

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]

Terms and Clauses

Enter COLUMN followed by column or expr and no other clauses to list the current display attributes for only the specified column or expression. Enter COLUMN with no clauses to list all current column display attributes.

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

{column|expr}
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]

Usage Notes

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

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

Examples

To make the ENAME column 20 characters wide and display EMPLOYEE NAME on two lines as the column heading, enter

SQL> COLUMN ENAME FORMAT A20 HEADING 'EMPLOYEE |NAME'

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

SQL> COLUMN SAL FORMAT $9,999,990.99

To assign the alias NET to a column containing a long expression, to display the result in a dollar format, and to display <NULL> for null values, you might enter

SQL> COLUMN SAL+COMM+BONUS-EXPENSES-INS-TAX ALIAS NET
SQL> COLUMN NET FORMAT $9,999,999.99 NULL '<NULL>'

Note that the example divides this column specification into two commands. The first defines the alias NET, and the second uses NET to define the format.

Also note that in the first command you must enter the expression exactly as you entered it (or will enter it) in the SELECT command. Otherwise, SQL*Plus cannot match the COLUMN command to the appropriate column.

To wrap long values in a column named REMARKS, you can enter

SQL> COLUMN REMARKS FORMAT A20 WRAP

For example:

CUSTOMER   DATE      QUANTITY REMARKS
---------- --------- -------- --------------------
123        25-AUG-86      144 This order must be s
                              hipped by air freigh
                              t to ORD

If you replace WRAP with WORD_WRAP, REMARKS looks like this:

CUSTOMER   DATE      QUANTITY REMARKS
---------- --------- -------- ---------------------
123        25-AUG-86      144 This order must be
                              shipped by air freight
                              to ORD

If you specify TRUNCATE, REMARKS looks like this:

CUSTOMER   DATE      QUANTITY REMARKS
---------- --------- -------- --------------------
123        25-AUG-86      144 This order must be s

In order to print the current date and the name of each job in the top title, enter the following. (For details on creating a date variable, see "Displaying the Current Date in Titles" under "Defining Page and Report Titles and Dimensions" in Chapter 4.)

SQL> COLUMN JOB NOPRINT NEW_VALUE JOBVAR
SQL> COLUMN TODAY  NOPRINT NEW_VALUE DATEVAR
SQL> BREAK ON JOB SKIP PAGE ON TODAY
SQL> TTITLE CENTER 'Job Report' RIGHT DATEVAR  SKIP 2 -
>    LEFT 'Job:     ' JOBVAR SKIP 2
SQL> SELECT TO_CHAR(SYSDATE, 'MM/DD/YY') TODAY,
  2  ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO
  3  FROM EMP WHERE JOB IN ('CLERK', 'SALESMAN')
  4  ORDER BY JOB, ENAME;

Your two page report would look similar to the following report, with "Job Report" centered within your current linesize:

                      Job Report             10/25/99

Job:     CLERK

ENAME             MGR HIREDATE         SAL     DEPTNO
---------- ---------- --------- ---------- ----------
ADAMS            7788 12-JAN-83       1100         20
JAMES            7698 03-DEC-81        950         30
MILLER           7782 23-JAN-82       1300         10
SMITH            7902 17-DEC-80        800         20
                                ----------
                                    1037.5

                      Job Report             10/25/99

Job:     SALESMAN

ENAME             MGR HIREDATE         SAL     DEPTNO
---------- ---------- --------- ---------- ----------
ALLEN            7698 20-FEB-81       1600         30
MARTIN           7698 28-SEP-81       1250         30
TURNER           7698 08-SEP-81       1500         30
WARD             7698 22-FEB-81       1250         30
                                ----------
                                      1400

8 rows selected.

To change the default format of DATE columns to 'YYYY-MM-DD', you can enter

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';

The following output results:

Session altered.

To display the change, enter a SELECT statement, such as:

SQL> SELECT HIREDATE
  2  FROM EMP
  3  WHERE EMPNO = 7839;

The following output results:

                      Job Report             10/25/99

Job:     SALESMAN

HIREDATE
----------
1981-11-17

See the Oracle8i SQL Reference for information on the ALTER SESSION command.

Note that in a SELECT statement, some SQL calculations or functions, such as TO_CHAR, may cause a column to be very wide. In such cases, use the FORMAT option to alter the column width.

COMPUTE

Purpose

Calculates and prints summary lines, using various standard computations, on subsets of selected rows, or lists all COMPUTE definitions. (For details on how to create summaries, see "Clarifying Your Report with Spacing and Summary Lines" in Chapter 4.)

Syntax

COMP[UTE] [function [LAB[EL] text] ...
   OF {expr|column|alias} ...
   ON {expr|column|alias|REPORT|ROW} ...]

Terms and Clauses

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

function ...
LAB[EL] text
OF {expr|column|alias} ...
ON {expr|column|alias|REPORT|ROW} ...

Enter COMPUTE without clauses to list all COMPUTE definitions.

Usage Notes

In order for the computations to occur, the following conditions must all be true:

To remove all COMPUTE definitions, use the CLEAR COMPUTES command.

Examples

To subtotal the salary for the "clerk", "analyst", and "salesman" job classifications with a compute label of "TOTAL", enter

SQL> BREAK ON JOB SKIP 1
SQL> COMPUTE SUM LABEL 'TOTAL' OF SAL ON JOB
SQL> SELECT JOB, ENAME, SAL
  2  FROM EMP
  3  WHERE JOB IN ('CLERK', 'ANALYST', 'SALESMAN')
  4  ORDER BY JOB, SAL;

The following output results:

JOB       ENAME             SAL
--------- ---------- ----------
ANALYST   SCOTT            3000
          FORD             3000
*********            ----------
TOTAL                      6000

CLERK     SMITH             800
          JAMES             950
          ADAMS            1100
          MILLER           1300
*********            ----------
TOTAL                      4150

SALESMAN  WARD             1250
          MARTIN           1250
          TURNER           1500
          ALLEN            1600
*********            ----------
TOTAL                      5600

To calculate the total of salaries less than 1,000 on a report, enter

SQL> COMPUTE SUM OF SAL ON REPORT
SQL> BREAK ON REPORT
SQL> COLUMN DUMMY HEADING ''
SQL> SELECT '   ' DUMMY, SAL, EMPNO
  2  FROM EMP
  3  WHERE SAL < 1000
  4  ORDER BY SAL;

The following output results:

           SAL       EMPNO
--- ---------- -----------
           800        7369
           950        7900
    ----------
sum       1750

To calculate the average and maximum salary for the accounting and sales departments, enter

SQL> BREAK ON DNAME SKIP 1
SQL> COMPUTE AVG LABEL 'Dept Average' -
>            MAX LABEL 'Dept Maximum' -
>       OF SAL ON DNAME
SQL> SELECT DNAME, ENAME, SAL
  2  FROM DEPT, EMP
  3  WHERE DEPT.DEPTNO = EMP.DEPTNO
  4  AND DNAME IN ('ACCOUNTING', 'SALES')
  5  ORDER BY DNAME;

The following output results:

DNAME          ENAME             SAL
-------------- ---------- ----------
ACCOUNTING     CLARK            2450
               KING             5000
               MILLER           1300
**************            ----------
Dept Average              2916.66667
Dept Maximum                    5000

SALES          ALLEN            1600
               BLAKE            2850
               MARTIN           1250
               JAMES             950
               TURNER           1500
               WARD             1250
**************            ----------
Dept Average              1566.66667
Dept Maximum                    2850

9 rows selected.

To calculate the sum of salaries for departments 10 and 20 without printing the compute label, enter

SQL> COLUMN DUMMY NOPRINT
SQL> COMPUTE SUM OF SAL ON DUMMY
SQL> BREAK ON DUMMY SKIP 1
SQL> SELECT DEPTNO DUMMY, DEPTNO, ENAME, SAL
  2  FROM EMP
  3  WHERE DEPTNO <= 20
  4  ORDER BY DEPTNO;

SQL*Plus displays the following output:

    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 CLARK            2450
        10 KING             5000
        10 MILLER           1300
                      ----------
                            8750

        20 SMITH             800
        20 ADAMS            1100
        20 FORD             3000
        20 SCOTT            3000
        20 JONES            2975
                      ----------
                           10875

8 rows selected.

To calculate the salary total at the end of the report without printing the compute label, enter

SQL> COLUMN DUMMY NOPRINT
SQL> COMPUTE SUM OF SAL ON DUMMY
SQL> BREAK ON DUMMY
SQL> SELECT NULL DUMMY, DEPTNO, ENAME, SAL
  2  FROM EMP
  3  WHERE DEPTNO <= 20
  4  ORDER BY DEPTNO;

SQL*Plus displays the following output:

    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 CLARK            2450
        10 KING             5000
        10 MILLER           1300
        20 SMITH             800
        20 ADAMS            1100
        20 FORD             3000
        20 SCOTT            3000
        20 JONES            2975
                      ----------
                           19625

8 rows selected.

CONNECT

Purpose

Connects a given username to Oracle.

Syntax

CONN[ECT]  [[logon] [AS {SYSOPER|SYSDBA}]]

where logon requires the following syntax:

username[/password][@connect_identifier]|/

Terms and Clauses

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

username[/password]
connect_identifier
/
AS {SYSOPER|SYSDBA}

Usage Notes

CONNECT commits the current transaction to the database, disconnects the current username from Oracle, and reconnects with the specified username.

If you log on or connect as a user whose account has expired, SQL*Plus prompts you to change your password before you can connect.

If an account is locked, a message is displayed and connection into that account (as that user) is not permitted until the account is unlocked by your DBA.

For more information about user account management, refer to the CREATE and ALTER USER commands, and the CREATE PROFILE command in the Oracle8i SQL Reference.

Examples

To connect across Net8 using username SCOTT and password TIGER to the database known by the Net8 alias as FLEETDB, enter

SQL> CONNECT SCOTT/TIGER@FLEETDB

To connect using username SCOTT, letting SQL*Plus prompt you for the password, enter

SQL> CONNECT SCOTT

For more information about setting up your password file, refer to the Oracle8i Administrator's Guide.

To use a password file to connect to an instance on the current node as a privileged user named SCOTT with the password TIGER, enter

SQL> CONNECT SCOTT/TIGER AS SYSDBA

To connect to an instance on the current node as a privileged default user, enter

SQL> CONNECT / AS SYSDBA

Note:
In the last two examples, your default schema becomes SYS. 

COPY

Purpose

Copies the data from a query to a table in a local or remote database. COPY supports the following datatypes:

CHAR
DATE
LONG
NUMBER
VARCHAR2

Syntax

COPY {FROM database | TO database | FROM database TO database}
            {APPEND|CREATE|INSERT|REPLACE} destination_table
            [(column, column, column, ...)] USING query

where database has the following syntax:

     username[/password]@connect_identifier

Terms and Clauses

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

FROM database
TO database
database
APPEND
CREATE
INSERT
REPLACE
destination_table
(column, column, column, ...)
USING query

Usage Notes

To enable the copying of data between Oracle and non-Oracle databases, NUMBER columns are changed to DECIMAL columns in the destination table. Hence, if you are copying between Oracle databases, a NUMBER column with no precision will be changed to a DECIMAL(38) column. When copying between Oracle databases, you should use SQL commands (CREATE TABLE AS and INSERT) or you should ensure that your columns have a precision specified.

The SQL*Plus SET variable LONG limits the length of LONG columns that you copy. If any LONG columns contain data longer than the value of LONG, COPY truncates the data.

SQL*Plus performs a commit at the end of each successful COPY. If you set the SQL*Plus SET variable COPYCOMMIT to a positive value n, SQL*Plus performs a commit after copying every n batches of records. The SQL*Plus SET variable ARRAYSIZE determines the size of a batch.

Some operating environments require that service names be placed in double quotes.

Examples

The following command copies the entire EMP table to a table named WESTEMP. Note that the tables are located in two different databases. If WESTEMP already exists, SQL*Plus replaces the table and its contents. The columns in WESTEMP have the same names as the columns in the source table, EMP.

SQL> COPY FROM SCOTT/TIGER@HQ TO JOHN/CHROME@WEST -
> REPLACE WESTEMP -
> USING SELECT * FROM EMP

The following command copies selected records from EMP to the database to which SQL*Plus is connected. SQL*Plus creates SALESMEN through the copy. SQL*Plus copies only the columns EMPNO and ENAME, and at the destination names them EMPNO and SALESMAN.

SQL> COPY FROM SCOTT/TIGER@HQ -
> CREATE SALESMEN (EMPNO,SALESMAN) -
> USING SELECT EMPNO, ENAME FROM EMP -
> WHERE JOB='SALESMAN'

DEFINE

Purpose

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

Syntax

DEF[INE] [variable]|[variable = text]

Terms and Clauses

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

variable
text
variable = text

Enter DEFINE followed by variable to list the value and type of variable. Enter DEFINE with no clauses to list the values and types of all user variables.

Usage Notes

DEFINEd variables retain their values until one of the following events occurs:

Whenever you run a stored query or command file, SQL*Plus substitutes the value of variable for each substitution variable referencing variable (in the form &variable or &&variable). SQL*Plus will not prompt you for the value of variable in this session until you UNDEFINE variable.

Note that you can use DEFINE to define the variable, _EDITOR, which establishes the host system editor invoked by the SQL*Plus EDIT command.

If you continue the value of a DEFINEd variable on multiple lines (using the SQL*Plus command continuation character), SQL*Plus replaces each continuation character and carriage return you enter with a space in the resulting variable. For example, SQL*Plus interprets

SQL> DEFINE TEXT = 'ONE-
> TWO-
> THREE'

as

SQL> DEFINE TEXT = 'ONE TWO THREE'

Examples

To assign the value MANAGER to the variable POS, type:

SQL> DEFINE POS = MANAGER

If you execute a command that contains a reference to &POS, SQL*Plus will substitute the value MANAGER for &POS and will not prompt you for a POS value.

To assign the CHAR value 20 to the variable DEPTNO, type:

SQL> DEFINE DEPTNO = 20

Even though you enter the number 20, SQL*Plus assigns a CHAR value to DEPTNO consisting of two characters, 2 and 0.

To list the definition of DEPTNO, enter

SQL> DEFINE DEPTNO
DEFINE DEPTNO          = "20" (CHAR)

This result shows that the value of DEPTNO is 20.

DEL

Purpose

Deletes one or more lines of the buffer.

Syntax

DEL [n|n m|n *|n LAST|*|* n|* LAST|LAST]

Terms and Clauses

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

Deletes line n

n m
 

Deletes lines n through m

n *
 

Deletes line n through the current line. 

n LAST
 

Deletes line n through the last line. 

*
 

Deletes the current line. 

* n
 

Deletes the current line through line n

* LAST
 

Deletes the current line through the last line. 

LAST
 

Deletes the last line. 

Enter DEL with no clauses to delete the current line of the buffer.

Usage Notes

DEL makes the following line of the buffer (if any) the current line. You can enter DEL several times to delete several consecutive lines.


Note:
DEL is a SQL*Plus command and DELETE is a SQL command. For more information about the SQL DELETE command, see the Oracle8i SQL Reference

Examples

Assume the SQL buffer contains the following query:

1  SELECT ENAME, DEPTNO
2  FROM EMP
3  WHERE JOB = 'SALESMAN'
4* ORDER BY DEPTNO

To make the line containing the WHERE clause the current line, you could enter

SQL> LIST 3
  3* WHERE JOB = 'SALESMAN'

followed by

SQL> DEL

The SQL buffer now contains the following lines:

1  SELECT ENAME, DEPTNO
2  FROM EMP
3* ORDER BY DEPTNO

To delete the second line of the buffer, enter

SQL> DEL 2

The SQL buffer now contains the following lines:

1  SELECT ENAME, DEPTNO
2* ORDER BY DEPTNO

DESCRIBE

Purpose

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

Syntax

DESC[RIBE] {[schema.]object[@connect_identifier]}

Terms and Clauses

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

schema
object
@connect_identifier

Usage Notes

The description for tables, views, types and synonyms contains the following information:

When you do a DESCRIBE, VARCHAR columns are returned with a type of VARCHAR2.

The DESCRIBE command allows you to describe objects recursively to the depth level set in the SET DESCRIBE command. You can also display the line number and indentation of the attribute or column name when an object contains multiple object types. For more information, see the SET command later in this chapter.

To control the width of the data displayed, use the SET LINESIZE command. For more information, see the SET command later in this chapter.

The description for functions and procedures contains the following information:

Examples

To describe the table EMP, enter

SQL> DESCRIBE EMP

SQL*Plus lists the following information:

Name                           Null?    Type
------------------------------ -------- ------------
EMPNO                          NOT NULL NUMBER(4)
ENAME                                   CHAR(10)
JOB                                     JOB(9)
MGR                                     NUMBER(4)
HIREDATE                                DATE
SAL                                     NUMBER(7,2)
COMM                                    NUMBER(7,2)
DEPTNO                                  NUMBER(2)

To describe a procedure called CUSTOMER_LOOKUP, enter

SQL> DESCRIBE customer_lookup

SQL*Plus lists the following information:

PROCEDURE customer_lookup
Argument Name          Type     In/Out   Default?
---------------------- -------- -------- ---------
CUST_ID                NUMBER   IN
CUST_NAME              VARCHAR2 OUT

To create and describe the package APACK that contains the procedures aproc and bproc, enter

SQL> CREATE PACKAGE apack AS
  2  PROCEDURE aproc(P1 CHAR, P2 NUMBER);
  3  PROCEDURE bproc(P1 CHAR, P2 NUMBER);
  4  END apack;
  5  /
SQL> DESCRIBE apack

SQL*Plus lists the following information:

PROCEDURE aproc
Argument Name          Type     In/Out   Default?
---------------------- -------- -------- ---------
P1                     CHAR     IN
P2                     NUMBER   IN
PROCEDURE bproc
Argument Name          Type     In/Out   Default?
---------------------- -------- -------- ---------
P1                     CHAR     IN
P2                     NUMBER   IN

To create and describe the object type ADDRESS that contains the attributes STREET and CITY, enter

SQL> CREATE TYPE ADDRESS AS OBJECT
  2  ( STREET  VARCHAR2(20),
  3    CITY    VARCHAR2(20)
  4  );
  5  /
SQL> DESCRIBE address

SQL*Plus lists the following information:

Name                           Null?    Type
------------------------------ -------- ------------
STREET                                  VARCHAR2(20)
CITY                                    VARCHAR2(20)

To create and describe the object type EMPLOYEE that contains the attributes ENAME, EMPADDR, JOB and SAL, enter

SQL> CREATE TYPE EMPLOYEE AS OBJECT
  2  ( ENAME   VARCHAR2(30),
  3    EMPADDR  ADDRESS,
  4    JOB     VARCHAR2(20),
  5    SAL     NUMBER(7,2)
  6  );
  7  /
SQL> DESCRIBE employee

SQL*Plus lists the following information:

Name                           Null?    Type
------------------------------ -------- ------------
ENAME                                   VARCHAR2(30)
EMPADDR                                 ADDRESS
JOB                                     VARCHAR2(20)
SAL                                     NUMBER(7,2)

To create and describe the object type addr_type as a table of the object type ADDRESS, enter

SQL> CREATE TYPE addr_type IS TABLE OF ADDRESS;
  2  /
SQL> DESCRIBE addr_type

SQL*Plus lists the following information:

addr_type TABLE OF ADDRESS
Name                           Null?    Type
------------------------------ -------- ------------
STREET                                  VARCHAR2(20)
CITY                                    VARCHAR2(20)

To create and describe the object type addr_varray as a varray of the object type ADDRESS, enter

SQL> CREATE TYPE addr_varray AS VARRAY(10) OF ADDRESS;
  2  /
SQL> DESCRIBE addr_varray

SQL*Plus lists the following information:

addr_varray VARRAY(10) OF ADDRESS
Name                           Null?    Type
------------------------------ -------- ------------
STREET                                  VARCHAR2(20)
CITY                                    VARCHAR2(20)

To create and describe the table dept_emp that contains the columns DEPTNO, PERSON and LOC, enter

SQL> CREATE TABLE dept_emp
  2  ( DEPTNO  NUMBER,
  3    PERSON  EMPLOYEE,
  4    LOC     NUMBER
  5  );
  6  /
SQL> DESCRIBE dept_emp

SQL*Plus lists the following information:

Name                           Null?    Type
------------------------------ -------- ------------
DEPTNO                                  NUMBER
PERSON                                  EMPLOYEE
LOC                                     NUMBER

To create and describe the object type rational that contains the attributes NUMERATOR and DENOMINATOR, and the METHOD rational_order, enter

SQL> CREATE OR REPLACE TYPE rational AS OBJECT
  2  ( NUMERATOR   NUMBER,
  3    DENOMINATOR NUMBER,
  4    MAP MEMBER FUNCTION rational_order - 
>       RETURN DOUBLE PRECISION,
  5    PRAGMA RESTRICT_REFERENCES
  6   (rational_order, RNDS, WNDS, RNPS, WNPS) );
  7  /
SQL> CREATE OR REPLACE TYPE BODY rational AS OBJECT
  2  MAP MEMBER FUNCTION rational_order - 
>     RETURN DOUBLE PRECISION IS 
  3   BEGIN
  4    RETURN NUMERATOR/DENOMINATOR;
  5   END;
  6  END;
  7  /
SQL> DESCRIBE rational

SQL*Plus lists the following information:

Name                           Null?    Type
------------------------------ -------- ------------
NUMERATOR                               NUMBER
DENOMINATOR                             NUMBER

METHOD
------
MAP MEMBER FUNCTION RATIONAL_ORDER RETURNS NUMBER

To describe the object emp_object and then format the output using the SET DESCRIBE command, first enter

SQL> DESCRIBE emp_object

SQL*Plus lists the following information:

 Name                                      Null     Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE                                           RECUR_PERSON
 DEPT                                               RECUR_DEPARTMENT
 START_DATE                                         DATE
 POSITION                                           VARCHAR2(1)
 SAL                                                RECUR_SALARY

To format the DESCRIBE output use the SET command as follows:

SQL> SET LINESIZE 80
SQL> SET DESCRIBE DEPTH 2
SQL> SET DESCRIBE INDENT ON
SQL> SET DESCRIBE LINE OFF

To display the settings for the object, use the SHOW command as follows:

SQL> SHOW DESCRIBE
describe DEPTH  2 LINENUM OFF INDENT ON
SQL> DESCRIBE emp_object

SQL*Plus lists the following information:

 Name                                      Null     Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE                                           RECUR_PERSON
   NAME                                             VARCHAR2(20)
   ADDR                                             RECUR_ADDRESS
   ADDR1                                            RECUR_ADDRESS1
   DOB                                              DATE
   GENDER                                           VARCHAR2(10)
 DEPT                                               RECUR_DEPARTMENT
   DEPTNO                                           NUMBER
   DEPT_NAME                                        VARCHAR2(20)
   LOCATION                                         VARCHAR2(20)
 START_DATE                                         DATE
 POSITION                                           VARCHAR2(1)
 SAL                                                RECUR_SALARY
   ANNUAL_SAL                                       NUMBER(10,2)
   EMP_TYPE                                         VARCHAR2(1)
   COMM                                             NUMBER(10,2)
   PENALTY_RATE                                     NUMBER(5,2)

For more information on using the CREATE TYPE command, see your Oracle8i SQL Reference.

For information about using the SET DESCRIBE and SHOW DESCRIBE commands, see the SET and SHOW commands later in this chapter.

DISCONNECT

Purpose

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

Syntax

DISC[ONNECT]
Usage Notes

Use DISCONNECT within a command file to prevent user access to the database when you want to log the user out of Oracle but have the user remain in SQL*Plus. Use EXIT or QUIT to log out of Oracle and return control to your host computer's operating system.

Example

Your command file might begin with a CONNECT command and end with a DISCONNECT, as shown below.

SQL> GET MYFILE
  1  CONNECT ...
       .
       .
       .
       .
 15* DISCONNECT

EDIT

Purpose

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

Syntax

ED[IT] [file_name[.ext]]

Terms and Clauses

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

file_name[.ext]

Enter EDIT with no filename to edit the contents of the SQL buffer with the host operating system text editor.

Usage Notes

If you omit the file extension, 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.

If you specify a filename, SQL*Plus searches for the file in the current working directory. If SQL*Plus cannot find the file in the current working directory, it creates a file with the specified name.

The user variable, _EDITOR, contains the name of the text editor invoked by EDIT. You can change the text editor by changing the value of _EDITOR. See DEFINE for information about changing the value of a user variable. If _EDITOR is undefined, EDIT attempts to invoke the default host operating system editor.

EDIT alone places the contents of the SQL buffer in a file by default named AFIEDT.BUF (in your current working directory) and invokes the text editor on the contents of the file. If the file AFIEDT.BUF already exists, it is overwritten with the contents of the buffer. You can change the default filename by using the SET EDITFILE command. For more information about setting a default filename for the EDIT command, see the EDITFILE variable of the SET command in this chapter.


Note:
The default file, AFIEDT.BUF, may have a different name on some operating systems. 

If you do not specify a filename and the buffer is empty, EDIT returns an error message.

To leave the editing session and return to SQL*Plus, terminate the editing session in the way customary for the text editor. When you leave the editor, SQL*Plus loads the contents of the file into the buffer.

Example

To edit the file REPORT with the extension SQL using your host operating system text editor, enter

SQL> EDIT REPORT

EXECUTE

Purpose

Executes a single PL/SQL statement. The EXECUTE command is often useful when you want to execute a PL/SQL statement that references a stored procedure. For more information on PL/SQL, see your PL/SQL User's Guide and Reference.

Syntax

EXEC[UTE] statement

Terms and Clauses

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

statement

Usage Notes

If your EXECUTE command cannot fit on one line because of the PL/SQL statement, use the SQL*Plus continuation character (a hyphen) as shown in the example below.

The length of the command and the PL/SQL statement cannot exceed the length defined by SET LINESIZE.

Examples

The following EXECUTE command assigns a value to a bind variable:

SQL> EXECUTE :n := 1

The following EXECUTE command runs a PL/SQL statement that references a stored procedure:

SQL> EXECUTE -
> :ID := EMP_MANAGEMENT.HIRE('BLAKE','MANAGER','KING',2990,'SALES')

Note that the value returned by the stored procedure is being placed in a bind variable, :ID. For information on how to create a bind variable, see the VARIABLE command in this chapter.

EXIT

Purpose

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

Syntax

{EXIT|QUIT} [SUCCESS|FAILURE|WARNING|n|variable|:BindVariable] [COMMIT|ROLLBACK]

Terms and Clauses

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

{EXIT|QUIT}
SUCCESS
FAILURE
WARNING
COMMIT
n
variable
:BindVariable
ROLLBACK

EXIT with no clauses commits and exits with a value of SUCCESS.

Usage Notes

EXIT allows you to specify an operating system return code. This allows you to run SQL*Plus command files in batch mode and to detect programmatically the occurrence of an unexpected event. The manner of detection is operating-system specific. See the Oracle installation and user's manual(s) provided for your operating system for details.

The key words SUCCESS, WARNING, and FAILURE represent operating-system dependent values. On some systems, WARNING and FAILURE may be indistinguishable.


Note:
SUCCESS, FAILURE, and WARNING are not reserved words. 

The range of operating system return codes is also restricted on some operating systems. This limits the portability of EXIT n and EXIT variable between platforms. For example, on UNIX there is only one byte of storage for return codes; therefore, the range for return codes is limited to zero to 255.

If you make a syntax error in the EXIT options or use a non-numeric variable, SQL*Plus performs an EXIT FAILURE COMMIT.

For information on exiting conditionally, see the WHENEVER SQLERROR and WHENEVER OSERROR commands later in this chapter.

Example

The following example commits all uncommitted transactions and returns the error code of the last executed SQL command or PL/SQL block:

SQL> EXIT SQL.SQLCODE

The location of the return code depends on your system. Consult your DBA for information concerning how your operating system retrieves data from a program. See the TTITLE command in this chapter for more information on SQL.SQLCODE.

GET

Purpose

Loads a host operating system file into the SQL buffer.

Syntax

GET file_name[.ext] [LIS[T]|NOL[IST]]

Terms and Clauses

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

file_name[.ext]
LIS[T]
NOL[IST]

Usage Notes

If you do not specify a file extension, 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.

If the filename you specify contains the word list or the word file, the name must be in double quotes. SQL*Plus searches for the file in the current working directory.

The operating system file should contain a single SQL statement or PL/SQL block. The statement should not be terminated with a semicolon.

If a SQL*Plus command or more than one SQL statement or PL/SQL block is loaded into the SQL buffer from an operating system file, an error occurs when the RUN or slash (/) command is used to execute the buffer.

The GET command can be used to load files created with the SAVE command. See the SAVE command in this chapter for more information.

Example

To load a file called YEARENDRPT with the extension SQL into the buffer, enter

SQL> GET YEARENDRPT

HELP

Purpose

Accesses the SQL*Plus help system.

Syntax

HELP [topic]

Terms and Clauses

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

topic

Enter HELP without topic to get help on the help system.

Usage Notes

You can only enter one topic after HELP. You can abbreviate the topic (for example, COL for COLUMN). However, if you enter only an abbreviated topic and the abbreviation is ambiguous, SQL*Plus displays help for all topics that match the abbreviation. For example, if you enter

SQL> HELP EX

SQL*Plus displays the syntax for the EXECUTE command followed by the syntax for the EXIT command.

If you get a response indicating that help is not available, consult your database administrator.

Example

To see a list of SQL*Plus commands for which help is available, enter

SQL> HELP INDEX

Alternatively, to see a single column display of SQL*Plus commands for which help is available, enter

SQL> HELP TOPICS

HOST

Purpose

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

Syntax

HO[ST] [command]

Terms and Clauses

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

command

Enter HOST without command to display an operating system prompt. You can then enter multiple operating system commands. For information on returning to SQL*Plus, refer to the Oracle installation and user's manual(s) provided for your operating system.


Note:
Operating system commands entered from a SQL*Plus session using the HOST command do not effect the current SQL*Plus session. For example, setting an operating system environment variable does not effect the current SQL*Plus session, it only effects SQL*Plus sessions started subsequently.

You can suppress access to the HOST command. For more information about suppressing the HOST command see Appendix E, "Security"


Usage Notes

With some operating systems, you can use a "$" (VMS), "!" (UNIX), or another character instead of HOST. See the Oracle installation and user's manual(s) provided for your operating system for details.

You may not have access to the HOST command, depending on your operating system. See the Oracle installation and user's manual(s) provided for your operating system or ask your DBA for more information.

SQL*Plus removes the SQLTERMINATOR (a semicolon by default) before the HOST 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 on the SQLTERMINATOR.

Example

To execute an operating system command, ls *.sql, enter

SQL> HOST ls *.sql

INPUT

Purpose

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

Syntax

I[NPUT] [text]

Terms and Clauses

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

text

To add several lines, enter INPUT with no text. INPUT prompts you for each line. To leave INPUT, enter a null (empty) line.

Usage Notes

If you enter a line number at the command prompt larger than the number of lines in the buffer, and follow the number with text, SQL*Plus adds the text in a new line at the end of the buffer. If you specify zero (0) for the line number and follow the zero with text, then SQL*Plus inserts the line at the beginning of the buffer (that line becomes line 1).

Examples

Assume the SQL buffer contains the following command:

1  SELECT ENAME, DEPTNO, SAL, COMM
2  FROM EMP

To add an ORDER BY clause to the query, enter

SQL> LIST 2
  2* FROM   EMP
SQL> INPUT ORDER BY ENAME

LIST 2 ensures that line 2 is the current line. INPUT adds a new line containing the ORDER BY clause after the current line. The SQL buffer now contains the following lines:

1  SELECT ENAME, DEPTNO, SAL, COMM
2  FROM  EMP
3* ORDER BY ENAME

To add a two-line WHERE clause, enter

SQL> LIST 2
  2* FROM EMP
SQL> INPUT
  3  WHERE JOB = 'SALESMAN'
  4  AND COMM  500
  5

INPUT prompts you for new lines until you enter an empty line. The SQL buffer now contains the following lines:

1  SELECT ENAME, DEPTNO, SAL, COMM
2  FROM EMP
3  WHERE JOB = 'SALESMAN'
4  AND COMM  500
5  ORDER BY ENAME

LIST

Purpose

Lists one or more lines of the SQL buffer.

Syntax

L[IST] [n|n m|n *|n LAST|*|* n|* LAST|LAST]

Terms and Clauses

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

Lists line n

n m
 

Lists lines n through m

n *
 

Lists line n through the current line. 

n LAST
 

Lists line n through the last line. 

*
 

Lists the current line. 

* n
 

Lists the current line through line n

* LAST
 

Lists the current line through the last line. 

LAST
 

Lists the last line. 

Enter LIST with no clauses to list all lines.

Usage Notes

The last line listed becomes the new current line (marked by an asterisk).

Example

To list the contents of the buffer, enter

SQL> LIST

You will see a listing of all lines in the buffer, similar to the following example:

  1  SELECT ENAME, DEPTNO, JOB
  2  FROM EMP
  3  WHERE JOB = 'CLERK'
  4* ORDER BY DEPTNO

The asterisk indicates that line 4 is the current line.

To list the second line only, enter

SQL> LIST 2

You will then see this:

  2* FROM EMP

To list the current line (now line 2) to the last line, enter

SQL> LIST * LAST

You will then see this:

  2  FROM EMP
  3  WHERE JOB = 'CLERK'
  4* ORDER BY DEPTNO

PASSWORD

Purpose

Allows you to change a password without echoing it on an input device.

Syntax

PASSW[ORD] [username]

Terms and Clauses

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

username

Usage Notes

To change the password of another user, you must have been granted the appropriate privilege.

For more information about changing your password, see the CONNECT command in this chapter.

Example

Suppose you are logged on as scott/tiger, and want to change the password to tigertiger

SQL> passw
Changing password for scott
Old password: tiger
New password: tigertiger
Retype new password: tigertiger
Password changed

Suppose you are logged on as a DBA, and want to change the password for user usera (currently identified by passa) to passusera

SQL> passw usera
Changing password for usera
New password: passusera
Retype new password: passusera
Password changed

PAUSE

Purpose

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.

Syntax

PAU[SE] [text]

Terms and Clauses

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

text

Enter PAUSE followed by no text to display two empty lines.

Usage Notes

Because PAUSE always waits for the user's response, it is best to use a message that tells the user explicitly to press [Return].

PAUSE reads input from the terminal (if a terminal is available) even when you have designated the source of the command input as a file.

For information on pausing between pages of a report, see the PAUSE variable of the SET command later in this chapter.

Example

To print "Adjust paper and press RETURN to continue." and to have SQL*Plus wait for the user to press [Return], you might include the following PAUSE command in a command file:

SET PAUSE OFF
PAUSE Adjust paper and press RETURN to continue.
SELECT ...

PRINT

Purpose

Displays the current value of bind variables. For more information on bind variables, see your PL/SQL User's Guide and Reference.

Syntax

PRI[NT] [variable ...]

Terms and Clauses

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

variable ...

Enter PRINT with no variables to print all bind variables.

Usage Notes

Bind variables are created using the VARIABLE command. For more information and examples, see the VARIABLE command in this chapter.

You can control the formatting of the PRINT output just as you would query output. For more information, see the formatting techniques described in Chapter 4.

To automatically display bind variables referenced in a successful PL/SQL block or used in an EXECUTE command, use the AUTOPRINT clause of the SET command. For more information, see the SET command in this chapter.

Example

The following example illustrates a PRINT command:

SQL> VARIABLE n NUMBER
SQL> BEGIN
  2   :n := 1;
  3  END;
SQL> PRINT n
         N
----------
         1

PROMPT

Purpose

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

Syntax

PRO[MPT] [text]

Terms and Clauses

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

text

Usage Notes

You can use this command in command files to give information to the user.

Example

The following example shows the use of PROMPT in conjunction with ACCEPT in a command file called ASKFORDEPT. ASKFORDEPT contains the following SQL*Plus and SQL commands:

PROMPT
PROMPT Please enter a valid department
PROMPT For example:  10, 20, 30, 40
ACCEPT NEWDEPT NUMBER PROMPT 'DEPT:> '
SELECT DNAME FROM DEPT
WHERE DEPTNO = &NEWDEPT

Assume you run the file using START or @:

SQL> @ASKFORDEPT

SQL*Plus displays the following prompts:

Please enter a valid department
For example:  10, 20, 30, 40
DEPT:>

You can enter a department number at the prompt DEPT:>. By default, SQL*Plus lists the line containing &NEWDEPT before and after substitution, and then displays the department name corresponding to the number entered at the DEPT:> prompt.

RECOVER

Purpose

Performs media recovery on one or more tablespaces, one or more datafiles, or the entire database. For more information on the RECOVER command, see the Oracle8i Administrator's Guide, the Oracle8i SQL Reference, and the Oracle8i Backup and Recovery Guide.

Syntax

RECOVER {general | managed} [parallel]

where the general clause has the following syntax:

  [AUTOMATIC] [FROM location]
  {[STANDBY] DATABASE [ [UNTIL {CANCEL | CHANGE integer | TIME date}
                        | USING BACKUP CONTROLFILE]...] 
   | STANDBY {DATAFILE datafilename [, DATAFILE datafilename...]
              | TABLESPACE tablespace [, TABLESPACE tablespace ...]}
       UNTIL [CONSISTENT] [WITH] CONTROLFILE
   | TABLESPACE tablespace [, tablespace ...]
   | DATAFILE datafilename [, datafilename ...]
   | LOGFILE filename
   | CONTINUE [DEFAULT]
   | CANCEL}

and where the managed clause has the following syntax:

  MANAGED STANDBY DATABASE {TIMEOUT integer | CANCEL [IMMEDIATE]}

and where the parallel clause has the following syntax:

  PARALLEL [integer] | NOPARALLEL

Terms and Clauses

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

AUTOMATIC
FROM location
STANDBY
DATABASE
UNTIL CANCEL
UNTIL CHANGE integer
UNTIL TIME date
USING BACKUP CONTROLFILE
STANDBY {[DATAFILE datafilename [, DATAFILE datafilename ...]}
STANDBY {TABLESPACE tablespace [, TABLESPACE tablespace ...]}
UNTIL [CONSISTENT] [WITH] CONTROLFILE
TABLESPACE tablespace
DATAFILE datafilename
LOGFILE filename
CONTINUE [DEFAULT]
CANCEL
MANAGED STANDBY DATABASE
TIMEOUT integer
CANCEL [IMMEDIATE]
PARALLEL [integer]
NOPARALLEL

Usage Notes

You must be connected to Oracle as SYSOPER, or SYSDBA.

You cannot use the RECOVER command when connected via the multi-threaded server.

To perform media recovery on an entire database (all tablespaces), the database must be mounted EXCLUSIVE and closed.

To perform media recovery on a tablespace, the database must be mounted and open, and the tablespace must be offline.

To perform media recovery on a datafile, the database can remain open and mounted with the damaged datafiles offline (unless the file is part of the SYSTEM tablespace).

Before using the RECOVER command you must have restored copies of the damaged datafile(s) from a previous backup. Be sure you can access all archived and online redo log files dating back to when that backup was made.

When another log file is required during recovery, a prompt suggests the names of files that are needed. The name is derived from the values specified in the initialization parameters LOG_ARCHIVE_DEST and LOG_ARCHIVE_FORMAT. You should restore copies of the archived redo log files needed for recovery to the destination specified in LOG_ARCHIVE_DEST, if necessary. You can override the initialization parameters by setting the LOGSOURCE variable with the SET LOGSOURCE command.

During recovery you can accept the suggested log name by pressing return, cancel recovery by entering CANCEL instead of a log name, or enter AUTO at the prompt for automatic file selection without further prompting.

If you have enabled autorecovery (that is, SET AUTORECOVERY ON), recovery proceeds without prompting you with filenames. Status messages are displayed when each log file is applied.

When normal media recovery is done, a completion status is returned.

Examples

To recover the entire database, enter

SQL> RECOVER DATABASE

To recover the database until a specified time, enter

SQL> RECOVER DATABASE UNTIL TIME 23-NOV-99:04:32:00

To recover the two tablespaces ts_one and ts_two from the database, enter

SQL> RECOVER TABLESPACE ts_one, ts_two

To recover the datafile data1.db from the database, enter

SQL> RECOVER DATAFILE 'data1.db'

REMARK

Purpose

Begins a comment in a command file. SQL*Plus does not interpret the comment as a command.

Syntax

REM[ARK]

Usage Notes

The REMARK command must appear at the beginning of a line, and the comment ends at the end of the line. A line cannot contain both a comment and a command.

For details on entering comments in command files using the SQL comment delimiters, /* ... */, or the ANSI/ISO comment delimiter, - - ..., refer to "Placing Comments in Command Files" in Chapter 3.

Example

The following command file contains some typical comments:

REM COMPUTE uses BREAK ON REPORT to break on end of table
BREAK ON REPORT
COMPUTE SUM OF "DEPARTMENT 10" "DEPARTMENT 20" -
"DEPARTMENT 30" "TOTAL BY JOB" ON REPORT
REM Each column displays the sums of salaries by job for
REM one of the departments 10, 20, 30.
SELECT JOB,
       SUM( DECODE( DEPTNO, 10, SAL, 0)) "DEPARTMENT 10",
       SUM( DECODE( DEPTNO, 20, SAL, 0)) "DEPARTMENT 20",
       SUM( DECODE( DEPTNO, 30, SAL, 0)) "DEPARTMENT 30",
       SUM(SAL) "TOTAL BY JOB"
FROM EMP
GROUP BY JOB

REPFOOTER

Purpose

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

Syntax

REPF[OOTER] [PAGE] [printspec [text|variable] ...] [ON|OFF]

Terms and Clauses

Refer to the REPHEADER command for additional information on terms and clauses in the REPFOOTER command syntax.

Enter REPFOOTER with no clauses to list the current REPFOOTER definition.

Usage Notes

If you do not enter a printspec clause before the text or variables, REPFOOTER left justifies the text or variables.

You can use any number of constants and variables in a printspec. SQL*Plus displays the constants and variables in the order you specify them, positioning and formatting each constant or variable as specified by the printspec clauses that precede it.


Note:
If SET EMBEDDED is ON, the report footer is suppressed. 

Example

To define "END EMPLOYEE LISTING REPORT" as a report footer on a separate page and to center it, enter:

SQL> REPFOOTER PAGE CENTER 'END EMPLOYEE LISTING REPORT'
SQL> TTITLE RIGHT 'Page: ' FORMAT 999 SQL.PNO
SQL> SELECT ENAME, SAL
  2  FROM EMP
  3  WHERE SAL > 2000;
                                                     Page: 1
ENAME             SAL
---------- ----------
JONES            2975
BLAKE            2850
CLARK            2450
SCOTT            3000
KING             5000
FORD             3000
                                                     Page: 2
                 END EMPLOYEE LISTING REPORT

6 rows selected.

To suppress the report footer without changing its definition, enter

SQL> REPFOOTER OFF

REPHEADER

Purpose

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

Syntax

REPH[EADER] [PAGE] [printspec [text|variable] ...] [ON|OFF]

where printspec represents one or more of the following clauses used to place and format the text:

COL n
S[KIP] [n]
TAB n
LE[FT]
CE[NTER]
R[IGHT]
BOLD
FORMAT text

Terms and Clauses

Refer to the following list for a description of each term or clause. These terms and clauses also apply to the REPFOOTER command.

PAGE
text
variable
OFF
COLn
S[KIP] [n]
TAB n
LE[FT] CE[NTER] R[IGHT]
BOLD
FORMATtext

Enter REPHEADER with no clauses to list the current REPHEADER definition.

Usage Notes

If you do not enter a printspec clause before the text or variables, REPHEADER left justifies the text or variables.

You can use any number of constants and variables in a printspec. SQL*Plus displays the constants and variables in the order you specify them, positioning and formatting each constant or variable as specified by the printspec clauses that precede it.

Example

To define "EMPLOYEE LISTING REPORT" as a report header on a separate page, and to center it, enter:

SQL> REPHEADER PAGE CENTER 'EMPLOYEE LISTING REPORT'
SQL> TTITLE RIGHT 'Page: ' FORMAT 999 SQL.PNO
SQL> SELECT ENAME, SAL
  2  FROM EMP
  3  WHERE SAL > 2000;
                                                     Page: 1
                  EMPLOYEE LISTING REPORT
                                                     Page: 2
ENAME             SAL
---------- ----------
JONES            2975
BLAKE            2850
CLARK            2450
SCOTT            3000
KING             5000
FORD             3000

6 rows selected.

To suppress the report header without changing its definition, enter:

SQL> REPHEADER OFF

RUN

Purpose

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

Syntax

R[UN]

Usage Notes

RUN causes the last line of the SQL buffer to become the current line.

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

Example

Assume the SQL buffer contains the following query:

SELECT DEPTNO FROM DEPT

To RUN the query, enter

SQL> RUN

The following output results:

1* SELECT DEPTNO FROM DEPT

    DEPTNO
----------
        10
        20
        30
        40

SAVE

Purpose

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

Syntax

SAV[E] file_name[.ext] [CRE[ATE]|REP[LACE]|APP[END]]

Terms and Clauses

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

file_name[.ext]
CREATE
REP[LACE]
APP[END]

Usage Notes

If you do not specify an extension, SQL*Plus assumes the default command-file extension (normally SQL). For information on changing this default extension, see the SUFFIX variable of the SET command in this chapter.

If you wish to SAVE a file under a name identical to a SAVE command clause (CREATE, REPLACE, or APPEND), you must specify a file extension.

When you SAVE the contents of the SQL buffer, SAVE adds a line containing a slash (/) to the end of the file.

If the filename you specify is the word file, you need to put the name in single quotes.

Examples

To save the contents of the buffer in a file named DEPTSALRPT with the extension SQL, enter

SQL> SAVE DEPTSALRPT

To save the contents of the buffer in a file named DEPTSALRPT with the extension OLD, enter

SQL> SAVE DEPTSALRPT.OLD

SET

Purpose

Sets a system variable to alter the SQL*Plus environment for your current session, such as

Syntax

SET system_variable value

where system_variable and value represent one of the following clauses.

APPI[NFO]{ON|OFF|text}
ARRAY[SIZE] {15|n}
AUTO[COMMIT]{ON|OFF|IMM[EDIATE]|n}
AUTOP[RINT] {ON|OFF}
AUTORECOVERY [ON|OFF]
AUTOT[RACE] {ON|OFF|TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
BLO[CKTERMINATOR] {.|c}
CMDS[EP] {;|c|ON|OFF}
COLSEP {_|text}
COM[PATIBILITY]{V7|V8|NATIVE}
CON[CAT] {.|c|ON|OFF}
COPYC[OMMIT] {0|n}
COPYTYPECHECK {ON|OFF}
DEF[INE] {&|c|ON|OFF}
DESCRIBE [DEPTH {1|n|ALL}][LINENUM {ON|OFF}][INDENT {ON|OFF}]
ECHO {ON|OFF}
EDITF[ILE] file_name[.ext]
EMB[EDDED] {ON|OFF}
ESC[APE] {\|c|ON|OFF}
FEED[BACK] {6|n|ON|OFF}
FLAGGER {OFF|ENTRY |INTERMED[IATE]|FULL}
FLU[SH] {ON|OFF}
HEA[DING] {ON|OFF}
HEADS[EP] {||c|ON|OFF}
INSTANCE [instance_path|LOCAL]
LIN[ESIZE] {80|n}
LOBOF[FSET] {n|1}
LOGSOURCE [pathname]
LONG {80|n}
LONGC[HUNKSIZE] {80|n}
MARK[UP] HTML [ON|OFF] [HEAD text] [BODY text] [TABLE text] [ENTMAP {ON|OFF}] 
[SPOOL {ON|OFF}] [PRE[FORMAT] {ON|OFF}]
NEWP[AGE] {1|n|NONE}
NULL text
NUMF[ORMAT] format
NUM[WIDTH] {10|n}
PAGES[IZE] {24|n}
PAU[SE] {ON|OFF|text}
RECSEP {WR[APPED]|EA[CH]|OFF} RECSEPCHAR {_|c}
SERVEROUT[PUT] {ON|OFF} [SIZE n] [FOR[MAT] {WRA[PPED]|WOR[D_
WRAPPED]|TRU[NCATED]}]
SHIFT[INOUT] {VIS[IBLE]|INV[ISIBLE]}
SHOW[MODE] {ON|OFF}
SQLBL[ANKLINES] {ON|OFF}
SQLC[ASE] {MIX[ED]|LO[WER]|UP[PER]}
SQLCO[NTINUE] {> |text}
SQLN[UMBER] {ON|OFF}
SQLPRE[FIX] {#|c}
SQLP[ROMPT] {SQL>|text}
SQLT[ERMINATOR] {;|c|ON|OFF}
SUF[FIX] {SQL|text}
TAB {ON|OFF}
TERM[OUT] {ON|OFF}
TI[ME] {ON|OFF}
TIMI[NG] {ON|OFF}
TRIM[OUT] {ON|OFF}
TRIMS[POOL] {ON|OFF}
UND[ERLINE] {-|c|ON|OFF}
VER[IFY] {ON|OFF}
WRA[P] {ON|OFF}

Terms and Clauses

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

APPI[NFO]{ON|OFF|text}
ARRAY[SIZE] {15|n}
AUTO[COMMIT]{ON|OFF|IMM[EDIATE]|n}
AUTOP[RINT] {ON|OFF}
AUTORECOVERY [ON|OFF]
AUTOT[RACE] {ON|OFF|TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
BLO[CKTERMINATOR] {.|c}
CMDS[EP] {;|c|ON|OFF}
COLSEP {_|text}
COM[PATIBILITY]{V7|V8|NATIVE}
CON[CAT] {.|c|ON|OFF}
COPYC[OMMIT] {0|n}
COPYTYPECHECK {ON|OFF}
DEF[INE] {&|c|ON|OFF}
DESCRIBE [DEPTH {1|n|ALL}][LINENUM {ON|OFF}][INDENT {ON|OFF}]
ECHO {ON|OFF}
EDITF[ILE] file_name[.ext]
EMB[EDDED] {ON|OFF}
ESC[APE] {\|c|ON|OFF}
FEED[BACK] {6|n|ON|OFF}
FLAGGER {OFF|ENTRY |INTERMED[IATE]|FULL}
FLU[SH] {ON|OFF}
HEA[DING] {ON|OFF}
HEADS[EP] {||c|ON|OFF}
INSTANCE [instance_path|LOCAL]
LIN[ESIZE] {80|n}
LOBOF[FSET] {n|1}
LOGSOURCE [pathname]
LONG {80|n}
LONGC[HUNKSIZE] {80|n}
MARK[UP] HTML [ON|OFF] [HEAD text] [BODY text] [TABLE text]

[ENTMAP {ON|OFF}] [SPOOL {ON|OFF}] [PRE[FORMAT] {ON|OFF}]
NEWP[AGE] {1|n|NONE}
NULL text
NUMF[ORMAT] format
NUM[WIDTH] {10|n}
PAGES[IZE] {24|n}
PAU[SE] {ON|OFF|text}
RECSEP {WR[APPED]|EA[CH]|OFF}

RECSEPCHAR {_|c}
SERVEROUT[PUT] {ON|OFF} [SIZE n] [FOR[MAT]

{WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]}]
SHIFT[INOUT] {VIS[IBLE]|INV[ISIBLE]}
SHOW[MODE] {ON|OFF}
SQLBL[ANKLINES] {ON|OFF}
SQLC[ASE] {MIX[ED]|LO[WER]|UP[PER]}
SQLCO[NTINUE] {> |text}
SQLN[UMBER] {ON|OFF}
SQLPRE[FIX] {#|c}
SQLP[ROMPT] {SQL>|text}
SQLT[ERMINATOR] {;|c|ON|OFF}
SUF[FIX] {SQL|text}
TAB {ON|OFF}
TERM[OUT] {ON|OFF}
TI[ME] {ON|OFF}
TIMI[NG] {ON|OFF}
TRIM[OUT] {ON|OFF}
TRIMS[POOL] {ON|OFF}
UND[ERLINE] {-|c|ON|OFF}
VER[IFY] {ON|OFF}
WRA[P] {ON|OFF}

Usage Notes

SQL*Plus maintains system variables (also called SET command variables) to allow you to establish a particular environment for a SQL*Plus session. You can change these system variables with the SET command and list them with the SHOW command.

SET ROLE and SET TRANSACTION are SQL commands (see theOracle8i SQL Reference for more information). When not followed by the keywords TRANSACTION or ROLE, SET is assumed to be a SQL*Plus command.

Examples

The following examples show sample uses of selected SET command variables.

APPINFO

To display the setting of APPINFO, enter

SQL> SHOW APPINFO
SQL> appinfo is ON and set to "SQL*Plus"

To change the default text, enter

SQL> SET APPI 'This is SQL*Plus'
SQL> SHOW APPINFO
SQL> appinfo is ON and set to "This is SQL*Plus"

To make sure that registration has taken place, enter

SQL> VARIABLE MOD VARCHAR2(50)
SQL> VARIABLE ACT VARCHAR2(40)
SQL> EXECUTE DBMS_APPLICATION_INFO.READ_MODULE(:MOD, :ACT);
SQL> PRINT MOD
MOD
---------------------------------------------------
This is SQL*Plus
AUTORECOVERY

To set the recovery mode to AUTOMATIC, enter

SQL> SET AUTORECOVERY ON
SQL> RECOVER DATABASE
CMDSEP

To specify a TTITLE and format a column on the same line, enter

SQL> SET CMDSEP +
SQL> TTITLE LEFT 'SALARIES' + COLUMN SAL FORMAT $9,999
SQL> SELECT ENAME, SAL FROM EMP
  2  WHERE JOB = 'CLERK';

The following output results:

SALARIES
ENAME          SAL
---------- -------
SMITH         $800
ADAMS       $1,100
JAMES         $950
MILLER      $1,300
COLSEP

To set the column separator to "|" enter

SQL> SET COLSEP '|'
SQL> SELECT ENAME, JOB, DEPTNO
  2  FROM EMP
  3  WHERE DEPTNO = 20;

The following output results:

ENAME     |JOB      |    DEPTNO
-------------------------------
SMITH     |CLERK    |        20
JONES     |MANAGER  |        20
SCOTT     |ANALYST  |        20
ADAMS     |CLERK    |        20
FORD      |ANALYST  |        20
COMPATIBILITY

To run a command file, SALARY.SQL, created with Oracle7, enter

SQL> SET COMPATIBILITY V7
SQL> START SALARY

After running the file, reset compatibility to V8 to run command files created with Oracle8i:

SQL> SET COMPATIBILITY V8

Alternatively, you can add the command SET COMPATIBILITY V7 to the beginning of the command file, and reset COMPATIBILITY to V8 at the end of the file.

DESCRIBE

To describe the object emp_object to a depth of two levels, and indent the output while also displaying line numbers, first describe the object as follows:

SQL> DESCRIBE emp_object

The following output results:

 Name                                      Null     Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE                                           RECUR_PERSON
 NAME                                               VARCHAR2(20)
 ADDR                                               RECUR_ADDRESS
 ADDR1                                              RECUR_ADDRESS1
 DOB                                                DATE
 GENDER                                             VARCHAR2(10)
 DEPT                                               RECUR_DEPARTMENT
 DEPTNO                                             NUMBER
 DEPT_NAME                                          VARCHAR2(20)
 LOCATION                                           VARCHAR2(20)
 START_DATE                                         DATE
 POSITION                                           VARCHAR2(1)
 SAL                                                RECUR_SALARY
 ANNUAL_SAL                                         NUMBER(10,2)
 EMP_TYPE                                           VARCHAR2(1)
 COMM                                               NUMBER(10,2)
 PENALTY_RATE                                       NUMBER(5,2)

To format emp_object so that the output displays with indentation and line numbers, use the SET DESCRIBE command as follows:

SQL> SET DESCRIBE DEPTH 2 LINENUM ON INDENT ON

To display the above settings, enter

SQL> DESCRIBE emp_object

The following output results:

           Name                           Null     Type
           ------------------------------  -------- ----------------------------
    1      EMPLOYEE                                 RECUR_PERSON
    2    1   NAME                                   VARCHAR2(20)
    3    1   ADDR                                   RECUR_ADDRESS
    4    1   ADDR1                                  RECUR_ADDRESS1
    5    1   DOB                                    DATE
    6    1   GENDER                                 VARCHAR2(10)
    7      DEPT                                     RECUR_DEPARTMENT
    8    7   DEPTNO                                 NUMBER
    9    7   DEPT_NAME                              VARCHAR2(20)
   10    7   LOCATION                               VARCHAR2(20)
   11      START_DATE                               DATE
   12      POSITION                                 VARCHAR2(1)
   13      SAL                                      RECUR_SALARY
   14   13   ANNUAL_SAL                             NUMBER(10,2)
   15   13   EMP_TYPE                               VARCHAR2(1)
   16   13   COMM                                   NUMBER(10,2)
   17   13   PENALTY_RATE                           NUMBER(5,2)
ESCAPE

If you define the escape character as an exclamation point (!), then

SQL> SET ESCAPE !
SQL> ACCEPT v1 PROMPT 'Enter !&1:'

displays this prompt:

Enter &1:
HEADING

To suppress the display of column headings in a report, enter

SQL> SET HEADING OFF

If you then run a SQL SELECT command,

SQL> SELECT ENAME, SAL FROM EMP
  2  WHERE JOB = 'CLERK';

the following output results:

ADAMS            1100
JAMES             950
MILLER           1300
INSTANCE

To set the default instance to "PROD1" enter

SQL> SET INSTANCE PROD1

To set the instance back to the default or local, enter

SQL> SET INSTANCE local

You must disconnect from any connected instances to change the instance.

LOBOFFSET

To set the starting position from which a CLOB column's data is retrieved to the 22nd position, enter

SQL> SET LOBOFFSET 22

The CLOB data will wrap on your screen; SQL*Plus will not truncate until the 23rd character.

LOGSOURCE

To set the default location of log files for recovery to the directory "/usr/oracle81/dbs/arch" enter

SQL> SET LOGSOURCE "/usr/oracle81/dbs/arch" 
SQL> RECOVER DATABASE
LONG

To set the maximum number of characters to fetch for displaying and copying LONG values to 500, enter

SQL> SET LONG 500

The LONG data will wrap on your screen; SQL*Plus will not truncate until the 501st character.

LONGCHUNKSIZE

To set the size of the increments in which SQL*Plus retrieves LONG values to 100 characters, enter

SQL> SET LONGCHUNKSIZE 100

The LONG data will be retrieved in increments of 100 characters until the entire value is retrieved or the value of SET LONG is reached, whichever is the smaller.

MARKUP

The following is a log of an interactive session to output HTML marked up text to a spool file:


Note:
The SET MARKUP example command is laid out for readability using line continuation characters "-" and spacing. Command options are concatenated in normal entry. 
SQL> SET MARKUP HTML ON SPOOL ON HEAD '<TITLE>Employee List</title> -
     <STYLE TYPE="TEXT/CSS"><!--BODY {background: yellow} --></STYLE>'
SQL&gt; SET ECHO OFF
<BR>
SQL&gt; SPOOL EMPLOYEE.HTML
<BR>
<HTML>
<HEAD>
<TITLE>EMPLOYEE LIST</TITLE><STYLE TYPE="TEXT/CSS"><!--BODY {background: yellow} 
--></STYLE>
<META NAME="GENERATOR" CONTENT="SQL*Plus 8.1.7">
</HEAD>
<BODY>
SQL&GT; SELECT DEPTNO, DNAME FROM DEPT;
<BR>
<P>
<TABLE BORDER="1" WIDTH="90%">
<TR>
<TH>
DEPTNO
</TH>
<TH>
DNAME
</TH>
</TR>
<TR>
<TD ALIGN="RIGHT">
        10
</TD>
<TD>
ACCOUNTING
</TD>
</TR>
<TR>
<TD ALIGN="RIGHT">
        20
</TD>
<TD>
RESEARCH
</TD>
</TR>
<TR>
<TD ALIGN="RIGHT">
        30
</TD>
<TD>
SALES
</TD>
</TR>
<TR>
<TD ALIGN="RIGHT">
        40
</TD>
<TD>
OPERATIONS
</TD>
</TR>
</TABLE>
<P>

SQL&GT; EXIT
<BR>
</BODY>
</HTML>
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production

The previous SQL*Plus session spools the HTML output to the spool file employee.html. This file is a standalone web page as follows:

Text description of 8-markup.gif follows.
Text description of the illustration 8-markup.gif

SERVEROUTPUT

To enable the display of text within a PL/SQL block using DBMS_OUTPUT.PUT_LINE, enter

SQL> SET SERVEROUTPUT ON

The following example shows what happens when you execute an anonymous procedure with SET SERVEROUTPUT ON:

SQL> BEGIN
  2  DBMS_OUTPUT.PUT_LINE('Task is complete');
  3  END;
  4  /
Task is complete.

PL/SQL procedure successfully completed.

The following example shows what happens when you create a trigger with SET SERVEROUTPUT ON:

SQL> CREATE TRIGGER SERVER_TRIG BEFORE INSERT OR UPDATE -
>    OR DELETE
  2  ON SERVER_TAB
  3  BEGIN
  4  DBMS_OUTPUT.PUT_LINE('Task is complete.');
  5  END;
  6  /
Trigger created.
SQL> INSERT INTO SERVER_TAB VALUES ('TEXT');
Task is complete.
1 row created.

To set the output to WORD_WRAPPED, enter

SQL> SET SERVEROUTPUT ON FORMAT WORD_WRAPPED
SQL> SET LINESIZE 20
SQL> BEGIN
  2  DBMS_OUTPUT.PUT_LINE('If there is nothing left to do');
  3  DBMS_OUTPUT.PUT_LINE('shall we continue with plan B?');
  4  end;
  5  /
If there is nothing
left to do
shall we continue
with plan B?

To set the output to TRUNCATED, enter

SQL> SET SERVEROUTPUT ON FORMAT TRUNCATED
SQL> SET LINESIZE 20
SQL> BEGIN
  2  DBMS_OUTPUT.PUT_LINE('If there is nothing left to do');
  3  DBMS_OUTPUT.PUT_LINE('shall we continue with plan B?');
  4  END;
  5  /
If there is nothing
shall we continue wi
SHIFTINOUT

To enable the display of shift characters, enter

SQL> SET SHIFTINOUT VISIBLE
SQL> SELECT ENAME, JOB FROM EMP;

The following output results:

ENAME      JOB
---------- ----------
:JJOO:     :AABBCC:
:AA:abc    :DDEE:e

where ":" = shift character

uppercase = multibyte character

lowercase = singlebyte character


Note:
This example illustrates that the columns are aligned correctly. The data used in this example is an illustration only and does not represent real data. 

SQLBLANKLINES

To preserve blank lines in a SQL statement, enter

SQL> SET SQLBLANKLINES ON
SQL> REM Using the SQLTERMINATOR (default is ";") 
SQL> REM Could have used the BLOCKTERMINATOR (default is ".")
SQL> SELECT *
  2  
  3  FROM
  4  
  5  DUAL
  6
  7 ;

The following output results:

D
-
X
SQLCONTINUE

To set the SQL*Plus command continuation prompt to an exclamation point followed by a space, enter

SQL> SET SQLCONTINUE '! '

SQL*Plus will prompt for continuation as follows:

SQL> TTITLE 'YEARLY INCOME' -
! RIGHT SQL.PNO SKIP 2 -
! CENTER 'PC DIVISION'
SQL>
SUFFIX

To change the default command-file extension from the default, .SQL to .UFI, enter

SQL> SET SUFFIX UFI

If you then enter

SQL> GET EXAMPLE

SQL*Plus will look for a file named EXAMPLE with an extension of UFI instead of EXAMPLE with an extension of SQL.

SHOW

Purpose

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

Syntax

SHO[W]  option

where option represents one of the following terms or clauses:

system_variable
ALL
BTI[TLE]
ERR[ORS] [{FUNCTION|PROCEDURE|PACKAGE|PACKAGE BODY|
   TRIGGER|VIEW|TYPE|TYPE BODY} [schema.]name]
LNO
PARAMETERS [parameter_name]
PNO
REL[EASE]
REPF[OOTER]
REPH[EADER]
SGA
SPOO[L]
SQLCODE
TTI[TLE]
USER

Terms and Clauses

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

system_variable
ALL
BTI[TLE]
ERR[ORS] [{FUNCTION|PROCEDURE|PACKAGE|PACKAGE BODY|TRIGGER|VIEW|TYPE|TYPE BODY} [schema.]name]
LNO
PARAMETERS [parameter_name]
PNO
REL[EASE]
REPF[OOTER]
REPH[EADER]
SPOO[L]
SGA
SQLCODE
TTI[TLE]
USER

Examples

To list the current LINESIZE, enter

SQL> SHOW LINESIZE

If the current linesize is 80 characters, SQL*Plus will give the following response:

linesize 80

The following example illustrates how to create a stored procedure and then show its compilation errors:

SQL> connect system/manager
SQL> create procedure scott.proc1 as
SQL> begin
SQL>   :p1 := 1;
SQL> end;
SQL> /
Warning: Procedure created with compilation errors.
SQL> show errors
Errors for PROCEDURE SCOTT.PROC1:
LINE/COL ERROR
--------------------------------------------------------
3/3      PLS-00049: bad bind variable 'P1'
SQL> show errors procedure proc1
No errors.
SQL> show errors procedure scott.proc1
Errors for PROCEDURE SCOTT.PROC1:
LINE/COL ERROR
--------------------------------------------------------
3/3      PLS-00049: bad bind variable 'P1'

To show whether AUTORECOVERY is enabled, enter

SQL> SHOW AUTORECOVERY
autorecovery ON

To display the connect identifier for the default instance, enter

SQL> SHOW INSTANCE
instance "local"

To display the location for archive logs, enter

SQL> SHOW LOGSOURCE
logsource "/usr/oracle81/dbs/arch"

To display information about the SGA, enter

SQL> SHOW SGA
Total System Global Area    7629732 bytes 
Fixed Size                    60324 bytes 
Variable Size               6627328 bytes 
Database Buffers             409600 bytes 
Redo Buffers                 532480 bytes

SHUTDOWN

Purpose

Shuts down a currently running Oracle instance, optionally closing and dismounting a database. You cannot use SHUTDOWN to stop Oracle instances on Oracle7 servers.

Syntax

SHUTDOWN [ABORT|IMMEDIATE|NORMAL]

Terms and Clauses

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

ABORT
IMMEDIATE
NORMAL

Usage Notes

SHUTDOWN with no arguments is equivalent to SHUTDOWN NORMAL.

You must be connected to a database as SYSOPER, or SYSDBA. You cannot connect via a multi-threaded server. For more information about connecting to a database, see the CONNECT command earlier in this chapter.

Example

To shutdown the database in normal mode, enter

SQL> SHUTDOWN 
Database closed. 
Database dismounted. 
Oracle instance shut down.

SPOOL

Purpose

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

Syntax

SPO[OL] [file_name[.ext]|OFF|OUT]

Terms and Clauses

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

file_name[.ext]
OFF
OUT

Enter SPOOL with no clauses to list the current spooling status.

Usage Notes

To spool output generated by commands in a command file without displaying the output on the screen, use SET TERMOUT OFF. SET TERMOUT OFF does not affect output from commands run interactively.

Examples

To record your displayed output in a file named DIARY using the default file extension, enter

SQL> SPOOL DIARY

To stop spooling and print the file on your default printer, enter

SQL> SPOOL OUT

START

Purpose

Executes the contents of the specified command file.

Syntax

STA[RT] file_name[.ext] [arg ...]

Terms and Clauses

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

file_name[.ext]
arg ...

Usage Notes

The @ ("at" sign) and @@ (double "at" sign) commands function similarly to START. Disabling the START command in the Product User Profile also disables the @ and @@ commands. See the @ ("at" sign) and @@ (double "at" sign) commands in this chapter for further information on these commands.

The EXIT or QUIT commands in a command file terminate SQL*Plus.

Example

A file named PROMOTE with the extension SQL, used to promote employees, might contain the following command:

SELECT * FROM EMP
WHERE MGR=&1 AND JOB='&2' AND SAL>&3;

To run this command file, enter

SQL> START PROMOTE 7280 CLERK 950

SQL*Plus then executes the following command:

SELECT * FROM EMP
WHERE MGR=7280 AND JOB='CLERK' AND SAL>950;

STARTUP

Purpose

Starts an Oracle instance with several options, including mounting and opening a database. You cannot use STARTUP to start Oracle instances on Oracle7 servers.

Syntax

STARTUP [FORCE] [RESTRICT] [PFILE=filename] [EXCLUSIVE] [PARALLEL [RETRY]] 
[SHARED [RETRY]] [MOUNT [dbname] | OPEN [open_options] [dbname] | NOMOUNT]

where open_options has the following syntax:

READ {ONLY | WRITE [RECOVER]} | RECOVER

Terms and Clauses

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

FORCE
RESTRICT
PFILE=filename
EXCLUSIVE
PARALLEL
SHARED
RETRY
MOUNT dbname
OPEN
NOMOUNT
RECOVER

Usage Notes

You must be connected to a database as SYSOPER, or SYSDBA. You cannot be connected via a multi-threaded server.

STARTUP with no arguments is equivalent to STARTUP OPEN.

STARTUP OPEN RECOVER mounts and opens the database even when recovery fails.

Examples

To start an instance using the standard parameter file, mount the default database in exclusive mode, and open the database, enter

SQL> STARTUP

or enter

SQL> STARTUP EXCLUSIVE OPEN database

To start an instance using the standard parameter file, mount the default database in parallel mode, and open the database, enter

SQL> STARTUP PARALLEL
SQL> STARTUP PARALLEL OPEN database

To restart an instance that went down in parallel mode and may not yet have been recovered by other instances, use the RETRY option:

SQL> STARTUP PARALLEL RETRY

To shutdown the current instance, immediately restart it without mounting or opening, and allow only database administrators to connect, enter

SQL> STARTUP FORCE RESTRICT NOMOUNT

To start an instance using the parameter file TESTPARM without mounting the database, enter

SQL> STARTUP PFILE=testparm NOMOUNT

To shutdown a particular database, immediately restart and open it in parallel mode, allow access only to database administrators, and use the parameter file MYINIT.ORA. enter

SQL> STARTUP FORCE RESTRICT PFILE=myinit.ora SHARED OPEN database

To startup an instance and mount but not open a database, enter

SQL> CONNECT / as SYSDBA 
Connected to an idle instance.
SQL> STARTUP MOUNT 
ORACLE instance started. 
 
Total System Global Area     7629732 bytes
Fixed Size                     60324 bytes
Variable Size                6627328 bytes
Database Buffers              409600 bytes
Redo Buffers                  532480 bytes

STORE

Purpose

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

Syntax

STORE SET file_name[.ext] [CRE[ATE]|REP[LACE]|APP[END]]

Terms and Clauses

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

SET

Refer to the SAVE command for information on the other terms and clauses in the STORE command syntax.

Usage Notes

This command creates a command file which can be executed with the START, @or @@ commands.

If you want to store a file under a name identical to a STORE command clause (that is, CREATE, REPLACE or APPEND), you must put the name in single quotes or specify a file extension.

Example

To store the current SQL*Plus system variables in a file named DEFAULTENV with the default command-file extension, enter

SQL> STORE SET DEFAULTENV

To append the current SQL*Plus system variables to an existing file called DEFAULTENV with the extension OLD, enter

SQL> STORE SET DEFAULTENV.OLD APPEND

TIMING

Purpose

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

Syntax

TIMI[NG] [START text|SHOW|STOP]

Terms and Clauses

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

START text
SHOW
STOP

Enter TIMING with no clauses to list the number of active timers.

Usage Notes

You can use this data to do a performance analysis on any commands or blocks run during the period.

For information about the data TIMING displays, see the Oracle installation and user's manual(s) provided for your operating system. Refer to SET TIMING ON for information on automatically displaying timing data after each SQL command or PL/SQL block you run.

To delete all timers, use the CLEAR TIMING command.

Examples

To create a timer named SQL_TIMER, enter

SQL> TIMING START SQL_TIMER

To list the current timer's title and accumulated time, enter

SQL> TIMING SHOW

To list the current timer's title and accumulated time and to remove the timer, enter

SQL> TIMING STOP

TTITLE

Purpose

Places and formats a specified title at the top of each report page or lists the current TTITLE definition. The old form of TTITLE is used if only a single word or string in quotes follows the TTITLE command.

For a description of the old form of TTITLE, see TTITLE in Appendix F.

Syntax

TTI[TLE] [printspec [text|variable] ...] [ON|OFF]

where printspec represents one or more of the following clauses used to place and format the text:

COL n
S[KIP] [n]
TAB n
LE[FT]
CE[NTER]
R[IGHT]
BOLD
FORMAT text

Terms and Clauses

Refer to the following list for a description of each term or clause. These terms and clauses also apply to the BTITLE command.

text
variable
OFF
ON
COLn
S[KIP] [n]
TAB n
LE[FT]|CE[NTER]|R[IGHT]
BOLD
FORMATtext

Enter TTITLE with no clauses to list the current TTITLE definition.

Usage Notes

If you do not enter a printspec clause before the first occurrence of text, TTITLE left justifies the text. SQL*Plus interprets TTITLE in the new form if a valid printspec clause (LEFT, SKIP, COL, and so on) immediately follows the command name.

See COLUMN NEW_VALUE for information on printing column and DATE values in the top title.

You can use any number of constants and variables in a printspec. SQL*Plus displays the constants and variables in the order you specify them, positioning and formatting each constant or variable as specified by the printspec clauses that precede it.

The length of the title you specify with TTITLE cannot exceed 2400 characters.

The continuation character (a hyphen) will not be recognized inside a single-quoted title text string. To be recognized, the continuation character must appear outside the quotes, as follows:

SQL> TTITLE CENTER 'Summary Report for' -
> 'the Month of May'

Examples

To define "Monthly Analysis" as the top title and to left-align it, to center the date, to right-align the page number with a three-digit format, and to display "Data in Thousands" in the center of the next line, enter

SQL> TTITLE LEFT 'Monthly Analysis' CENTER '23 Nov 99' -
> RIGHT 'Page:' FORMAT 999 SQL.PNO SKIP CENTER -
> 'Data in Thousands'

The following title results:

Monthly Analysis               23 Nov 99           Page:   1
                            Data in Thousands

To suppress the top title display without changing its definition, enter

SQL> TTITLE OFF

UNDEFINE

Purpose

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

Syntax

UNDEF[INE] variable ...

Terms and Clauses

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

variable

Examples

To undefine a user variable named POS, enter

SQL> UNDEFINE POS

To undefine two user variables named MYVAR1 and MYVAR2, enter

SQL> UNDEFINE MYVAR1 MYVAR2

VARIABLE

Purpose

Declares a bind variable that can then be referenced in PL/SQL. For more information on bind variables, see "Using Bind Variables" in Chapter 3. For more information about PL/SQL, see your PL/SQL User's Guide and Reference.

VARIABLE without arguments displays a list of all the variables declared in the session. VARIABLE followed only by a variable name lists that variable.

Syntax

VAR[IABLE] [variable [NUMBER|CHAR|CHAR (n)|NCHAR|NCHAR (n   |VARCHAR2 (n)|NVARCHAR2 (n)|CLOB|NCLOB|REFCURSOR]]

Terms and Clauses

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

variable
NUMBER
CHAR
CHAR (n)
NCHAR
NCHAR (n)
VARCHAR2 (n)
NVARCHAR2 (n)
CLOB
NCLOB
REFCURSOR

Usage Notes

Bind variables may be used as parameters to stored procedures, or may be directly referenced in anonymous PL/SQL blocks.

To display the value of a bind variable created with VARIABLE, use the PRINT command. For more information, see the PRINT command in this chapter.

To automatically display the value of a bind variable created with VARIABLE, use the SET AUTOPRINT command. For more information, see the SET AUTOPRINT command in this chapter.

Bind variables cannot be used in the COPY command or SQL statements, except in PL/SQL blocks. Instead, use substitution variables.

When you execute a VARIABLE ... CLOB or NCLOB command, SQL*Plus associates a LOB locator with the bind variable. The LOB locator is automatically populated when you execute a SELECT clob_column INTO :cv statement in a PL/SQL block. SQL*Plus closes the LOB locator after completing a PRINT statement for that bind variable, or when you exit SQL*Plus.

SQL*Plus SET commands such as SET LONG and SET LONGCHUNKSIZE and SET LOBOFFSET may be used to control the size of the buffer while PRINTing CLOB or NCLOB bind variables.

SQL*Plus REFCURSOR bind variables may be used to reference PL/SQL 2.3 or higher Cursor Variables, allowing PL/SQL output to be formatted by SQL*Plus. For more information on PL/SQL Cursor Variables, see your PL/SQL User's Guide and Reference.

When you execute a VARIABLE ... REFCURSOR command, SQL*Plus creates a cursor bind variable. The cursor is automatically opened by an OPEN ... FOR SELECT statement referencing the bind variable in a PL/SQL block. SQL*Plus closes the cursor after completing a PRINT statement for that bind variable, or on exit.

SQL*Plus formatting commands such as BREAK, COLUMN, COMPUTE and SET may be used to format the output from PRINTing a REFCURSOR.

A REFCURSOR bind variable may not be PRINTed more than once without re-executing the PL/SQL OPEN ... FOR statement.

Examples

The following example illustrates creating a bind variable and then setting it to the value returned by a function:

SQL> VARIABLE id NUMBER
SQL> BEGIN
  2    :id := emp_management.hire
  3      ('BLAKE','MANAGER','KING',2990,'SALES');
  4  END;

The bind variable named id can be displayed with the PRINT command or used in subsequent PL/SQL subprograms.

The following example illustrates automatically displaying a bind variable:

SQL> SET AUTOPRINT ON
SQL> VARIABLE a REFCURSOR
SQL> BEGIN
  2  OPEN :a FOR SELECT * FROM DEPT ORDER BY DEPTNO;
  3  END;
  4  /

PL/SQL procedure successfully completed.

DEPTNO   DNAME         LOC
-------- ------------- -------------
      10 ACCOUNTING    NEW YORK
      20 RESEARCH      DALLAS
      30 SALES         CHICAGO
      40 OPERATIONS    BOSTON

In the above example, there is no need to issue a PRINT command to display the variable.

The following example creates some variables and then lists them:

SQL> VARIABLE id NUMBER
SQL> VARIABLE txt CHAR (20)
SQL> VARIABLE myvar REFCURSOR
SQL> VARIABLE
variable id
datatype NUMBER

variable txt
datatype CHAR(20)

variable myvar
datatype REFCURSOR

The following example lists a single variable:

SQL> VARIABLE txt
variable txt
datatype CHAR(20)

The following example illustrates producing a report listing individual salaries and computing the departmental and total salary cost:

SQL> VARIABLE RC REFCURSOR
  2  BEGIN
  3    OPEN :RC FOR SELECT DNAME, ENAME, SAL
  4            FROM EMP, DEPT
  5            WHERE EMP.DEPTNO = DEPT.DEPTNO
  6            ORDER BY EMP.DEPTNO, ENAME;
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL> SET PAGESIZE 100 FEEDBACK OFF
SQL> TTITLE LEFT '*** Departmental Salary Bill ***' SKIP 2
SQL> COLUMN SAL FORMAT $999,990.99 HEADING 'Salary'
SQL> COLUMN DNAME HEADING 'Department'
SQL> COLUMN ENAME HEADING 'Employee'
SQL> COMPUTE SUM LABEL 'Subtotal:' OF SAL ON DNAME
SQL> COMPUTE SUM LABEL 'Total:' OF SAL ON REPORT
SQL> BREAK ON DNAME SKIP 1 ON REPORT SKIP 1
SQL> PRINT RC


*** Departmental Salary Bill ***

Department     Employee     Salary
-------------- ------------ ----------
ACCOUNTING     CLARK         $2,450.00
               KING          $5,000.00
               MILLER        $1,300.00
**************              ----------
Subtotal:                    $8,750.00

RESEARCH       ADAMS         $1,100.00
               FORD          $3,000.00
               JONES         $2,975.00
               SCOTT         $3,000.00
               SMITH           $800.00
**************              ----------
Subtotal:                   $10,875.00

SALES          ALLEN         $1,600.00
               BLAKE         $2,850.00
               JAMES           $950.00
               MARTIN        $1,250.00
               TURNER        $1,500.00
               WARD          $1,250.00
**************              ----------
Subtotal:                    $9,400.00

                            ----------
Total:                      $29,025.00

The following example illustrates producing a report containing a CLOB column, and then displaying it with the SET LOBOFFSET command.

Assume you have already created a table named clob_tab which contains a column named clob_col of type CLOB. The clob_col contains the following data:

Remember to run the Departmental Salary Bill report each month. This report 
contains confidential information.

To produce a report listing the data in the col_clob column, enter

SQL> variable t clob
SQL> begin
  2    select clob_col into t: from clob_tab;
  3  end;
  4  /
PL/SQL procedure successfully completed

To print 200 characters from the column clob_col, enter
SQL> set LONG 200
SQL> print t

The following output results:

T
------------------------------------------------------------
Remember to run the Departmental Salary Bill report each month. This report
contains confidential information.

To set the printing position to the 21st character, enter

SQL> set LOBOFFSET 21
SQL> print t

the following output results:

T
------------------------------------------------------------
Departmental Salary Bill report each month. This report contains confidential
information.

For more information on creating CLOB columns, see your Oracle8i SQL Reference.

WHENEVER OSERROR

Purpose

Exits SQL*Plus if an operating system error occurs (such as a file I/O error).

Syntax

WHENEVER OSERROR 
   {EXIT [SUCCESS|FAILURE|n|variable|:BindVariable][COMMIT|ROLLBACK]
   |CONTINUE [COMMIT|ROLLBACK|NONE]}

Terms and Clauses

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

EXIT [SUCCESS|FAILURE|n|variable|:BindVariable]
CONTINUE
COMMIT
ROLLBACK
NONE

Usage Notes

If you do not enter the WHENEVER OSERROR command, the default behavior of SQL*Plus is to continue and take no action when an operating system error occurs.

If you do not enter the WHENEVER SQLERROR command, the default behavior of SQL*Plus is to continue and take no action when a SQL error occurs.

Examples

The commands in the following command file cause SQL*Plus to exit and COMMIT any pending changes if a failure occurs when writing to the output file:

SQL> WHENEVER OSERROR EXIT
SQL> START no_such_file
OS Message: No such file or directory
Disconnected from Oracle8......

WHENEVER SQLERROR

Purpose

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

Syntax

WHENEVER SQLERROR
   {EXIT [SUCCESS|FAILURE|WARNING|n|variable|:BindVariable]
   [COMMIT|ROLLBACK]|CONTINUE [COMMIT|ROLLBACK|NONE]}

Terms and Clauses

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

EXIT [SUCCESS|FAILURE|WARNING|n|variable|:BindVariable]
CONTINUE
COMMIT
ROLLBACK
NONE

Usage Notes

The WHENEVER SQLERROR command is triggered by SQL command or PL/SQL block errors, and not by SQL*Plus command errors.

Examples

The commands in the following command file cause SQL*Plus to exit and return the SQL error code if the SQL UPDATE command fails:

SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE
SQL> UPDATE EMP SET SAL = SAL*1.1

The following SQL command error causes SQL*Plus to exit and return the SQL error code:

SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE
SQL> select column_does_not_exiSt from dual;
select column_does_not_exiSt from dual
       *
ERROR at line 1:
ORA-00904: invalid column name

Disconnected from Oracle.....

The following SQL command error causes SQL*Plus to exit and return the value of the variable my_error_var:

SQL> define my_error_var = 99
SQL> WHENEVER SQLERROR EXIT my_error_var
SQL> UPDATE non_existed_table set col1 = col1 + 1;

UPDATE NON_EXISTED_TABLE set col1 = col1 + 1
       *
ERROR at line 1:
ORA-00942: table or view does not exist

Disconnected from Oracle.....

The following examples show that the WHENEVER SQLERROR command does not have any effect on SQL*Plus commands, but does on SQL commands and PL/SQL blocks:

SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE
SQL> column ename headIing "Employee Name"

Unknown COLUMN option "headiing"

SQL> show non_existed_option

Unknown SHOW option "non_existed_option"
SQL> get non_existed_file.sql
Unable to open "non_existed_file.sql"

The following PL/SQL block error causes SQL*Plus to exit and return the SQL error code:

SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE
SQL> begin
  2    select column_does_not_exiSt from dual;
  3  end;
  4  /

select column_does_not_exiSt from dual;
       *
ERROR at line 2:
ORA-06550: line 2, column 10:
PLS-00201: identifier 'COLUMN_DOES_NOT_EXIST' must be declared
ORA-06550: line 2, column 3:
PL/SQL: SQL Statement ignored

Disconnected from Oracle.....

Go to previous page Go to next page
Oracle
Copyright © 2000 Oracle Corporation.
All Rights Reserved.

Library

Product

Contents

Index