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



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


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]

Represents the file you wish to edit (typically a command file).

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

Represents a PL/SQL 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}

Can be used interchangeably (QUIT is a synonym for EXIT).

SUCCESS

Exits normally.

FAILURE

Exits with a return code indicating failure.

WARNING

Exits with a return code indicating warning.

COMMIT

Saves pending changes to the database before exiting.

n

Represents an integer you specify as the return code.

variable

Represents a user-defined or system variable (but not a bind variable), such as SQL.SQLCODE. EXIT variable exits with the value of variable as the return code.

:BindVariable

Represents a variable created in SQL*Plus with the VARIABLE command, and then referenced in PL/SQL, or other subprograms. :BindVariable exits the subprogram and returns you to SQL*Plus.

ROLLBACK

Executes a ROLLBACK statement and abandons pending changes to the database before exiting.

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 TTITLE 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]

Represents the file you wish to load (typically a command file).

LIS[T]

Lists the contents of the file.

NOL[IST]

Suppresses the listing.

Usage Note

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 part of the filename you are specifying contains the word list or the word file, you need to put the name 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, type

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

Represents a SQL*Plus help topic. This can be a SQL*Plus command (for example, COLUMN), a SQL statement (for example, INSERT), or a PL/SQL statement (for example, IF).

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 (e.g., COL for COLUMN). However, if you enter only an abbreviated topic and the abbreviation is ambiguous, SQL*Plus will display help for all topics that match the abbreviation. For example, if you entered

SQL> HELP EX

SQL*Plus would display 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 and PL/SQL and SQL statements, 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

Represents a host operating system 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.

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

Represents the text you wish to add. To add a single line, enter the text of the line after the command INPUT, separating the text from the command with a space. To begin the line with one or more spaces, enter two or more spaces between INPUT and the first non-blank character of 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 in form 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 the password on an input device.

Syntax

PASSW[ORD] [username]

Terms and Clauses

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

username

Specifies the user. If you do not specify a username, username defaults to the current user.

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
SQL>

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
SQL>

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

Represents the text you wish to display.

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

Represents the names of the bind variables whose values you wish to display.

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

PROMPT [text]

Terms and Clauses

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

text

Represents the text of the message you wish to display. If you omit text, PROMPT displays a blank line on the user's screen.

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.

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".

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] ...] | [OFF|ON]

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

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] ...] | [OFF|ON]

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

Begins a new page after printing the specified report header or before printing the specified report footer.

Note: You must specify SET NEWPAGE 0 to create a physical page break using this command.

text

Represents the report header or footer text. Enter text in single quotes if you want to place more than one word on a single line. The default is NULL.

variable

Represents a user variable or any of the following system-maintained values:

To print one of these values, reference the appropriate variable in the report header or footer. You can format variable with the FORMAT clause.

OFF

Turns the report header or footer off (suppresses its display) without affecting its definition.

ON

Turns the report header or footer on (restores its display). When you define a report header or footer, SQL*Plus automatically sets REPHEADER or REPFOOTER to ON.

COL n

Indents to column n of the current line (backward if column n has been passed). "Column" in this context means print position, not table column.

S[KIP] [n]

Skips to the start of a new line n times; if you omit n, one time; if you enter zero for n, backward to the start of the current line.

TAB n

Skips forward n columns (backward if you enter a negative value for n). "Column" in this context means print position, not table column.

LE[FT], CE[NTER], and R[IGHT]

Left-align, center, and right-align data on the current line respectively. SQL*Plus aligns following data items as a group, up to the end of the printspec or the next LEFT, CENTER, RIGHT, or COL command. CENTER and RIGHT use the SET LINESIZE value to calculate the position of the data item that follows.

BOLD

Prints data in bold print. SQL*Plus represents bold print on your terminal by repeating the data on three consecutive lines. On some operating systems, SQL*Plus may instruct your printer to print bolded text on three consecutive lines, instead of bold.

FORMAT text

Specifies a format model that determines the format of following data items, up to the next FORMAT clause or the end of the command. The format model must be a text constant such as A10 or $999. See COLUMN FORMAT for more information on formatting and valid format models.

If the datatype of the format model does not match the datatype of a given data item, the FORMAT clause has no effect on that item.

If no appropriate FORMAT model precedes a given data item, SQL*Plus prints NUMBER values according to the format specified by SET NUMFORMAT or, if you have not used SET NUMFORMAT, the default format. SQL*Plus prints DATE values according to the default format.

Refer to the FORMAT clause of the COLUMN command in this chapter for more information on default formats.

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]

Specifies the command file in which you wish to save the buffer's contents.

CRE[ATE]

Creates the file if the file does not exist.

REP[LACE]

Replaces the contents of an existing file. If the file does not exist, REPLACE creates the file.

APP[END]

Adds the contents of the buffer to the end of the file you specify.

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.

Example

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 value represents a system variable followed by a value, as shown below:

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

Terms and Clauses

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

APPI[NFO]{ON|OFF|text}

Sets automatic registering of command files through the DBMS_APPLICATION_INFO package. This enables the performance and resource usage of each command file to be monitored by your DBA. The registered name appears in the MODULE column of the V$SESSION and V$SQLAREA virtual tables. You can also read the registered name using the DBMS_APPLICATION_INFO.READ_MODULE procedure.

ON registers command files invoked by the @, @@ or START commands. OFF disables registering of command files. Instead, the current value of text is registered. Text specifies the text to register when no command file is being run or when APPINFO is OFF. The default for text is "SQL*Plus." If you enter multiple words for text, you must enclose them in quotes. The maximum length for text is limited by the DBMS_APPLICATION_INFO package.

The registered name has the format nn@xfilename where: nn is the depth level of command file; x is '<' when the command file name is truncated, otherwise, it is blank; and filename is the command file name, possibly truncated to the length allowed by the DBMS_APPLICATION_INFO package interface.

Note: To use this feature, you must have access to the DBMS_APPLICATION_INFO package. Run DBMSUTIL.SQL (this name may vary depending on your operating system) as SYS to create the DBMS_APPLICATION_INFO package. DBMSUTIL.SQL is part of the Oracle8 Server product.

For more information on the DBMS_APPLICATION_INFO package, see "Registering Applications" in the Oracle8 Server Tuning manual.

Note: APPINFO is not available with TRUSTED Oracle.

ARRAY[SIZE] {20|n}

Sets the number of rows--called a batch--that SQL*Plus will fetch from the database at one time. Valid values are 1 to 5000. A large value increases the efficiency of queries and subqueries that fetch many rows, but requires more memory. Values over approximately 100 provide little added performance. ARRAYSIZE has no effect on the results of SQL*Plus operations other than increasing efficiency.

AUTO[COMMIT] {OFF|ON|IMM[EDIATE]|n}

Controls when Oracle commits pending changes to the database. ON commits pending changes to the database after Oracle executes each successful INSERT, UPDATE, or DELETE command or PL/SQL block. OFF suppresses automatic committing so that you must commit changes manually (for example, with the SQL command COMMIT). IMMEDIATE functions in the same manner as the ON option. n commits pending changes to the database after Oracle executes n successful SQL INSERT, UPDATE, or DELETE commands or PL/SQL blocks. n cannot be less than zero or greater than 2,000,000,000. The statement counter is reset to zero after successful completion of

Note: For this feature, a PL/SQL block is considered one transaction, regardless of the actual number of SQL commands contained within it.

AUTOP[RINT] {OFF|ON}

Sets the automatic PRINTing of bind variables. ON or OFF controls whether SQL*Plus automatically displays bind variables (referenced in a successful PL/SQL block or used in an EXECUTE command). For more information about displaying bind variables, see the PRINT command in this chapter.

AUTOT[RACE] {OFF|ON|TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

Displays a report on the execution of successful SQL DML statements (SELECT, INSERT, UPDATE or DELETE). The report can include execution statistics and the query execution path.

OFF does not display a trace report. ON displays a trace report. TRACEONLY displays a trace report, but does not print query data, if any. EXPLAIN shows the query execution path by performing an EXPLAIN PLAN. STATISTICS displays SQL statement statistics.

Using ON or TRACEONLY with no explicit options defaults to EXPLAIN STATISTICS.

The TRACEONLY option may be useful to suppress the query data of large queries. If STATISTICS is specified, SQL*Plus still fetches the query data from the server, however, the data is not displayed.

The AUTOTRACE report is printed after the statement has successfully completed.

Information about Execution Plans and the statistics is documented in the Oracle8 Server Tuning manual.

To use the EXPLAIN option, you must first create the table PLAN_TABLE in your schema. The description of this table is specific to the version of the database to which you are connected. Use UTLXPLAN.SQL (this name may vary depending on your operating system) to create PLAN_TABLE. UTLXPLAN.SQL is part of the Oracle8 Server product. Contact your DBA if you cannot create this table.

To access STATISTICS data, you must have access to several Dynamic Performance tables (for information about the Dynamic Performance or "V$" tables, see the Oracle8 Server documentation). Access can be granted using the role created in PLUSTRCE.SQL (this name may vary depending on your operating system). You must run PLUSTRCE.SQL as SYS and grant the role to users who will use SET AUTOTRACE. Contact your DBA to perform these steps.

When SQL*Plus produces a STATISTICS report, a second connection to the database is automatically created. This connection is closed when the STATISTICS option is set to OFF, or you log out of SQL*Plus.

The formatting of your AUTOTRACE report may vary depending on the version of the server to which you are connected and the configuration of the server.

AUTOTRACE is not available when FIPS flagging is enabled, or with TRUSTED Oracle.

See "Tracing Statements for more information on AUTOTRACE.

BLO[CKTERMINATOR] {.|c}

Sets the non-alphanumeric character used to end PL/SQL blocks to c. To execute the block, you must issue a RUN or / (slash) command.

CMDS[EP] {;|c|OFF|ON}

Sets the non-alphanumeric character used to separate multiple SQL*Plus commands entered on one line to c. ON or OFF controls whether you can enter multiple commands on a line; ON automatically sets the command separator character to a semicolon (;).

COLSEP { |text}

Sets the text to be printed between SELECTed columns. If the COLSEP variable contains blanks or punctuation characters, you must enclose it with single quotes. The default value for text is a single space.

In multi-line rows, the column separator does not print between columns that begin on different lines. The column separator does not appear on blank lines produced by BREAK ... SKIP n and does not overwrite the record separator. See SET RECSEP in this chapter for more information.

COM[PATIBILITY] {V7|V8|NATIVE}

Specifies the version of Oracle to which you are currently connected. Set COMPATIBILITY to V7 for Oracle7, or V8 for Oracle8. Set COMPATIBILITY to NATIVE if you wish the database to determine the setting (for example, if connected to Oracle8, compatibility would default to V8). COMPATIBILITY must be correctly set for the version of Oracle to which you are connected; otherwise, you will be unable to run any SQL commands. Note that you can set COMPATIBILITY to V7 when connected to Oracle8. This enables you to run Oracle7 SQL against Oracle8.

CON[CAT] {.|c|OFF|ON}

Sets the character you can use to terminate a substitution variable reference if you wish to immediately follow the variable with a character that SQL*Plus would otherwise interpret as a part of the substitution variable name. SQL*Plus resets the value of CONCAT to a period when you switch CONCAT on.

COPYC[OMMIT] {0|n}

Controls the number of batches after which the COPY command commits changes to the database. COPY commits rows to the destination database each time it copies n row batches. Valid values are zero to 5000. You can set the size of a batch with the ARRAYSIZE variable. If you set COPYCOMMIT to zero, COPY performs a commit only at the end of a copy operation.

COPYTYPECHECK {OFF|ON}

Sets the suppression of the comparison of datatypes while inserting or appending to tables with the COPY command. This is to facilitate copying to DB2, which requires that a CHAR be copied to a DB2 DATE.

DEF[INE] {&|c|OFF|ON}

Sets the character used to prefix substitution variables to c. ON or OFF controls whether SQL*Plus will scan commands for substitution variables and replace them with their values. ON changes the value of c back to the default '&', not the most recently used character. The setting of DEFINE to OFF overrides the setting of the SCAN variable. For more information on the SCAN variable, see the SET SCAN command in Appendix F.

ECHO {OFF|ON}

Controls whether the START command lists each command in a command file as the command is executed. ON lists the commands; OFF suppresses the listing.

EDITF[ILE] file_name[.ext]

Sets the default filename for the EDIT command. For more information about the EDIT command, see EDIT in this chapter.

You can include a path and/or file extension. For information on changing the default extension, see the SUFFIX variable of this command. The default filename and maximum filename length are operating system specific.

EMB[EDDED] {OFF|ON}

Controls where on a page each report begins. OFF forces each report to start at the top of a new page. ON allows a report to begin anywhere on a page. Set EMBEDDED to ON when you want a report to begin printing immediately following the end of the previously run report.

Note: When you use SET EMBEDDED ON and change the pagesize with SET PAGESIZE n, SQL*Plus finishes the current page using the existing pagesize setting and, if required, begins a new page with the new pagesize setting.

Note: When you use a BTITLE with SET EMBEDDED ON, the second and subsequent SELECT statements will always begin on a new page. This is because SQL*Plus has no input read ahead. Since SQL*Plus cannot anticipate whether you will enter another SELECT statement or, for example, EXIT, SQL*Plus has to complete processing all output from the first SELECT statement before it reads the next command. This processing includes printing the BTITLE. Therefore, given two SELECT statements, SQL*Plus prints the final BTITLE of the first SELECT statement before it processes the second. The second SELECT statement will then begin at the top of a new page.

Note: When you use a REPFOOTER with SET EMBEDDED ON, no footer will be displayed.

ESC[APE] {\|c|OFF|ON}

Defines the character you enter as the escape character. OFF undefines the escape character. ON enables the escape character. ON changes the value of c back to the default "\".

You can use the escape character before the substitution character (set through SET DEFINE) to indicate that SQL*Plus should treat the substitution character as an ordinary character rather than as a request for variable substitution.

FEED[BACK] {6|n|OFF|ON}

Displays the number of records returned by a query when a query selects at least n records. ON or OFF turns this display on or off. Turning feedback ON sets n to 1. Setting feedback to zero is equivalent to turning it OFF.

FLAGGER {OFF|ENTRY|INTERMED[IATE]|FULL}

Checks to make sure that SQL statements conform to the ANSI/ISO SQL92 standard. If any non-standard constructs are found, the Oracle Server flags them as errors and displays the violating syntax. This is the equivalent of the SQL language ALTER SESSION SET FLAGGER command.

You may execute SET FLAGGER even if you are not connected to a database. FIPS flagging will remain in effect across SQL*Plus sessions until a SET FLAGGER OFF (or ALTER SESSION SET FLAGGER = OFF) command is successful or you exit SQL*Plus.

When FIPS flagging is enabled, SQL*Plus displays a warning for the CONNECT, DISCONNECT, and ALTER SESSION SET FLAGGER commands, even if they are successful.

FLU[SH] {OFF|ON}

Controls when output is sent to the user's display device. OFF allows the host operating system to buffer output. ON disables buffering.

Use OFF only when you run a command file non-interactively (that is, when you do not need to see output and/or prompts until the command file finishes running). The use of FLUSH OFF may improve performance by reducing the amount of program I/O.

HEA[DING] {OFF|ON}

Controls printing of column headings in reports. ON prints column headings in reports; OFF suppresses column headings.

HEADS[EP] {||c|OFF|ON}

Defines the character you enter as the heading separator character. The heading separator character cannot be alphanumeric or white space. You can use the heading separator character in the COLUMN command and in the old forms of BTITLE and TTITLE to divide a column heading or title onto more than one line. ON or OFF turns heading separation on or off. When heading separation is OFF, SQL*Plus prints a heading separator character like any other character. ON changes the value of c back to the default "|".

LIN[ESIZE] {80|n}

Sets the total number of characters that SQL*Plus displays on one line before beginning a new line. It also controls the position of centered and right-aligned text in TTITLE, BTITLE, REPHEADER and REPFOOTER. You can define LINESIZE as a value from 1 to a maximum that is system dependent. Refer to the Oracle installation and user's manual(s) provided for your operating system.

LOBOF[FSET] {n|1}

Sets the starting position from which CLOB and NCLOB data is retrieved and displayed.

LONG {80|n}

Sets maximum width (in bytes) for displaying LONG, CLOB and NCLOB values; and for copying LONG values. The maximum value of n is 2 gigabytes.

LONGC[HUNKSIZE] {80|n}

Sets the size (in bytes) of the increments in which SQL*Plus retrieves a LONG, CLOB or NCLOB value.

When retrieving a CLOB or NCLOB value, you may want to retrieve it in increments rather than all at once because of memory size restrictions.

NEWP[AGE] {1|n|NONE}

Sets the number of blank lines to be printed from the top of each page to the top title. A value of zero places a formfeed at the beginning of each page (including the first page) and clears the screen on most terminals. If you set NEWPAGE to NONE, SQL*Plus does not print a blank line or formfeed between the report pages.

NULL text

Sets the text that represents a null value in the result of a SQL SELECT command. Use the NULL clause of the COLUMN command to override the setting of the NULL variable for a given column.

NUMF[ORMAT] format

Sets the default format for displaying numbers. Enter a number format for format. For number format descriptions, see the FORMAT clause of the COLUMN command in this chapter.

NUM[WIDTH] {10|n}

Sets the default width for displaying numbers. SQL*Plus rounds numbers up or down to the value of SET NUMWIDTH.

PAGES[IZE] {24|n}

Sets the number of lines in each page. You can set PAGESIZE to zero to suppress all headings, page breaks, titles, the initial blank line, and other formatting information.

PAU[SE] {OFF|ON|text}

Allows you to control scrolling of your terminal when running reports. ON causes SQL*Plus to pause at the beginning of each page of report output. You must press [Return] after each pause. The text you enter specifies the text to be displayed each time SQL*Plus pauses. If you enter multiple words, you must enclose text in single quotes.

You can embed terminal-dependent escape sequences in the PAUSE command. These sequences allow you to create inverse video messages or other effects on terminals that support such characteristics.

RECSEP {WR[APPED]|EA[CH]|OFF} RECSEPCHAR { |c}

Display or print record separators. A record separator consists of a single line of the RECSEPCHAR (record separating character) repeated LINESIZE times.

RECSEPCHAR defines the record separating character. A single space is the default.

RECSEP tells SQL*Plus where to make the record separation. For example, if you set RECSEP to WRAPPED, SQL*Plus prints a record separator only after wrapped lines. If you set RECSEP to EACH, SQL*Plus prints a record separator following every row. If you set RECSEP to OFF, SQL*Plus does not print a record separator.

SERVEROUT[PUT] {OFF|ON} [SIZE n] [FOR[MAT] {WRA[PPED]| WOR[D_WRAPPED]|TRU[NCATED]}]

Controls whether to display the output (that is, DBMS_OUTPUT.PUT_LINE) of stored procedures or PL/SQL blocks in SQL*Plus. OFF suppresses the output of DBMS_OUTPUT.PUT_LINE; ON displays the output.

SIZE sets the number of bytes of the output that can be buffered within the Oracle8 Server. The default for n is 2000. n cannot be less than 2000 or greater than 1,000,000.

When WRAPPED is enabled SQL*Plus wraps the server output within the line size specified by SET LINESIZE, beginning new lines when required.

When WORD_WRAPPED is enabled, each line of server output is wrapped within the line size specified by SET LINESIZE. Lines are broken on word boundaries. SQL*Plus left justifies each line, skipping all leading whitespace.

When TRUNCATED is enabled, each line of server output is truncated to the line size specified by SET LINESIZE.

For each FORMAT, every server output line begins on a new output line.

Note: The output is displayed synchronously after the stored procedure or PL/SQL block has been executed by the Oracle8 Server.

For more information on DBMS_OUTPUT.PUT_LINE, see your Oracle8 Server Application Developer's Guide.

SHIFT[INOUT] {VIS[IBLE]|INV[ISIBLE]}

Allows correct alignment for terminals that display shift characters. The SET SHIFTINOUT command is useful for terminals which display shift characters together with data (for example, IBM 3270 terminals). You can only use this command with shift sensitive character sets (for example, JA16DBCS).

Use VISIBLE for terminals that display shift characters as a visible character (for example, a space or a colon). INVISIBLE is the opposite and does not display any shift characters.

SHOW[MODE] {OFF|ON}

Controls whether SQL*Plus lists the old and new settings of a SQL*Plus system variable when you change the setting with SET. ON lists the settings; OFF suppresses the listing. SHOWMODE ON has the same behavior as the obsolete SHOWMODE BOTH.

SQLC[ASE] {MIX[ED]|LO[WER]|UP[PER]}

Converts the case of SQL commands and PL/SQL blocks just prior to execution. SQL*Plus converts all text within the command, including quoted literals and identifiers, as follows:

SQLCASE does not change the SQL buffer itself.

SQLCO[NTINUE] {> |text}

Sets the character sequence SQL*Plus displays as a prompt after you continue a SQL*Plus command on an additional line using a hyphen (-).

SQLN[UMBER] {OFF|ON}

Sets the prompt for the second and subsequent lines of a SQL command or PL/SQL block. ON sets the prompt to be the line number. OFF sets the prompt to the value of SQLPROMPT.

SQLPRE[FIX] {#|c}

Sets the SQL*Plus prefix character. While you are entering a SQL command or PL/SQL block, you can enter a SQL*Plus command on a separate line, prefixed by the SQL*Plus prefix character. SQL*Plus will execute the command immediately without affecting the SQL command or PL/SQL block that you are entering. The prefix character must be a non-alphanumeric character.

SQLP[ROMPT] {SQL>|text}

Sets the SQL*Plus command prompt.

SQLT[ERMINATOR] {;|c|OFF|ON}

Sets the character used to end and execute SQL commands to c. OFF means that SQL*Plus recognizes no command terminator; you terminate a SQL command by entering an empty line. ON resets the terminator to the default semicolon (;).

SUF[FIX] {SQL|text}

Sets the default file extension that SQL*Plus uses in commands that refer to command files. SUFFIX does not control extensions for spool files.

TAB {OFF|ON}

Determines how SQL*Plus formats white space in terminal output. OFF uses spaces to format white space in the output. ON uses the TAB character. TAB settings are every eight characters. The default value for TAB is system dependent.

Note: This option applies only to terminal output. Tabs will not be placed in output files.

TERM[OUT] {OFF|ON}

Controls the display of output generated by commands executed from a command file. OFF suppresses the display so that you can spool output from a command file without seeing the output on the screen. ON displays the output. TERMOUT OFF does not affect output from commands you enter interactively.

TI[ME] {OFF|ON}

Controls the display of the current time. ON displays the current time before each command prompt. OFF suppresses the time display.

TIMI[NG] {OFF|ON}

Controls the display of timing statistics. ON displays timing statistics on each SQL command or PL/SQL block run. OFF suppresses timing of each command. For information about the data SET TIMING ON displays, see the Oracle installation and user's manual(s) provided for your operating system. Refer to the TIMING command for information on timing multiple commands.

TRIM[OUT] {OFF|ON}

Determines whether SQL*Plus allows trailing blanks at the end of each displayed line. ON removes blanks at the end of each line, improving performance especially when you access SQL*Plus from a slow communications device. OFF allows SQL*Plus to display trailing blanks. TRIMOUT ON does not affect spooled output.

TRIMS[POOL] {ON|OFF}

Determines whether SQL*Plus allows trailing blanks at the end of each spooled line. ON removes blanks at the end of each line. OFF allows SQL*Plus to include trailing blanks. TRIMSPOOL ON does not affect terminal output.

UND[ERLINE] {-|c|ON|OFF}

Sets the character used to underline column headings in SQL*Plus reports to c. c cannot be an alphanumeric character or a white space. ON or OFF turns underlining on or off. ON changes the value of c back to the default "-".

VER[IFY] {OFF|ON}

Controls whether SQL*Plus lists the text of a SQL statement or PL/SQL command before and after SQL*Plus replaces substitution variables with values. ON lists the text; OFF suppresses the listing.

WRA[P] {OFF|ON}

Controls whether SQL*Plus truncates the display of a SELECTed row if it is too long for the current line width. OFF truncates the SELECTed row; ON allows the SELECTed row to wrap to the next line.

Use the WRAPPED and TRUNCATED clauses of the COLUMN command to override the setting of WRAP for specific columns.

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 the Oracle8 Server SQL Reference Manual 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
CMDSEP

To specify a TTITLE and format a column on the same line:

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 "|":

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 Oracle8:

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.

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

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.

LONG

To set the maximum width 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.

SERVEROUTPUT

To enable the display of 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.

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 set the default command-file extension 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]
LABEL
LNO
PNO
REL[EASE]
REPF[OOTER]
REPH[EADER]
SPOO[L]
SQLCODE
TTI[TLE]
USER

Terms and Clauses

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

system_variable

Represents any system variable set by the SET command.

ALL

Lists the settings of all SHOW options, except ERRORS and LABEL, in alphabetical order.

BTI[TLE]

Shows the current BTITLE definition.

ERR[ORS] [{FUNCTION|PROCEDURE|PACKAGE|PACKAGE BODY|TRIGGER|VIEW|TYPE|TYPE BODY} [schema.]name]

Shows the compilation errors of a stored procedure (includes stored functions, procedures, and packages). After you use the CREATE command to create a stored procedure, a message is displayed if the stored procedure has any compilation errors. To see the errors, you use SHOW ERRORS.

When you specify SHOW ERRORS with no arguments, SQL*Plus shows compilation errors for the most recently created or altered stored procedure. When you specify the type (function, procedure, package, package body, trigger, view, type, or type body) and the name of the PL/SQL stored procedure, SQL*Plus shows errors for that stored procedure. For more information on compilation errors, see your PL/SQL User's Guide and Reference.

schema contains the named object. If you omit schema, SHOW ERRORS assumes the object is located in your current schema.

Note: You must have DBA privilege to view other schemas, or other schema's object errors.

SHOW ERRORS output displays the line and column number of the error (LINE/COL) as well as the error itself (ERROR). LINE/COL and ERROR have default widths of 8 and 65, respectively. You can alter these widths using the COLUMN command.

LABEL

Shows the security level for the current session. For more information, see your Trusted Oracle Administrator's Guide.

LNO

Shows the current line number (the position in the current page of the display and/or spooled output).

PNO

Shows the current page number.

REL[EASE]

Shows the release number of Oracle that SQL*Plus is accessing.

REPF[OOTER]

Shows the current REPFOOTER definition.

REPH[EADER]

Shows the current REPHEADER definition.

SPOO[L]

Shows whether output is being spooled.

SQLCODE

Shows the value of SQL.SQLCODE (the SQL return code of the most recent operation).

TTI[TLE]

Shows the current TTITLE definition.

USER

Shows the username under which you are currently accessing SQL*Plus.

Example

To list the current LINESIZE, enter

SQL> SHOW LINESIZE

If the current linesize equals 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'

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]

Represents the name of the file to which you wish to spool. SPOOL followed by file_name begins spooling displayed output to the named file. If you do not specify an extension, SPOOL uses a default extension (LST or LIS on most systems).

OFF

Stops spooling.

OUT

Stops spooling and sends the file to your host computer's standard (default) printer.

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, type

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]

Represents the command file you wish to execute. The file can contain any command that you can run interactively.

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.

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

arg ...

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

The START command DEFINEs the parameters with the values of the arguments; if you START the command file again in this session, you can enter new arguments or omit the arguments to use the old values.

For more information on using parameters, refer to the subsection "Passing Parameters through the START Command" under "Writing Interactive Commands".

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 @ and @@ 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;

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

Saves the values of the system variables.

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

Sets up a timer and makes text the name of the timer. You can have more than one active timer by STARTing additional timers before STOPping the first; SQL*Plus nests each new timer within the preceding one. The timer most recently STARTed becomes the current timer.

SHOW

Lists the current timer's name and timing data.

STOP

Lists the current timer's name and timing data, then deletes the timer. If any other timers are active, the next most recently STARTed timer becomes the current timer.

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




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