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.

The syntax shown here for ACCEPT is valid for SQL*Plus in Oracle8 i Database onward. Not all of the clauses are available when using prior versions. Check your documentation if you are writing scripts that need to work under earlier versions of SQL*Plus.

 

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_AFTER and FOLD_BEFORE are ignored when generating HTML output unless PREFORMAT has been set to ON.

 

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.

NEWLINE is ignored when generating HTML output unless PREFORMAT has been set to ON.

 

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.

COPY is not being enhanced to handle datatypes and features introduced with or after Oracle8 i Database. COPY is a deprecated command and may be removed in a future release of SQL*Plus.

 

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:

  1. Make your current working directory $ORACLE_HOME/sqlplus/admin/help .
  2. Run SQL*Plus and connect to your database as the SYSTEM user.
  3. 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 .

Older releases of Oracle used SQL*Loader to load the help table. For example, for Oracle8 i Database, Release 8.1.5, you needed to connect as SYSTEM and run helptbl.sql followed by helpindx.sql to create the necessary table and indexes. Then, to load the help text, you would invoke SQL*Loader against the plushelp.ctl control file.

 

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.

PRINT

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.

Changing the terminator to a slash character by using SET BLOCKTERMINATOR / prevents you from subsequently using the / character to execute the contents of the buffer.

 

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.

The concatenation character is never left in the line. When SQL*Plus substitutes a value for the variable, the concatenation character goes away along with the variable name.

 

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.

If you are writing a script that spools data to a file, you will almost certainly want to leave ECHO off. Otherwise, the commands in your script would be spooled to the file along with the data.

 

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.

Be sure to execute the first query of a report with SET EMBEDDED OFF. Otherwise, you may find that SQL*Plus continues the page numbering from a query executed earlier during the session.

 

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

 

Issuing a SET PAGESIZE 0 command also turns off headings. If you want to enable headings and SET HEADING ON doesn't appear to be working, you should check the pagesize as well.

 

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.

The line break in a heading is set when you first define that heading using the COLUMN command. Subsequently changing the heading separator character doesn't affect column headings you have defined.

 

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

text

text >.

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.

Use SET NEWPAGE 0 if you want a formfeed printed at the start of each new page.

 

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

 

Although you can issue the SHOW WRAP command to see the current value of the WRAP setting, you can't issue SHOW TRUNCATE. The SHOW command doesn't recognize TRUNCATE as a valid SQL*Plus setting.

 

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.

SQL*Plus, not the Oracle instance, reads the parameter file. The path to the parameter file must be relative to the machine running SQL*Plus. This matters, for example, if you are using SQL*Plus on a PC and connecting remotely to an instance on a server to start it.

 

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.

When used from i SQL*Plus, WHENEVER performs the action you specify (e.g., COMMIT or ROLLBACK) and returns you to the i SQL*Plus input area. You aren't disconnected from your database session.

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.

     

Introduction to SQL*Plus

Command-Line SQL*Plus

Browser-Based SQL*Plus

A Lightning SQL Tutorial

Generating Reports with SQL*Plus

Creating HTML Reports

Advanced Reports

Writing SQL*Plus Scripts

Extracting and Loading Data

Exploring Your Database

Advanced Scripting

Tuning and Timing

The Product User Profile

Customizing Your SQL*Plus Environment

Appendix A. SQL*Plus Command Reference

Appendix B. SQL*Plus Format Elements



Oracle SQL Plus The Definitive Guide, 2nd Edition
Oracle SQL*Plus: The Definitive Guide (Definitive Guides)
ISBN: 0596007469
EAN: 2147483647
Year: N/A
Pages: 151

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net