Sqlplus show error

Вас заинтересует / Intresting for you:

@ («at» sign)

Syntax

@{url | file_name[.ext] } [arg…]

Runs the SQL*Plus statements in the specified script. The script can be called from the local file system or from a web server. Only the url form is supported in iSQL*Plus. The @ command functions similarly to START.

Usage

All previous settings like COLUMN command settings stay in effect when the script starts. If the script changes any setting, this new value stays in effect after the script has finished.

You can include in a script any command you would normally enter interactively (typically, SQL, SQL*Plus commands, or PL/SQL blocks).

If the START command is disabled (see «Disabling SQL*Plus, SQL, and PL/SQL Commands»), this will also disable the @ command. See START for information on the START command.

SQL*Plus removes the SQLTERMINATOR (a semicolon by default) before the @ command is issued. If you require a semicolon in your command, add a second SQLTERMINATOR. See SET SQLT[ERMINATOR] {; | c | ON | OFF} for more information.

Examples

To run a script named PRINTRPT with the extension SQL, enter

@PRINTRPT

To run a script named WKRPT with the extension QRY, enter

@WKRPT.QRY

You can run a script named YEAREND specified by a URL, and pass values to variables referenced in YEAREND in the usual way:

@HTTP://machine_name.domain:port/YEAREND.SQL VAL1 VAL2
@FTP://machine_name.domain:port/YEAREND.SQL  VAL1 VAL2

On a web server configured to serve SQL reports, you could request SQL*Plus to execute a dynamic script with:

@HTTP://machine_name.domain:port/SCRIPTSERVER?ENDOFYEAR VAL1 VAL2

@@ (double «at» sign)

Syntax

@@url | file_name[.ext]

Runs a script. This command is almost identical to the @ («at» sign) command. When running nested scripts it looks for nested scripts in the same path or url as the calling script. Only the url form is supported in iSQL*Plus. The @@ command functions similarly to START.

Terms

url

Specifies the Uniform Resource Locator of a script to run on the specified web server. SQL*Plus supports HTTP and FTP protocols, but not HTTPS. HTTP authentication in the form http://username:password@machine_name.domain… is not supported in this release.

file_name[.ext]

Represents the nested script you wish to run. If you omit ext, SQL*Plus assumes the default command-file extension (normally SQL). For information on changing the default extension, see SET SUF[FIX] {SQL | text}.

When you enter @@file_name.ext from within a script, SQL*Plus runs file_name.ext from the same directory as the script.

When you enter @@file_name.ext interactively, SQL*Plus runs file_name.ext from the current working directory or from the same url as the script from which it was called. If SQL*Plus does not find the file, it searches a system-dependent path to find the file. Some operating systems may not support the path search. See the platform-specific Oracle documentation provided for your operating system for specific information related to your operating system environment.

Usage

All previous settings like COLUMN command settings stay in effect when the script starts. If the script changes any setting, the new value stays in effect after the script has finished.

You can include in a script any command you would normally enter interactively (typically, SQL or SQL*Plus commands).

If the START command is disabled (see «Disabling SQL*Plus, SQL, and PL/SQL Commands»), this will also disable the @@ command. For more information, see the SPOOL command.

SQL*Plus removes the SQLTERMINATOR (a semicolon by default) before the @@ command is issued. A workaround for this is to add another SQLTERMINATOR. See SET SQLT[ERMINATOR] {; | c | ON | OFF} for more information.

Examples

Suppose that you have the following script named PRINTRPT:

SELECT DEPARTMENT_ID, CITY FROM EMP_DETAILS_VIEW WHERE SALARY>12000;@EMPRPT.SQL@@ WKRPT.SQL

When you START PRINTRPT and it reaches the @ command, it looks for the script named EMPRPT in the current working directory and runs it. When PRINTRPT reaches the @@ command, it looks for the script named WKRPT in the same path as PRINTRPT and runs it.

Suppose that the same script PRINTRPT was located on a web server and you ran it with START HTTP://machine_name.domain:port/PRINTRPT. When it reaches the @ command, it looks for the script named EMPRPT in the current working directory and runs it. When PRINTRPT reaches the @@ command, it looks for the script named WKRPT in the same url as PRINTRPT, HTTP://machine_name.domain:port/WKRPT.SQL and runs it.

/ (slash)

Syntax

/(slash)

Executes the most recently executed SQL command or PL/SQL block which is stored in the SQL buffer.

The buffer has no command history list and does not record SQL*Plus commands.

Usage

You can enter a slash (/) at the command prompt or at a line number prompt of a multi-line command, or in the input area of the iSQL*Plus Workspace.

The slash command functions similarly to RUN, but does not list the command.

Executing a SQL command or PL/SQL block using the slash command will not cause the current line number in the SQL buffer to change unless the command in the buffer contains an error. In that case, SQL*Plus changes the current line number to the number of the line containing the error.

Examples

Type the following SQL script:

SELECT CITY, COUNTRY_NAMEFROM EMP_DETAILS_VIEWWHERE SALARY=12000;

Enter a slash (/) to re-execute the command in the buffer:

/

CITY                           COUNTRY_NAME
------------------------------ ----------------------------------------
Seattle                        United States of America
Oxford                         United Kingdom
Seattle                        United States of America

ACCEPT

Syntax

ACC[EPT] variable [NUM[BER] | CHAR | DATE | BINARY_FLOAT | BINARY_DOUBLE] [FOR[MAT] format] [DEF[AULT] default] [PROMPT text|NOPR[OMPT]] [HIDE]

Reads a line of input and stores it in a given substitution variable.

In iSQL*Plus, displays the Input Required screen for you to enter a value for the substitution variable.

Terms

variable

Represents the name of the variable in which you wish to store a value. If variable does not exist, SQL*Plus creates it.

NUM[BER]

Makes the variable a NUMBER datatype. If the reply does not match the datatype, ACCEPT gives an error message and prompts again.

CHAR

Makes the variable a CHAR datatype. The maximum CHAR length is 240 bytes. If a multi-byte character set is used, one CHAR may be more than one byte in size.

DATE

Makes reply a valid DATE format. If the reply is not a valid DATE format, ACCEPT gives an error message and prompts again. The datatype is CHAR.

BINARY_FLOAT

Makes the variable a BINARY_FLOAT datatype.

BINARY_DOUBLE

Makes the variable a BINARY_DOUBLE datatype.

FOR[MAT]

Specifies the input format for the reply. If the reply does not match the specified format, ACCEPT gives an error message and prompts again. If an attempt is made to enter more characters than are specified by the char format, an error message is given and the value must be reentered. If an attempt is made to enter a greater number precision than is specified by the number format, an error message is given and the value must be reentered. The format element must be a text constant such as A10 or 9.999. See COLUMN FORMAT for a complete list of format elements.

Oracle Database date formats such as «dd/mm/yy» are valid when the datatype is DATE. DATE without a specified format defaults to the NLS_DATE_FORMAT of the current session. See the Oracle Database Administrator’s Guide and the Oracle Database SQL Reference for information on Oracle Database date formats.

DEF[AULT]

Sets the default value if a reply is not given. The reply must be in the specified format if defined.

PROMPT text 

Displays text on-screen before accepting the value of variable from the user.

NOPR[OMPT] 

Skips a line and waits for input without displaying a prompt.

ACCEPT NOPR[OMPT] is not applicable in iSQL*Plus.

HIDE 

Suppresses the display as you type the reply.

To display or reference variables, use the DEFINE command. See the DEFINE command for more information.

Examples

To display the prompt «Password: «, place the reply in a CHAR variable named PSWD, and suppress the display, enter

ACCEPT pswd CHAR PROMPT 'Password:  ' HIDE

To display the prompt «Enter weekly salary: » and place the reply in a NUMBER variable named SALARY with a default of 000.0, enter

ACCEPT salary NUMBER FORMAT '999.99' DEFAULT '000.0' -
PROMPT 'Enter weekly salary:  '

To display the prompt «Enter date hired: » and place the reply in a DATE variable, HIRED, with the format «dd/mm/yyyy» and a default of «01/01/2003», enter

ACCEPT hired DATE FORMAT 'dd/mm/yyyy' DEFAULT '01/01/2003'-
PROMPT 'Enter date hired:  '

To display the prompt «Enter employee lastname: » and place the reply in a CHAR variable named LASTNAME, enter

ACCEPT lastname CHAR FORMAT 'A20' -
PROMPT 'Enter employee lastname:  '

APPEND

APPEND is not available in iSQL*Plus.

Syntax

A[PPEND] text

where text represents the text to append.

Adds specified text to the end of the current line in the SQL buffer. The buffer has no command history list and does not record SQL*Plus commands.

To separate text from the preceding characters with a space, enter two spaces between APPEND and text.

To APPEND text that ends with a semicolon, end the command with two semicolons (SQL*Plus interprets a single semicolon as an optional command terminator).

Examples

To append a comma delimiter, a space and the column name CITY to the first line of the buffer, make that line the current line by listing the line as follows:

1

Now enter APPEND:

APPEND , CITY
1

1* SELECT DEPARTMENT_ID, CITY

To append a semicolon to the line, enter

APPEND ;;

SQL*Plus appends the first semicolon to the line and interprets the second as the terminator for the APPEND command.

ARCHIVE LOG

Syntax

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

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

Terms

LIST

Requests a display that shows the range of redo log files to be archived, the current log file group’s sequence number, and the current archive destination (specified by either the optional command text or by the initialization parameter LOG_ARCHIVE_DEST).

If you are using both ARCHIVELOG mode and automatic archiving, the display might appear like:

ARCHIVE LOG LIST

Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /vobs/oracle/dbs/arch
Oldest online log sequence     221
Next log sequence to archive   222
Current log sequence           222

Since the log sequence number of the current log group and the next log group to archive are the same, automatic archival has archived all log groups up to the current one.

If you are using ARCHIVELOG but have disabled automatic archiving, the last three lines might look like:

Oldest online log sequence            222
Next log sequence to archive          222
Current log sequence                  225

If you are using NOARCHIVELOG mode, the «next log sequence to archive» line is suppressed.

The log sequence increments every time the Log Writer begins to write to another redo log file group; it does not indicate the number of logs being used. Every time an online redo log file group is reused, the contents are assigned a new log sequence number.

STOP

Disables automatic archival. If the instance is still in ARCHIVELOG mode and all redo log file groups fill, database operation is suspended until a redo log file is archived (for example, until you enter the command ARCHIVE LOG NEXT or ARCHIVE LOG ALL).

START

Enables automatic archiving. Starts the background process ARCH, which performs automatic archiving as required. If ARCH is started and a filename is supplied, the filename becomes the new default archive destination. ARCH automatically starts on instance startup if the initialization parameter LOG_ARCHIVE_START is set to TRUE.

NEXT

Manually archives the next online redo log file group that has been filled, but not yet archived.

ALL

Manually archives all filled, but not yet archived, online redo log file groups.

integer

Causes archival of the online redo log file group with log sequence number n. You can specify any redo log file group that is still online. An error occurs if the log file cannot be found online or the sequence number is not valid. This option can be used to re-archive a log file group.

destination

Specifies the destination device or directory in an operating system. Specification of archive destination devices is installation-specific; see your platform-specific Oracle Database documentation for examples of specifying archive destinations. On many operating systems, multiple log files can be spooled to the same tape.

If not specified in the command-line, the archive destination is derived from the initialization parameter LOG_ARCHIVE_DEST. The command ARCHIVE LOG START destination causes the specified device or directory to become the new default archive destination for all future automatic or manual archives. A destination specified with any other option is a temporary destination that is in effect only for the current (manual) archive. It does not change the default archive destination for subsequent automatic archives. For information about specifying archive destinations, see your platform-specific Oracle Database documentation.

Usage

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

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

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

Examples

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

ARCHIVE LOG START

To stop automatic archiving, enter

ARCHIVE LOG STOP 

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

ARCHIVE LOG 1001 '/vobs/oracle/dbs/arch' 

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

ATTRIBUTE

Syntax

ATTRIBUTE [type_name.attribute_name [option …]]

where option represents one of the following clauses:

ALI[AS] alias
CLE[AR]
FOR[MAT] format
LIKE {type_name.attribute_name | alias}
ON | OFF

Specifies display characteristics for a given attribute of an Object Type column, such as the format of NUMBER data. Columns and attributes should not have the same names as they share a common namespace.

Also lists the current display characteristics for a single attribute or all attributes.

Enter ATTRIBUTE followed by type_name.attribute_name and no other clauses to list the current display characteristics for only the specified attribute. Enter ATTRIBUTE with no clauses to list all current attribute display characteristics.

Terms

type_name.attribute_name

Identifies the data item (typically the name of an attribute) within the set of attributes for a given object of Object Type, type_name.

If you select objects of the same Object Type, an ATTRIBUTE command for that type_name.attribute_name applies to all such objects you reference in that session.

ALI[AS] alias

Assigns a specified alias to a type_name.attribute_name, which can be used to refer to the type_name.attribute_name in other ATTRIBUTE commands.

CLE[AR]

Resets the display characteristics for the attribute_name. The format specification must be a text constant such as A10 or $9,999—not a variable.

FOR[MAT] format

Specifies the display format of the column. The format specification must be a text constant such as A10 or $9,999—not a variable.

LIKE {type_name.attribute_name | alias}

Copies the display characteristics of another attribute. LIKE copies only characteristics not defined by another clause in the current ATTRIBUTE command.

ON | OFF

Controls the status of display characteristics for a column. OFF disables the characteristics for an attribute without affecting the characteristics’ definition. ON reinstates the characteristics.

Usage

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

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

Examples

To make the LAST_NAME attribute of the Object Type EMPLOYEE_TYPE 20 characters wide, enter

ATTRIBUTE EMPLOYEE_TYPE.LAST_NAME FORMAT A20

To format the SALARY attribute of the Object Type EMPLOYEE_TYPE so that it shows millions of dollars, rounds to cents, uses commas to separate thousands, and displays $0.00 when a value is zero, enter

ATTRIBUTE EMPLOYEE_TYPE.SALARY FORMAT $9,999,990.99

BREAK

Syntax

BRE[AK] [ON report_element [action [action]]] …

where report_element has the syntax {column|expr|ROW|REPORT}

and action has the syntax [SKI[P] n|[SKI[P]] PAGE] [NODUP[LICATES]|DUP[LICATES]]

Specifies where changes occur in a report and the formatting action to perform, such as:

• suppressing display of duplicate values for a given column

• skipping a line each time a given column value changes
  (In iSQL*Plus, only when Preformatted Output is ON)

• printing computed figures each time a given column value changes or at the end of the report.

  See the COMPUTE command.

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

Terms

ON column [action [action]]

When you include actions, specifies actions for SQL*Plus to take whenever a break occurs in the specified column (called the break column). (column cannot have a table or view appended to it. To achieve this, you can alias the column in the SQL statement.) A break is one of three events, a change in the value of a column or expression, the output of a row, or the end of a report

When you omit actions, BREAK ON column suppresses printing of duplicate values in column and marks a place in the report where SQL*Plus will perform the computation you specify in a corresponding COMPUTE command.

You can specify ON column one or more times. If you specify multiple ON clauses, as in

BREAK ON DEPARTMENT_ID SKIP PAGE ON JOB_ID - 
SKIP 1 ON SALARY SKIP 1

the first ON clause represents the outermost break (in this case, ON DEPARTMENT_ID) and the last ON clause represents the innermost break (in this case, ON SALARY). SQL*Plus searches each row of output for the specified breaks, starting with the outermost break and proceeding—in the order you enter the clauses—to the innermost. In the example, SQL*Plus searches for a change in the value of DEPARTMENT_ID, then JOB_ID, then SALARY.

Next, SQL*Plus executes actions beginning with the action specified for the innermost break and proceeding in reverse order toward the outermost break (in this case, from SKIP 1 for ON SALARY toward SKIP PAGE for ON DEPARTMENT_ID). SQL*Plus executes each action up to and including the action specified for the first break encountered in the initial search.

If, for example, in a given row the value of JOB_ID changes—but the values of DEPARTMENT_ID and SALARY remain the same—SQL*Plus skips two lines before printing the row (one as a result of SKIP 1 ON SALARY and one as a result of SKIP 1 ON JOB_ID).

Whenever you use ON column, you should also use an ORDER BY clause in the SQL SELECT command. Typically, the columns used in the BREAK command should appear in the same order in the ORDER BY clause (although all columns specified in the ORDER BY clause need not appear in the BREAK command). This prevents breaks from occurring at meaningless points in the report.

If the BREAK command specified earlier in this section is used, the following SELECT command produces meaningful results:

SELECT DEPARTMENT_ID, JOB_ID, SALARY, LAST_NAME
FROM EMP_DETAILS_VIEW
WHERE SALARY > 12000
ORDER BY DEPARTMENT_ID, JOB_ID, SALARY, LAST_NAME;

All rows with the same DEPARTMENT_ID print together on one page, and within that page all rows with the same JOB_ID print in groups. Within each group of jobs, those jobs with the same SALARY print in groups. Breaks in LAST_NAME cause no action because LAST_NAME does not appear in the BREAK command.

In BREAK commands, nulls are considered equal to each other, but not equal to anything else. This is different to the treatment of nulls in WHERE clauses.

ON expr [action [action]]

When you include actions, specifies actions for SQL*Plus to take when the value of the expression changes.

When you omit actions, BREAK ON expr suppresses printing of duplicate values of expr and marks where SQL*Plus will perform the computation you specify in a corresponding COMPUTE command.

You can use an expression involving one or more table columns or an alias assigned to a report column in a SQL SELECT or SQL*Plus COLUMN command. If you use an expression in a BREAK command, you must enter expr exactly as it appears in the SELECT command. If the expression in the SELECT command is a+b, for example, you cannot use b+a or (a+b) in a BREAK command to refer to the expression in the SELECT command.

The information given for ON column also applies to ON expr.

ON ROW [action [action]]

When you include actions, specifies actions for SQL*Plus to take when a SQL SELECT command returns a row. The ROW break becomes the innermost break regardless of where you specify it in the BREAK command. You should always specify an action when you BREAK on a row.

ON REPORT [action]

Marks a place in the report where SQL*Plus will perform the computation you specify in a corresponding COMPUTE command. Use BREAK ON REPORT in conjunction with COMPUTE to print grand totals or other «grand» computed values.

The REPORT break becomes the outermost break regardless of where you specify it in the BREAK command.

Note that SQL*Plus will not skip a page at the end of a report, so you cannot use BREAK ON REPORT SKIP PAGE.

SKI[P] n

Skips n lines before printing the row where the break occurred. BREAK SKIP n does not work in SET MARKUP HTML ON mode or in iSQL*Plus unless PREFORMAT is SET ON.

[SKI[P]] PAGE

Skips the number of lines that are defined to be a page before printing the row where the break occurred. The number of lines per page can be set with the PAGESIZE clause of the SET command. Note that PAGESIZE only changes the number of lines that SQL*Plus considers to be a page. Therefore, SKIP PAGE may not always cause a physical page break, unless you have also specified NEWPAGE 0. Note also that if there is a break after the last row of data to be printed in a report, SQL*Plus will not skip the page.

NODUP[LICATES]

Prints blanks rather than the value of a break column when the value is a duplicate of the column’s value in the preceding row.

DUP[LICATES]

Prints the value of a break column in every selected row.

Enter BREAK with no clauses to list the current break definition.

Usage

Each new BREAK command you enter replaces the preceding one.

To remove the BREAK command, use CLEAR BREAKS.

Examples

To produce a report that prints duplicate job values, prints the average of SALARY, and additionally prints the sum of SALARY, you could enter the following commands. (The example selects departments 50 and 80 and the jobs of clerk and salesman only.)

BREAK ON DEPARTMENT_ID ON JOB_ID DUPLICATES
COMPUTE SUM OF SALARY ON DEPARTMENT_ID
COMPUTE AVG OF SALARY ON JOB_ID
SELECT DEPARTMENT_ID, JOB_ID, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE JOB_ID IN ('SH_CLERK', 'SA_MAN')
AND DEPARTMENT_ID IN (50, 80)
ORDER BY DEPARTMENT_ID, JOB_ID;

DEPARTMENT_ID JOB_ID     LAST_NAME                     SALARY
------------- ---------- ------------------------- ----------
           50 SH_CLERK   Taylor                          3200
              SH_CLERK   Fleaur                          3100
                 .
                 .
                 .
              SH_CLERK   Gates                           2900

DEPARTMENT_ID JOB_ID     LAST_NAME                     SALARY
------------- ---------- ------------------------- ----------
           50 SH_CLERK   Perkins                         2500
              SH_CLERK   Bell                            4000
                 .
                 .
                 .
              SH_CLERK   Grant                           2600
              **********                           ----------
              avg                                        3215

DEPARTMENT_ID JOB_ID     LAST_NAME                     SALARY
------------- ---------- ------------------------- ----------

*************                                      ----------
sum                                                     64300

           80 SA_MAN     Russell                        14000
              SA_MAN     Partners                       13500
              SA_MAN     Errazuriz                      12000
              SA_MAN     Cambrault                      11000
              SA_MAN     Zlotkey                        10500
              **********                           ----------
              avg                                       12200

DEPARTMENT_ID JOB_ID     LAST_NAME                     SALARY
------------- ---------- ------------------------- ----------

*************                                      ----------
sum                                                     61000

25 rows selected.

BTITLE

Syntax

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

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

BOLD

CE[NTER]

COL n

FORMAT text

LE[FT]

R[IGHT]

S[KIP] [n]

TAB n

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

Enter BTITLE with no clauses to list the current BTITLE definition. For a description of the old form of BTITLE, see BTI[TLE] text (obsolete old form).

Terms

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

Usage

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

SQL*Plus substitution variables (& variables) are expanded before BTITLE is executed. The resulting string is stored as the BTITLE text. During subsequent execution for each page of results, the expanded value of a variable may itself be interpreted as a variable with unexpected results.

You can avoid this double substitution in a BTITLE command by not using the & prefix for variables that are to be substituted on each page of results. If you want to use a substitution variable to insert unchanging text in a BTITLE, enclose it in quotes so that it is only substituted once.

Examples

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

BTITLE LEFT 'CORPORATE PLANNING DEPARTMENT' -
RIGHT '1 JAN 2001'

To set a bottom title with CONFIDENTIAL in column 50, followed by

six spaces and

a date, enter

BTITLE COL 50 'CONFIDENTIAL' 
TAB 6 
'1 JAN 2001'

CHANGE

CHANGE is not available in iSQL*Plus.

Syntax

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

Changes the first occurrence of the specified text on the current line in the buffer. The buffer has no command history list and does not record SQL*Plus commands.

Terms

sepchar

Represents any non-alphanumeric character such as «/» or «!». Use a sepchar that does not appear in old or new.

old

Represents the text you wish to change. CHANGE ignores case in searching for old. For example,

CHANGE /aq/aw

finds the first occurrence of «aq», «AQ», «aQ», or «Aq» and changes it to «aw». SQL*Plus inserts the new text exactly as you specify it.

If old is prefixed with «…», it matches everything up to and including the first occurrence of old. If it is suffixed with «…», it matches the first occurrence of old and everything that follows on that line. If it contains an embedded «…», it matches everything from the preceding part of old through the following part of old.

new

Represents the text with which you wish to replace old. If you omit new and, optionally, the second and third sepchars, CHANGE deletes old from the current line of the buffer.

Usage

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

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

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

Examples

Enter 3 so the current line of the buffer contains the following text:

3

3* WHERE JOB_ID IS IN ('CLERK', 'SA_MAN')

Enter the following command:

CHANGE /CLERK/SH_CLERK/

The text in the buffer changes as follows:

3* WHERE JOB_ID IS IN ('SH_CLERK', 'SA_MAN')

Or enter the following command:

CHANGE /'CLERK',... /'SH_CLERK'/

The original line changes to

3* WHERE JOB_ID IS IN ('SH_CLERK')

Or enter the following command:

CHANGE /(...)/('SA_MAN')/

The original line changes to

3* WHERE JOB_ID IS IN ('SA_MAN')

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

3  WHERE JOB_ID IS IN ('SH_CLERK')

causes the second line of the buffer to be replaced with

WHERE JOB_ID IS IN ('SH_CLERK')

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

2  CHANGE/OLD/NEW/

will change the second line of the buffer to be

CLEAR

Syntax

CL[EAR] option …

where option represents one of the following clauses:

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

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

CLEAR SCREEN command not available in iSQL*Plus.

Terms

BRE[AKS]

Removes the break definition set by the BREAK command.

BUFF[ER]

Clears text from the buffer. CLEAR BUFFER has the same effect as CLEAR SQL, unless you are using multiple buffers.

See SET BUF[FER] {buffer|SQL} (obsolete) for more information about the obsolete form of this command.

COL[UMNS]

Resets column display attributes set by the COLUMN command to default settings for all columns. To reset display attributes for a single column, use the CLEAR clause of the COLUMN command. CLEAR COLUMNS also clears the ATTRIBUTEs for that column.

COMP[UTES]

Removes all COMPUTE definitions set by the COMPUTE command.

SCR[EEN]

Clears your screen.

CLEAR SCREEN is not available in iSQL*Plus.

SQL

Clears the text from SQL buffer. CLEAR SQL has the same effect as CLEAR BUFFER, unless you are using multiple buffers.

See SET BUF[FER] {buffer|SQL} (obsolete) for more information about the obsolete form of this command.

TIMI[NG]

Deletes all timers created by the TIMING command.

Examples

To clear breaks, enter

CLEAR BREAKS

To clear column definitions, enter

CLEAR COLUMNS

COLUMN

Syntax

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

where option represents one of the following clauses:

ALI[AS] alias
CLE[AR]
ENTMAP {ON | OFF}
FOLD_A[FTER]
FOLD_B[EFORE]
FOR[MAT] format
HEA[DING] text
JUS[TIFY] {L[EFT] | C[ENTER] | R[IGHT]}
LIKE {expr | alias}
NEWL[INE]
NEW_V[ALUE] variable
NOPRI[NT] | PRI[NT]
NUL[L] text
OLD_V[ALUE] variable
ON | OFF
WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED]

Specifies display attributes for a given column, such as

• text for the column heading

• alignment of the column heading

• format for NUMBER data

• wrapping of column data

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

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

Terms

{column | expr}

Identifies the data item (typically, the name of a column) in a SQL SELECT command to which the column command refers. If you use an expression in a COLUMN command, you must enter expr exactly as it appears in the SELECT command. If the expression in the SELECT command is a+b, for example, you cannot use b+a or (a+b) in a COLUMN command to refer to the expression in the SELECT command.

If you select columns with the same name from different tables, a COLUMN command for that column name will apply to both columns. That is, a COLUMN command for the column LAST_NAME applies to all columns named LAST_NAME that you reference in this session. COLUMN ignores table name prefixes in SELECT commands. Also, spaces are ignored unless the name is placed in double quotes.

To format the columns differently, assign a unique alias to each column within the SELECT command itself (do not use the ALIAS clause of the COLUMN command) and enter a COLUMN command for each column’s alias.

ALI[AS] alias

Assigns a specified alias to a column, which can be used to refer to the column in BREAK, COMPUTE, and other COLUMN commands.

CLE[AR]

Resets the display attributes for the column to default values.

To reset the attributes for all columns, use the CLEAR COLUMNS command. CLEAR COLUMNS also clears the ATTRIBUTEs for that column.

ENTMAP {ON | OFF}

Enables entity mapping to be turned on or off for selected columns in HTML output. This feature enables you to include, for example, HTML hyperlinks in a column of data, while still mapping entities in other columns of the same report. By turning entity mapping off for a column containing HTML hyperlinks, the HTML anchor tag delimiters, <, >, » and &, are correctly interpreted in the report. Otherwise they would be replaced with their respective entities, &lt;, &gt;, &quot; and &amp;, preventing web browsers from correctly interpreting the HTML.

Entities in the column heading and any COMPUTE labels or output appearing in the column are mapped or not mapped according to the value of ENTMAP for the column.

The default setting for COLUMN ENTMAP is the current setting of the MARKUP HTML ENTMAP option.

For more information about the MARKUP HTML ENTMAP option, see SET «MARKUP Options».

FOLD_A[FTER]

Inserts a carriage return after the column heading and after each row in the column. SQL*Plus does not insert an extra carriage return after the last column in the SELECT list. FOLD_A[FTER] does not work in SET MARKUP HTML ON mode or in iSQL*Plus unless PREFORMAT is set ON.

FOLD_B[EFORE]

Inserts a carriage return before the column heading and before each row of the column. SQL*Plus does not insert an extra carriage return before the first column in the SELECT list. FOLD_A[FTER] does not work in SET MARKUP HTML ON mode or in iSQL*Plus unless PREFORMAT is set ON.

FOR[MAT] format

Specifies the display format of the column. The format specification must be a text constant such as A10 or $9,999.

Character Columns

The default width of CHAR, NCHAR, VARCHAR2 (VARCHAR) and NVARCHAR2 (NCHAR VARYING) columns is the width of the column in the database. SQL*Plus formats these datatypes left-justified. If a value does not fit within the column width, SQL*Plus wraps or truncates the character string depending on the setting of SET WRAP.

A LONG, CLOB, NCLOB or XMLType column’s width defaults to the value of SET LONGCHUNKSIZE or SET LONG, whichever one is smaller.

To change the width of a datatype to n, use FORMAT An. (A stands for alphabetic.) If you specify a width shorter than the column heading, SQL*Plus truncates the heading.

DATE Columns

The default width and format of unformatted DATE columns in SQL*Plus is derived from the NLS_DATE_FORMAT parameter. The NLS_DATE_FORMAT setting is determined by the NLS territory parameter. For example, the default format for the NLS territory, America, is DD-Mon-RR, and the default width is A9. The NLS parameters may be set in your database parameter file, in environment variables or an equivalent platform-specific mechanism. They may also be specified for each session with the ALTER SESSION command. For more information about DATE formats, and about NLS parameters, see the Oracle Database SQL Reference.

You can change the format of any DATE column using the SQL function TO_CHAR in your SQL SELECT statement. You may also wish to use an explicit COLUMN FORMAT command to adjust the column width.

When you use SQL functions like TO_CHAR, Oracle Database automatically enables a very wide column. The default column width may also depend on the character sets in use in SQL*Plus and in the database. To maximize script portability if multiple characters sets are used, Oracle Database recommends using COLUMN FORMAT for each column selected.

To change the width of a DATE column to n, use the COLUMN command with FORMAT An. If you specify a width shorter than the column heading, the heading is truncated.

NUMBER Columns

For numeric columns, COLUMN FORMAT settings take precedence over SET NUMFORMAT settings, which take precedence over SET NUMWIDTH settings.

See «SET NUMF[ORMAT] format» and «SET NUM[WIDTH] {10 | n}».

To change a NUMBER column’s width, use FORMAT followed by an element as specified in Table 13-1, «Number Formats».

9,999

99.99

$9999

0999
9990

9999

B9999

C999

99D99

9.999EEEE

9G999

L999

9999MI

9999PR

RN
rn

S9999
9999S

TM

U9999

999V99

XXXX
xxxx

The MI and PR format elements can only appear in the last position of a number format model. The S format element can only appear in the first or last position.

If a number format model does not contain the MI, S or PR format elements, negative return values automatically contain a leading negative sign and positive values automatically contain a leading space.

A number format model can contain only a single decimal character (D) or period (.), but it can contain multiple group separators (G) or commas (,). A group separator or comma cannot appear to the right of a decimal character or period in a number format model.

SQL*Plus formats NUMBER data right-justified. A NUMBER column’s width equals the width of the heading or the width of the FORMAT plus one space for the sign, whichever is greater. If you do not explicitly use COLUMN FORMAT or SET NUMFORMAT, then the column’s width will always be at least the value of SET NUMWIDTH.

SQL*Plus may round your NUMBER data to fit your format or field width.

If a value cannot fit in the column, SQL*Plus displays pound signs (#) instead of the number.

If a positive value is extremely large and a numeric overflow occurs when rounding a number, then the infinity sign (~) replaces the value. Likewise, if a negative value is extremely small and a numeric overflow occurs when rounding a number, then the negative infinity sign replaces the value (-~).

HEA[DING] text

Defines a column heading. If you do not use a HEADING clause, the column’s heading defaults to column or expr. If text contains blanks or punctuation characters, you must enclose it with single or double quotes. Each occurrence of the HEADSEP character (by default, «|») begins a new line.

For example,

COLUMN LAST_NAME HEADING 'Employee |Name'

would produce a two-line column heading.

See SET HEADS[EP] { | | c | ON | OFF} in this chapter for information on changing the HEADSEP character.

JUS[TIFY] {L[EFT] | C[ENTER] | R[IGHT]}

Aligns the heading. If you do not use a JUSTIFY clause, headings for NUMBER columns default to RIGHT and headings for other column types default to LEFT.

LIKE {expr | alias}

Copies the display attributes of another column or expression (whose attributes you have already defined with another COLUMN command). LIKE copies only attributes not defined by another clause in the current COLUMN command.

NEWL[INE]

Starts a new line before displaying the column’s value. NEWLINE has the same effect as FOLD_BEFORE. NEWL[INE] does not work in SET MARKUP HTML ON mode or in iSQL*Plus unless PREFORMAT is SET ON.

NEW_V[ALUE] variable

Specifies a variable to hold a column value. You can reference the variable in TTITLE commands. Use NEW_VALUE to display column values or the date in the top title. You must include the column in a BREAK command with the SKIP PAGE action. The variable name cannot contain a pound sign (#).

NEW_VALUE is useful for master/detail reports in which there is a new master record for each page. For master/detail reporting, you must also include the column in the ORDER BY clause. See the example at the end of this command description.

Variables specified with NEW_V[ALUE] are expanded before TTITLE is executed. The resulting string is stored as the TTITLE text. During subsequent execution for each page of the report, the expanded value of a variable may itself be interpreted as a variable with unexpected results.

You can avoid this double substitution in a TTITLE command by not using the & prefix for NEW_V[ALUE] variables that are to be substituted on each page of the report. If you want to use a substitution variable to insert unchanging text in a TTITLE, enclose it in quotes so that it is only substituted once.

For information on displaying a column value in the bottom title, see OLD_V[ALUE] variable below. For more information on referencing variables in titles, see the TTITLE command later in this chapter. For information on formatting and valid format models, see FOR[MAT] format above.

NOPRI[NT] | PRI[NT]

Controls the printing of the column (the column heading and all the selected values). NOPRINT turns off the screen output and printing of the column. PRINT turns the printing of the column ON.

NUL[L] text

Controls the text SQL*Plus displays for null values in the given column. The default is a white space. SET NULL controls the text displayed for all null values for all columns, unless overridden for a specific column by the NULL clause of the COLUMN command. When a NULL value is selected, a variable’s type always becomes CHAR so the SET NULL text can be stored in it.

OLD_V[ALUE] variable

Specifies a variable to hold a column value. You can reference the variable in BTITLE commands. Use OLD_VALUE to display column values in the bottom title. You must include the column in a BREAK command with the SKIP PAGE action.

OLD_VALUE is useful for master/detail reports in which there is a new master record for each page. For master/detail reporting, you must also include the column in the ORDER BY clause.

Variables specified with OLD_V[ALUE] are expanded before BTITLE is executed. The resulting string is stored as the BTITLE text. During subsequent execution for each page of the report, the expanded value of a variable may itself be interpreted as a variable with unexpected results.

You can avoid this double substitution in a BTITLE command by not using the & prefix for OLD_V[ALUE] variables that are to be substituted on each page of the report. If you want to use a substitution variable to insert unchanging text in a BTITLE, enclose it in quotes so that it is only substituted once.

For information on displaying a column value in the top title, see NEW_V[ALUE] variable. For more information on referencing variables in titles, see the TTITLE command later in this chapter.

ON | OFF

Controls the status of display attributes for a column. OFF disables the attributes for a column without affecting the attributes’ definition. ON reinstates the attributes.

WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED]

Specifies how SQL*Plus will treat a datatype or DATE string that is too wide for a column. WRAPPED wraps the string within the column bounds, beginning new lines when required. When WORD_WRAP is enabled, SQL*Plus left justifies each new line, skipping all leading whitespace (for example, returns, newline characters, tabs and spaces), including embedded newline characters. Embedded whitespace not on a line boundary is not skipped. TRUNCATED truncates the string at the end of the first line of display.

Usage

The COLUMN commands you enter can control a column’s display attributes for multiple SQL SELECT commands.

You can enter any number of COLUMN commands for one or more columns. All column attributes set for each column remain in effect for the remainder of the session, until you turn the column OFF, or until you use the CLEAR COLUMN command.

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

Examples

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

COLUMN LAST_NAME FORMAT A20 HEADING 'EMPLOYEE|NAME'

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

COLUMN SALARY FORMAT $9,999,990.99

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

COLUMN SALARY+COMMISSION_PCT+BONUS-EXPENSES-INS-TAX ALIAS NET
COLUMN NET FORMAT $9,999,999.99 NULL '<NULL>'

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

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

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

COLUMN REMARKS FORMAT A20 WRAP

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

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

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

If you specify TRUNCATE, REMARKS looks like this:

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

To print the current date and the name of each job in the top title, enter the following. Use the EMPLOYEES table of the HR schema instead of EMP_DETAILS_VIEW.

For details on creating a date variable, see «Displaying the Current Date in Titles».

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

COLUMN JOB_ID NOPRINT NEW_VALUE JOBVAR
COLUMN TODAY  NOPRINT NEW_VALUE DATEVAR
BREAK ON JOB_ID SKIP PAGE ON TODAY
TTITLE CENTER 'Job Report' RIGHT DATEVAR  SKIP 2 -
LEFT 'Job:     ' JOBVAR SKIP 2
SELECT TO_CHAR(SYSDATE, 'MM/DD/YYYY') TODAY,
LAST_NAME, JOB_ID, MANAGER_ID, HIRE_DATE, SALARY, DEPARTMENT_ID
FROM EMPLOYEES WHERE JOB_ID IN ('MK_MAN', 'SA_MAN')
ORDER BY JOB_ID, LAST_NAME;

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

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

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

SELECT HIRE_DATEFROM EMPLOYEESWHERE EMPLOYEE_ID = 206;

Job Report                         04/19/01

Job:     SA_MAN

HIRE_DATE
----------
1994-06-07

See the Oracle Database SQL Reference for information on the ALTER SESSION command.

COMPUTE

Syntax

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

In combination with the BREAK command, calculates and prints summary lines, using various standard computations on subsets of selected rows. It also lists all COMPUTE definitions. For details on how to create summaries, see «Clarifying Your Report with Spacing and Summary Lines».

Terms

function …

Represents one of the functions listed in Table 13-2, «COMPUTE Functions». If you specify more than one function, use spaces to separate the functions.

COMPUTE command functions are always executed in the sequence AVG, COUNT, MINIMUM, MAXIMUM, NUMBER, SUM, STD, VARIANCE, regardless of their order in the COMPUTE command.

AVG

COU[NT]

MIN[IMUM]

MAX[IMUM]

NUM[BER]

SUM

STD

VAR[IANCE]

LAB[EL] text

Defines the label to be printed for the computed value. If no LABEL clause is used, text defaults to the unabbreviated function keyword. You must place single quotes around text containing spaces or punctuation. The label prints left justified and truncates to the column width or linesize, whichever is smaller. The maximum label length is 500 characters.

The label for the computed value appears in the break column specified. To suppress the label, use the NOPRINT option of the COLUMN command on the break column.

If you repeat a function in a COMPUTE command, SQL*Plus issues a warning and uses the first occurrence of the function.

With ON REPORT and ON ROW computations, the label appears in the first column listed in the SELECT statement. The label can be suppressed by using a NOPRINT column first in the SELECT statement. When you compute a function of the first column in the SELECT statement ON REPORT or ON ROW, then the computed value appears in the first column and the label is not displayed. To see the label, select a dummy column first in the SELECT list.

OF {expr | column | alias} …

In the OF clause, you can refer to an expression or function reference in the SELECT statement by placing the expression or function reference in double quotes. Column names and aliases do not need quotes.

ON {expr | column | alias | REPORT | ROW} …

If multiple COMPUTE commands reference the same column in the ON clause, only the last COMPUTE command applies.

To reference a SQL SELECT expression or function reference in an ON clause, place the expression or function reference in quotes. Column names and aliases do not need quotes.

Enter COMPUTE without clauses to list all COMPUTE definitions.

Usage

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

• One or more of the expressions, columns, or column aliases you reference in the OF clause must also be in the SELECT command.

• The expression, column, or column alias you reference in the ON clause must occur in the SELECT command and in the most recent BREAK command.

• If you reference either ROW or REPORT in the ON clause, also reference ROW or REPORT in the most recent BREAK command.

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

Note that if you use the NOPRINT option for the column on which the COMPUTE is being performed, the COMPUTE result is also suppressed.

Examples

To subtotal the salary for the «account manager», AC_MGR, and «salesman», SA_MAN, job classifications with a compute label of «TOTAL», enter

BREAK ON JOB_ID SKIP 1;
COMPUTE SUM LABEL 'TOTAL' OF SALARY ON JOB_ID;
SELECT JOB_ID, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE JOB_ID IN ('AC_MGR', 'SA_MAN')
ORDER BY JOB_ID, SALARY;

JOB_ID     LAST_NAME                     SALARY
---------- ------------------------- ----------
AC_MGR     Higgins                        12000
**********                           ----------
TOTAL                                     12000
SA_MAN     Zlotkey                        10500
           Cambrault                      11000
           Errazuriz                      12000
           Partners                       13500
           Russell                        14000
**********                           ----------
TOTAL                                     61000

6 rows selected.

To calculate the total of salaries greater than 12,000 on a report, enter

COMPUTE SUM OF SALARY ON REPORT
BREAK ON REPORT
COLUMN DUMMY HEADING ''
SELECT '   ' DUMMY, SALARY, EMPLOYEE_ID
FROM EMP_DETAILS_VIEW
WHERE SALARY > 12000
ORDER BY SALARY;

SALARY EMPLOYEE_ID
--- ---------- -----------
         13000         201
         13500         146
         14000         145
         17000         101
         17000         102
         24000         100
    ----------
sum      98500

6 rows selected.

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

BREAK ON DEPARTMENT_NAME SKIP 1
COMPUTE AVG LABEL 'Dept Average' -
        MAX LABEL 'Dept Maximum' -
        OF SALARY ON DEPARTMENT_NAME
SELECT DEPARTMENT_NAME, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE DEPARTMENT_NAME IN ('Executive', 'Accounting')
ORDER BY DEPARTMENT_NAME;

DEPARTMENT_NAME                LAST_NAME                     SALARY
------------------------------ ------------------------- ----------
Accounting                     Higgins                        12000
                               Gietz                           8300
******************************                           ----------
Dept Average                                                  10150
Dept Maximum                                                  12000

Executive                      King                           24000
                               Kochhar                        17000
                               De Haan                        17000
******************************                           ----------
Dept Average                                             19333.3333
Dept Maximum                                                  24000

To sum salaries for departments <= 20 without printing the compute label, enter

COLUMN DUMMY NOPRINT
COMPUTE SUM OF SALARY ON DUMMY
BREAK ON DUMMY SKIP 1
SELECT DEPARTMENT_ID DUMMY, DEPARTMENT_ID, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE DEPARTMENT_ID <= 20
ORDER BY DEPARTMENT_ID;

DEPARTMENT_ID LAST_NAME                     SALARY
------------- ------------------------- ----------
           10 Whalen                          4400
                                        ----------
                                              4400

           20 Hartstein                      13000
           20 Fay                             6000
                                        ----------
                                             19000

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

COLUMN DUMMY NOPRINT
COMPUTE SUM OF SALARY ON DUMMY
BREAK ON DUMMY
SELECT NULL DUMMY, DEPARTMENT_ID, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE DEPARTMENT_ID <= 30
ORDER BY DEPARTMENT_ID;

DEPARTMENT_ID LAST_NAME                     SALARY
------------- ------------------------- ----------
           10 Whalen                          4400
           20 Hartstein                      13000
           20 Fay                             6000
           30 Raphaely                       11000
           30 Khoo                            3100
           30 Baida                           2900
           30 Tobias                          2800
           30 Himuro                          2600
           30 Colmenares                      2500
                                        ----------
                                             48300

9 rows selected.

CONNECT

Syntax

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

where logon has the syntax username[/password] [@connect_identifier]

Connects a given username to the Oracle Database. When you run a CONNECT command, the site profile, glogin.sql, and the user profile, login.sql, are executed.

CONNECT does not reprompt for username or password if the initial connection does not succeed.

Terms

username[/password]

The username and password you use to connect to Oracle Database. If you omit username and password, SQL*Plus prompts you for them. If you enter a slash (/) or enter Return or click Execute when prompted for username, SQL*Plus logs you in using a default logon. See «/ (slash)» for more information.

If you omit only , SQL*Plus prompts you for . When prompting, SQL*Plus does not display on your terminal screen.

See the PASSWORD command for information about changing your password in SQL*Plus, and see «Changing Your Password in iSQL*Plus» for information about changing passwords in iSQL*Plus.

connect_identifier

An Oracle Net connect identifier. The exact syntax depends on the Oracle Net communications protocol your Oracle Database installation uses. For more information, refer to the Oracle Net manual for your protocol or contact your DBA. SQL*Plus does not prompt for a service name, but uses your default database if you do not include a connect identifier.

/ (slash)

Represents a default logon using operating system authentication. You cannot enter a connect_identifier if you use a default logon. In a default logon, SQL*Plus typically attempts to log you in using the username OPS$name, where name is your operating system username. See the Oracle Database Administrator’s Guide for information about operating system authentication.

AS {SYSOPER | SYSDBA}

The AS clause enables privileged connections by users who have been granted SYSOPER or SYSDBA system privileges. You can use either of these privileged connections with the default logon, /.

For information about system privileges, see the Oracle Database Administrator’s Guide.

Usage

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

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

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

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

Examples

To connect across Oracle Net with username HR and password HR to the database known by the Oracle Net alias as FLEETDB, enter

CONNECT HR/your_password@FLEETDB

To connect as user HR, letting SQL*Plus prompt you for the password, enter

CONNECT HR

For more information about setting up your password file, refer to the Oracle Database Administrator’s Guide.

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

CONNECT HR/your_password AS SYSDBA

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

CONNECT / AS SYSDBA

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

COPY

The COPY command is not being enhanced to handle datatypes or features introduced with, or after Oracle8i. The COPY command is likely to be made obsolete in a future release.

For COPY command details and syntax, see Appendix B, » SQL*Plus COPY Command».

DEFINE

Syntax

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

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

Terms

variable

Represents the user or predefined variable whose value you wish to assign or list.

text

Represents the CHAR value you wish to assign to variable. Enclose text in single quotes if it contains punctuation or blanks.

variable = text

Defines (names) a substitution variable and assigns it a CHAR value.

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

Usage

Defined variables retain their values until you:

• enter a new DEFINE command referencing the variable

• enter an UNDEFINE command referencing the variable

• enter an ACCEPT command referencing the variable

• reference the variable in the NEW_VALUE or OLD_VALUE clause of a COLUMN command and then reference the column in a SELECT command

• EXIT SQL*Plus

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

If the value of a defined variable extends over multiple lines (using the SQL*Plus command continuation character), SQL*Plus replaces each continuation character and carriage return with a space. For example, SQL*Plus interprets

DEFINE TEXT = 'ONE-
TWO-
THREE'

as

DEFINE TEXT = 'ONE TWO THREE'

You should avoid defining variables with names that may be identical to values that you will pass to them, as unexpected results can occur. If a value supplied for a defined variable matches a variable name, then the contents of the matching variable are used instead of the supplied value.

Some variables are predefined when SQL*Plus starts. Enter DEFINE to see their definitions.

Examples

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

DEFINE POS = MANAGER

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

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

DEFINE DEPARTMENT_ID = 20

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

To list the definition of DEPARTMENT_ID, enter

DEFINE DEPARTMENT_ID

DEFINE DEPARTMENT_ID = "20" (CHAR)

This result shows that the value of DEPARTMENT_ID is 20.

Predefined Variables

There are eight variables defined during SQL*Plus installation. These variables only differ from user defined variables by having predefined values.

_CONNECT_IDENTIFIER

Contains the connection identifier as supplied by the user to make a connection where it is available.

_DATE

Contains the current date, or a fixed string. _DATE can be either dynamic, showing the current date which is the default, or it can be set to a fixed string. The current date is formatted using the value of NLS_DATE_FORMAT.

Because _DATE can be used as a normal substitution variable, users may put it in TTITLE. If _DATE is dynamic and is used in TTITLE it will have all the normal variable semantics. If it is used with an ampersand than the value will be set to the time when the TTITLE command is executed. If it is used without an ampersand prefix, it will be re-evaluated for each page. For long reports with _DATE in the TTITLE or with multiple references to &_DATE, different times may be displayed for each occurrence of the variable.

User’s using _DATE in TTITLEs will almost certainly want to use an ampersand: &_DATE, so that each page of the report has exactly the same timestamp. This is especially true when the current date format contains a «seconds» component.

A DEFINE (with no arguments) or dereference using &_DATE will give the current date.

The _DATE value can be UNDEFINED, or set to a fixed string with an explicit DEFINE _DATE.

You can re-enable the default dynamic date behavior with:

DEFINE _DATE = "" (an empty string)

_DATE enables time values to be included in your SQL*Plus prompt.

_EDITOR

Specifies the default editor used by the EDIT command.

During SQL*Plus installation on Windows operating systems, it is set to Notepad. On UNIX operating systems, it is set to the value of the UNIX environment variable, EDITOR, if it exists, otherwise it is set to Ed.

You can use the DEFINE command to redefine _EDITOR, to hold the name of your preferred text editor. For example, to define the editor used by EDIT to be vi, enter the following command:

DEFINE _EDITOR = vi

_O_VERSION

Contains the current version of the installed Oracle Database in the form:

Oracle Database 10g Release 10.1.0.2.0 — Production

_O_RELEASE

Contains the full release number of the installed Oracle Database in the form:

101020000

_PRIVILEGE

Contains a value indicating the privilege level of the current connection. It contains one of the following values:

• AS SYSDBA

• AS SYSOPER

• An empty string for normal-user connections or when there is no connection.

AS SYSDBA and AS SYSOPER are database administrator level privileges.

_SQLPLUS_RELEASE

Contains the full release number of the installed SQL*Plus component in the form:

101020000

_USER

Contains the user name connected to the current connection.

You can view the value of each of these variables with the DEFINE command.

These variables can be accessed and redefined like any other substitution variable. They can be used in TTITLE, in ‘&’ substitution variables, or in your SQL*Plus command-line prompt.

You can use the DEFINE command to view the definitions of these eight predefined variables in the same way as you view other DEFINE definitions. You can also use the DEFINE command to redefine their values, or you can use the UNDEFINE command to remove their definitions and make them unavailable.

To view a specific variable definition, enter

DEFINE variable

where variable is the name of the substitution variable whose definition you want to view.

To view all predefined and user defined variable definitions, enter

DEFINE

All predefined and all user defined variable definitions are displayed.

You can use UNDEFINE to remove a substitution variable definition and make it unavailable.

Examples of Use of Predefined Variables

To change your SQL*Plus prompt to display your connection identifier, enter:

SET SQLPROMPT '_CONNECT_IDENTIFIER > '

To view the predefined value of the _SQLPLUS_RELEASE substitution variable, enter

DEFINE _SQLPLUS_RELEASE

DEL

DEL is not available in iSQL*Plus.

Syntax

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

Deletes one or more lines of the buffer.

SQL*Plus commands are not stored in the buffer. There is no history of commands previously entered in the buffer.

Terms

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

Usage

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

Examples

Assume the SQL buffer contains the following query:

SELECT LAST_NAME, DEPARTMENT_ID
FROM EMP_DETAILS_VIEW
WHERE JOB_ID = 'SA_MAN'
ORDER BY DEPARTMENT_ID;

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

LIST 3

3* WHERE JOB_ID = 'SA_MAN'

followed by

DEL

The SQL buffer now contains the following lines:

SELECT LAST_NAME, DEPARTMENT_ID
FROM EMP_DETAILS_VIEW
ORDER BY DEPARTMENT_ID

To delete the third line of the buffer, enter

DEL 3

The SQL buffer now contains the following lines:

SELECT LAST_NAME, DEPARTMENT_ID
FROM EMP_DETAILS_VIEW

DESCRIBE

Syntax

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

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

Terms

schema

Represents the schema where the object resides. If you omit schema, SQL*Plus assumes you own object.

object

Represents the table, view, type, procedure, function, package or synonym you wish to describe.

@connect_identifier

Consists of the database link name corresponding to the database where object exists. For more information on which privileges allow access to another table in a different schema, refer to the Oracle Database SQL Reference.

Usage

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

• each column’s name

• whether or not null values are allowed (NULL or NOT NULL) for each column

• datatype of columns, for example, CHAR, DATE, LONG, LONGRAW, NUMBER, RAW, ROWID, VARCHAR2 (VARCHAR), or XMLType

• precision of columns (and scale, if any, for a numeric column)

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

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

To control the width of the data displayed, use the SET LINESIZE command.

Columns output for the DESCRIBE command are typically allocated a proportion of the linesize currently specified. Decreasing or increasing the linesize with the SET LINESIZE command usually makes each column proportionally smaller or larger. This may give unexpected text wrapping in your display. For more information, see the SET command later in this chapter.

The description for functions and procedures contains the following information:

• the type of PL/SQL object (function or procedure)

• the name of the function or procedure

• the type of value returned (for functions)

• the argument names, types, whether input or output, and default values, if any

Examples

To describe the view EMP_DETAILS_VIEW, enter

DESCRIBE EMP_DETAILS_VIEW

Name                                      Null?    Type
 ----------------------------------------- -------- ----------------
 EMPLOYEE_ID                               NOT NULL NUMBER(6)
 JOB_ID                                    NOT NULL VARCHAR2(10)
 MANAGER_ID                                         NUMBER(6)
 DEPARTMENT_ID                                      NUMBER(4)
 LOCATION_ID                                        NUMBER(4)
 COUNTRY_ID                                         CHAR(2)
 FIRST_NAME                                         VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(25)
 SALARY                                             NUMBER(8,2)
 COMMISSION_PCT                                     NUMBER(2,2)
 DEPARTMENT_NAME                           NOT NULL VARCHAR2(30)
 JOB_TITLE                                 NOT NULL VARCHAR2(35)
 CITY                                      NOT NULL VARCHAR2(30)
 STATE_PROVINCE                                     VARCHAR2(25)
 COUNTRY_NAME                                       VARCHAR2(40)
 REGION_NAME                                        VARCHAR2(25)

To describe a procedure called CUSTOMER_LOOKUP, enter

DESCRIBE customer_lookup

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

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

CREATE PACKAGE apack AS
PROCEDURE aproc(P1 CHAR, P2 NUMBER);
PROCEDURE bproc(P1 CHAR, P2 NUMBER);
END apack;
/

DESCRIBE apack

PROCEDURE APROC
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P1                             CHAR                    IN
 P2                             NUMBER                  IN
PROCEDURE BPROC
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P1                             CHAR                    IN
 P2                             NUMBER                  IN

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

CREATE TYPE ADDRESS AS OBJECT
  ( STREET  VARCHAR2(20),
    CITY    VARCHAR2(20)
  );
/

DESCRIBE address

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

To create and describe the object type EMPLOYEE that contains the attributes LAST_NAME, EMPADDR, JOB_ID and SALARY, enter

CREATE TYPE EMPLOYEE AS OBJECT
(LAST_NAME VARCHAR2(30),
EMPADDR ADDRESS,
JOB_ID VARCHAR2(20),
SALARY NUMBER(7,2)
);
/

DESCRIBE employee

Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------
 LAST_NAME                                          VARCHAR2(30)
 EMPADDR                                            ADDRESS
 JOB_ID                                             VARCHAR2(20)
 SALARY                                             NUMBER(7,2)

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

CREATE TYPE addr_type IS TABLE OF ADDRESS;/

DESCRIBE addr_type

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

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

CREATE TYPE addr_varray AS VARRAY(10) OF ADDRESS;/

DESCRIBE addr_varray

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

To create and describe the table department that contains the columns DEPARTMENT_ID, PERSON and LOC, enter

CREATE TABLE department
(DEPARTMENT_ID NUMBER,
PERSON EMPLOYEE,
LOC NUMBER
);
/

DESCRIBE department

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

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

CREATE OR REPLACE TYPE rational AS OBJECT
(NUMERATOR NUMBER,
DENOMINATOR NUMBER,
MAP MEMBER FUNCTION rational_order - 
RETURN DOUBLE PRECISION,
PRAGMA RESTRICT_REFERENCES
(rational_order, RNDS, WNDS, RNPS, WNPS) );
/

CREATE OR REPLACE TYPE BODY rational AS OBJECT
MAP MEMBER FUNCTION rational_order - 
RETURN DOUBLE PRECISION IS 
BEGIN
  RETURN NUMERATOR/DENOMINATOR;
END;
END;
/
DESCRIBE rational

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

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

To create a table which contains a column of XMLType, and describe it, enter

CREATE TABLE PROPERTY (Price NUMBER, Description SYS.XMLTYPE);

DESCRIBE PROPERTY;

Name                                       Null?     Type
-----------------------------------------  --------  ----------------------
PRICE                                                NUMBER
DESCRIPTION                                          SYS.XMLTYPE

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

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

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

SHOW DESCRIBE

DESCRIBE DEPTH 2 LINENUM OFF INDENT ON

DESCRIBE employee

Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------
 FIRST_NAME                                         VARCHAR2(30)
 EMPADDR                                            ADDRESS
   STREET                                           VARCHAR2(20)
   CITY                                             VARCHAR2(20)
 JOB_ID                                             VARCHAR2(20)
 SALARY                                             NUMBER(7,2)

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

For information about using the SET DESCRIBE and SHOW DESCRIBE commands, see the SET and SHOW commands.

DISCONNECT

Syntax

DISC[ONNECT]

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

Usage

Use DISCONNECT within a script to prevent user access to the database when you want to log the user out of Oracle Database but have the user remain in SQL*Plus. In SQL*Plus command-line, use EXIT or QUIT to log out of Oracle Database and return control to your computer’s operating system. In iSQL*Plus, click the Logout button to log out of Oracle Database.

Examples

Your script might begin with a CONNECT command and end with a DISCONNECT, as shown later.

CONNECT HR/your_password
SELECT LAST_NAME, DEPARTMENT_NAME FROM EMP_DETAILS_VIEW;
DISCONNECT
SET INSTANCE FIN2
CONNECT HR2/your_password

EDIT

EDIT is not available in iSQL*Plus.

Syntax

ED[IT] [file_name[.ext]]

where file_name[.ext] represents the file you wish to edit (typically a script).

Invokes an operating system text editor on the contents of the specified file or on the contents of the buffer. The buffer has no command history list and does not record SQL*Plus commands.

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

Usage

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

If you specify a filename, SQL*Plus searches for the file in the directory set by ORACLE_PATH. If SQL*Plus cannot find the file in ORACLE_PATH, or if ORACLE_PATH is not set, it searches for the file in the current working directory. If SQL*Plus cannot find the file in either directory, it creates a file with the specified name.

The substitution variable, _EDITOR, contains the name of the text editor invoked by EDIT. You can change the text editor by changing the value of _EDITOR. For information about changing the value of a substitution variable, see DEFINE. EDIT attempts to run the default operating system editor if _EDITOR is undefined.

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

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

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

Examples

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

EDIT REPORT

EXECUTE

Syntax

EXEC[UTE] statement

where statement represents a PL/SQL statement.

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

Usage

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

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

You can suppress printing of the message «PL/SQL procedure successfully completed» with SET FEEDBACK OFF.

Examples

If the variable :n has been defined with:

VARIABLE n NUMBER

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

EXECUTE :n := 1

PL/SQL procedure successfully completed.

For information on how to create a bind variable, see the VARIABLE command.

EXIT

Syntax

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

Commits or rolls back all pending changes, logs out of Oracle Database, terminates SQL*Plus and returns control to the operating system.

In iSQL*Plus, commits or rolls back all pending changes, stops processing the current iSQL*Plus script and returns focus to the Input area. There is no way to access the return code in iSQL*Plus. In iSQL*Plus click the Logout button to exit the Oracle Database.

Commit on exit, or commit on termination of processing in iSQL*Plus, is performed regardless of the status of SET AUTOCOMMIT.

Terms

{EXIT | QUIT}

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

SUCCESS

Exits normally.

FAILURE

Exits with a return code indicating failure.

WARNING

Exits with a return code indicating warning.

COMMIT

Saves pending changes to the database before exiting.

n

Represents an integer you specify as the return code.

variable

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

:BindVariable

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

ROLLBACK

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

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

Usage

EXIT enables you to specify an operating system return code. This enables you to run SQL*Plus scripts in batch mode and to detect programmatically the occurrence of an unexpected event. The manner of detection is operating-system specific.

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

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

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

For information on exiting conditionally, see the WHENEVER SQLERROR and WHENEVER OSERROR commands.

Examples

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

EXIT SQL.SQLCODE

GET

GET is not available in iSQL*Plus. In iSQL*Plus use Load Script.

Syntax

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

Loads an operating system file into the SQL buffer.

In iSQL*Plus click the Load Script button to load a script into the Input area.

The buffer has no command history list and does not record SQL*Plus commands.

Terms

FILE

Keyword to specify that the following argument is the name of the script you want to load. This optional keyword is usually omitted.

If you want to load a script with the name file, because it is a command keyword, you need to put the name file in single quotes.

file_name[.ext]

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

LIS[T]

Lists the contents of the file after it is loaded. This is the default.

NOL[IST]

Suppresses the listing.

Examples

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

GET YEARENDRPT

HELP

Syntax

HELP | ? [topic]

where topic represents a SQL*Plus help topic, for example, COLUMN.

Accesses the SQL*Plus command-line help system. Enter HELP INDEX or ? INDEX for a list of topics. You can view SQL*Plus resources at http://otn.oracle.com/tech/sql_plus/ and the Oracle Database Library at http://otn.oracle.com/documentation/.

In iSQL*Plus, click the Help icon to access the iSQL*Plus Online Help.

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

Usage

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

HELP EX

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

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

Examples

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

HELP INDEX
or
? INDEX

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

HELP TOPICS

HOST

HOST is not available in iSQL*Plus.

Syntax

HO[ST] [command]

where command represents an operating system command.

Executes an operating system command without leaving SQL*Plus.

Enter HOST without command to display an operating system prompt. You can then enter multiple operating system commands. For information on returning to SQL*Plus, refer to the platform-specific Oracle documentation provided for your operating system.

Usage

In some operating systems, you can use a character in place of HOST such as «$» in Windows or «!» in UNIX, or you may not have access to the HOST command. See the platform-specific Oracle documentation provided for your operating system or ask your DBA for more information.

On some platforms, an _RC substitution variable may be created with a HOST return value that is operation system dependent. It is recommended that you do not use the _RC substitution variable in scripts as it is not portable.

SQL*Plus removes the SQLTERMINATOR (a semicolon by default) before the HOST command is issued. A workaround for this is to add another SQLTERMINATOR. See SET SQLT[ERMINATOR] {; | c | ON | OFF} for more information.

Examples

To execute a UNIX operating system command, ls *.sql, enter

HOST ls *.sql

To execute a Windows operating system command, dir *.sql, enter

HOST dir *.sql

INPUT

INPUT is not available in iSQL*Plus.

Syntax

I[NPUT] [text]

where text represents the text you wish to add.

Adds one or more new lines of text after the current line in the buffer. The buffer has no command history list and does not record SQL*Plus commands.

To add a single line, enter the text of the line after the command INPUT, separating the text from the command with a space. To begin the line with one or more spaces, enter two or more spaces between INPUT and the first non-blank character of text.

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

Usage

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

Examples

Assume the SQL buffer contains the following command:

SELECT LAST_NAME, DEPARTMENT_ID, SALARY, COMMISSION_PCT
FROM EMP_DETAILS_VIEW

To add an ORDER BY clause to the query, enter

LIST 2

INPUT ORDER BY LAST_NAME

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

1 SELECT LAST_NAME, DEPARTMENT_ID, SALARY, COMMISSION_PCT
2 FROM EMP_DETAILS_VIEW
3* ORDER BY LAST_NAME

To add a two-line WHERE clause, enter

LIST 2

INPUT
  3  WHERE JOB_ID = 'SA_MAN'
  4  AND COMMISSION_PCT=.25
  5

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

SELECT LAST_NAME, DEPARTMENT_ID, SALARY, COMMISSION_PCT
FROM EMP_DETAILS_VIEW
WHERE JOB_ID = 'SA_MAN'
AND COMMISSION_PCT = .25
ORDER BY LAST_NAME

LIST

Syntax

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

Lists one or more lines of the SQL buffer.

The buffer has no command history list and does not record SQL*Plus commands. In SQL*Plus command-line you can also use «;» to list all the lines in the SQL buffer.

Terms

Enter LIST with no clauses, or «;» to list all lines. The last line listed becomes the new current line (marked by an asterisk).

Examples

To list the contents of the buffer, enter

LIST

or enter

;

1 SELECT LAST_NAME, DEPARTMENT_ID, JOB_ID
2 FROM EMP_DETAILS_VIEW
3 WHERE JOB_ID = 'SH_CLERK'
4* ORDER BY DEPARTMENT_ID

The asterisk indicates that line 4 is the current line.

To list the second line only, enter

LIST 2

The second line is displayed:

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

LIST * LAST

You will then see this:

2 FROM EMP_DETAILS_VIEW
3 WHERE JOB_ID = 'SH_CLERK'
4* ORDER BY DEPARTMENT_ID

PASSWORD

Syntax

PASSW[ORD] [username]

where username specifies the user. If omitted, username defaults to the current user.

Enables you to change a password without echoing it on an input device. In iSQL*Plus, use the Password screen to change your password.

Usage

To change the password of another user, you must have been granted the appropriate privilege. See CONNECT for more information about changing your password.

Examples

If you want to change your current password, enter

PASSWORD
Changing password for your_password
Old password: your_password
New password: new_password
Retype new password: new_password
Password changed

If you are logged on as a DBA, and want to change the password for user johnw (currently identified by johnwpass) to johnwnewpass

PASSWORD johnw
Changing password for johnw
New password: johnwnewpass
Retype new password: johnwnewpass
Password changed

Passwords are not echoed to the screen, they are shown here for your convenience.

PAUSE

Syntax

PAU[SE] [text]

where text represents the text you wish to display.

Displays the specified text then waits for the user to press RETURN.

In iSQL*Plus, displays the Next Page button which the user must click to continue.

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

Usage

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

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

See SET PAU[SE] {ON | OFF | text} for information on pausing between pages of a report.

Examples

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

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

PRINT

Syntax

PRI[NT] [variable …]

where variable … represents names of bind variables whose values you want to display.

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

Enter PRINT with no variables to print all bind variables.

Usage

Bind variables are created using the VARIABLE command. See VARIABLE for more information and examples.

You can control the formatting of the PRINT output just as you would query output. For more information, see the formatting techniques described in » Formatting SQL*Plus Reports».

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

Examples

The following example illustrates a PRINT command:

VARIABLE n NUMBER
BEGIN
:n := 1;
END;
/

PL/SQL procedure successfully completed.

PRINT n

PROMPT

Syntax

PRO[MPT] [text]

where text represents the text of the message you want to display.

Sends the specified message or a blank line to the user’s screen. If you omit text, PROMPT displays a blank line on the user’s screen.

Usage

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

Examples

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

PROMPTPROMPT Please enter a valid departmentPROMPT For example:  10SELECT DEPARTMENT_NAME FROM EMP_DETAILS_VIEWWHERE DEPARTMENT_ID = &NEWDEPT

Assume you run the file using START or @:

@ASKFORDEPT.SQL VAL1
@HTTP://machine_name.domain:port/ASKFORDEPT.SQL VAL1

Please enter a valid department
For example: 10
Department ID?>

You can enter a department number at the prompt Department ID?>. By default, SQL*Plus lists the line containing &NEWDEPT before and after substitution, and then displays the department name corresponding to the number entered at the Department ID?> prompt. You can use SET VERIFY OFF to prevent this behavior.

RECOVER

Syntax

RECOVER {general | managed | BEGIN BACKUP | END BACKUP}

where the general clause has the following syntax:

[AUTOMATIC] [FROM location]
{ {full_database_recovery | partial_database_recovery | LOGFILE filename}
[ {TEST | ALLOW integer CORRUPTION | parallel_clause } [TEST | ALLOW integer CORRUPTION | parallel_clause ]…]
| CONTINUE [DEFAULT] | CANCEL}

where the full_database_recovery clause has the following syntax:

[STANDBY] DATABASE
[ {UNTIL {CANCEL | TIME date | CHANGE integer} | USING BACKUP CONTROLFILE}
[UNTIL {CANCEL | TIME date | CHANGE integer} | USING BACKUP CONTROLFILE]…]

where the partial_database_recovery clause has the following syntax:

{TABLESPACE tablespace [, tablespace]…
| DATAFILE {filename | filenumber} [, filename | filenumber]…
| STANDBY {TABLESPACE tablespace [, tablespace]…
| DATAFILE {filename | filenumber} [, filename | filenumber]…}
UNTIL [CONSISTENT WITH] CONTROLFILE }

where the parallel clause has the following syntax:

{ NOPARALLEL | PARALLEL [ integer ] }

where the managed clause has the following syntax:

MANAGED STANDBY DATABASE recover_clause | cancel_clause | finish_clause

where the recover_clause has the following syntax:

{ { DISCONNECT [ FROM SESSION ] | { TIMEOUT integer | NOTIMEOUT } }
| { NODELAY | DEFAULT DELAY | DELAY integer } | NEXT integer
| { EXPIRE integer | NO EXPIRE } | parallel_clause
| USING CURRENT LOGFILE | UNTIL CHANGE integer
| THROUGH { [ THREAD integer ] SEQUENCE integer
| ALL ARCHIVELOG | { ALL | LAST | NEXT } SWITCHOVER} }
[ DISCONNECT [ FROM SESSION ] | { TIMEOUT integer | NOTIMEOUT }
| { NODELAY | DEFAULT DELAY | DELAY integer } | NEXT integer
| { EXPIRE integer | NO EXPIRE } | parallel_clause
| USING CURRENT LOGFILE | UNTIL CHANGE integer
| THROUGH { [ THREAD integer ] SEQUENCE integer
| ALL ARCHIVELOG | { ALL | LAST | NEXT } SWITCHOVER} ] …

where the cancel_clause has the following syntax:

CANCEL [IMMEDIATE] [WAIT | NOWAIT]

where the finish_clause has the following syntax:

[ DISCONNECT [ FROM SESSION ] ] [ parallel_clause ]
FINISH [ SKIP [ STANDBY LOGFILE ] ] [ WAIT | NOWAIT ]

where the parallel_clause has the following syntax:

{ NOPARALLEL | PARALLEL [ integer ] }

Performs media recovery on one or more tablespaces, one or more datafiles, or the entire database. For more information on the RECOVER command, see the Oracle Database Administrator’s Guide, the ALTER DATABASE RECOVER command in the Oracle Database SQL Reference, and the Oracle Database Backup and Recovery Basics guide.

Because of possible network timeouts, it is recommended that you use SQL*Plus command-line, not iSQL*Plus, for long running DBA operations such as RECOVER.

Terms

AUTOMATIC

Automatically generates the name of the next archived redo log file needed to continue the recovery operation. Oracle Database uses the LOG_ARCHIVE_DEST (or LOG_ARCHIVE_DEST_ 1) and LOG_ARCHIVE_FORMAT parameters (or their defaults) to generate the target redo log filename. If the file is found, the redo contained in that file is applied. If the file is not found, SQL*Plus prompts you for a filename, displaying a generated filename as a suggestion.

If you do not specify either AUTOMATIC or LOGFILE, SQL*Plus prompts you for a filename, suggesting the generated filename. You can either accept the generated filename or replace it with a fully qualified filename. You can save time by using the LOGFILE clause to specify the filename if you know the archived filename differs from the filename Oracle Database would generate.

FROM location

Specifies the location from which the archived redo log file group is read. The value of location must be a fully specified file location. If you omit this parameter, SQL*Plus assumes the archived redo log file group is in the location specified by the initialization parameter LOG_ARCHIVE_DEST or LOG_ARCHIVE_DEST_1. Do not specify FROM if you have set a file with SET LOGSOURCE.

full_database_recovery

Enables you to specify the recovery of a full database.

partial_database_recovery

Enables you to specify the recovery of individual tablespaces and datafiles.

LOGFILE

Continues media recovery by applying the specified redo log file. In interactive recovery mode (AUTORECOVERY OFF), if a bad log name is entered, errors for the bad log name are displayed and you are prompted to enter a new log name.

TEST

Specifies a trial recovery to detect possible problems. Redo is applied normally, but no changes are written to disk, and changes are rolled back at the end of the trial recovery. You can only use the TEST clause for a trial recovery if you have restored a backup. In the event of logfile corruption, specifies the number of corrupt blocks that can be tolerated while allowing recovery to proceed. During normal recovery, integer cannot exceed 1.

ALLOW integer CORRUPTION

In the event of logfile corruption, specifies the number of corrupt blocks that can be tolerated while allowing recovery to proceed. During normal recovery, integer cannot exceed 1.

parallel _clause

Enables you to specify the degree of parallel processing to use during the recovery operation.

CONTINUE

Continues multi-instance recovery after it has been interrupted to disable a thread.

CONTINUE DEFAULT

Continues recovery using the redo log file generated automatically by Oracle Database if no other logfile is specified. This is equivalent to specifying AUTOMATIC, except that Oracle Database does not prompt for a filename.

CANCEL

Terminates cancel-based recovery.

STANDBY DATABASE

Recovers the standby database using the control file and archived redo log files copied from the primary database. The standby database must be mounted but not open.

DATABASE

Recovers the entire database.

UNTIL CANCEL

Specifies an incomplete, cancel-based recovery. Recovery proceeds by prompting you with suggested filenames of archived redo log files, and recovery completes when you specify CANCEL instead of a filename.

UNTIL TIME

Specifies an incomplete, time-based recovery. Use single quotes, and the following format:

'YYYY-MM-DD:HH24:MI:SS'

UNTIL CHANGE

Specifies an incomplete, change-based recovery. integer is the number of the System Change Number (SCN) following the last change you wish to recover. For example, if you want to restore your database up to the transaction with an SCN of 9, you would specify UNTIL CHANGE 10.

USING BACKUP CONTROLFILE

Specifies that a backup of the control file be used instead of the current control file.

TABLESPACE

Recovers a particular tablespace. tablespace is the name of a tablespace in the current database. You may recover up to 16 tablespaces in one statement.

DATAFILE

Recovers a particular datafile. You can specify any number of datafiles.

STANDBY TABLESPACE

Reconstructs a lost or damaged tablespace in the standby database using archived redo log files copied from the primary database and a control file.

STANDBY DATAFILE

Reconstructs a lost or damaged datafile in the standby database using archived redo log files copied from the primary database and a control file.

UNTIL CONSISTENT WITH CONTROLFILE

Specifies that the recovery of an old standby datafile or tablespace uses the current standby database control file.

PARALLEL [integer]

SQL*Plus selects a degree of parallelism equal to the number of CPUs available on all participating instances times the value of the PARALLEL_THREADS_PER_CPU initialization parameter.

The PARALLEL keyword overrides the RECOVERY_PARALLELISM initialization parameter. For more information about the PARALLEL keyword see the Oracle Real Application Clusters Quick Start guide.

Use integer to specify the degree of parallelism, which is the number of parallel threads used in the parallel operation. Each parallel thread may use one or two parallel execution processes.

NOPARALLEL

Specifies serial recovery processing. This is the default.

MANAGED STANDBY DATABASE

Specifies sustained standby recovery mode. This mode assumes that the standby database is an active component of an overall standby database architecture. A primary database actively archives its redo log files to the standby site. As these archived redo logs arrive at the standby site, they become available for use by a managed standby recovery operation. Sustained standby recovery is restricted to media recovery.

For more information on the parameters of this clause, see the Oracle Database Backup and Recovery Advanced User’s Guide.

DISCONNECT

Indicates that the managed redo process (MRP) should apply archived redo files as a detached background process. Doing so leaves the current session available.

TIMEOUT

Specifies in minutes the wait period of the sustained recovery operation. The recovery process waits for integer minutes for a requested archived log redo to be available for writing to the standby database. If the redo log file does not become available within that time, the recovery process terminates with an error message. You can then issue the statement again to return to sustained standby recovery mode.

If you do not specify this clause, or if you specify NOTIMEOUT, the database remains in sustained standby recovery mode until you reissue the statement with the RECOVER CANCEL clause or until instance shutdown or failure.

NODELAY

Applies a delayed archivelog immediately to the standby database overriding any DELAY setting in the LOG_ARCHIVE_DEST_n parameter on the primary database. If you omit this clause, application of the archivelog is delayed according to the parameter setting. If DELAY was not specified in the parameter, the archivelog is applied immediately.

DEFAULT DELAY

Waits the default number of minutes specified in the LOG_ARCHIVE_DEST_n initialization parameter before applying the archived redo logs.

DELAY integer

Waits integer minutes before applying the archived redo logs.

NEXT integer

Applies the specified number of archived redo logs as soon as possible after they have been archived. It temporarily overrides any DELAY setting in the LOG_ARCHIVE_DEST_n parameter on the primary database, and any delay values set in an earlier SQL*Plus RECOVER command or an ALTER DATABASE RECOVER command.

EXPIRE integer

Specifies the number of minutes from the current time after which managed recovery terminates automatically.

NO EXPIRE

Disables a previously specified EXPIRE integer option.

USING CURRENT LOGFILE

Recovers redo from standby online logs as they are being filled, without requiring them to be archived in the standby database first.

UNTIL CHANGE integer

Processes managed recovery up to but not including the specified system change number (SCN).

THROUGH THREAD integer SEQUENCE integer

Terminates managed recovery based on archivelog thread number and sequence number. Managed recovery terminates when the corresponding archivelog has been applied. If omitted, THREAD defaults to 1.

THROUGH ALL ARCHIVELOG

Continues managed standby until all archivelogs have been recovered. You can use this statement to override a THROUGH THREAD integer SEQUENCE integer clause issued in an earlier statement. If the THROUGH clause is omitted, this is the default.

THROUGH ALL SWITCHOVER

Keeps managed standby recovery running through all switchover operations.

THROUGH LAST SWITCHOVER

Terminates managed standby recovery after the final end-of-redo archival indicator.

THROUGH NEXT SWITCHOVER

Terminates managed standby recovery after recovering the next end-of-redo archival indicator.

CANCEL (managed clause)

Terminates managed standby recovery after applying the current archived redo file. Session control returns when the recovery process terminates.

CANCEL IMMEDIATE

Terminates managed standby recovery after applying the current archived redo file, or after the next redo log file read, whichever comes first. Session control returns when the recovery process terminates.

CANCEL IMMEDIATE WAIT

Terminates managed standby recovery after applying the current archived redo file or after the next redo log file read, whichever comes first. Session control returns when the managed standby recovery terminates.

CANCEL IMMEDIATE cannot be issued from the same session that issued the RECOVER MANAGED STANDBY DATABASE statement.

CANCEL IMMEDIATE NOWAIT

Terminates managed standby recovery after applying the current archived redo file, or after the next redo log file read, whichever comes first. Session control returns immediately.

CANCEL NOWAIT

Terminates managed standby recovery after the next redo log file read and returns session control immediately.

FINISH

Recovers the current standby online logfiles of the standby database. This clause may be useful if the primary database fails. It overrides any delays specified for archivelogs, so that logs are applied immediately.

FINISH cannot be issued if you have also specified TIMEOUT, DELAY, EXPIRE or NEXT clauses.

Usage

You must have the OSDBA role enabled. You cannot use the RECOVER command when connected through the multi-threaded server.

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

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

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

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

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

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

If you have enabled autorecovery (that is, SET AUTORECOVERY ON), recovery proceeds without prompting you with filenames. Status messages are displayed when each log file is applied. When normal media recovery is done, a completion status is returned.

Examples

To recover the entire database, enter

RECOVER DATABASE 

To recover the database until a specified time, enter

RECOVER DATABASE UNTIL TIME 01-JAN-2001:04:32:00 

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

RECOVER TABLESPACE ts_one, ts_two 

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

RECOVER DATAFILE 'data1.db' 

REMARK

Syntax

REM[ARK]

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

Usage

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

A «–» at the end of a REMARK line is treated as a line continuation character.

For details on entering comments in scripts using the SQL comment delimiters, /* … */, or the ANSI/ISO comment delimiter, — -, see «Placing Comments in Scripts».

Examples

The following script contains some typical comments:

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

REPFOOTER

Syntax

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

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

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

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

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

Terms

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

Usage

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

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

Examples

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

REPFOOTER PAGE CENTER 'END EMPLOYEE LISTING REPORT'
TTITLE RIGHT 'Page: ' FORMAT 999 SQL.PNO
SELECT LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE SALARY > 12000;

LAST_NAME                     SALARY
------------------------- ----------
King                           24000
Kochhar                        17000
De Haan                        17000
Russell                        14000
Partners                       13500
Hartstein                      13000
                          ----------
sum                            98500

                                                                      Page:  2
                           END EMPLOYEE LISTING REPORT

6 rows selected.

To suppress the report footer without changing its definition, enter

REPFOOTER OFF

REPHEADER

Syntax

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

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

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

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

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

Terms

These terms and clauses also apply to the REPFOOTER command.

PAGE

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

text

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

variable

A substitution variable or any of the following system-maintained values. SQL.LNO is the current line number, SQL.PNO is the current page number, SQL.CODE is the current error code, SQL.RELEASE is the current Oracle Database release number, and SQL.USER is the current username.

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

OFF

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

COL n

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

S[KIP] [n]

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

TAB n

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

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

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

BOLD

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

FORMAT text

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

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

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

Usage

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

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

Examples

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

REPHEADER PAGE CENTER 'EMPLOYEE LISTING REPORT'
TTITLE RIGHT 'Page: ' FORMAT 999 SQL.PNO
SELECT LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE SALARY > 12000;

Page:   1
                             EMPLOYEE LISTING REPORT
                                                                 Page:   2
LAST_NAME                     SALARY
------------------------- ----------
King                           24000
Kochhar                        17000
De Haan                        17000
Russell                        14000
Partners                       13500
Hartstein                      13000
                          ----------
sum                            98500

6 rows selected.

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

REPHEADER OFF

RUN

Syntax

R[UN]

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

The buffer has no command history list and does not record SQL*Plus commands.

Usage

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

The slash command (/) functions similarly to RUN, but does not list the command in the SQL buffer on your screen. The SQL buffer always contains the last SQL statement or PL/SQL block entered.

Examples

Assume the SQL buffer contains the following script:

SELECT DEPARTMENT_ID
FROM EMP_DETAILS_VIEW
WHERE SALARY>12000

To RUN the script, enter

RUN

1  SELECT DEPARTMENT_ID
  2  FROM EMP_DETAILS_VIEW
  3 WHERE SALARY>12000

DEPARTMENT_ID
-------------
           90
           90
           90
           80
           80
           20

6 rows selected.

SAVE

SAVE is not available in iSQL*Plus. In iSQL*Plus use Save Script.

Syntax

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

Saves the contents of the SQL buffer in an operating system script. In iSQL*Plus, click the Save Script button to save the Input area contents to a script.

The buffer has no command history list and does not record SQL*Plus commands.

Terms

FILE

Keyword to specify that the following argument is the name you want to give to the saved script. This optional keyword is usually omitted.

If you want to save the script with the name file, because it is a command keyword, you need to put the name file in single quotes.

file_name[.ext]

Specifies the script in which you wish to save the buffer’s contents.

CREATE

Creates a new file with the name specified. This is the default behavior.

REP[LACE]

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

APP[END]

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

Usage

If you do not specify an extension, SQL*Plus assumes the default command-file extension (normally SQL). See SET SUF[FIX] {SQL | text} for information on changing this default extension.

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

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

Examples

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

SAVE DEPTSALRPT

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

SAVE DEPTSALRPT.OLD

SET

Sets a system variable to alter the SQL*Plus environment settings for your current session, for example, to:

• customize HTML formatting

• enable or disable the printing of column headings

• set the number of lines per page

• set the display width for data

You also use the Preferences screens in iSQL*Plus to set system variables.

Syntax

SET system_variable value

where system_variable and value represent one of the clauses shown in the «SET System Variable Summary» table following.

Usage

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

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

SET System Variable Summary

*SET command not available in iSQL*Plus.

SET APPI[NFO]{ON | OFF | text}

Sets automatic registering of scripts through the DBMS_APPLICATION_INFO package.

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

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

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

For more information on the DBMS_APPLICATION_INFO package, see the Oracle Database Performance Tuning Guide.

Example

To display the value of APPINFO, as it is SET OFF by default, enter

SET APPINFO ON
SHOW APPINFO

APPINFO is ON and set to "SQL*Plus"

To change the default text, enter

SET APPINFO 'This is SQL*Plus'

To make sure that registration has taken place, enter

VARIABLE MOD VARCHAR2(50)
VARIABLE ACT VARCHAR2(40)
EXECUTE DBMS_APPLICATION_INFO.READ_MODULE(:MOD, :ACT);

PL/SQL procedure successfully completed.

PRINT MOD

MOD
---------------------------------------------------
This is SQL*Plus

To change APPINFO back to its default setting, enter

SET APPINFO OFF

SET ARRAY[SIZE] {15 | n}

Sets the number of rows that SQL*Plus will fetch from the database at one time.

Valid values are 1 to 5000. A large value increases the efficiency of queries and subqueries that fetch many rows, but requires more memory. Values over approximately 100 provide little added performance. ARRAYSIZE has no effect on the results of SQL*Plus operations other than increasing efficiency.

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

Controls when Oracle Database commits pending changes to the database after SQL or PL/SQL commands.

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

SET AUTOCOMMIT does not alter the commit behavior when SQL*Plus exits. Any uncommitted data is committed by default.

SET AUTOP[RINT] {ON | OFF}

Sets the automatic printing of bind variables.

ON or OFF controls whether SQL*Plus automatically displays bind variables (referenced in a successful PL/SQL block or used in an EXECUTE command).

See PRINT for more information about displaying bind variables.

SET AUTORECOVERY [ON | OFF]

ON sets the RECOVER command to automatically apply the default filenames of archived redo log files needed during recovery.

No interaction is needed, provided the necessary files are in the expected locations with the expected names. The filenames used are derived from the values of the initialization parameters LOG_ARCHIVE_DEST and LOG_ARCHIVE_FORMAT.

OFF, the default option, requires that you enter the filenames manually or accept the suggested default filename given. See RECOVER for more information about database recovery.

Example

To set the recovery mode to AUTOMATIC, enter

SET AUTORECOVERY ON
RECOVER DATABASE

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

Displays a report on the execution of successful SQL DML statements (SELECT, INSERT, UPDATE or DELETE).

The report can include execution statistics and the query execution path.

OFF does not display a trace report. ON displays a trace report. TRACEONLY displays a trace report, but does not print query data, if any. EXPLAIN shows the query execution path by performing an EXPLAIN PLAN. STATISTICS displays SQL statement statistics. Information about EXPLAIN PLAN is documented in the Oracle Database SQL Reference.

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

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

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

Information about Execution Plans and the statistics is documented in the Oracle Database Performance Tuning Guide.

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

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

AUTOTRACE is not available when FIPS flagging is enabled.

See Tracing Statements for more information on AUTOTRACE.

SET BLO[CKTERMINATOR] {. | c | ON | OFF}

Sets the character used to end PL/SQL blocks to c.

It cannot be an alphanumeric character or a whitespace. To execute the block, you must issue a RUN or / (slash) command.

OFF means that SQL*Plus recognizes no PL/SQL block terminator. ON changes the value of c back to the default period (.), not the most recently used character.

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

Sets the non-alphanumeric character used to separate multiple SQL*Plus commands entered on one line to c.

ON or OFF controls whether you can enter multiple commands on a line. ON automatically sets the command separator character to a semicolon (;).

Example

To specify a title with TTITLE and format a column with COLUMN, both on the same line, enter

SET CMDSEP +
TTITLE LEFT 'SALARIES' + COLUMN SALARY FORMAT $99,999
SELECT LAST_NAME, SALARY FROM EMP_DETAILS_VIEW
WHERE JOB_ID = 'SH_CLERK';

SALARIES
LAST_NAME                   SALARY
------------------------- --------
Taylor                      $3,200
Fleaur                      $3,100
Sullivan                    $2,500
Geoni                       $2,800
Sarchand                    $4,200
Bull                        $4,100
Dellinger                   $3,400
Cabrio                      $3,000
Chung                       $3,800
Dilly                       $3,600
Gates                       $2,900
Perkins                     $2,500
Bell                        $4,000
Everett                     $3,900
McCain                      $3,200
Jones                       $2,800

SALARIES
LAST_NAME                   SALARY
------------------------- --------
Walsh                       $3,100
Feeney                      $3,000
OConnell                    $2,600
Grant                       $2,600

20 rows selected.

SET COLSEP { | text}

Sets the column separator character printed between columns in output.

If the COLSEP variable contains blanks or punctuation characters, you must enclose it with single quotes. The default value for text is a single space.

In multi-line rows, the column separator does not print between columns that begin on different lines. The column separator does not appear on blank lines produced by BREAK … SKIP n and does not overwrite the record separator. See SET RECSEP {WR[APPED] | EA[CH] | OFF} for more information.

The Column Separator (SET COLSEP) is only used in iSQL*Plus when Preformatted Output is ON (SET MARKUP HTML PREFORMAT).

Example

To set the column separator to «|» enter

SET MARKUP HTML PREFORMAT ON
SET COLSEP '|'
SELECT LAST_NAME, JOB_ID, DEPARTMENT_ID
FROM EMP_DETAILS_VIEW
WHERE DEPARTMENT_ID = 20;

LAST_NAME                |JOB_ID    |DEPARTMENT_ID
-------------------------|----------|-------------
Hartstein                |MK_MAN    |           20
Fay                      |MK_REP    |           20

SET COM[PATIBILITY]{V7 | V8 | NATIVE}

Specifies the version of Oracle Database SQL syntax to use.

Set COMPATIBILITY to V7 for Oracle7, or to V8 for Oracle8 or later. COMPATIBILITY always defaults to NATIVE. Set COMPATIBILITY for the version of Oracle Database SQL syntax you want to use on the connected database, otherwise, you may be unable to run any SQL commands.

Example

To run a script, SALARY.SQL, created with Oracle7 SQL syntax, enter

SET COMPATIBILITY V7
START SALARY

After running the file, reset compatibility to NATIVE to run scripts created for Oracle Database 10g:

SET COMPATIBILITY NATIVE

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

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

Sets the character used to terminate a substitution variable reference when SQL*Plus would otherwise interpret the next character as a part of the variable name.

SQL*Plus resets the value of CONCAT to a period when you switch CONCAT on.

SET COPYC[OMMIT] {0 | n}

Controls the number of rows after which the COPY command commits changes to the database.

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

SET COPYTYPECHECK {ON | OFF}

Sets the suppression of the comparison of datatypes while inserting or appending to tables with the COPY command.

This is to facilitate copying to DB2, which requires that a CHAR be copied to a DB2 DATE.

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

Sets the character used to prefix substitution variables to c.

ON or OFF controls whether SQL*Plus will scan commands for substitution variables and replace them with their values. ON changes the value of c back to the default ‘&’, not the most recently used character. The setting of DEFINE to OFF overrides the setting of the SCAN variable.

See SET SCAN {ON|OFF} (obsolete) for more information on the SCAN variable.

SET DESCRIBE [DEPTH {1 | n | ALL}] [LINENUM {ON | OFF}] [INDENT {ON | OFF}]

Sets the depth of the level to which you can recursively describe an object.

The valid range of the DEPTH clause is from 1 to 50. If you SET DESCRIBE DEPTH ALL, then the depth will be set to 50, which is the maximum level allowed. You can also display the line number and indentation of the attribute or column name when an object contains multiple object types. Use the SET LINESIZE command to control the width of the data displayed.

See DESCRIBE for more information about describing objects.

Example

To create an object type ADDRESS, enter

CREATE TYPE ADDRESS AS OBJECT
  ( STREET  VARCHAR2(20),
    CITY    VARCHAR2(20)
  );
/

To create the table EMPLOYEE that contains a nested object, EMPADDR, of type ADDRESS, enter

CREATE TABLE EMPLOYEE
  (LAST_NAME VARCHAR2(30),
   EMPADDR ADDRESS,
   JOB_ID VARCHAR2(20),
   SALARY NUMBER(7,2)
  );
/

To describe the table EMPLOYEE to a depth of two levels, and to indent the output and display line numbers, enter:

SET DESCRIBE DEPTH 2 LINENUM ON INDENT ON
DESCRIBE employee

Name                            Null?    Type
       ------------------------------- -------- --------------------------
1      LAST_NAME                                VARCHAR2(30)
2      EMPADDR                                  ADDRESS
3    2   STREET                                 VARCHAR2(20)
4    2   CITY                                   VARCHAR2(20)
5      JOB_ID                                   VARCHAR2(20)
6      SALARY                                   NUMBER(7,2)

SET ECHO {ON | OFF}

Controls whether or not to echo commands in a script that is executed with @, @@ or START. ON displays the commands on screen. OFF suppresses the display. ECHO does not affect the display of commands you enter interactively or redirect to SQL*Plus from the operating system.

SET EDITF[ILE] file_name[.ext]

SET EDITFILE is not supported in iSQL*Plus

Sets the default filename for the EDIT command. See EDIT for more information about the EDIT command. The default filename for the EDIT command is afiedt.buf which is the SQL buffer. The buffer has no command history list and does not record SQL*Plus commands.

You can include a path and/or file extension. See SET SUF[FIX] {SQL | text} for information on changing the default extension. The default filename and maximum filename length are operating system specific.

SET EMB[EDDED] {ON | OFF}

Controls where on a page each report begins.

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

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

Defines the character used as the escape character.

OFF undefines the escape character. ON enables the escape character. ON changes the value of c back to the default «».

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

Example

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

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

displays this prompt:

To set the escape character back to the default value of (backslash), enter

SET ESCAPE ON

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

Displays the number of records returned by a script when a script selects at least n records.

ON or OFF turns this display on or off. Turning feedback ON sets n to 1. Setting feedback to zero is equivalent to turning it OFF.

SET FEEDBACK OFF also turns off the statement confirmation messages such as ‘Table created’ and ‘PL/SQL procedure successfully completed’ that are displayed after successful SQL or PL/SQL statements.

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

Checks to make sure that SQL statements conform to the ANSI/ISO SQL92 standard.

If any non-standard constructs are found, the Oracle Database Server flags them as errors and displays the violating syntax. This is the equivalent of the SQL language ALTER SESSION SET FLAGGER command.

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

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

SET FLU[SH] {ON | OFF}

SET FLUSH is not supported in iSQL*Plus

Controls when output is sent to the user’s display device. OFF enables the operating system to buffer output. ON disables buffering. FLUSH only affects display output, it does not affect spooled output.

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

SET HEA[DING] {ON | OFF}

Controls printing of column headings in reports.

ON prints column headings in reports; OFF suppresses column headings.

The SET HEADING OFF command does not affect the column width displayed, it only suppresses the printing of the column header itself.

Example

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

SET HEADING OFF

If you then run a SQL SELECT command

SELECT LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE JOB_ID = 'AC_MGR';

the following output results:

To turn the display of column headings back on, enter

SET HEADING ON

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

Defines the character used as a line break in column headings.

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

The Heading Separator character (SET HEADSEP) is only supported in iSQL*Plus when the Preformatted Output preference is ON (SET MARKUP HTML PREFORMAT).

SET INSTANCE [instance_path | LOCAL]

Changes the default instance for your session to the specified instance path.

Using the SET INSTANCE command does not connect to a database. The default instance is used for commands when no instance is specified. Any commands preceding the first use of SET INSTANCE communicate with the default instance.

To reset the instance to the default value for your operating system, you can either enter SET INSTANCE with no instance_path or SET INSTANCE LOCAL.

Note, you can only change the instance when you are not currently connected to any instance. That is, you must first make sure that you have disconnected from the current instance, then set or change the instance, and reconnect to an instance in order for the new setting to be enabled.

This command may only be issued when Oracle Net is running. You can use any valid Oracle Net connect identifier as the specified instance path. See your operating system-specific Oracle Database documentation for a complete description of how your operating system specifies Oracle Net connect identifiers. The maximum length of the instance path is 64 characters.

Example

To set the default instance to «PROD1» enter

DISCONNECT
SET INSTANCE PROD1

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

SET INSTANCE local

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

SET LIN[ESIZE] {80 | n}
SET LIN[ESIZE] {150 | n} in iSQL*Plus

Sets the total number of characters that SQL*Plus displays on one line before beginning a new line.

It also controls the position of centered and right-aligned text in TTITLE, BTITLE, REPHEADER and REPFOOTER. Changing the linesize setting can affect text wrapping in output from the DESCRIBE command. DESCRIBE output columns are typically allocated a proportion of the linesize. Decreasing or increasing the linesize may give unexpected text wrapping in your display. You can define LINESIZE as a value from 1 to a maximum that is system dependent.

SET LOBOF[FSET] {1 | n}

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

Example

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

SET LOBOFFSET 22

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

SET LOGSOURCE [pathname]

Specifies the location from which archive logs are retrieved during recovery.

The default value is set by the LOG_ARCHIVE_DEST initialization parameter in the Oracle Database initialization file, init.ora. Using the SET LOGSOURCE command without a pathname restores the default location.

Example

To set the default location of log files for recovery to the directory «/usr/oracle10/dbs/arch» enter

SET LOGSOURCE "/usr/oracle10/dbs/arch"
RECOVER DATABASE

SET LONG {80 | n}

Sets maximum width (in bytes) for displaying CLOB, LONG, NCLOB and XMLType values; and for copying LONG values.

The maximum value of n is 2,000,000,000 bytes.

Example

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

SET LONG 500

The LONG data will wrap on your screen; SQL*Plus will not truncate until the 501st byte. The default for LONG is 80 bytes.

SET LONGC[HUNKSIZE] {80 | n}

Sets the size (in bytes) of the increments SQL*Plus uses to retrieve a CLOB, LONG, NCLOB or XMLType value.

LONGCHUNKSIZE is not used for object relational queries such as CLOB, or NCLOB.

Example

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

SET LONGCHUNKSIZE 100

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

SET MARK[UP] HTML [ON | OFF] [HEAD text] [BODY text] [TABLE text] [ENTMAP {ON | OFF}] [SPOOL {ON | OFF}] [PRE[FORMAT] {ON | OFF}]

Outputs HTML marked up text, which is the output used by iSQL*Plus.

Beware of using options which generate invalid HTML output in iSQL*Plus as it may corrupt the browser screen. The HEAD and BODY options may be useful for dynamic reports and for reports saved to local files.

To be effective, SET MARKUP commands that change values in dynamic report output must occur before statements that produce query output. The first statement that produces query output triggers the output of information affected by SET MARKUP such as HEAD and TABLE settings. Subsequent SET MARKUP commands have no effect on the information already sent to the report.

SET MARKUP only specifies that SQL*Plus output will be HTML encoded. You must use SET MARKUP HTML ON SPOOL ON and the SQL*Plus SPOOL command to create and name a spool file, and to begin writing HMTL output to it. SET MARKUP has the same options and behavior as SQLPLUS -MARKUP.

See «MARKUP Options» for detailed information. For examples of usage, see SET MARK[UP] HTML [ON | OFF] [HEAD text] [BODY text] [TABLE text] [ENTMAP {ON | OFF}] [SPOOL {ON | OFF}] [PRE[FORMAT] {ON | OFF}], and «Generating HTML Reports from SQL*Plus».

Use the SHOW MARKUP command to view the status of MARKUP options.

Example

The following is a script which uses the SET MARKUP HTML command to enable HTML marked up text to be spooled to a specified file:

Use your favorite text editor to enter the commands necessary to set up the HTML options and the query you want for your report.

SET MARKUP HTML ON SPOOL ON HEAD "<TITLE>SQL*Plus Report</title> -
STYLE TYPE='TEXT/CSS'><!--BODY {background: ffffc6} --></STYLE>"
SET ECHO OFF
SPOOL employee.htm
SELECT FIRST_NAME, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE SALARY>12000;
SPOOL OFF
SET MARKUP HTML OFF
SET ECHO ON

As this script contains SQL*Plus commands, do not attempt to run it with / (slash) from the buffer because it will fail. Save the script in your text editor and use START to execute it:

START employee.sql

As well as writing the HTML spool file, employee.htm, the output is also displayed on screen because SET TERMOUT defaults to ON. You can view the spool file, employee.htm, in your web browser. It should appear something like the following:

Description of the illustration markup.gif

SET NEWP[AGE] {1 | n | NONE}

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

SET NULL text

Sets the text displayed whenever a null value occurs in the result of a SQL SELECT command.

Use the NULL clause of the COLUMN command to override the setting of the NULL variable for a given column. The default output for a null is blank («»).

SET NUMF[ORMAT] format

Sets the default format for displaying numbers. Enter a number format for format. Enter

SET NUMFORMAT "" 

to use the default field width and formatting model specified by SET NUMWIDTH.

SET NUM[WIDTH] {10 | n}

Sets the default width for displaying numbers. See the FORMAT clause of the COLUMN command and SET NUMF[ORMAT] format and SET NUM[WIDTH] {10 | n}. for number format descriptions.

COLUMN FORMAT settings take precedence over SET NUMFORMAT settings, which take precedence over SET NUMWIDTH settings.

SET PAGES[IZE] {14 | n}

Sets the number of rows on each page of output in iSQL*Plus, and the number of lines on each page of output in command-line and Windows GUI. You can set PAGESIZE to zero to suppress all headings, page breaks, titles, the initial blank line, and other formatting information.

In iSQL*Plus, sets the number of rows displayed on each page. Error and informational messages are not counted in the page size, so pages may not always be exactly the same length. The default pagesize for iSQL*Plus is 24.

SET PAU[SE] {ON | OFF | text}

Enables you to control scrolling of your terminal when running reports. You need to first, SET PAUSE text, and then SET PAUSE ON if you want text to appear each time SQL*Plus pauses.

In command-line and Windows GUI, SET PAUSE ON pauses output at the beginning of each PAGESIZE number of lines of report output. Press Return to view more output. SET PAUSE text specifies the text to be displayed each time SQL*Plus pauses. Multiple words in text must be enclosed in single quotes.

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

In iSQL*Plus, SET PAUSE ON displays the value of text, then pauses output and displays a Next Page button after PAGESIZE number of rows of report output. Click the Next Page button to view more report output. The Next Page button is not displayed on the final page of output.

SET RECSEP {WR[APPED] | EA[CH] | OFF}

RECSEP tells SQL*Plus where to make the record separation.

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

The Display Record Separator preference (SET RECSEP) is only supported in iSQL*Plus when Preformatted Output is On (SET MARKUP HTML PREFORMAT).

SET RECSEPCHAR { | c}

Defines the character to display or print to separate records.

A record separator consists of a single line of the RECSEPCHAR (record separating character) repeated LINESIZE times. The default is a single space.

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

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

OFF suppresses the output of DBMS_OUTPUT.PUT_LINE; ON displays the output.

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

Every server output line begins on a new output line.

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

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

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

For more information on DBMS_OUTPUT.PUT_LINE, see your Oracle Database Application Developer’s Guide — Fundamentals.

Example

To enable text display in a PL/SQL block using DBMS_OUTPUT.PUT_LINE, enter