 Library |
 Product |
 Contents |
 Index |
Library |
Product |
Contents |
Index |
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
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.
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
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.
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]
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
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
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
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
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
n m
n *
Lists line n through the current line.
n LAST
Lists line n through the last 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
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>
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 ...
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
----------
1Sends 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.
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 JOBPlaces 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 REPORTTo suppress the report footer without changing its definition, enter:
SQL> REPFOOTER OFF
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
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
40Saves 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
Sets a system variable to alter the SQL*Plus environment for your current session, such as
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 (;).
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.
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.
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.
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.
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.
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.
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 (;).
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.
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.
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.
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
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*PlusCMDSEP
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 wiSHIFTINOUT
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.
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]
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'
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
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
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;
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
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
|
Prev Next |
 Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library |
Product |
Contents |
Index |