Section A.2. Commands You Can Issue Within SQL*Plus
A 2 Commands You Can Issue Within SQL*Plus
The following sections describe in detail all the many commands that you can issue from within SQL*Plus. These are SQL*Plus commands only. For information on SQL statements or PL/SQL syntax, see Oracle's documentation, or a book such as Oracle in a Nutshell by Rick Greenwald and David C. Kreines (O'Reilly).
| Comment Delimiters (/* . . . */) |
The /* and */ delimiters set off a comment in SQL*Plus. Comments entered this way may span multiple lines. If you use /* . . . */ in a script file, the comments are displayed on the screen when the script is executed.
Syntax
/* comment_text comment_text comment_text */
Parameters
/*
Marks the beginning of the comment
comment_text
Is the text making up the comment
*/
Marks the end of the comment
| Double Hyphen (- -) |
The double hyphen places a single-line comment in a SQL*Plus script. The double hyphen works the same way as REMARK, except that it may be used in SQL statements and PL/SQL blocks. When used in a SQL statement or PL/SQL block, the double hyphen may be used to add trailing comments to a line.
Syntax
comment_text
Parameters
- -
Is a double hyphen and tells SQL*Plus that the line in question is a comment line
comment_text
Is the comment
| At Sign (@) |
The at sign executes a SQL*Plus script file. A script file is a text file containing SQL*Plus commands. The commands appear in the file just as you would enter them from the keyboard. See Chapter 2 and Chapter 8 for an introduction to this command. Also see the @@ and START commands.
From i SQL*Plus, you can only run scripts via http or ftp .
Syntax
@{
url
file
} [
argument
[
argument
. . . ]]
Parameters
url
Is a URL, either http or ftp , pointing to a script that you wish to execute. Oracle9 i Database Release 1 brought this option to SQL*Plus on Windows; Oracle9 i Database Release 2 brought it to SQL*Plus on all platforms.
file
Is the name of the file you want to execute, which may include the path and extension. The default extension is .sql . If you don't specify a path, SQL*Plus looks for the file first in the current working directory and then searches each directory listed in the SQLPATH environment variable. See Chapter 14 for information about customizing the search path.
argument
Is an argument you wish to pass to the script. You may pass as many arguments as you like. Arguments must be separated from each other by at least one space. Arguments may be enclosed in quotes, and should be if they contain spaces. Either single or double quotation marks may be used, at your discretion. Your script may reference the first argument as &1, the second as &2, and so forth.
| Double At Sign (@@) |
The double at sign is used within a script file to execute another script file contained in the same directory as the first. This is convenient if you have two scripts, one that calls the other. Use the @@ command in the first script file to call the second. Then put both files in the same directory. Now, regardless of whether that directory is in the search path, SQL*Plus will find the second file whenever the first calls for it. If used interactively, @@ functions exactly the same as @.
From i SQL*Plus, you can only run scripts via http or ftp .
Syntax
@{
url
file
} [
argument
[
argument
...]]
Parameters
Are the same as for the @ command.
| Forward Slash (/) |
A forward slash (/) executes the SQL statement or PL/SQL block that is in the buffer. See Chapter 2 for more information and for examples.
| ACCEPT |
The ACCEPT command gets input from a user. It causes SQL*Plus to display a prompt and wait for the user to type something in response. You can read about ACCEPT in Chapter 8 and in Chapter 11.
Syntax
ACC[EPT] variable [NUM[BER] CHAR DATE BINARY_FLOAT BINARY_DOUBLE] [FOR[MAT] format_specification ] [DEF[AULT] default_value ] [PROMPT prompt_text NOPR[OMPT]] [HIDE]
Parameters
ACC[EPT]
Is the command, which may be abbreviated ACC.
variable
Is the variable you want to define. Don't include leading ampersands. If your script uses &table_name for a substitution variable, you should use table_name here.
NUM[BER] CHAR DATE BINARY_FLOAT BINARY_DOUBLE
Is the type of data you are after. The default is CHAR, which allows the user to type in anything as a response. Use NUMBER, BINARY_FLOAT, or BINARY_DOUBLE to force the user to enter a number. Use DATE when you want a date.
FOR[MAT] format_specification
Is a format specification, which may optionally be enclosed in quotation marks. ACCEPT rejects any input that doesn't conform to the specification. An error message is displayed and the prompt is reissued. Specifying a format makes the most sense when dealing with numeric and date data, and SQL*Plus is somewhat loose in enforcing the format. Chapter 11 delves into this aspect of the ACCEPT command in detail. Format elements are described in Appendix B.
DEF[AULT] default_value
Specifies a default value to assign to the variable. This is used if the user bypasses the prompt by pressing ENTER without entering a response. The default value should be enclosed within quotes.
PROMPT prompt_text
This is the prompt text displayed to the user before waiting for input. Enclose it within quotes.
NOPR[OMPT]
Indicates that you don't want the user to see a visible prompt. i SQL*Plus, because it is not a traditional command-line application, ignores NOPROMPT and prompts anyway.
HIDE
Causes SQL*Plus not to echo the user's response back to the display. This is useful if you are prompting for a password.
|
| APPEND | Not available in i SQL*Plus |
APPEND, an editing command, lets you add text onto the end of the current line in the SQL buffer.
Syntax
A[PPEND] text
Parameters
A[PPEND]
Is the command, which may be abbreviated A
text
Is the text you want appended to the current line
APPEND, and all other editing commands, are described in Chapter 2.
| ARCHIVE LOG |
The ARCHIVE LOG command controls or displays information about archive logging. You must be connected as SYSDBA or SYSOPER to use this command.
Syntax
ARCHIVE LOG { LIST
STOP
START [TO
destination
]
NEXT [TO
destination
]
ALL [TO
destination
]
log_sequence_number
[TO
destination
]}
Parameters
LIST
Causes SQL*Plus to display information about the current state of archiving. This includes the current destination, an indication of whether automatic archiving is enabled (the ARCH process), the oldest online log sequence number, the sequence number of the next log to be archived, and the current log sequence number.
STOP
Stops log files from being automatically archived. You must manually archive redo log files as they fill; otherwise , you run the risk of the instance suspending operation because the log files have run out of space.
START
Turns on automatic archiving of redo log files.
NEXT
Manually archives the next log file group in the sequence provided that it is filled. Use ARCHIVE LOG LIST to see the sequence number of this file.
ALL
Manually archives all log file groups that have been filled but not previously archived.
log_sequence_number
Manually archives a specific log file group, provided that group is still online. Use ARCHIVE LOG LIST to find the sequence number of the oldest remaining log file group.
TO destination
Specifies a destination for archived log files. If used with ARCHIVE LOG START, this becomes the destination for all log files as they are archived. If used with NEXT, ALL, or a specific sequence number, this becomes the destination for files archived by that one command. If you don't specify a destination when using ARCHIVE LOG START, the value from the LOG_ARCHIVE_DEST initialization parameter is used.
| ATTRIBUTE |
The ATTRIBUTE command formats attributes of an Oracle object type. It functions like the COLUMN command but with fewer parameters. Chapter 7 shows examples of this command. Issuing the ATTRIBUTE command with no parameters gives you a list of all current attribute settings.
Syntax
ATTRIBUTE [
object_type
.
attribute
attribute_alias
[option [option . . . ]]
option ::= {ALI[AS]
alias
CLE[AR]
FOR[MAT]
format_spec
LIKE
source_attribute
ON
OFF}
Parameters
object_type
Is the name of an Oracle object type.
attribute
Is the name of an attribute of the specified object type and is the attribute you are formatting. If you stop here and don't supply any other parameters, the current display settings for this attribute are shown.
attribute_alias
Is an alias that you have previously given to an attribute via an ATTRIBUTE ALIAS command.
ALI[AS]
May be abbreviated ALI. ALIAS allows you to specify an alternate name for this attribute that is meaningful to SQL*Plus.
alias
Is an alternate name for the attribute that may be used in other ATTRIBUTE commands, in place of having to spell out the full object type and attribute name again.
CLE[AR]
May be abbreviated CLE. CLEAR erases any format settings for the attribute in question. This puts things back the way they were before any ATTRIBUTE commands were issued for the attribute.
FOR[MAT]
May be abbreviated FOR. Allows you to control how the data for the attribute are displayed. For text fields, the format controls the maximum display length. For numeric fields, you can control the width, placement of commas, placement of the dollar sign, etc.
format_spec
Is a string that specifies the display format for the attribute. Appendix B describes the format specification elements that may be used with the ATTRIBUTE command.
LIKE
Causes the attribute to be defined with the same format attributes as another attribute.
source_column
Is the name of the source attribute used with the LIKE parameter. This may be an alias or a complete attribute reference using the standard dot notation.
ON
Causes SQL*Plus to print the attribute using the format you have specified. This is the default behavior. You don't need to use ON unless you have previously used OFF.
OFF
Disables the format settings for the attribute. SQL*Plus acts as if you had never issued any ATTRIBUTE commands for the attribute in question.
When used with text attributes, formats such as A10 specify a maximum length to be displayed. Longer values are truncated to match the length specified, and shorter values are left alone.
| BREAK |
The BREAK command defines page breaks and line breaks based on changing column values in a report. It controls whether or not duplicate values print in a column, and it controls the printing of computed values such as totals and subtotals. Chapter 5 demonstrates the use of this command.
Syntax
BRE[AK] [ON
element_actiON
[ON
element_action
...]]
element_action
::= {
element
[
action_1
[
action_2
]]
element
[
action_2
[
action_1
]]}
element
::= {
column_name
ROW REPORT}
action_1
::= SKI[P] {
lines_to_skip
PAGE}
action_2
::= {NODUP[LICATES] DUP[LICATES]}
Parameters
BRE[AK]
Is the command, which may be abbreviated BRE. Issuing the BREAK command with no parameters causes SQL*Plus to display the current break setting.
column_name
Specifies a report column to watch. When the value in the column changes, SQL*Plus skips lines or pages as specified. SQL*Plus inhibits repeating, or duplicate, values from printing more than once unless the DUPLICATES keyword is used.
ROW
Causes SQL*Plus to break on each row. You could double space a report by using BREAK ON ROW SKIP 1.
REPORT
Specifies a report-level break and is used to cause SQL*Plus to print grand totals at the end of the report. SKIP PAGE is ignored if it is specified as a report break action, but, strangely enough, the other form of the SKIP parameter will work. You can skip lines on a report break.
SKI[P] lines_to_skip
Tells SQL*Plus to skip the specified number of lines when a break occurs. SKIP may be abbreviated SKI. Line skipping does not apply to HTML output, whether from i SQL*Plus or not, unless you have also requested such output to be preformatted within
. . .
tags.
SKI[P] PAGE
Tells SQL*Plus to advance to a new page when a break occurs.
NODUP[LICATES]
Tells SQL*Plus to print a column's value only when it changes. By default, whenever you put a break on a column, you get this behavior. May be abbreviated NODUP.
DUP[LICATES]
Forces SQL*Plus to print a column's value in every line on the report, regardless of whether or not the value is the same as that printed for the previous record. May be abbreviated DUP.
| BTITLE |
The BTITLE command defines page footers for a report. Chapter 5 discusses BTITLE and contains several examples. Also see the TTITLE command. BTITLE and TTITLE work the same way.
Syntax
BTITLE [OFF ON] option [ option . . . ] option ::= [COL x S[KIP] x TAB x LE[FT] CE[NTER] R[IGHT] BOLD FOR[MAT] format_spec text variable ]
Parameters
BTI[TLE]
Is the command, which may be abbreviated BTI. Issuing the BTITLE command with no parameters causes SQL*Plus to display the current bottom title setting.
OFF
Turns the page footer off but does not erase its definition. You can turn it back on with ON.
ON
Turns on printing of page footers. The default footer, if you do not specify another, will be the first part of the SELECT statement.
COL x
Causes any footer text following this parameter to print at the specified column position.
S[KIP] x
May be abbreviated S. Inserts the specified number of line breaks before printing any subsequent footer text.
TAB x
TAB is similar to COL but moves you the specified number of columns relative to the current position. Negative numbers move you backward. TAB has nothing whatsoever to do with tab characters .
LE[FT]
May be abbreviated LE. Causes subsequent footer text to be printed beginning at the leftmost column of the current footer line.
CE[NTER]
May be abbreviated CE. Causes subsequent footer text to be centered within the current line. The LINESIZE setting controls the line width.
R[IGHT]
May be abbreviated R. Causes subsequent footer text to be printed flush right. The LINESIZE setting controls where SQL*Plus thinks the right end of the line is.
BOLD
Makes your footer "bold" by printing it three times. Only title text following the BOLD command is repeated on each line. There is no NOBOLD parameter.
FOR[MAT]
May be abbreviated FOR. Allows you to control how subsequent numeric data in the footer is displayed.
format_spec
Is a string that specifies the display format to use for subsequent numeric data in the footer. The format elements you can use here are the same as for the COLUMN command, and are described in Appendix B. It is possible to specify a character format, such as A20, but that has no effect on subsequent character strings.
text
Is any text you want to have in the footer. To be safe, enclose this in quotes, but you don't have to as long as your title text doesn't include any keywords like BOLD or TAB that have meaning to BTITLE. Either single or double quotes may be used. If you need to include a quote as part of your text, use two quote characters back to back.
variable
May be one of the variables shown in Table A-1.
Table A-1. SQL*Plus system variables
|
System variable |
Value |
|---|---|
|
SQL.PNO |
Current page number |
|
SQL.LNO |
Current line number |
|
SQL.RELEASE |
Current Oracle release |
|
SQL.SQLCODE |
Error code returned by the most recent SQL query |
|
SQL.USER |
Oracle username of the user running the report |
When using BTITLE, you should start off with one of the keywords such as LEFT, RIGHT, or CENTER. Otherwise, if the first parameter after the command is text, SQL*Plus will assume you have used an obsolete syntax for this command, and you won't get the results you want.
| CHANGE | Not available in i SQL*Plus |
CHANGE, an editing command, allows you to search and replace on a line in the SQL buffer. The CHANGE command also deletes text.
Syntax
C[HANGE] / old_text [/[ new_text [/]]
Parameters
C[HANGE]
Is the command, which may be abbreviated C.
old_text
Is the text you want to change or delete.
new_text
Is the replacement text.
/
The forward slash character is commonly used to delimit the old and new text strings, but any other character may be used as long as it is not a number or a letter, and as long as it is used consistently throughout the command.
CHANGE and all the other editing commands are described in Chapter 2.
| CLEAR |
The CLEAR command deletes all column definitions, break settings, compute definitions, etc.
Syntax
CL[EAR] {BRE[AKS] BUFF[ER] COL[UMNS] COMP[UTES] SCR[EEN] SQL
TIMI[NG]}
Parameters
CL[EAR]
Is the command, which may be abbreviated CL.
BRE[AKS]
Deletes any break setting you may have defined using the BREAK command.
BUFF[ER]
Erases the contents of the buffer.
COL[UMNS]
Deletes any column definitions you may have made using the COLUMN command.
COMP[UTES]
Deletes any computations you may have defined using the COMPUTE command.
SCR[EEN]
Clears the screen. Does not apply to i SQL*Plus.
SQL
Erases the contents of the SQL buffer.
TIMI[NG]
Deletes any timers you may have created using the TIMING command.
| COLUMN |
The COLUMN command formats report output for columnar reports . Using this command, you can control column width, the column title, the way numbers are displayed, whether or not long values wrap to a new line, and a host of other things. Chapter 5 discusses this command.
Syntax
COL[UMN] [
column_name
[ALI[AS]
alias
CLE[AR]
ENTMAP {ON OFF}
FOLD_A[FTER]
FOLD_B[EFORE]
FOR[MAT]
format_spec
HEA[DING]
heading_text
JUS[TIFY] {LEFT CENTER CENTRE RIGHT}
LIKE
source_column_name
NEWL[INE]
NEW_V[ALUE]
variable
NOPRI[NT]
PRI[NT]
NUL[L]
null_text
OLD_V[ALUE]
variable
ON
OFF
TRU[NCATED]
WOR[D_WRAPPED]
WRA[PPED] . . . ]]
Parameters
COL[UMN]
May be abbreviated COL. Issuing the COLUMN command with no parameters gets you a list of all current column formats.
column_name
Is the name of the column you are formating. If it is a computed column, the expression is the name. If your SELECT statement aliases the column, you must use that alias name here. Issuing the command COLUMN with no further parameters causes SQL*Plus to display the current format for that column.
ALI[AS]
May be abbreviated ALI. ALIAS allows you to specify an alternate name for this column that is meaningful to SQL*Plus. Do not confuse this with the column alias in a SELECT statement.
alias
Is an alternate name for the column that may be used in BREAK commands, COMPUTE commands, and other COLUMN commands.
CLE[AR]
May be abbreviated CLE. CLEAR erases any format settings for the column in question. This puts you back to the way things were before any COLUMN commands were issued for the column.
ENTMAP {ON OFF}
Enables or disables HTML entity mapping for the column. When enabled, characters such as "<" are replaced by their corresponding HTML entity names , as in "<". The default is to follow the ENTMAP setting from the SET MARKUP options current in force.
FOLD_A[FTER]
May be abbreviated FOLD_A. Causes SQL*Plus to advance to a new line before displaying the next column. In other words, the output is wrapped after this column prints.
|
FOLD_B[EFORE]
May be abbreviated FOLD_B. This is the opposite of FOLD_AFTER and causes SQL*Plus to wrap to a new line before this column is printed.
FOR[MAT]
May be abbreviated FOR, and allows you to control how the data for the column are displayed. For text fields, you can control the width. For numeric fields, you can control the width, placement of commas, placement of the dollar sign, etc.
format_spec
Is a string that specifies the display format for the column. Appendix B describes the format specification elements that may be used with the COLUMN command.
HEA[DING]
May be abbreviated HEA. Allows you to define a heading for the column. The heading text displays at the top of each column and is redisplayed every time a page break occurs.
heading_text
Is the text you want for the column heading. You should enclose this in quotation marks, but you don't have to if the heading is a single word. Single or double quotation marks may be used. If you need to include a quotation mark as part of your heading, use two quote characters back to back.
JUS[TIFY]
May be abbreviated JUS. Controls where the heading text prints relative to the column width. By default, headings for numeric fields print flush right, and headings for text fields print flush left. This parameter allows you to change that behavior. You must follow this keyword with one of the following: LEFT, RIGHT, CENTER, or CENTRE. LEFT causes the heading to print flush left. RIGHT causes the heading to print flush right. CENTER and CENTRE cause the heading to print centered over the top of the column. Note that this parameter has no effect whatsoever on how the data for the column is displayed.
LIKE
Causes the column to be defined with the same format attributes as another column. LIKE must be followed by a column name, and that column becomes the source column.
source_column_name
Is the name of the source column used with the LIKE parameter.
NEWL[INE]
May be abbreviated NEWL. This is the same as FOLD_BEFORE and causes SQL*Plus to wrap to a new line before the column is printed.
|
NEW_V[ALUE]
May be abbreviated NEW_V. Causes SQL*Plus to keep a user variable updated with the current value of the column. The user variable is updated whenever the column value changes.
variable
Is the name of a user variable for use with the NEW_VALUE and OLD_VALUE parameters.
NOPRI[NT]
May be abbreviated NOPRI. Tells SQL*Plus not to print the column. NOPRINT is sometimes used when you want to get a column value into a user variable (see NEW_VALUE), but you don't want it displayed. This is often done when generating master/detail reports, as shown in Chapter 5.
PRI[NT]
May be abbreviated PRI. Is the opposite of NOPRINT. Use PRINT when you want to turn printing back on for a column.
NUL[L]
May be abbreviated NUL. Allows you to specify text to be displayed when the column value is null.
null_text
Is the text you want displayed when the column in question is null. As with the heading text, this may be optionally enclosed in quotes.
OLD_V[ALUE]
May be abbreviated OLD_V. Must be followed by a user variable name. OLD_VALUE works like NEW_VALUE, except that when the column changes, the previous value is stored in a user variable. This is useful when you need to print a value in the page footer of a master/detail report, as shown in Chapter 5.
ON
Causes SQL*Plus to print the column using the format you have specified. This is the default behavior. You don't need to use ON unless you have previously used OFF.
OFF
Disables the format settings for the column. SQL*Plus acts as if you had never issued any COLUMN commands for the column in question.
TRU[NCATED]
May be abbreviated TRU. Causes the column text to be truncated to the width of the column. Longer values are not wrapped.
WOR[D_WRAPPED]
May be abbreviated WOR. WORD_WRAPPED is similar to WRAPPED, but line breaks occur at word boundaries. Words that are longer than the column is wide will still be broken at the column boundary.
WRA[PPED]
May be abbreviated WRA. WRAPPED affects the printing of values that are longer than the column is wide and causes SQL*Plus to wrap those values to a new line as many times as necessary to print the entire value. Line breaks will occur exactly at the column boundary, even in the middle of a word.
| COMPUTE |
The COMPUTE command defines summary calculations needed in a report. You can use COMPUTE in conjunction with BREAK to calculate and print column totals, averages, minimum and maximum values, etc. These calculations are performed by SQL*Plus as the report runs. COMPUTE is a complex command, and must be used in conjunction with the BREAK command to get results. See the Section 7.1 in Chapter 7 for help with this command.
Syntax
COMP[UTE] [
functions
OF
column_names
ON
breaks
]
function
::=
function
[
function
...]
function
::= {AVG COU[NT] MAX[IMUM] MIN[IMUM]
NUM[BER] STD SUM VAR[IANCE]} [LABEL
label_text
]
column_names
::=
column_name
[
column_name
...]
break
::=
break
[
break
...]
break
::=
group_column_name
ROW REPORT}...]
You must have the same number of functions , column_names , and breaks .
Parameters
COMP[UTE]
Is the command, which may be abbreviated COMP. Entering COMPUTE with no parameters causes SQL*Plus to list all currently defined computations.
AVG
Computes the average of all non-null values for a column. AVG applies only to columns of type NUMBER.
COU[NT]
Computes the total number of non-null values for a column. COUNT may be used with columns of any datatype and may be abbreviated COU.
MAX[IMUM]
Computes the maximum value returned for a column. MAXIMUM may be abbreviated MAX, and applies to columns of type NUMBER, CHAR, VARCHAR2, NCHAR, and NVARCHAR2.
MIN[IMUM]
Computes the minimum value returned for a column. MINIMUM may be abbreviated MIN, and applies to columns of type NUMBER, CHAR, VARCHAR2, NCHAR, and NVARCHAR2.
NUM[BER]
Similar to COUNT, but computes the number of all values, including nulls. This applies to columns of any datatype and may be abbreviated NUM.
STD
Computes the standard deviation of all non-null values for a column. STD applies only to columns of type NUMBER.
SUM
Computes the sum of all non-null values for a column. SUM applies only to columns of type NUMBER.
VAR[IANCE]
Computes the variance of all non-null values for a column. VARIANCE applies only to columns of type NUMBER and may be abbreviated VAR.
LABEL
Allows you to specify a label for the computed value. If possible, this label will be printed to the left of the computed value.
label_text
Is the text you want to use as a label when the computed value is printed. This may be enclosed in single or double quotation marks. To embed a quotation mark within the label when that label has been quoted, place two quote characters back to back.
column_name
Is the name of the column you are summarizing. If the column is a computed column, the expression is the name. If your SELECT statement aliases the column, you must use that alias name here.
group_column_name
Causes SQL*Plus to restart the calculation every time this column changes. Typically, the report is sorted or grouped by this column, and the computed value is printed once for each distinct value of the group column.
ROW
Causes the computation to be performed once for each row returned by the query.
REPORT
Causes the computation to be performed at the end of the report and to include values from all rows. REPORT is used for grand totals.
| CONNECT |
The CONNECT command changes your database connection, logs in as a different user, or connects you to the database in an administrative mode. Chapter 2 provides examples of CONNECT.
Syntax
CONN[ECT] [
username
[/
password
][@
connect
] / ] [AS {SYSOPER SYSDBA}]
Parameters
CONN[ECT]
Is the command, which may be abbreviated CONN.
username
Is your database username.
password
Is your database password.
connect
Is the connect string, or host string, telling SQL*Plus the database to which you want to connect.
/
Use a forward slash instead of your username, password, and connect string when you want to connect to a local database using operating system authentication or when you want to connect as SYSDBA or SYSOPER while logged in as the Oracle software owner.
AS {SYSOPER SYSDBA}
Tells SQL*Plus you are connecting in an administrative role.
| COPY |
The COPY command uses SQL*Plus as a conduit for transferring data between two Oracle databases.
|
Syntax
COPY [FROM
connection
] [TO
connection
]
{APPEND CREATE INSERT REPLACE}
destination_table
[(
column_list
)]
USING
select_statement
Parameters
[FROM connection] [TO connection]
Specifies the source and target databases for the copy. The database to which you are connected is the default in both cases and will be used as both source and destination unless you specify otherwise.
connection
Is the login information to use when connecting to the other database. This must be in the typical username / password @ connect_string format.
APP[END]
Causes SQL*Plus to insert the copied rows into the destination table, creating it first if necessary.
CRE[ATE]
Causes SQL*Plus to copy the data only if the destination table is a new table. If the destination table exists, the COPY command will abort.
INSERT
Causes SQL*Plus to insert the copied rows into the destination table only if it exists. If the destination table is a new table, the COPY command will abort.
REP[LACE]
Causes SQL*Plus to drop the destination table if it currently exists. A new table is created, and the data are copied.
destination_table
Is the name of the table to which you want to copy the data.
column_list
Specifies column names to use when the COPY command creates a new destination table. This is a comma-delimited list, and the number of column names must match the number of columns in the SELECT statement.
select_statement
Specifies a SELECT statement that returns the data you want to COPY.
| DEFINE |
The DEFINE command creates a user variable (or substitution variable) and may assign it a value. DEFINE may be used to list the value of a particular variable, or of all variables. DEFINE is discussed in Chapter 8.
Syntax
DEF[INE] [ variable_name [= text ]]
Parameters
DEF[INE]
Is the command, which may be abbreviated DEF. Entering DEFINE by itself causes SQL*Plus to display a list of all currently defined user variables.
variable_name
Is the name of the variable you want to create. Issue the command with only a variable name, and SQL*Plus will display the current contents of that variable if it exists.
text
Is the text you want to assign to that variable. This may be optionally enclosed by single or double quotes, which you should use any time the value contains spaces or any other nonalphabetic character.
| DEL | Not available in i SQL*Plus |
The DEL command, an editing command, deletes the current line from the buffer.
Syntax
DEL [{
b
* L}[ {
e
* L}]]
Parameters
b
Is a line number representing the beginning of a range of lines to delete. If no ending line number is specified, then only this one line will be deleted.
*
The asterisk refers to the current line number. It may be used in place of a line number to mark either the beginning or the end (or both) of a range of lines to be deleted.
L
L functions similarly to *, but refers to the last line in the buffer.
e
Is a line number representing the end of a range of lines to delete.
DEL, and all the other editing commands, are described in Chapter 2.
| DESCRIBE |
The DESCRIBE command displays information about a table, a view, an Oracle object type, a stored package, a stored procedure, or a stored function. When used against a table or view, DESCRIBE returns a list of columns, including datatypes and lengths. When used against an Oracle object type or a stored package, DESCRIBE returns a list of procedures, functions, and variables accessible from outside the package or type. Parameters for each function, procedure, and method are listed as well. When used against a stored procedure or function, DESCRIBE returns a list of parameters. In the case of a function, DESCRIBE displays the return type as well. DESCRIBE is discussed in Chapter 10.
Syntax
DESC[RIBE] [ schema .] object_name [@ database_link_name ]
Parameters
DESC[RIBE]
Is the command, which may be abbreviated DESC.
schema
Is the name of the object's owner. This defaults to your username.
object_name
Is the name of the object, often a table or a view, that you want to describe. You can describe any of the following: a table, a view, a stored procedure, a stored function, a stored package, or an Oracle object type.
database_link_name
Is the name of a database link pointing to the database where the object exists. You need to specify this only if the object you want to describe exists in a database other than the one to which you are currently connected. Your DBA can help create a database link if you need one.
| DISCONNECT |
The DISCONNECT command closes your database connection without terminating SQL*Plus. DISCONNECT is discussed in Chapter 2.
Syntax
DISC[ONNECT]
Parameters
DISC[ONNECT]
Is the command, which may be abbreviated DISC.
| EDIT | Not available in i SQL*Plus |
The EDIT command invokes an external editor to edit the contents of the buffer or to edit the contents of an operating system file.
Syntax
ED[IT] [ filename ]
Parameters
ED[IT]
Is the command, which may be abbreviated ED. The EDIT command with no parameters allows you to edit the current contents of the buffer.
filename
Specifies an external file to edit instead of the buffer. The filename may include a path and an extension.
EDIT, and all the other editing commands, are described in Chapter 2.
| EXECUTE |
The EXECUTE command allows you to execute a single PL/SQL statement and is discussed in Chapter 2.
Syntax
EXEC[UTE] statement
Parameters
EXEC[UTE]
Is the command, which may be abbreviated EXEC.
statement
Is the PL/SQL statement you want to execute.
| EXIT |
The EXIT command terminates a SQL*Plus session and returns to the operating system. In i SQL*Plus, you are returned to the i SQL*Plus input area without being disconnected from your database.
Syntax
EXIT [SUCCESS FAILURE WARNING value sub_variable : bind_variable ] [COMMIT ROLLBACK]
Parameters
SUCCESS
Returns a success status. The exact value of the success status is operating system-dependent, but is often 0. SUCCESS is the default setting, and applies if no other return value is specified.
FAILURE
Returns a failure status. The value of the failure status is operating system-dependent, but is often 1.
WARNING
Returns a warning status. The exact value of the warning status is operating system-dependent.
value
Returns an arbitrary value as the status.
sub_variable
Returns the value of the specified substitution variable as the status. You can also specify SQL.SQLCODE here, to return the status of the most recently executed SQL statement.
:bind_variable
Returns the value of the specified bind variable as the status.
COMMIT
Causes SQL*Plus to automatically commit before exiting.
ROLLBACK
Causes SQL*Plus to automatically roll back any open transaction before exiting.
| GET | Not available in i SQL*Plus |
The GET command reads a SQL statement from a file and loads it into the buffer.
Syntax
GET filename [LIS[T] NOL[IST]]
Parameters
filename
Is the name of the file containing the SQL statement you want to load. This can be any filename, including path and extension, that your operating system recognizes.
LI[ST]
Causes SQL*Plus to display the buffer after loading the file. This is the default.
NOL[IST]
Causes SQL*Plus to load the file without displaying it.
GET, and all the other editing commands, are described in Chapter 2.
| HELP |
The HELP command gets help on SQL*Plus commands, SQL statements, PL/SQL statements, and other topics. HELP is described in Chapter 2.
Syntax
HELP [ topic ]
Parameters
topic
Is the help topic you want to read about. Most SQL statements, SQL*Plus commands, and PL/SQL statements are valid help topics. There are others as well. Entering HELP INDEX will get you a complete list of valid topics.
Installing the online help
The SQL*Plus HELP command reads from a table named help in the SYSTEM schema of whatever database you are connected to. Thus, the help that you get will be for the version of SQL*Plus that corresponds to your database version and will not necessarily correspond to the version of SQL*Plus you are running.
To load the system help table, do the following:
- Make your current working directory $ORACLE_HOME/sqlplus/admin/help .
- Run SQL*Plus and connect to your database as the SYSTEM user.
- Execute the hlpbld.sql script as follows :
@hlpbld helpus.sql
The helpus.sql file loads English language help text. You may need to load help in some other language. Issue an ls command to see what other helpxx.sql files are available.
To drop the help table (and possibly an associated view), should you ever wish to do that, just log in as SYSTEM and run $ORACLE_HOME/sqlplus/admin/help/helpdrop.sql .
|
| HOST | Not available in i SQL*Plus |
The HOST command executes an operating system command or invokes the command interpreter so you can execute several such commands.
Syntax
HO[ST] [ os_command ]
Parameters
HO[ST]
Is the command, which may be abbreviated HO. Issuing HOST without specifying a command will get you a command prompt from which you may enter several commands.
os_command
Is the operating system command you wish to execute. SQL*Plus will execute this one command for you, and then you will be returned to the SQL*Plus prompt.
| INPUT | Not available in i SQL*Plus |
The INPUT command inserts one or more lines of text into the buffer. The lines are inserted after the current line.
Syntax
I[NPUT] [ text ]
Parameters
I[NPUT]
Is the command, which may be abbreviated I. When you issue the INPUT command with no text after it, SQL*Plus puts you in insert mode, allowing you to type as many lines as you like. These are all inserted into the buffer following the current line. Press ENTER on a blank line to terminate insert mode.
text
Is the text you want to insert. Use this if you are only inserting one line.
INPUT, and all the other editing commands, are described in Chapter 2.
| LIST |
The LIST command, an editing command, lists the current line from the buffer.
Syntax
L[IST] [{
b
* L}[ {
e
* L}]]
Parameters
L[IST]
Is the command, which may be abbreviated L. LIST by itself will cause SQL*Plus to display all lines in the buffer.
b
Is a line number representing the beginning of a range of lines to list. If no ending line number is specified, only this one line will be listed.
*
The asterisk refers to the current line number. It may be used in place of a line number to mark either the beginning or the end (or both) of a range of lines to be list.
L
L functions similarly to * but refers to the last line in the buffer.
e
Is a line number representing the end of a range of lines to list.
LIST, and all the other editing commands, are described in Chapter 2.
| PASSWORD | Not available in i SQL*Plus |
The PASSWORD command allows you to change your Oracle password using SQL*Plus and is described in Chapter 2.
Syntax
PASSW[ORD] [ username ]
Parameters
PASSW[ORD]
Is the command, which may be abbreviated PASSW.
username
Is the user whose password you want to change. Usually, only DBAs can change passwords for other users. You don't need to supply a username if you are changing your own password.
| PAUSE |
The PAUSE command, most commonly used from script files, prompts the user to press the ENTER key before the script can continue.
Syntax
PAU[SE] [ pause_message ]
Parameters
PAU[SE]
Is the command, which may be abbreviated PAU.
pause_message
Is an optional message you want displayed to the user. It's generally a good idea to include a message telling the users to press ENTER, lest they think the system has locked up on them.
The PRINT command displays the value of a bind variable. One of its most useful applications is to retrieve and print data from a REFCURSOR variable that has been opened within a PL/SQL block or returned from a PL/SQL procedure. PRINT is discussed in Chapter 11.
Syntax
PRI[NT] [ bind_variable_name ]
Parameters
PRI[NT]
Is the command, which may be abbreviated PRI. Entering PRINT by itself causes SQL* Plus to print the values of all bind variables.
bind_variable_name
Is the name of the bind variable you want to print.
| PROMPT |
The PROMPT command displays a message for the user to see. See Chapter 8 for more information.
Syntax
PRO[MPT] text_to_be_displayed
Parameters
PRO[MPT]
Is the command, which may be abbreviated PRO
text_to_be_displayed
Is whatever text you want displayed to the user. This should not be a quoted string. If you include quotes, they will appear in the output.
| QUIT |
The QUIT command functions the same way as the EXIT command. It terminates a SQL*Plus session and returns you to the operating system. In i SQL*Plus, you are returned to the i SQL*Plus input area without being disconnected from your database.
Syntax
QUIT [SUCCESS FAILURE WARNING value sub_variable : bind_variable ] [COMMIT ROLLBACK]
Parameters
SUCCESS
Returns a success status. The exact value of the success status is operating system-dependent, but is often 0. SUCCESS is the default setting, and it applies if no other return value is specified.
FAILURE
Returns a failure status. The value of the failure status is operating system-dependent but is often 1.
WARNING
Returns a warning status. The exact value of the warning status is operating system-dependent.
value
Returns an arbitrary value as the status.
sub_variable
Returns the value of the specified user variable as the status. You can specify SQL.SQLCODE here, to return the status of the most recently executed SQL statement.
:bind_variable
Returns the value of the specified bind variable as the status.
COMMIT
Causes SQL*Plus to automatically commit before exiting.
ROLLBACK
Causes SQL*Plus to automatically roll back any open transaction before exiting.
| RECOVER |
The RECOVER command initiates media recovery on a database, a tablespace, or a datafile. You must be connected as SYSDBA or SYSOPER in order to use this command. The syntax shown here is for Oracle Database 10g.
Syntax
RECOVER {
general
managed
bracket
}
general
::= [AUTOMATIC] [FROM
location
]
{
full_recovery
partial_recovery
LOGFILE
filename
}
[
general_option
[
general_option
...]]
managed
::= MANAGED STANDBY DATABASE {
managed_recovery
cancel
finish
}
bracket
::= {BEGIN BACKUP END BACKUP}
full_recovery
::= [STANDBY]DATABASE [
full_option
[
full_option
...]]
partial_recovery
::= {
partial_type
STANDBY
partial_type
}
UNTIL [CONSISTENT WITH] CONTROLFILE
general_option
::= {TEST ALLOW
integer
CORRUPTION
parallel
}
managed_recovery
::=
managed_recovery_option
[
managed recovery option
...]
cancel
::= CANCEL [IMMEDIATE] [WAIT NOWAIT]
finish
::= [DISCONNECT [FROM SESSION]] [
parallel
]
FINISH [SKIP [STANDBY LOGFILE]] [WAIT NOWAIT]
full_option
::= {UNTIL {CANCEL TIME
datetime
CHANGE
integer
}
USING BACKUP CONTROLFILE}
partial_type
::= {TABLESPACE
tablespace
[,
tablespace
...]
DATAFILE {
filename
filenumber
}[,
filename
filenumber
...]}
managed_recovery_option
::=
{{DISCONNECT [FROM SESSION] TIMEOUT
integer
NOTIMEOUT}
{NODELAY DEFAULT DELAY DELAY
integer
}
NEXT
integer
{EXPIRE
integer
NO EXPIRE}
parallel
USING CURRENT LOGFILE
UNTIL CHANGE
integer
THROUGH {[THREAD
integer
] SEQUENCE
integer
ALL ARCHIVELOG
{ALL LAST NEXT} SWITCHOVER}
parallel
::= {NOPARALLEL PARALLEL [degree]}
Parameters
AUTOMATIC
Automatically determines the names of the redo log files to apply. The default is to prompt you for each name.
FROM location
Specifies the directory in which archived redo log files are located. The default is to use the location specified by the LOG_ARCHIVE_DEST or LOG_ARCHIVE_DEST_1 initialization paremeters.
LOGFILE filename
Begins media recovery with the specified log file.
MANAGED STANDBY DATABASE
Places a standby database into sustained recovery mode, in which logs from the primary are automatically applied.
BEGIN BACKUP
Moves all database files into online backup mode.
END BACKUP
Moves all database files out of online backup mode.
STANDBY DATABASE
Initiates recovery of the standby database.
UNTIL [CONSISTENT WITH] CONTROLFILE
Recovers datafiles in the standby database until they are consistent with the standby database's control file.
TEST
Initiates a trial recovery for the purpose of detecting problems that would occur during a real recovery. Redo is applied in memory, but none of the resulting data blocks are written to disk.
ALLOW integer CORRUPTION
Specifies the number of corrupt blocks to allow before aborting a recovery operation.
CANCEL [IMMEDIATE] [WAIT NOWAIT]
Terminates recovery. Specify no options to terminate recovery after the current archived redo log file is applied. Use IMMEDIATE to cancel after the next redo log read, or after the current redo log file is applied, whichever comes first. Use WAIT to have the command wait for the termination. The default is to WAIT. Use NOWAIT if you wish to get back to the SQL*Plus command prompt without waiting for the termination to occur.
DISCONNECT [FROM SESSION]
Causes recovery to proceed in the background, freeing up your session for other work.
FINISH [SKIP [STANDBY LOGFILE]] [WAIT NOWAIT]
Initiates recovery of any remaining, unapplied redo logs against the standby database. Use the SKIP clause to skip the application of standby redo log files. Use NOWAIT to get a command prompt back immediately without waiting for the recovery operation to finish. Use WAIT to specify the default behavior, in which the command waits for the operation to finish.
UNTIL CANCEL
Performs recovery one log file at a time until you cancel the operation.
UNTIL TIME datetime
Recovers up through a date and time, which you should specify using the format `YYYY-MM-DD:HH24:MI:SS'.
UNTIL CHANGE integer
Recovers up through a specified System Change Number (SCN).
USING BACKUP CONTROLFILE
Uses the backup control file as the basis for recovery.
tablespace
Specifies a tablespace to be recovered.
filename
Specifies a datafile to be recovered.
filenumber
Specifies the number of a datafile to be recovered.
DISCONNECT [FROM SESSION]
Allows you to do other work in your session while the recovery is running.
TIMEOUT integer
Specifies a timeout in minutes, after which, if a requested archive redo log is not made available to the standby database, recovery terminates.
NOTIMEOUT
Explicitly requests the default behavior of sustained recovery with no timeout.
NODELAY
Overrides any DELAY setting in LOG_ARCHIVE_DEST_ n .
DEFAULT DELAY
Delays application of a redo log file by the number of minutes specified in LOG_ARCHIVE_DEST_ n .
DELAY integer
Overrides the DELAY setting in LOG_ARCHIVE_DEST_ n with a new timeout, specified in minutes.
NEXT integer
Overrides any default DELAY, and applies the next integer log files as soon as they can be applied.
EXPIRE integer
Sets the number of minutes for which managed recovery will run, after which the operation will terminate.
NOEXPIRE
Removes any expiration time that may have been set for a managed recovery operation.
USING CURRENT logfile
Recovers redo from standby online logs, obviating the need to archive them first.
THROUGH [THREAD integer] SEQUENCE integer
Performs a managed standby recovery up through the specified thread and log sequence number.
THROUGH ALL ARCHIVELOG
Performs a managed standby recovery until all logs have been applied.
THROUGH {ALL LAST NEXT} SWITCHOVER
Continues a managed recovery through all switchover operations, through only the next switchover, or through the last switchover.
NOPARALLEL
Specifies that a recovery be done serially .
PARALLEL [degree]
Specifies that recovery be done in parallel and, optionally, the degree of parallelism to use. The default degree of the parallelism is the number of CPUs available on all instances multiplied by the value of the PARALLEL_THREADS_PER_CPU initialization parameter.
| REMARK |
The REMARK command places comments in a SQL*Plus script. See Chapter 5. In addition to REMARK, comments may set off with /* . . . */, or by preceding each comment line with a double hyphen (- -).
Syntax
REM[ARK] comment_text
Parameters
REM[ARK]
Is the command, which may be abbreviated REM.
comment_text
Is your comment.
| REPFOOTER |
The REPFOOTER command defines a report footer. Report footers print on the last page of a report, after the last detail line and before the bottom title. See Chapter 7 for more information.
Syntax
REPF[OOTER] [PAGE] [OFF ON] option [ option . . . ] option ::= [COL x S[KIP] x TAB x LE[FT] CE[NTER] R[IGHT] BOLD FOR[MAT] format_spec text variable ]
Parameters
REPF[OOTER]
REPFOOTER is the command, which may be abbreviated REPF. Issuing the REPFOOTER command with no parameters causes SQL*Plus to display the current report footer setting.
[PAGE]
Begins a new page before printing the footer.
OFF
Turns the report footer off but does not erase its definition. You can turn it back on with ON.
ON
Turns on printing of report footers.
COL x
Causes any footer text following this parameter to print at the specified column position.
S[KIP] x
May be abbreviated S. Inserts the specified number of line breaks before printing any subsequent footer text.
TAB x
TAB is similar to COL but moves you the specified number of columns relative to the current position. Negative numbers move you backward. TAB has nothing to do with tab characters.
LE[FT]
May be abbreviated LE. Causes subsequent footer text to be printed beginning at the leftmost column of the current footer line.
CE[NTER]
May be abbreviated CE. Causes subsequent footer text to be centered within the current line. The LINESIZE setting controls the line width.
R[IGHT]
May be abbreviated R. Causes subsequent footer text to be printed flush right. The LINESIZE setting controls where SQL*Plus thinks the right end of the line is.
BOLD
Makes a footer "bold" by printing it three times. Only text following the BOLD command is repeated on each line. There is no NOBOLD parameter.
FOR[MAT]
May be abbreviated FOR. Allows you to control how subsequent numeric data in the footer is displayed.
format_spec
Is a string that specifies the display format to use for subsequent numeric data in the footer. The format elements you can use here are the same as for the COLUMN command and are described in Appendix B. It is possible to specify a character format, such as A20, but that has no effect on subsequent character strings.
text
Is any text you want to have in the footer. To be safe, you should enclose this in quotes, but you don't have to as long as your title text doesn't include any keywords like BOLD or TAB that have meaning to REPFOOTER. Either single or double quotes may be used. If you need to include a quote as part of your text, use two quote characters back to back.
variable
May be one of the variables shown in Table A-1 (see the BTITLE command).
| REPHEADER |
The REPHEADER command defines a report header. Report headers print on the first page of a report, after the page title and before the first detail line. See Chapter 7 for more information.
Syntax
REPH[EADER] [PAGE] [OFF ON] option [ option . . . ] option ::= [COL x S[KIP] x TAB x LE[FT] CE[NTER] R[IGHT] BOLD FOR[MAT] format_spec text variable ]
Parameteres
REPH[EADER]
Is the command, which may be abbreviated REPH. Issuing the REPHEADER command with no parameters causes SQL*Plus to display the current report header setting.
[PAGE]
Begins a new page after printing the header.
OFF
Turns the report header off but does not erase its definition. You can turn it back on with ON.
ON
Turns on printing of report headers.
COL x
Causes any header text following this parameter to print at the specified column position.
S[KIP] x
May be abbreviated S. Insert the specified number of line breaks before printing any subsequent header text.
TAB x
TAB is similar to COL, but moves you the specified number of columns relative to the current position. Negative numbers move you backward. TAB has nothing to do with tab characters.
LE[FT]
May be abbreviated LE. Causes subsequent footer text to be printed beginning at the leftmost column of the current footer line.
CE[NTER]
May be abbreviated CE. Causes subsequent header text to be centered within the current line. The LINESIZE setting controls the line width.
R[IGHT]
May be abbreviated R. Causes subsequent header text to be printed flush right. The LINESIZE setting controls where SQL*Plus thinks the right end of the line is.
BOLD
Makes a footer "bold" by printing it three times. Only text following the BOLD command is repeated on each line. There is no NOBOLD parameter.
FOR[MAT]
May be abbreviated FOR. Allows you to control how subsequent numeric data in the header is displayed.
format_spec
Is a string that specifies the display format to use for subsequent numeric data in the header. The format elements you can use here are the same as for the COLUMN command and are described in Appendix B. It is possible to specify a character format, such as A20, but that has no effect on subsequent character strings.
text
Is any text you want to have in the header. To be safe, you should enclose this in quotes, but you don't have to as long as your title text doesn't include any keywords like BOLD or TAB that have meaning to REPHEADER. Single or double quotation marks may be used. If you need to include a quotation mark as part of your text, use two quote characters back to back.
variable
May be one of the variables shown in Table A-1 (see the BTITLE command).
| RUN |
The RUN command displays and executes the command currently in the SQL buffer.
Syntax
R[UN]
Parameters
R[UN]
Is the command, which may be abbreviated R. No parameters are necessary.
RUN, and all the other editing commands, are described in Chapter 2.
| SAVE | Not available in i SQL*Plus |
The SAVE command writes the contents of the SQL buffer to an operating system file.
Syntax
SAV[E] filename [CRE[ATE] REP[LACE] APP[END]]
Parameters
SAV[E]
Is the command, which may be abbreviated SAV.
filename
Is the filename, including the path and extension, to which you want to write the buffer contents.
CRE[ATE]
Causes the operation to succeed only if the file doesn't exist. This is the default setting.
REP[LACE]
Overwrites any existing file of the same name.
APP[END]
Appends the contents of the buffer to the file.
SAVE, and all the other editing commands, are described in Chapter 2.
| SET APPINFO |
The APPINFO setting controls whether or not SQL*Plus automatically registers command files using the DBMS_APPLICATION_INFO package.
Syntax
SET APPI[NFO] {OFF ON
app_text
}
Paremeters
SET APPI[NFO]
Is the command, which may be abbreviated SET APPI.
OFF
Disables the automatic registration of command filenames. With this off, SQL*Plus will make an entry using the current value of app_text whenever you execute a command file.
ON
Enables the automatic registration of command files. This is the default setting.
app_text
Provides a text string that is used instead of the command filename. The default setting for this is "SQL*Plus".
Examples
The DBMS_APPLICATION_INFO package controls the contents of the module field in both the v$session and v$sqlarea views. Whenever you connect to a database, SQL*Plus registers itself as the active application by making a call to the DBMS_APPLICATION_INFO.SET_MODULE procedure. This sets the module name for your session to "SQL*Plus". This is reflected in the v$session view, as the following example demonstrates:
SELECT module FROM v$session WHERE username=USER; MODULE ------------------------------------------------ SQL*Plus
SQL*Plus has the ability to update the module name whenever you execute a command file. The module name can be set to the command filename or to some arbitrary text that you specify. The default setting for APPINFO causes SQL*Plus to register the name of each command file you execute. So, if you execute a command file from one SQL*Plus session and query the v$session view from a second SQL*Plus session, you will get results like the following:
SELECT module FROM v$session WHERE username='JEFF'; MODULE ------------------------------------------------- 01@ /home/oracle/sqlplus/ExampleScripts/ex5-3.sql
In this example the module column tells you the name of the command file being executed by the user named JEFF. SQL*Plus crams in more information than the filename. You can break down the format of the module field as follows:
NL@tFFFFFFFF
in which:
NL
Is the nesting level. Command files executed from the SQL*Plus prompt will have a 01 in this position. If one command file executes another command file, then the nesting level will be 02, indicating that a second command file was invoked. The deeper the command files are nested, the larger this number becomes.
@
Is a constant.
t
Is a flag indicating whether or not SQL*Plus had to truncate the name of the command file in order for it to fit within the module field. The maximum length of a module is 48 bytes. If the filename was truncated, this value will be a less-than sign (<).
FFFFFFFF
Is the filename, or as much as will fit within 48 characters.
You may find that you don't care about command filenames but that you want to know when users are using SQL*Plus. You can accomplish that by setting APPINFO to OFF. In that case, SQL*Plus will register itself but will not subsequently change the module name. It will always be "SQL*Plus". For this to apply to all users, you would need to place the setting in each user's global or site profile.
An additional option is to supply a fixed-text string that SQL*Plus can use instead of a filename. This string is passed as the module name whenever a command file is executed. The result is that while you will know that a command file is being executed, you won't know which one.
| SET ARRAYSIZE |
The ARRAYSIZE setting controls the number of rows SQL*Plus fetches from the database at one time.
Syntax
SET ARRAY[SIZE] array_size
Parameters
SET ARRAY[SIZE]
Is the command, which may be abbreviated SET ARRAY.
array_size
Is the number of rows fetched at one time. The default value is 15. The allowed range is from 1 to 5000.
Increasing the array size allows SQL*Plus to return more rows in one fetch, thus lessening the required number of network round trips between it and the database server. The tradeoff is that larger array size settings require more memory. Using the default value of 15, SQL*Plus would require 10 fetches to return 150 rows from a query. By increasing the array size to 50, you reduce the number of fetches to three.
| SET AUTOCOMMIT |
The AUTOCOMMIT setting controls whether SQL*Plus automatically commits changes you make to the database, and it controls how often those changes are committed.
Syntax
SET AUTO[COMMIT] {OFF ON IMMEDIATE
statement_count
}
Parameters
SET AUTO[COMMIT]
Is the command, which may be abbreviated SET AUTO.
OFF
Turns off autocommit and requires you to commit (or roll back) changes manually. This is the default setting.
ON
Causes SQL*Plus to issue a COMMIT after each successful SQL statement or PL/SQL block you execute.
IMMEDIATE
Has the same effect as ON.
statement_count
Causes SQL*Plus to issue a COMMIT after successfully executing the specified number of SQL statements or PL/SQL blocks. This value may range from 1 to 2,000,000.
Examples
When you set autocommit to occur after a specified number of successful SQL statements, be aware that manually executing a COMMIT, a ROLLBACK, or another SET AUTOCOMMIT command will cause the counter to be reset back to zero. Take a look at the following example:
SET AUTOCOMMIT 5 DELETE FROM project_hours WHERE employee_id = 101 AND project_id = 1001; DELETE FROM project_hours WHERE employee_id = 102 AND project_id = 1001; COMMIT; DELETE FROM project_hours WHERE employee_id = 103 AND project_id = 1001; DELETE FROM project_hours WHERE employee_id = 104 AND project_id = 1001; DELETE FROM project_hours WHERE employee_id = 105 AND project_id = 1001; DELETE FROM project_hours WHERE employee_id = 106 AND project_id = 1001; DELETE FROM project_hours WHERE employee_id = 107 AND project_id = 1001;
The COMMIT statement in the fourth line will cause the counter to be reset. Counting will start over again, and five more SQL statements must be executed successfully before an automatic commit occurs.
| SET AUTOPRINT |
The AUTOPRINT setting controls whether SQL*Plus automatically prints the contents of any bind variables referenced by a PL/SQL block after it executes.
Syntax
SET AUTOP[RINT] {OFF ON}
Parameters
SET AUTOP[RINT]
Is the command, which may be abbreviated SET AUTOP.
OFF
Keeps bind variables from being automatically printed after being referenced by a PL/SQL block. This is the default setting.
ON
Causes bind variables to be printed automatically, following the execution of any PL/SQL block or SQL statement that references them.
| SET AUTORECOVERY |
The AUTORECOVERY setting causes the RECOVER command to run without user intervention, as long as the archived log files are in the destination pointed to by the LOG_ARCHIVE_DEST parameter and the names conform to the LOG_ARCHIVE_FORMAT parameter.
Syntax
SET AUTORECOVERY {OFF ON}
Parameters
OFF
Turns off autorecovery. This is the default setting.
ON
Turns on autorecovery, causing the RECOVER command to run without user intervention.
| SET AUTOTRACE |
The AUTOTRACE setting controls whether SQL*Plus displays the execution plan and statistics for each SQL statement as it is executed.
Syntax
SET AUTOT[RACE] {OFF ON TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
Parameters
SET AUTOT[RACE]
Is the command, which may be abbreviated SET AUTOT.
OFF
Disables the autotrace feature. SQL*Plus won't display the execution plan or the statistics for each SQL statement.
ON
Turns the autotrace feature on. If no other parameters are supplied, SQL*Plus will default to displaying the statistics. SET AUTOTRACE ON is equivalent to SET AUTOTRACE ON STATISTICS.
TRACE[ONLY]
Inhibits the display of all data returned when the SQL statement in question is a SELECT statement.
EXP[LAIN]
Causes SQL*Plus to display the execution plan for each SQL statement you execute.
STAT[ISTICS]
Causes SQL*Plus to display execution statistics for each SQL statement you execute.
See Chapter 12 for comprehensive examples showing how to use the autotrace feature of SQL*Plus. Chapter 12 shows how to interpret the execution plan output and describes the operations that may be used in that plan.
| SET BLOCKTERMINATOR |
The BLOCKTERMINATOR setting controls the character used to terminate a PL/SQL block being entered into the buffer for editing.
Syntax
SET BLO[CKTERMINATOR] { block_term_char ON OFF}
Parameters
SET BLO[CKTERMINATOR]
Is the command, which may be abbreviated SET BLO.
block_term_char
Is the new terminator character for use when entering PL/SQL blocks. The default value is a period.
ON
Enables the use of a block termination character and resets that character to the default of a period.
OFF
Disables the use of a block termination character, which means that the only way to terminate a block is to execute it using the forward slash (/).
Examples
When you enter a PL/SQL block into the buffer, you need a way to tell SQL*Plus when the block has ended. By default, the period can be used for this purpose, but you can use the SET BLOCKTERMINATOR command to change that. The following example changes the block terminator to a pound sign character:
SQL>
SET BLOCKTERMINATOR #
SQL>
BEGIN
2
DBMS_OUTPUT.PUT_LINE('PL/SQL is powerful.');
3
END;
4
#
SQL>
Terminating the block this way leaves it in the buffer for you to edit. Don't confuse this with the use of the slash command, which terminates and executes a block.
|
| SET BUFFER |
The SET BUFFER setting switches to another buffer for editing purposes.
Syntax
SET BUF[FER] {
buffer_name
SQL}
Parameters
SET BUF[FER]
Is the command, which may be abbreviated SET BUF.
buffer_name
Is the name of the buffer you want to edit. You can create any name you like. If the named buffer doesn't exist, SQL*Plus will create it for you.
SQL
Switches you to the SQL buffer. This is the default setting. The SQL buffer is the one used when you type in a SQL statement at the command prompt and is the only buffer from which you can execute a SQL statement (or PL/SQL block).
Examples
Changing the buffer has limited use because you can execute a statement only from the SQL buffer. The GET, SAVE, and EDIT commands work, as do all the editing commands. The following example shows a second buffer being used to edit a statement that exists in a text file without disturbing the statement currently in the SQL buffer:
SQL> SHOW BUFFER buffer SQL SQL> SELECT * FROM employee 2 SQL> L 1* SELECT * FROM employee SQL> SET BUFFER project SQL> L No lines in PROJECT buffer. SQL> GET c:aproject.sql 1* SELECT * FROM project SQL> I 2 WHERE project_budget > 1000000 3 SQL> SAVE c:aproject.sql REPLACE Wrote file c:aproject.sql SQL> SET BUFFER SQL SQL> L 1* SELECT * FROM employee SQL>
As you can see, using a second buffer made it possible to edit the SQL statement in the project.sql file without disturbing the statement currently in the SQL buffer. You could do the same thing more easily with the EDIT command.
| SET CLOSECURSOR |
The CLOSECURSOR setting controls whether SQL*Plus closes the cursor used to execute a SQL statement after the statement has executed.
Syntax
SET CLOSECUR[SOR] {OFF ON}
Parameters
SET CLOSECUR[SOR]
Is the command, which may be abbreviated SET CLOSECUR.
OFF
Causes SQL*Plus to leave the cursor open for use by subsequent SQL statements. This is the default setting.
ON
Causes SQL*Plus to close the cursor after a SQL statement has been executed.
While you normally think of a cursor only in the context of returning data from a SELECT statement, Oracle also uses cursors to execute other SQL statements, such as DELETE, INSERT, UPDATE, etc. The same cursor can be used to execute many SQL statements, so SQL*Plus leaves it open all the time by default.
| SET CMDSEP |
The CMDSEP setting controls whether you can enter multiple commands on one line. Both commands in question must be SQL*Plus commands. SET CMDSEP is used to change the character used to separate these commands.
Syntax
SET CMDS[EP] {OFF ON
separator_char}
Parameters
SET CMDS[EP]
Is the command, which may be abbreviated SET CMDS.
OFF
Turns off the feature, requiring you to enter each command on a separate line. This is the default setting.
ON
Allows you to enter multiple SQL*Plus commands on one line and resets the separator character back to the default of a semicolon.
separator_char
Causes SQL*Plus to recognize the specified character as the command separator. You won't be allowed to make the command separator an alphabetic, numeric, or space character. This character may optionally be enclosed in either single or double quotes. In some cases, such as when you change it to a semicolon, you will need the quotes.
Examples
The following example turns this feature on, sets the separator character to an exclamation point, and shows how two commands may be placed on one line:
SQL> SET CMDSEP ON SQL> SET CMDSEP "! " SQL> SHOW CMDSEP! SHOW BUFFER! cmdsep "!" (hex 21) buffer SQL SQL>
| SET COLSEP |
The COLSEP setting changes the text that prints between columns of data.
Syntax
SET COLSEP column_separator
Parameters
column_separator
Is the text you want to print between columns. You should enclose this text in quotes if it contains any spaces or punctuation.
Examples
The default column separator is a single space. The following example shows how you can change it to a comma:
SQL> SET COLSEP ", " SQL> SELECT 'One' one , 2 ' Two' two 3 FROM dual; ONE,TWO ---,--- One,Two
Like a space, the comma in this example is a single character. You aren't limited to one character though. You could just as easily change the column separator to a string of several characters.
| SET COMPATIBILITY |
The COMPATIBILITY setting tells SQL*Plus the version of Oracle to which you are connected.
Syntax
SET COM[PATIBILITY] {V7 V8 NATIVE}
Parameters
SET COM[PATIBILITY]
Is the command, which may be abbreviated SET COM.
V7
Tells SQL*Plus you are connected to a Version 7 server or that you want SQL*Plus to act as if you were.
V8
Tells SQL*Plus that you are connected to a Version 8 database.
NATIVE
Causes SQL*Plus to automatically determine the compatibility setting based on the version of the database to which you are connected. This is the default setting.
This setting controls the way that SQL statements are transmitted to the server and the way that the results are brought back. It's usually best to leave this at the default, which causes SQL*Plus to choose the correct method automatically based on the database to which you are connected.
| SET CONCAT |
The CONCAT setting changes the character used to terminate a substitution variable reference. You can use the command to turn the feature off so that SQL*Plus doesn't recognize any character as the terminator.
Syntax
SET CON[CAT] {OFF ON
concat_char
}
Parameters
SET CON[CAT]
Is the command, which may be abbreviated SET CON.
OFF
Turns off this feature completely. SQL*Plus won't recognize any character as the termination character for substitution variable names.
ON
Turns this feature back on and resets the character back to the default value of a period.
concat_char
Is the new termination character. The default value is a period.
Examples
This setting is important only when you immediately follow a substitution variable name with characters that SQL*Plus might interpret as part of the name. Consider the following example:
DEFINE table="PROJECT" SELECT &&table._name FROM &&table;
The period (or concatenation character) in the SELECT statement is used to terminate the reference to &&table. Without the period, SQL*Plus would see &&table_name as the substitution variable.
|
| SET COPYCOMMIT |
The COPYCOMMIT setting controls how often SQL*Plus commits during execution of a COPY command.
Syntax
SET COPYC[OMMIT] batch_count
Parameters
SET COPYC[OMMIT]
Is the command, which may be abbreviated SET COPYC.
batch_count
Is the maximum number of uncommitted batches you want to allow during a copy operation. After this many batches are sent to the server, SQL*Plus commits the changes and resets the counter before sending another batch. The default value is 0, which means that SQL*Plus commits changes only when the COPY command is finished. The maximum value for this setting is 5000.
Examples
Normally, when you execute a COPY command, SQL*Plus copies all the rows from the source table to the destination table, then commits those changes. This can make for a rather large transaction if you are copying a large number of records, and your rollback segments may not be big enough to accommodate it. You can use SET COPYCOMMIT to have SQL*Plus periodically commit the changes, thus reducing the transaction size.
The COPYCOMMIT setting works in conjunction with the ARRAYSIZE setting. The ARRAYSIZE setting controls the number of rows in a batch. The COPYCOMMIT setting controls how many batches are copied before committing. The number of rows copied before each commit is equal to ARRAYSIZE * COPYCOMMIT. Take a look at this example:
SET ARRAYSIZE 15 SET COPYCOMMIT 10 COPY TO jonathan/secret@jonathan CREATE employee_copy USING SELECT * FROM employee;
Because the ARRAYSIZE is 15 and the COPYCOMMIT setting is 10, the COPY statement shown here will commit changes after every 150 rows (15 * 10).
| SET COPYTYPECHECK |
The COPYTYPECHECK setting controls whether SQL*Plus checks the datatypes when you use the COPY command to move data between two databases.
Syntax
SET COPYTYPECHECK {OFF ON}
Parameters
OFF
Turns off type checking.
ON
Enables type checking. This is the default setting.
This setting was created specifically for use when copying data to a DB2 database.
| SET DEFINE |
The SET DEFINE command changes the prefix character used to mark substitution variables. You can use SET DEFINE to turn variable substitution off.
Syntax
SET DEF[INE] {OFF ON
prefix_char
}
Parameters
SET DEF[INE]
Is the command, which may be abbreviated SET DEF.
OFF
Disables variable substitution.
ON
Enables variable substitution, and resets the substitution prefix character back to the default ampersand (&) character. Variable substitution is on by default.
prefix_char
Is the new substitution prefix character.
When you start SQL*Plus, variable substitution will be on by default, and the default prefix character is an ampersand. If you are running a script that uses ampersands in text strings, you may want to change the prefix character to something else. If your script doesn't use substitution variables, you may find it easiest to turn the feature off.
| SET DESCRIBE |
The DESCRIBE setting controls the depth to which an object such as an object type or object table is described.
Syntax
SET DESCRIBE [DEPTH {
levels
ALL}] [LINENUM {ON OFF}] [INDENT {ON OFF}]
Parameters
DEPTH {levels ALL}
Specifies the depth to which to recursively describe an object. The default is to describe only the top level of columns, attributes, or parameters.
LINENUM {ON OFF}
Adds or removes line numbers from the object's description. The default is LINENUM OFF.
INDENT {ON OFF}
Indents nested descriptions. The default is INDENT ON.
Examples
Suppose you had the following type and table:
CREATE OR REPLACE TYPE employee_type AS (employee_name VARCHAR2(40), employee_hire_date DATE, employee_salary NUMBER(9,2)); / CREATE TABLE employees AS (employee_id NUMBER, employee_data employee_type);
If you describe the table, DESCRIBE's default behavior is to list the datatype for each column in the table, but not to expand any object types:
DESCRIBE employees Name Null? Type ----------------------------------------- -------- --------------- EMPLOYEE_ID NUMBER EMPLOYEE EMPLOYEE_TYPE
If you want to know what EMPLOYEE_TYPE looks like, you must issue a second DESCRIBE command:
DESCRIBE employee_type Name Null? Type ----------------------------------------- -------- --------------- EMPLOYEE_NAME VARCHAR2(40) EMPLOYEE_HIRE_DATE DATE EMPLOYEE_SALARY NUMBER(9,2)
The SET DESCRIBE command gives you the option of getting all this information with only one DESCRIBE command. The following example requests that all levels of nested object types be expanded. The example requests line numbering to make it easier to refer to specific definitions:
SET DESCRIBE DEPTH ALL LINENUM ON DESCRIBE employees Name Null? Type ------------------------------- -------- --------------- 1 EMPLOYEE_ID NUMBER 2 EMPLOYEE EMPLOYEE_TYPE 3 2 EMPLOYEE_NAME VARCHAR2(40) 4 2 EMPLOYEE_HIRE_DATE DATE 5 2 EMPLOYEE_SALARY NUMBER(9,2)
The first column of numbers in this example shows line numbers. The second column of numbers shows level numbers, indicating the nesting level of the element being described. Both come from using LINENUM ON.
| SET DOCUMENT |
The DOCUMENT setting controls whether SQL*Plus prints text created with the DOCUMENT command.
Syntax
SET DOC[UMENT] {ON OFF}
Parameters
SET DOC[UMENT]
Is the command, which may be abbreviated SET DOC.
ON
Allows DOCUMENT text to be displayed. This is the default setting.
OFF
Keeps DOCUMENT text from being displayed.
Examples
This setting affects the DOCUMENT command only when it is issued from a script file. Suppose that you had a file with the following lines:
DOCUMENT This is documentation. #
The following example shows the results of executing this file with the DOCUMENT setting on and then with it off.
SQL> SET DOCUMENT ON SQL> @c:adoc DOC>This is documentation. DOC># SQL> SET DOCUMENT OFF SQL> @c:adoc
| SET ECHO |
The ECHO setting tells SQL*Plus whether you want the contents of script files to be echoed to the screen as they are executed.
Syntax
SET ECHO {OFF ON}
Parameters
OFF
Keeps commands from being echoed to the screen while a script file is being executed. This is the default setting.
ON
Causes commands from a script file to be echoed to the screen as they are being executed.
Examples
SET ECHO is one of the few debugging tools SQL*Plus has. It often helps to turn on command echoing while you are developing and testing a new script file. The following example shows the same script file being executed, once with ECHO on and once with it off:
SQL> @c:aecho_test D - X SQL> SET ECHO ON SQL> @c:aecho_test SQL> SELECT * FROM dual; D - X
For the second execution of the script, ECHO had been turned on, so the SELECT statement was displayed on the screen when SQL*Plus executed it.
|
| SET EDITFILE | Not available in i SQL*Plus |
The EDITFILE setting lets you change the name of the work file that is created when you use the EDIT command to edit the SQL statement in the buffer.
Syntax
SET EDITF[ILE] edit_filename
Parameters
SET EDITF[ILE]
Is the command, which may be abbreviated SET EDITF.
edit_filename
Is the filename you want SQL*Plus to use when you issue an EDIT command. The default value is afiedt.buf . The filename you specify may optionally include a path.
If you don't include an extension as part of the filename, the current value of the SUFFIX setting will be used as the extension.
| SET EMBEDDED |
The EMBEDDED setting command controls the printing of embedded reports. The default setting is OFF, which causes the results of each new query to print on a new page and causes page numbering to start over each time a SELECT statement is executed.
Syntax
SET EMB[EDDED] {ON OFF}
Parameters
SET EMB[EDDED]
Is the command, which may be abbreviated SET EMB.
ON
Turns the embedded report feature on. Executing a SELECT statement will not force a page break, nor will it reset the page number.
OFF
Turns the embedded report feature off. Executing a SELECT statement will force a page break, and the page number will be reset to 1.
Examples
An embedded report is one that prints as if it were the continuation of a previous report. The following example shows the results of executing two SELECT statements with the default setting. A page title has been created to show the effect on page numbering:
SQL> SET EMBEDDED OFF SQL> SET PAGESIZE 24 SQL> SET NEWPAGE 1 SQL> TTITLE LEFT "Example of SET EMBEDDED, Page " SQL.PNO SQL> SELECT * FROM dual; Example of SET EMBEDDED, Page 1 D - X SQL> SELECT * FROM dual; Example of SET EMBEDDED, Page 1 D - X
The second SELECT statement generated a page break; you can see the page title printed again. By looking at the titles, the page numbering for each query began with page one. Look at the same example but with SET EMBEDDED ON:
SQL> SET EMBEDDED OFF SQL> SET PAGESIZE 24 SQL> SET NEWPAGE 1 SQL> TTITLE LEFT "Example of SET EMBEDDED, Page " SQL.PNO SQL> SELECT * FROM dual; Example of SET EMBEDDED, Page 1 D - X SQL> SET EMBEDDED ON SQL> SELECT * FROM dual; D - X
This time, the second SELECT statement didn't generate a page break. The second report began printing on the same page on which the first report ended.
|
| SET ESCAPE |
The ESCAPE setting specifies the character used to escape the substitution variable prefix.
Syntax
SET ESC[APE] {OFF ON
escape_char
}
Parameters
SET ESC[APE]
Is the command, which may be abbreviated SET ESC.
OFF
Turns the escape feature off completely. SQL*Plus will not recognize any character as an escape character. This is the default setting.
ON
Enables the escape feature and resets the escape character back to the default value, a backslash ().
escape_char
Is the new escape character. By default, this is a backslash.
Examples
You use the escape character when you want to place an ampersand in a command and you don't want that ampersand interpreted as a substitution variable prefix character. The following example shows a case where this can be a problem:
SQL> SELECT 'Matt & Joe Williams' FROM dual; Enter value for joe:
The ampersand in front of the word "Joe" causes SQL*Plus to interpret it as a substitution variable name. To work around this behavior, you can turn the escape feature on and precede the ampersand with a backslash. Here's an example:
SQL> SET ESCAPE ON SQL> SELECT 'Matt & Joe Williams' FROM dual; 'MATT&JOEWILLIAMS' ------------------- Matt & Joe Williams
You can use the SET ESCAPE command to change the escape character to something other than a backslash.
| SET FEEDBACK |
The FEEDBACK setting controls whether SQL*Plus displays the number of records returned by a SELECT statement, deleted by a DELETE statement, updated by an UPDATE statement, or inserted by an INSERT statement. You can set a threshold, below which you don't get any feedback regardless of whether the setting is on.
Syntax
SET FEED[BACK] {OFF ON
row_threshold
}
Parameters
SET FEED[BACK]
Is the command, which may be abbreviated SET FEED.
OFF
Turns off feedback completely. SQL*Plus won't tell you how many rows are affected by any SQL statements you issue.
ON
Turns on feedback, and is equivalent to SET FEEDBACK 1. For any SQL statement you issue, SQL*Plus will tell you how many rows were affected. By default, feedback is on with a threshold of 6 rows.
row_threshold
Allows you to specify a row threshold, and also turns feedback on if it is not already on. A row threshold causes SQL*Plus to print the row count returned by a SELECT statement when that row count exceeds the threshold. The row threshold applies only to the SELECT statement. As long as feedback is on, the INSERT, DELETE, and UPDATE statements always return the number of rows affected, regardless of the row threshold.
Examples
The following example shows a feedback message from a SELECT statement:
SQL> SET FEEDBACK 1 SQL> SELECT * FROM dual; D - X 1 row selected.
In this example, feedback was set to 1 prior to executing the SELECT statement. That caused a feedback message to be displayed even though the statement returned only one row.
| SET FLAGGER |
The FLAGGER setting checks your SQL statements for conformance to ANSI/ISO SQL92 syntax. You can choose from three compliance levels: entry, intermediate, and full.
Syntax
SET FLAGGER {OFF ENTRY INTERMED[IATE] FULL}
Parameters
OFF
Turns off this feature. This is the default setting.
ENTRY
Allows SQL statements that use only the entry-level features of the standard.
INTERMED[IATE]
Allows SQL statements that use the intermediate-level features of the standard.
FULL
Allows any SQL statement that is defined in the standard.
Using the SET FLAGGER command has the same effect as executing the statement ALTER DATABASE SET FLAGGER. However, SET FLAGGER is a SQL*Plus command, so you can execute it even before connecting to the database. Once you have turned this feature on, any attempt to execute a nonconforming statement will result in an error message such as the following:
ERROR: ORA-00097: Use of Oracle SQL feature not in SQL92 Entry Level
This feature is useful if you are writing software for the federal government and are required to deliver an implementation that uses no nonstandard, vendor-specific features.
| SET FLUSH | Not available in i SQL*Plus |
The FLUSH setting indicates whether the host operating system is allowed to buffer output.
Syntax
SET FLU[SH] {OFF ON}
Paremeters
SET FLU[SH]
Is the command, which may be abbreviated SET FLU.
OFF
Allows output to be buffered.
ON
Causes output to be displayed immediately. This is the default.
If you are running a command file, turning off FLUSH might keep you from seeing any output until SQL*Plus is finished executing that file.
| SET HEADING |
The HEADING setting controls whether column headings print when you SELECT or PRINT data. The default value for this setting is ON, which allows column headings to print.
Syntax
SET HEA[DING] [ON OFF]
Parameters
SET HEA[DING]
Is the command, which may be abbreviated SET HEA.
ON
Causes column headings to print when you select data.
OFF
Suppresses column headings.
Examples
This setting is on by default, so you normally get a column heading whenever you select or print data using the SELECT statement or the PRINT command:
SQL> SELECT * FROM dual; D - X
Change the value to OFF and your column headings go away. The following example shows this:
SQL> SET HEADING OFF SQL> SELECT * FROM dual; X
|
| SET HEADSEP |
The HEADSEP setting changes the character used when defining a two-line column heading.
Syntax
SET HEADS[EP] heading_separator
Parameters
SET HEADS[EP]
Is the command, which may be abbreviated SET HEADS.
heading_separator
Is the new heading separator character, which may be used in subsequent COLUMN commands to mark line breaks in multiline column headings.
Examples
Normally, the heading separator is a vertical bar and marks the place in a column's heading where you want a line break to occur. The following example shows a two-line heading being defined:
SQL> COLUMN dummy FORMAT A10 HEADING 'Line 1 Line 2 ' SQL> SELECT * FROM dual; Line 1 Line 2 ---------- X
The vertical bar in the column's heading text was replaced by a line break when the column heading was printed. If you need to use the vertical bar as part of a column heading, use SET HEADSEP to choose some other character to act as the line break marker.
|
| SET INSTANCE |
The INSTANCE setting specifies a default database to which to connect when you use the CONNECT command without specifying a service name. You can't issue the SET INSTANCE command while connected to a database; you must disconnect first. Issuing the command SET INSTANCE with no parameters has the same effect as SET INSTANCE LOCAL.
Syntax
SET INSTANCE [ service_name LOCAL]
Parameters
service_name
Is a net service name.
LOCAL
Sets the default instance to be your local database. This is the default setting. In a Windows environment, the local database is the one specified by the LOCAL registry setting.
| SET LINESIZE |
The LINESIZE setting controls the number of characters SQL*Plus prints on one physical line. The default setting is 80 (150 in i SQL*Plus). The maximum width is system-dependent, though it's often 32,767 characters.
Syntax
SET LIN[ESIZE] line_width
Parameters
SET LIN[ESIZE]
Is the command, which may be abbreviated SET LIN.
line_width
Is the new line width, expressed as a number of characters.
The LINESIZE setting is referenced by SQL*Plus when you define any headers or footers (see TTITLE, BTITLE, REPHEADER, REPFOOTER) that are centered or right-justified.
| SET LOBOFFSET |
The LOBOFFSET setting represents an index into a LONG column. When SQL*Plus displays a LONG, it begins with the character pointed to by LOBOFFSET.
Syntax
SET LOBOF[FSET] offset
Parameters
SET LOBOF[FSET]
Is the command, which may be abbreviated SET LOBOF.
offset
Is the offset used when retrieving LONG values and represents the first character you want to display.
Using the LOBOFFSET setting in conjunction with the LONG setting allows you to print any arbitrary substring from a LONG column.
| SET LOGSOURCE |
The LOGSOURCE setting specifies the location of the archive log files and is referenced during recovery.
Syntax
SET LOGSOURCE logpath
Parameters
logpath
Is the path to the directory containing the archived redo log files.
| SET LONG |
The LONG setting controls the number of characters displayed by SQL*Plus from any LONG columns returned by a query.
Syntax
SET LONG long_length
Parameters
long_length
Represents the number of characters you want displayed from any LONG columns you select from the database. The default setting is 80 characters.
| SET LONGCHUNKSIZE |
The LONGCHUNKSIZE setting, a performance- related setting, controls the number of characters retrieved at one time from a LONG column.
Syntax
SET LONGC[HUNKSIZE] size
Parameters
SET LONGC[HUNKSIZE]
Is the command, which may be abbreviated SET LONGC.
size
Is the number of characters you want to retrieve from a LONG column in one fetch. The default value is 80.
With the default setting of 80 characters, SQL*Plus will need 10 round trips to the database to retrieve an 800-character LONG value. These network round trips take time, so you will tend to get better performance by increasing this setting. If you can afford the memory, make LONGCHUNKSIZE equal to the LONG setting. That way, the entire LONG value will be retrieved in one fetch.
| SET MARKUP |
The MARKUP setting specifies markup options for use in generating HTML output. See Chapter 6 for detailed information on generating HTML reports from SQL*Plus.
Syntax
SET MARKUP ::= HTML [ON OFF] [HEAD
text
] [BODY
text
] [TABLE
text
]
[ENTMAP {ON OFF}] [SPOOL {ON OFF}] [PREFORMAT {ON OFF}]
Parameters
texttext >.
| SET MAXDATA | Obsolete |
The MAXDATA setting is an obsolete setting; don't use it. SQL*Plus supports it in order to be backward compatible. MAXDATA controls the maximum row length SQL*Plus can handle.
Syntax
SET MAXD[ATA] max_row_width
Parameters
SET MAXD[ATA]
Is the command, which may be abbreviated SET MAXD.
max_row_width
Is the new setting for the maximum row width you expect to process.
The default setting is usually zero but may vary from one operating system to the next. Under some operating systems and/or versions of SQL*Plus, this setting seems to have no effect on SQL*Plus's behavior.
| SET NEWPAGE |
The NEWPAGE setting controls the manner in which the transition from one page to the next is marked . You can have SQL*Plus print a formfeed character at the start of each new page, skip a specific number of lines between pages, or do nothing at all when advancing from one page to another.
Syntax
SET NEWP[AGE] {
lines_to_print
NONE}
Parameters
SET NEWP[AGE]
Is the command, which may be abbreviated SET NEWP.
lines_to_print
Tells SQL*Plus to print a specific number of blank lines when a page break occurs. These lines will be printed following the footer (BTITLE) of the page just ending, and prior to the header (TTITLE) of the page just starting. If this value is zero, a formfeed character will be printed instead of any blank lines. The default value is 1.
NONE
Causes SQL*Plus to do nothing at all when a page break occurs. You'll get no blank lines and no formfeed.
|
| SET NULL |
The NULL setting changes the text SQL*Plus prints in a column when the value for that column is null.
Syntax
SET NULL null_text
Parameters
null_text
Is the text you want to print in place of a null value.
Examples
The default null text setting is an empty string, which causes null values to print as blanks. The following example shows this and shows how the null text may be changed:
SQL> SELECT employee_termination_date 2 FROM employee 3 WHERE employee_id=101; EMPLOYEE_ --------- SQL> SET NULL "*NULL* " SQL> SELECT employee_termination_date 2 FROM employee 3 WHERE employee_id=101; EMPLOYEE_ --------- *NULL*
If you use the COLUMN command to format a column, the NULL clause of that command will override this setting but only for that one column.
| SET NUMFORMAT |
The NUMFORMAT setting specifies the default formatting of numeric values returned from a SELECT statement. Any number format usable with the COLUMN command may also be used with SET NUMFORMAT.
Syntax
SET NUMF[ORMAT] format_spec
Parameters
SET NUMF[ORMAT]
Is the command, which may be abbreviated SET NUMF.
format_spec
Is a numeric format specification, which controls the default manner in which numeric values are displayed. See Appendix B for a list of formats.
Examples
The following example shows the effect of changing the NUMFORMAT setting:
SQL> SELECT 123456.7 FROM dual; 123456.7 --------- 123456.7 SQL> SET NUMFORMAT 9,999.99 SQL> SELECT 123456.7 FROM dual; 123456.7 ------------ 3,456.70
The NUMFORMAT setting controls the default display format for numeric values. You can use the COLUMN command to specify display formats on a column-by-column basis, and those take precedence over the NUMFORMAT setting.
| SET NUMWIDTH |
The NUMWIDTH setting controls the default width used when displaying numeric values.
Syntax
SET NUM[WIDTH] width
Paremeters
SET NUM[WIDTH]
Is the command, which may be abbreviated SET NUM.
width
Is the default column width used when displaying a numeric value.
Examples
The default NUMWIDTH setting is 10. NUMWIDTH is used only when no other settings apply. The following example shows the effect of setting NUMWIDTH to 5:
SQL> SET NUMWIDTH 5 SQL> SELECT 123 FROM dual; 123 ---- 123
A numeric format specified by a COLUMN command or by a SET NUMFORMAT command will override NUMWIDTH. The following example shows this:
SQL> SET NUMWIDTH 5 SQL> SET NUMFORMAT 999,999.99 SQL> SELECT 123 FROM dual; 123 ----------- 123.00 SQL> SHOW NUMWIDTH numwidth 5
NUMWIDTH is still five, but that value is ignored because the NUMFORMAT setting takes precedence. A long column title can cause NUMWIDTH to be ignored:
SQL> SET NUMWIDTH 5 SQL> SET NUMFORMAT " " SQL> COLUMN a HEADING "This is a long column title " SQL> SELECT 123 a FROM dual; This is a long column title --------------------------- 123
The column title takes precedence over NUMWIDTH when it comes to determining the width of the column.
| SET PAGESIZE |
The PAGESIZE setting tells SQL*Plus the number of printed lines that will fit on one page of output. You can also use this setting to completely turn off all pagination functions.
Syntax
SET PAGES[IZE] lines_on_page
Parameters
SET PAGES[IZE]
Is the command, which may be abbreviated SET PAGES.
lines_on_page
Is the number of lines you want SQL*Plus to print on one page. This includes detail lines, header lines, and footer lines. The default value for PAGESIZE is 14 (24 in i SQL*Plus).
The PAGESIZE must be set in conjunction with NEWPAGE. The sum of PAGESIZE and NEWPAGE should equal the number of lines that will physically fit on one page. SQL*Plus will print headers, detail, and footers until PAGESIZE lines have been printed. Then it will print NEWPAGE lines to advance to the next page, where the process starts again. Your page titles will drift up or down with each new page if these settings don't match the page's physical size. The exception to this is when you use SET NEWPAGE 0.
If you use SET NEWPAGE 0 to cause a formfeed to print at the beginning of each page, you should set PAGESIZE to at least one less than the physical number of lines on a page. Failure to do so may result in alternating blank pages in your printed report.
You can turn off all pagination by issuing a SET PAGESIZE 0 command. This will eliminate page titles, page footers, column titles, and any blank lines or formfeeds from the NEWPAGE setting.
| SET PAUSE |
The PAUSE setting pauses SQL*Plus after each page of output when displaying rows returned by a query.
Syntax
SET PAU[SE] {ON OFF
pause_message
}
Parameters
SET PAU[SE]
Is the command, which may be abbreviated SET PAU.
ON
Causes SQL*Plus to pause after each page of output. The user must press Enter to continue to the next page.
OFF
Turns off the pause feature. This is the default setting.
pause_message
Provides a message for SQL*Plus to display after each page when prompting the user to continue. This doesn't turn on the pause feature. You must issue a separate SET PAUSE ON command to do that.
Examples
The following example shows how the pause feature works:
SQL> SET PAGESIZE 10 SQL> SET PAUSE ON SQL> SET PAUSE "Press ENTER to continue . . . " SQL> SELECT view_name FROM all_views; Press ENTER to continue . . . VIEW_NAME ------------------------------ ALL_ALL_TABLES ALL_ARGUMENTS ALL_CATALOG ALL_CLUSTERS ALL_CLUSTER_HASH_EXPRESSIONS ALL_COLL_TYPES ALL_COL_COMMENTS Press ENTER to continue . . .
The PAGESIZE setting controls the number of lines printed on a page. In this example, PAGESIZE was set to 10, so SQL*Plus paused after every 10 lines of output.
| SET RECSEP |
The RECSEP setting tells SQL*Plus whether or not to print a record separator between each record displayed as the result of a query. The default setting is not to print anything, except when a long record wraps to a second line. In that case, a blank line is printed as a record separator.
Syntax
SET RECSEP {WR[APPED] EA[CH] OFF}
Parameters
WR[APPED]
Tells SQL*Plus to print a record separator only when a line wraps. This is the default setting.
EA[CH]
Tells SQL*Plus to print a record separator after each record.
OFF
Tells SQL*Plus not to print any record separators at all.
Examples
The default setting is WRAPPED, which is probably a safe choice because it causes a separator to print only in cases where a break between records may not be obvious, that is when each line doesn't necessarily start a new record. Here is an example of a record separator being printed:
SQL> COLUMN view_name FORMAT A15 SQL> SELECT view_name FROM all_views; ALL_ALL_TABLES ALL_ARGUMENTS ALL_CATALOG ALL_CLUSTERS ALL_CLUSTER_HAS H_EXPRESSIONS ALL_COLL_TYPES . . .
You can see that the record separator, a blank line by default, was printed after the ALL_CLUSTER_HASH_EXPRESSIONS view was listed. This blank line removes any ambiguity about whether H_EXPRESSIONS and ALL_CLUSTER_HAS are two separate views or if a long view name has wrapped to a second line. Setting RECSEP to OFF yields the following results:
SQL> SET RECSEP OFF SQL> SELECT VIEW_NAME from all_views; ALL_ALL_TABLES ALL_ARGUMENTS ALL_CATALOG ALL_CLUSTERS ALL_CLUSTER_HAS H_EXPRESSIONS ALL_COLL_TYPES
Do you see six views listed, or seven? How would you know?
In addition to controlling whether or not the record separator prints, you can control the character used as well. See the SET RECSEPCHAR command.
| SET RECSEPCHAR |
The RECSEPCHAR setting changes the record separator to something other than a line of space characters.
Syntax
SET RECSEPCHAR separator_char
Parameters
separator_char
Is the character you want to use in the record separator line. The default value is a space.
Examples
The default record separator is a blank line. The following example shows how you can change it to be a line of asterisks instead:
SQL> SET RECSEPCHAR "* " SQL> SET RECSEP EACH SQL> SELECT view_name FROM all_views; ALL_ALL_TABLES ******************************************************** ALL_ARGUMENTS ******************************************************** ALL_CATALOG ********************************************************
This example uses SET RECSEP EACH to cause a separator to print after each record, so you will see something. The separator character was set to an asterisk, causing you to get a full line of asterisks between each record. The length of the separator line will match the LINESIZE setting.
| SET SCAN | Obsolete |
The SCAN setting, an obsolete setting, allows you to choose whether SQL*Plus scans for substitution variables.
Syntax
SET SCAN {OFF ON}
Parameters
OFF
Disables variable substitution, and has the same effect as SET DEFINE OFF.
ON
Enables variable substitution, and has the same effect as SET DEFINE ON.
The SET SCAN command duplicates functionality provided by the SET DEFINE command.
| SET SERVEROUTPUT |
The SERVEROUTPUT setting controls whether SQL*Plus prints the output generated by the DBMS_OUTPUT package from PL/SQL procedures.
Syntax
SET SERVEROUT[PUT] {OFF ON}
[SIZE
buffer_size
]
[FOR[MAT] {WRA[PPED] WOR[D_WRAPPED] TRU[NCATED]}]
Parameters
SET SERVEROUT[PUT]
Is the command, which may be abbreviated SET SERVEROUT.
OFF
Keeps PL/SQL output from being displayed. This is the default setting.
ON
Causes SQL*Plus to check for and display output generated by the DBMS_OUTPUT package after each PL/SQL block, procedure, or function you execute.
SIZE buffer_size
Sets the size of the buffer, in bytes, on the server that holds the output. This value can range from 2,000 to 1,000,000, and controls the maximum amount of output that any one PL/SQL routine can produce. The default buffer size is 2,000 bytes.
WRA[PPED]
Causes the output to be wrapped within the current line size. Line breaks will occur in the middle of words, if necessary.
WOR[D_WRAPPED]
Causes the output to be word-wrapped within the current line size. Line breaks will occur only at word boundaries.
TRU[NCATED]
Causes any output longer than the line size to be truncated.
Examples
By default, SQL*Plus doesn't display output from PL/SQL. The following example shows this:
SQL>
BEGIN
2
DBMS_OUTPUT.PUT_LINE('Hello World');
3
END;
4
/
PL/SQL procedure successfully completed.
The same block is executed again after issuing a SET SERVEROUTPUT ON command:
SQL>
SET SERVEROUTPUT ON
SQL>
BEGIN
2
DBMS_OUTPUT.PUT_LINE('Hello World');
3
END;
4
/
Hello World
PL/SQL procedure successfully completed.
Older versions of SQL*Plus don't support the SIZE and FORMAT clauses of this command.
| SET SHIFTINOUT | Not available in i SQL*Plus |
The SHIFTINOUT setting controls whether shift characters are displayed as part of the output. It is usable only with IBM 3270 terminals and their equivalent, and only when SQL*Plus is displaying data in a shift-sensitive character set.
Syntax
SET SHIFT[INOUT] {VIS[IBLE] INV[ISIBLE]}
Parameters
SET SHIFT[INOUT]
Is the command, which may be abbreviated SET SHIFT.
VIS[IBLE]
Enables the display of shift characters.
INV[ISIBLE]
Keeps shift characters from being displayed. This is the default.
| SET SHOWMODE | Not available in i SQL*Plus |
The SHOWMODE setting controls the feedback you get when you use the SET command to change a setting.
Syntax
SET SHOW[MODE] {ON OFF BOTH}
Parameters
SET SHOW[MODE]
Is the command, which may be abbreviated SET SHOW.
ON
Turns on SHOWMODE, causing SQL*Plus to list the before and after values of each setting you change using the SET command.
OFF
Turns off SHOWMODE and is the default setting.
BOTH
Has the same effect as ON.
Examples
The following example shows the results of turning on SHOWMODE:
SQL> SET SHOWMODE ON new: showmode BOTH SQL> SET LINESIZE 132 old: linesize 80 new: linesize 132 SQL> SET PAGESIZE 60 old: pagesize 10 new: pagesize 60
With SHOWMODE on, the old and new values of each setting are displayed when they are changed.
| SET SPACE | Obsolete |
The SPACE setting, an obsolete setting, is similar to SET COLSEP. It allows you to specify the number of spaces SQL*Plus prints between columns of output.
Syntax
SET SPACE num_of_spaces
Parameters
num_of_spaces
Is the new setting for the number of spaces you want to print between columns. The default setting is one space.
Examples
The following example shows how SET SPACE works by changing the spacing between columns from one to five spaces:
SQL> SELECT 'A' a, 'B' b FROM dual; A B - - A B SQL> SET SPACE 5 SQL> SELECT 'A' a, 'B' b FROM dual; A B - - A B
Issuing SET SPACE 5 has the exact same effect as issuing SET COLSEP " " (with five spaces). In fact, the two settings are kept in sync with one another. The SET SPACE command will change the COLSEP setting to match.
| SET SQLBLANKLINES | Not available in i SQL*Plus |
The SQLBLANKLINES setting, a new feature in Version 8.1 of SQL*Plus, allows SQL statements to contain embedded blank lines.
Syntax
SET SQLBLANKLINES {OFF ON}
Parameters
OFF
Turns off this feature. This is the default setting and doesn't allow a SQL statement to have embedded blank lines.
ON
Turns on the feature and allows you to enter a SQL statement with an embedded blank line.
Examples
Pressing Enter on a blank line while typing a SQL statement into SQL*Plus normally signals the end of the statement. The statement is placed into the buffer, and you have the option of making further edits or of executing the statement. Turning SQLBLANKLINES ON allows you to put a blank line in the middle of your statement, as in the following example:
SQL> SET SQLBLANKLINES ON SQL> SELECT 2 * 3 4 FROM EMPLOYEE 5 SQL>
This feature was added to SQL*Plus to allow it to execute Server Manager scripts, such as catproc.sql , without the need to go through and modify all the SQL statements in those scripts. Unlike SQL*Plus, the now defunct Server Manager utility allowed blank lines in a SQL statement.
| SQLCASE |
The SQLCASE setting controls whether SQL*Plus automatically uppercases or lowercases SQL statements and PL/SQL blocks as they are transmitted to the server for execution.
Syntax
SET SQLC[ASE] {MIXED UPPER LOWER}
Parameters
SET SQLC[ASE]
Is the command, which may be abbreviated SET SQLC.
MIXED
Leaves each statement just as you entered it. This is the default setting.
UPPER
Uppercases each statement, including any quoted text literals.
LOWER
Lowercases each statement, including any quoted text literals.
Examples
Be careful when changing this setting. Any case conversions that SQL*Plus does will affect your SQL statement keywords and any quoted text literals as well. This is seldom desirable behavior, as in the following example:
SQL> SET SQLCASE UPPER SQL> SELECT * FROM dual WHERE dummy='x'; D - X
You can see the SELECT statement succeeded even though the lowercase "x" in the WHERE clause doesn't match the uppercase "X" in the dummy column.
Case conversion occurs when the statement is transmitted to the database server. The contents of the buffer always reflect what you typed.
| SET SQLCONTINUE | Not available in i SQL*Plus |
The SQLCONTINUE setting controls the prompt used when you continue a statement to a second line, using the SQL*Plus continuation character.
Syntax
SET SQLCO[NTINUE] continuation_prompt
Parameters
SET SQLCO[NTINUE]
Is the command, which may be abbreviated SET SQLCO.
continuation_prompt
Is the new continuation prompt. The default value is ">"the greater-than character. You may optionally enclose the prompt in either single or double quotation marks.
Examples
The following example shows the effect of changing this setting:
SQL> SET SQLCONTINUE "Continue > " SQL> DESCRIBE - Continue >
The SQL*Plus continuation character, a dash, was used following the DESCRIBE command. The continuation prompt is used only when you use the continuation character to continue a command to a new line. It isn't used when you enter a multiline SQL statement.
| SET SQLNUMBER | Not available in i SQL*Plus |
The SQLNUMBER setting controls whether SQL*Plus uses the line number as a prompt when you enter a multiline SQL statement.
Syntax
SET SQLN[UMBER] {OFF ON}
Parameters
SET SQLN[UMBER]
Is the command, which may be abbreviated SET SQLN.
OFF
Causes SQL*Plus to use the same prompt for all lines of a SQL statement or PL/SQL block.
ON
Causes SQL*Plus to use the line number as the prompt for the second and subsequent lines of a SQL statement or PL/SQL block. This is the default setting.
Examples
The following example shows the difference between the ON and OFF settings:
SQL> SET SQLNUMBER ON SQL> SELECT 2 * 3 FROM dual 4 SQL> SET SQLNUMBER OFF SQL> SELECT SQL> * SQL> FROM dual SQL>
I can't imagine any reason to ever turn this setting off. Look at the first and second statements in the example. It's immediately obvious that the three lines of the first query all belong together as one statement. This is not so obvious with the second statementyou have to think about it a bit. The visual cue provided by the line numbers is missing, making you take more time to figure out what you are really looking at.
| SET SQLPLUSCOMPATIBILITY |
The SQLPLUSCOMPATIBILITY setting requests the behavior of a specific version of SQL*Plus. Its use is much like that of the COMPATIBILITY setting of a database instance.
Syntax
SET SQLPLUSCOMPAT[IBILITY] version . release [. update ]
Parameters
SET SQLPLUSCOMPAT[IBILITY]
Is the command, which may be abbreviated SET SQLPLUSCOMPAT.
version
Is a version number, such as the 8 in 8.1.7.
release
Is a release number, such as the 1 in 8.1.7.
update
Is an update number, such as the 7 in 8.1.7.
Table A-2 describes those aspects of SQL*Plus behavior controlled by the SQLPLUSCOMPATIBILITY setting. The threshold in the table represents the point at which the described behavior became available. The earliest acceptable value for SQLPLUSCOMPATIBILITY is 7.3.4.
Table A-2. SQLPLUSCOMPATIBILITY behaviors
|
Threshold |
Behavior |
|---|---|
|
10.1 |
SHOW ERRORS sorts PL/SQL error messages using some new data dictionary columns from user_errors that are available only beginning in Oracle Database 10 g . |
|
10.1 |
The CREATE, REPLACE, and SAVE options become available for the SPOOL command. |
|
10.1 |
The Windows versions of SQL*Plus allows filenames enclosed in quotes to contain whitespace characters. |
|
10.1 |
glogin.sql and login.sql are executed when SQL*Plus is first run and after every successful CONNECT command. |
|
10.1 |
SQL*Plus stops prefacing lines from /* comments with "DOC>". |
|
9.2 |
A column defined as FOLD_AFTER may be displayed at the beginning of a new line if that's necessary because of the column's width. |
|
9.0 |
A slash preceded by whitespace will be treated as a terminator and will execute the statement. See the "Examples" section. |
|
9. |
NCHAR and NVARCHAR2 column lengths are consistently treated as numbers of characters. Prior to this release, whether such lengths were byte lengths or character lengths depended on the character set being used. |
Examples
The following is an example from a Windows system demonstrating SQL*Plus 10.1's acceptance of filenames containing spaces:
SQL> SET SQLPLUSCOMPATIBILITY 9.2 SQL> SPOOL "two words " SP2-0556: Invalid filename. SQL> SET SQLPLUSCOMPATIBILITY 10.1 SQL> SPOOL "two words " SQL> SPOOL OFF SQL>
Similarly, beginning in 9.0, a forward slash (/) can't appear on a line by itself, even when it is preceded by spaces. Prior to 9.0, you could write a division operation that spanned multiple lines:
SQL> SET SQLPLUSCOMPAT 7.3.4 SQL> SELECT 1 2 / 3 2 4 FROM dual; 1/2 ---------- .5
However, beginning in 9.0, the forward slash in the second line of the query will trigger the as yet incompletely entered query's execution:
SQL> SET SQLPLUSCOMPAT 9.0 SQL> SELECT 1 2 / SELECT 1 * ERROR at line 1: ORA-00923: FROM keyword not found where expected
Any non-whitespace character on the same line with the forward slash prevents premature statement execution. For example, with respect to the preceding example, had /2 been entered on line 2, the statement would not have executed.
| SET SQLPREFIX | Not available in i SQL*Plus |
The SQLPREFIX setting controls the SQL*Plus prefix character. The prefix character allows you to execute a SQL*Plus command while in the middle of entering an SQL statement (or PL/SQL block).
Syntax
SET SQLPRE[FIX] prefix_char
Parameters
SET SQLPRE[FIX]
Is the command, which may be abbreviated SET SQLPRE.
prefix_char
Is the new prefix character. The default prefix character is a pound sign. This may optionally be enclosed in single or double quotation marks.
Examples
The following example shows how the prefix character is used by using it to execute a DESCRIBE command while entering a SELECT statement:
SQL> SELECT 2 #DESCRIBE EMPLOYEE Name Null? Type ------------------------------- -------- ---- EMPLOYEE_ID NOT NULL NUMBER EMPLOYEE_NAME VARCHAR2(40 EMPLOYEE_HIRE_DATE DATE EMPLOYEE_TERMINATION_DATE DATE EMPLOYEE_BILLING_RATE NUMBER 2 employee_id, employee_name 3 FROM employee 4
This ability to execute a SQL*Plus command (and it must be a SQL*Plus command) while entering a SQL statement can come in handy when you need to refresh your memory regarding the column names in the table.
| SET SQLPROMPT | Not available in i SQL*Plus |
The SET SQLPROMPT setting changes the SQL*Plus command prompt.
Syntax
SET SQLP[ROMPT] prompt_text
Parameters
SET SQLP[ROMPT]
Is the command, which may be abbreviated SET SQLP.
prompt_text
Is the new prompt text. The default prompt text is "SQL> ".
Examples
The following example shows the prompt being changed from "SQL> " to "SQL*Plus> ":
SQL> SET SQLPROMPT "SQL*Plus> " SQL*Plus>
In Chapter 14, the section Section 14.2 shows how you can set your prompt to automatically reflect the database to which you are connected.
| SET SQLTERMINATOR |
The SQLTERMINATOR setting controls whether SQL*Plus allows you to use a semicolon to terminate and execute a SQL statement. This setting controls the specific character used for this purpose.
Syntax
SET SQLT[ERMINATOR] {OFF ON
term_char
}
Parameters
SET SQLT[ERMINATOR]
Is the command, which may be abbreviated SET SQLT.
OFF
Turns off the feature that allows you to terminate and execute a SQL statement using a semicolon or other character.
ON
Turns on this feature and resets the terminator character to the default value of a semicolon.
term_char
Is the character you want to use as a statement terminator. This may be optionally enclosed in single or double quotation marks.
Examples
The following example changes the terminator character to a percent sign and uses it to terminate and execute a SELECT statement:
SQL> SET SQLTERMINATOR "% " SQL> SELECT employee_name FROM employee% EMPLOYEE_NAME ---------------------------------------- Pavlo Chubynsky Ivan Mazepa Taras Shevchenko . . .
| SET SUFFIX | Not available in i SQL*Plus |
The SUFFIX setting controls the default extension used for command files.
Syntax
SET SUF[FIX] extension
Paremeters
SET SUF[FIX]
Is the command, which may be abbreviated SET SUF.
extension
Is the default extension to use when referring to SQL files. The default value for this setting is sql.
This setting is used by commands such as START, @, SAVE, and others that refer to SQL files. It doesn't apply to files created with the SPOOL command.
| SET TAB | Not available in i SQL*Plus |
The TAB setting controls whether SQL*Plus uses tab characters when generating whitespace in terminal output. This setting is a throwback to the days when terminal connections ran at slow data rates (e.g., 1200 bits per second).
Syntax
SET TAB {OFF ON}
Parameters
OFF
Forces SQL*Plus to use space characters for all whitespace.
ON
Is the default setting, and allows SQL*Plus to insert tabs into the output rather than displaying a large number of space characters.
| SET TERMOUT | Not available in i SQL*Plus |
The TERMOUT setting controls whether SQL*Plus displays output generated by SQL statements, PL/SQL blocks, and SQL*Plus commands. This setting applies only when SQL*Plus is executing a script file. SQL*Plus displays output from commands entered interactively.
Syntax
SET TERM[OUT] {OFF ON}
Parameters
SET TERM[OUT]
Is the command, which may be abbreviated SET TERM.
OFF
Turns off terminal output.
ON
Turns on terminal output. This is the default setting.
Terminal output is often turned off while a command file is running to keep the user's screen from becoming cluttered with query output and feedback messages.
| SET TIME | Not available in i SQL*Plus |
The TIME setting controls whether SQL*Plus displays the current time with each command prompt.
Syntax
SET TI[ME] {OFF ON}
Parameters
SET TI[ME]
Is the command, which may be abbreviated SET TI.
OFF
Keeps the time from being displayed with the prompt. This is the default setting.
ON
Causes the time to be displayed as part of each prompt.
Examples
The following example shows the effect of issuing a SET TIME ON command:
SQL> SET TIME ON 22:44:41 SQL>
| SET TIMING |
The TIMING setting controls whether or not SQL*Plus displays the elapsed time for each SQL statement or PL/SQL block you execute.
Syntax
SET TIMI[NG] {OFF ON}
Parameters
SET TIMI[NG]
Is the command, which may be abbreviated SET TIMI.
OFF
Turns off the timing feature. This is the default setting.
ON
Enables the display of elapsed execution time for SQL statements and PL/SQL blocks.
See Chapter 12 for examples of this command.
| SET TRIMOUT | Not available in i SQL*Plus |
The TRIMOUT setting controls whether SQL*Plus displays any trailing spaces that may occur at the end of a line. The default setting is ON, which causes SQL*Plus to display only up to the last nonblank character on a line.
Syntax
SET TRIM[OUT] {ON OFF}
Parameters
SET TRIM[OUT]
Is the command, which may be abbreviated SET TRIM.
ON
Causes SQL*Plus to trim any trailing spaces from each line before it's displayed. This is the default setting.
OFF
Causes SQL*Plus to display all characters on a line, even the trailing spaces.
The default setting of ON usually works well when displaying data on a terminal, so there's rarely a reason to turn this setting off. TRIMOUT has no effect on spooled output. If you are spooling output to a file, the TRIMSPOOL setting controls whether trailing spaces are spooled as well.
| SET TRIMSPOOL | Not available in i SQL*Plus |
The TRIMSPOOL setting controls whether SQL*Plus writes trailing spaces when spooling data to a file. The default setting is OFF, which causes SQL*Plus to write each line to the spool file in its entirety, trailing spaces and all.
Syntax
SET TRIMS[POOL] {ON OFF}
Parameters
SET TRIMS[POOL]
Is the command, which may be abbreviated SET TRIMS.
ON
Causes SQL*Plus to trim any trailing spaces from each line before it is written to the spool file.
OFF
Causes SQL*Plus to write all characters of a line to the spool file, even the trailing spaces. This is the default setting.
If you are spooling data to a file to load it into another program, you may want to leave TRIMSPOOL ON. Otherwise, the program reading the spool file might return errors because the records are shorter than expected.
| SET TRUNCATE | Obsolete |
The obselete TRUNCATE setting gives you the same choice as SET WRAP. You can choose whether to truncate lines longer than the LINESIZE setting.
Syntax
SET TRU[NCATE] {OFF ON}
Parameters
SET TRU[NCATE]
Is the command, which may be abbreviated SET TRU.
OFF
Is the default setting, and allows long lines to be wrapped.
ON
Causes long lines of output to be truncated to match the current LINESIZE setting.
Examples
TRUNCATE and WRAP affect the same internal setting. Turning one on results in the other being turned off, and vice versa:
SQL> SHOW WRAP wrap : lines will be wrapped SQL> SET TRUNCATE ON SQL> SHOW WRAP wrap : lines will be truncated
|
When you select data longer than the LINESIZE and the WRAP setting is off, some versions of SQL*Plus display a warning message telling you that your rows may be truncated.
| SET UNDERLINE |
The SET UNDERLINE setting controls the character used to underline column headings. You can also control whether column headings are underlined at all.
Syntax
SET UND[ERLINE] {
underline_char
{ON OFF}}
Parameters
SET UND[ERLINE]
Is the command, which may be abbreviated SET UND.
underline_char
Is the character you want to use when underlining column headings. By default, a dash character (-) is used.
ON
Causes column headings to be underlined and resets the underline character back to the dash.
OFF
Keeps column headings from being underlined.
Examples
The following example shows the underline character being changed to an asterisk:
SQL> SET UNDERLINE * SQL> SELECT USER FROM dual; USER ****************************** JEFF
You can use SET UNDERLINE to turn off underlining:
SQL> SET UNDERLINE OFF SQL> SELECT USER FROM dual; USER JEFF
One reason you might turn underlining off would be if you wanted some columns to print without any headings at all. First, you would turn UNDERLINE off. Then you would define column headings for all columns with the COLUMN command. Those headings you did want to print would need to include the underlines as part of the heading definition. Other headings would be defined as empty strings.
| SET VERIFY |
The VERIFY setting controls whether or not SQL*Plus displays before and after images of each line that contains a substitution variable.
Syntax
SET VER[IFY] {OFF ON}
Parameters
SET VER[IFY]
Is the command, which may be abbreviated SET VER.
OFF
Turns off verification.
ON
Turns on verification. Lines containing substitution variables will be displayed before and after the substitution occurs. This is the default setting.
Examples
Verification is done only on lines that are part of a SQL statement or a PL/SQL block. When substitution occurs in a SQL*Plus command, before and after images are never displayed. The following example shows what verification looks like:
SQL> DEFINE dummy_char = 'X ' SQL> SELECT * FROM dual 2 WHERE dummy = '&&dummy_char'; old 2: where dummy = '&&dummy_char' new 2: where dummy = 'X' D - X
Notice that line 2, which contained the reference to the &&dummy_char substitution variable, was displayed before and after the reference to the variable was replaced by its value. If you don't like this display, you can turn it off with SET VERIFY OFF.
| SET WRAP |
The WRAP setting controls how SQL*Plus prints lines that contain more characters than the current LINESIZE setting allows. With WRAP ON, long lines are wrapped around and continued on as many physical lines as necessary. With WRAP OFF, lines are truncated to match the LINESIZE setting.
Syntax
SET WRA[P] {ON OFF}
Parameters
SET WRA[P]
Is the command, which may be abbreviated SET WRA.
ON
Causes long lines to be wrapped around to two or more physical lines in order to print and still fit within the LINESIZE setting.
OFF
Causes long records to be truncated to the current LINESIZE setting.
Examples
The following example shows the results of issuing the same SELECT statement, once with WRAP turned on and once with it turned off:
SQL> SET WRAP ON SQL> SELECT * FROM ALL_VIEWS WHERE VIEW_NAME = 'ALL_VIEWS'; OWNER VIEW_NAME TEXT_LENGTH ------------------------------ ------------------------------ ----------- TEXT ----------------------------------------------------------------------------- TYPE_TEXT_LENGTH ---------------- TYPE_TEXT ----------------------------------------------------------------------------- OID_TEXT_LENGTH --------------- OID_TEXT ----------------------------------------------------------------------------- VIEW_TYPE_OWNER VIEW_TYPE ------------------------------ ------------------------------ SYS ALL_VIEWS 821 select u.name, o.name, v.textlength, v.text, t.typetextlength, t.typetext, SQL> SET WRAP OFF SQL> / rows will be truncated rows will be truncated rows will be truncated rows will be truncated rows will be truncated rows will be truncated OWNER VIEW_NAME TEXT_LENGTH TEXT ------------------------------ ----------------------------- ----------- ---- SYS ALL_VIEWS 821 select
SQL*Plus displays several warning messages telling you that the rows you are about to see displayed may have been truncated.
| SHOW |
The SHOW command looks at the state of your SQL*Plus environment. You can use it to display the current value of any setting controlled by the SET command. (TRUNCATE is the one exception.) SHOW can look at page titles, page footers, etc.
Syntax
SHO[W] {
setting
ALL
BTI[TLE]
ERR[ORS] [{FUNCTION PROCEDURE PACKAGE PACKAGE BODY
TRIGGER TYPE TYPE BODY VIEW} [
owner
.]
object_name
]
LNO
PARAMETER[S] [
parameter_name
]
PNO
REL[EASE]
REPF[OOTER]
REPH[EADER]
SGA
SPOO[L]
SQLCODE
TTI[TLE]
USER}
Parameters
SHO[W]
Is the command, which may be abbreviated SHO.
setting
Is any one of the settings you can set using the SET command.
ALL
Shows everything, except for errors and the System Global Area (SGA).
BTI[TLE]
Displays the current page footer.
ERR[ORS]
Displays an error listing for a stored object. The command SHOW ERRORS causes the error listing for the most recently created object to be displayed. You can get the error listing for a specific object by specifying the object type (function, procedure, and so forth) and the object name.
FUNCTION PROCEDURE PACKAGE PACKAGE BODY TRIGGER TYPE TYPE BODY VIEW
Used with SHOW ERRORS to specify the object type of interest. This is necessary only if you are specifying the name of the object.
[owner.]object_name
Used with SHOW ERRORS to name the object for which you want to display an error listing.
LNO
Displays the current line number.
PARAMETER[S] [parameter_name]
Displays the current value of one or more initialization parameters.
PNO
Displays the current page number.
REL[EASE]
Displays the release number (the version) of the Oracle database to which you are connected.
REPF[OOTER]
Displays the current report footer.
REPH[EADER]
Displays the current report header.
SGA
Displays information about the current state of the System Global Area.
SPOO[L]
Tells you whether or not output is currently being spooled to a file.
SQLCODE
Displays the SQL code returned by the most recent SQL statement.
TTI[TLE]
Displays the current page title.
USER
Displays the current username.
Examples
The following few examples demonstrate how the SHOW command displays the value of one item, such as a setting or a page title:
SQL> SHOW LINESIZE linesize 80 SQL> SHOW USER USER is "JEFF" SQL> SHOW TTITLE ttitle OFF and is the 1st few characters of the next SELECT statement
The ALL option may be used to display the values of all settings at once:
SQL> SHOW ALL appinfo is ON and set to "SQL*Plus" arraysize 15 autocommit OFF autoprint OFF autotrace OFF shiftinout INVISIBLE blockterminator "." (hex 2e) . . .
When you use SHOW ALL, the settings are displayed in alphabetical order.
The SHOW ERRORS command has more parameters than the other options, so it's more complex. SHOW ERRORS is used to display error listings for stored procedures, stored functions, packages, triggers, and other such objects. Typically, you first issue a CREATE statement, and then, if errors are reported , you follow that with a SHOW ERRORS command. The following example uses SHOW ERRORS to display an error listing for the most recent creation attempt:
SQL> CREATE OR REPLACE TRIGGER employee_set_key 2 BEFORE INSERT ON employee 3 FOR EACH ROW 4 BEGIN 5 :new.employee_id := employee_seq.nextval; 6 END; 7 / Warning: Trigger created with compilation errors. SQL> SHOW ERRORS Errors for TRIGGER EMPLOYEE_SET_KEY: LINE/COL ----------------------------------------------------------------- ERROR ----------------------------------------------------------------- 2/3 PL/SQL: Statement ignored 2/23 PLS-00201: identifier 'EMPLOYEE_SEQ.NEXTVAL' must be declared SQL>
You can show errors for a specific object by telling SQL*Plus the object type and the object name:
SQL> CREATE OR REPLACE PROCEDURE JEFF.DISABLE_TRIGGER AS 2 BEGIN 3 ALTER TABLE EMPLOYEE DISABLE TRIGGER EMPLOYEE_SET_KEY; 4 END; 5 / Warning: Procedure created with compilation errors. SQL> SHOW ERRORS PROCEDURE JEFF.DISABLE_TRIGGER Errors for PROCEDURE JEFF.DISABLE_TRIGGER: LINE/COL ----------------------------------------------------------------------------- ERROR ----------------------------------------------------------------------------- 3/3 PLS-00103: Encountered the symbol "ALTER" when expecting one of the following: begin declare exit for goto if loop mod null pragma raise return select update while << close current delete fetch lock insert open rollback savepoint set sql commit
The error listings remain even after you end the session in which you tried to create the object. You can come back later, display the errors, and pick up where you left off. But when you do that, you must name the object you are working with.
| SHUTDOWN |
The SHUTDOWN command closes a database and stops an Oracle instance. Chapter 10 discusses this command. To use SHUTDOWN, you must be connected as SYSDBA, SYSOPER, or INTERNAL.
Syntax
SHUTDOWN [NORMAL IMMEDIATE TRANSACTIONAL [LOCAL] ABORT]
Parameters
NORMAL
Causes a normal shutdown to take place. New users are blocked from connecting. The database remains open until all currently connected users voluntarily disconnect. When the last user disconnects, the database files are closed, the database is dismounted, and the instance is stopped .
IMMEDIATE
Causes users to be summarily disconnected when their current SQL statement completes execution. Users not in the middle of executing a statement are disconnected immediately. As each remaining user's currently executing SQL statement completes, she is forcibly disconnected from the database. Any open transactions are rolled back, the database files are closed, the database is dismounted, and the instance is stopped.
TRANSACTIONAL
A compromise between NORMAL and IMMEDIATE. Users are allowed to finish their current transactions. As each user completes his current transaction, he is forcibly disconnected. When the last user disconnects, the database is closed and dismounted, and the instance is stopped.
[LOCAL]
A TRANSACTIONAL LOCAL shutdown waits only for local transactions to finish, and then aborts any distributed transactions in progress.
ABORT
Is tantamount to pulling the plug on the server. All background processes are immediately aborted. Users are summarily disconnected. No rollback is done on open transactions, and dirty buffers aren't written back to the disk. Crash recovery occurs the next time you start the database. This is the only shutdown option that doesn't leave the database files in a consistent state.
| SPOOL | Not available in i SQL*Plus |
The SPOOL command writes output to a text file. You must use this if you are going to print a report. The only way to print a report is to spool it to a file, then print that file. See Chapter 5 for an example of SPOOL being used to generate a report file. SPOOL may be used to generate a new file of SQL statements to be executed. Chapter 11 shows you how to take advantage of that powerful technique.
Syntax
SP[OOL]
file_name
[
option
] OFF OUToption ::= {CRE[ATE] REP[LACE] APP[END]}
Parameters
SP[OOL]
Is the command, which may be abbreviated SP.
file_name
Is the name of the file to which you want to write the report. The default extension is usually .lst , or sometimes .lis , and it's an operating system-specific value.
OFF
Turns off spooling. You must have turned spooling on before you can turn it off.
OUT
Turns off spooling and prints the file on the default printer. This option isn't available in the Windows versions of SQL*Plus.
CREATE
Requires that a new file be created. If the named file exists, then the SPOOL command aborts with an error.
REPLACE
Replaces an existing file or creates an entirely new file, whichever applies. This represents the default behavior.
APPEND
Adds subsequent output to the end of an existing spool file.
| START |
The START command functions the same way as the @ command and is used to execute a SQL*Plus script file. From i SQL*Plus, you can only run scripts via http or ftp .
Syntax
STA[RT] {
script_file
url
} [
argument
[
argument
. . . ]]
Paremeters
STA[RT]
Is the command, which may be abbreviated STA.
script_file
Is the name of the file you want to execute and may include the path and the extension. The default extension is sql. If you don't specify a path, SQL*Plus will look for the file first in the current working directory and then search each directory listed in the SQLPATH environment variable. See Chapter 14 for information about customizing the search path.
url
Is an http or ftp URL pointing to a script that you wish to execute. Oracle9 i Database Release 1 brought this option to SQL*Plus on Windows; Oracle9 i Database Release 2 brought it to SQL*Plus on all platforms.
argument
Is an argument you wish to pass to the script. You may pass as many arguments as you like. Arguments must be separated from each other by at least one space. Arguments may be enclosed in quotation marks, and should be if they contain spaces. Single or double quotation marks may be used at your discretion. Your script may reference the first argument as &1, the second as &2, etc.
| STARTUP |
The STARTUP command starts an Oracle instance and opens a database. Chapter 10 discusses this command. To use STARTUP, you must be connected as SYSDBA, SYSOPER, or INTERNAL.
Syntax
STARTUP {
options
upgrade_options
}
options
::= [FORCE] [RESTRICT]
[PFILE=
parameter_filename
]
[QUIET] [
mount
]
mount
::= { MOUNT [
database_name
]
OPEN [
open_options
]
NOMOUNT}
open_options
::= {READ {ONLY WRITE [RECOVER]}
RECOVER}
upgrade_options
::= [PFILE=
parameter_filename
]
{UPGRADE DOWNGRADE}
[QUIET]
Parameters
FORCE
Forces the instance to start. If the instance is currently running, then FORCE will cause the equivalent of a SHUTDOWN ABORT to be done first; then the instance will be restarted.
RESTRICT
Opens the database in restricted session mode. Only users with the RESTRICTED SESSION system privilege will be allowed to connect.
PFILE=parameter_filename
Tells SQL*Plus to use the specified parameter file (initialization file) when starting the instance. You may specify a path with the filename.
|
QUIET
Prevents SQL*Plus from displaying details about the allocation of memory for the instance's SGA.
MOUNT [database_name]
Causes the database to be mounted but not opened. The instance will be started, and the control file will be opened, but none of the other database files will be opened. The MOUNT stage is the one between NOMOUNT and OPEN. You may optionally specify a non-default name for the database. Otherwise, the database name is derived from the DB_NAME initialization parameter.
OPEN
Causes the database to be mounted, then opened for normal operation.
NOMOUNT
Starts the instance without opening the control file, nor any other database files.
READ ONLY
Opens the database for reading but disallows any writes.
READ WRITE
Opens the database for normal read/write operation. This is the default.
RECOVER or READ WRITE RECOVER
Tells Oracle to perform media recovery if necessary. If no recovery is necessary, the database will open in the usual way. If recovery is necessary, it will proceed automatically. You will be prompted for any needed log files that can't be found. A failed recovery leaves the database mounted but not opened.
UPGRADE
Opens the database in a mode that allows you to run scripts (supplied by Oracle) to upgrade the database to a newer version. See the Oracle Database Upgrade Guide (Oracle Corporation) before using this option.
DOWNGRADE
Similar to UPGRADE but enables database downgrade scripts to be run.
| STORE | Not available in i SQL*Plus |
STORE generates a file of SET commands based on the current state of those settings. This file can be used after those settings have been changed to reset everything back to a known state.
Syntax
STORE SET filename [CRE[ATE] REP[LACE] APP[END]]
Parameters
SET
Is an option indicating what you want to store. Currently, the only option available is SET.
filename
Is the name of the file, including the path and extension, to which you want to write the SET commands.
CRE[ATE]
Causes the command to fail if the file already exists.
REP[LACE]
Causes SQL*Plus to overwrite any existing file with the same name.
APP[END]
Causes the SET commands to be appended to an existing file.
| TIMING |
The TIMING command starts, stops, or displays the value of a timer. Timers let you measure elapsed time and are described in Chapter 12.
Syntax
TIMI[NG] [START [ timer_name ] SHOW STOP]
Parameters
TIMI[NG]
Is the command, which may be abbreviated TIMI.
START [timer_name]
Starts a new timer and optionally gives it the name you provide.
SHOW
Shows the current value of the most recently started timer.
STOP
Stops the most recently started timer, shows its current value, then deletes it.
| TTITLE |
Use the TTITLE command to define page titles for a report. Chapter 5 discusses TTITLE and provides several examples. Also see the BTITLE command. TTITLE and BTITLE work the same way.
Syntax
TTITLE [OFF ON] option [ option . . . ] option ::= [COL x S[KIP] x TAB x LE[FT] CE[NTER] R[IGHT] BOLD FOR[MAT] format_spec text variable ]
Parameters
TTI[TLE]
Is the command, which may be abbreviated TTI. Issuing the TTITLE command with no parameters causes SQL*Plus to display the current top title setting.
OFF
Turns the page title off but doesn't erase its definition. You can turn it back on with ON.
ON
Turns on printing of page titles. The default title, if you don't specify another, is the current date, the page number, and all or part of the SELECT statement.
COL x
Causes any title text following this parameter to print at the specified column position.
S[KIP] x
May be abbreviated S. Inserts the specified number of line breaks before printing any subsequent title text.
TAB x
TAB is similar to COL, but moves you the specified number of columns relative to the current position. Negative numbers move you backward. TAB has nothing to do with tab characters.
LE[FT]
May be abbreviated LE. Causes subsequent title text to be printed beginning at the leftmost column of the current title line.
CE[NTER]
May be abbreviated CE. Causes subsequent title text to be centered within the current line. The LINESIZE setting controls the line width.
R[IGHT]
May be abbreviated R. Causes subsequent title text to be printed flush right. The LINESIZE setting controls where SQL*Plus thinks the right end of the line is.
BOLD
Makes your title "bold" by printing it three times. Only title text following the BOLD command is repeated on each line. There is no NOBOLD parameter.
FOR[MAT]
May be abbreviated FOR. Allows you to control how subsequent numeric data in the title is displayed.
format_spec
Is a string that specifies the display format to use for subsequent numeric data in the title. The format elements you can use here are the same as for the COLUMN command, and are described in Appendix B. Specifying a character format, such as A20, is possible but that has no effect on subsequent character strings.
text
Is any text you want to have in the title. To be safe, you should enclose this in quotes, but you need not as long as your title text doesn't include any keywords like BOLD or TAB that have meaning to TTITLE. Single or double quotation marks may be used. If you need to include a quotation mark as part of your text, use two quote characters back to back.
variable
May be one of the system variables maintained by SQL*Plus and listed in Table A-1 (see the BTITLE command).
When using TTITLE, you should start off with one of the keywords, such as LEFT, RIGHT, or CENTER. Otherwise, if the first parameter after the command is only text, SQL*Plus will assume you have used an obsolete syntax for this command and you won't get the results you want.
| UNDEFINE |
UNDEFINE, the opposite of DEFINE, erases a user variable definition. UNDEFINE is discussed in Chapter 8.
Syntax
UNDEF[INE] variable_name [ variable_name . . . ]
Parameters
UNDEF[INE]
Is the command, which may be abbreviated UNDEF.
variable_name
Is the name of a substitution variable to delete. You can delete several variables with one command by listing them out separated by spaces.
| VARIABLE |
The VARIABLE command declares bind variables. Bind variables are discussed in Chapter 7. They are real variables that can be used within a PL/SQL block or SQL statement.
Syntax
VAR[IABLE] variable_name data_type
Parameters
VAR[IABLE]
Is the command, which may be abbreviated VAR.
variable_name
Is whatever name you want to give the variable. A variable name must start with a letter, but after that the name may contain any combination of letters , digits, underscores, pound signs, and dollar signs. Thirty characters is the maximum length for a variable name.
data_type
Is the datatype of the variable. The following datatypes are allowed:
NUMBER
Results in a floating-point number and is the same as a NUMBER variable in PL/SQL or a NUMBER column in a table. Unlike PL/SQL, SQL*Plus doesn't let you specify a length or a precision, so a declaration like NUMBER (9,2) wouldn't be allowed.
BINARY_FLOAT
Results in a BINARY_FLOAT value. Not available before Oracle9 i Database.
BINARY_DOUBLE
Results in a BINARY_DOUBLE value. Not available before Oracle9 i Database.
CHAR [( length )]
Results in a fixed-length character string. length is optional. If it is omitted, you get a one-character string.
NCHAR [( length )]
Results in a fixed-length character string in the national character set. Length is optional. If it is omitted, you get a one-character string.
VARCHAR2 ( length )
Results in a variable-length character string.
NVARCHAR2 ( length )
Results in a variable-length character string using the national language character set.
CLOB
Results in a character large object (CLOB) variable.
NCLOB
Results in a CLOB variable using the national language character set.
REFCURSOR
Gives you a cursor variable you can use to return the results of a SQL query from PL/SQL to SQL*Plus.
| WHENEVER |
The WHENEVER command controls the behavior of SQL*Plus when an operating system or SQL error occurs and is discussed in Chapter 11. You can choose between having SQL*Plus exit immediately or continue on whenever an error occurs. You can choose whether to automatically COMMIT or ROLLBACK in the event of an error. If you decide to abort in the event of an error, you can pass a value back to the operating system. If you are calling SQL*Plus from an operating system script, you can use this return value to determine that script's next course of action.
|
HTML [ON OFF]
Enables or disables HTML output. The default is HTML OFF.
[HEAD text]
Specifies content for the HEAD tag, which ends up as
.
[BODY text]
Specifies attributes for the BODY tag, which ends up as
[TABLE text]
Specifies attributes for the TABLE tag used to define tables holding query output, which ends up as
. Tables used to hold page headers and footers aren't affected by this parameter.
[ENTMAP {ON OFF}]
Specifies whether SQL*Plus replaces special characters such as "<" and ">" with their corresponding HTML entities (e.g., "<" and ">"). The default is ENTMAP ON.
[SPOOL {ON OFF}
Specifies whether a spooled report is generated as a complete, HTML page (ON) or as an HTML fragment (OFF) suitable for embedding into a page that you create. The default is SPOOL OFF.
[PREFORMAT {ON OFF}]
Matters only when HTML output is enabled, and specifies whether SQL*Plus writes query output as an HTML table or as a preformatted text block (using
...
). The default is PREFORMAT OFF.
Syntax
WHENEVER {OSERROR SQLERROR}
{EXIT [SUCCESS FAILURE
value
:
bind_variable
sub_variable
]
[COMMIT ROLLBACK]
CONTINUE [COMMIT ROLLBACK NONE]}
Parameters
WHENEVER OSERROR
Use this command form to tell SQL*Plus what to do in case an operating system error occurs.
WHENEVER SQLERROR
Use this command form to tell SQL*Plus what to do in case an error is returned from a SQL statement or PL/SQL block.
EXIT SUCCESS
Exit with a success status. The exact value of the success status is operating-system-dependent but is often 0. This is the default setting and it applies if the EXIT keyword is used without specifying any return value.
EXIT FAILURE
Exit with a failure status. The value of the failure status is operating-system-dependent but is often 1.
EXIT value
Exit and return the value specified as the status.
EXIT :bind_variable
Exit and return the value of the specified bind variable as the status.
EXIT sub_variable
Exit and return the contents of the specified substitution variable as the status.
CONTINUE
Don't exit if an error occurs. This is the default behavior when you first start SQL*Plus.
COMMIT
This keyword may be used in conjunction with EXIT and CONTINUE. It causes SQL*Plus to COMMIT the current transaction automatically when an error occurs. This is the default behavior when you use the EXIT keyword.
ROLLBACK
May be used in conjunction with EXIT and CONTINUE and causes SQL*Plus to roll back the current transaction when an error occurs.
NONE
May be used only in conjunction with CONTINUE, and causes SQL*Plus neither to COMMIT nor ROLLBACK when an error occurs. This is the default behavior when you use the CONTINUE keyword.
