Various SET commands in SQL
@ ("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
1* SELECT DEPARTMENT_ID
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.
Note::
This command applies only to the current instance. To specify archiving for a different instance or for all instances in a Parallel Server, use the SQL command ALTER SYSTEM. For more information about using SQL commands, see the Oracle Database SQL Reference.
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
2* C/OLD/NEW/
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 columndisplay 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, <, >, " and &, 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".
Table 13-1 Number Formats
Element Examples Description
, (comma) 9,999 Displays a comma in the specified position.
. (period) 99.99 Displays a period (decimal point) to separate the integral and fractional parts of a number.
$ $9999 Displays a leading dollar sign.
0 0999
9990 Displays leading zeros
Displays trailing zeros.
9 9999 Displays a value with the number of digits specified by the number of 9s. Value has a leading space if positive, a leading minus sign if negative. Blanks are displayed for leading zeroes. A zero (0) is displayed for a value of zero.
B B9999 Displays blanks for the integer part of a fixed-point number when the integer part is zero, regardless of zeros in the format model.
C C999 Displays the ISO currency symbol in the specified position.
D 99D99 Displays the decimal character to separate the integral and fractional parts of a number.
EEEE 9.999EEEE Displays value in scientific notation (format must contain exactly four "E"s).
G 9G999 Displays the group separator in the specified positions in the integral part of a number.
L L999 Displays the local currency symbol in the specified position.
MI 9999MI Displays a trailing minus sign after a negative value.
Display a trailing space after a positive value.
PR 9999PR Displays a negative value in <angle brackets>.
Displays a positive value with a leading and trailing space.
RN
rn RN
rn Displays uppercase Roman numerals.
Displays lowercase Roman numerals.
Value can be an integer between 1 and 3999.
S S9999
9999S Displays a leading minus or plus sign.
Displays a trailing minus or plus sign.
TM TM Displays the smallest number of decimal characters possible. The default is TM9. Fixed notation is used for output up to 64 characters, scientific notation for more than 64 characters. Cannot precede TM with any other element. TM can only be followed by a single 9 or E
U U9999 Displays the dual currency symbol in the specified position.
V 999V99 Displays value multiplied by 10n, where n is the number of 9's after the V.
X XXXX
xxxx Displays the hexadecimal value for the rounded value of the specified number of digits.
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';
Session altered.
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.
Table 13-2 COMPUTE Functions
Function Computes Applies to Datatypes
AVG Average of non-null values NUMBER
COU[NT] Count of non-null values all types
MIN[IMUM] Minimum value NUMBER, CHAR, NCHAR, VARCHAR2 (VARCHAR), NVARCHAR2 (NCHAR VARYING)
MAX[IMUM] Maximum value NUMBER, CHAR, NCHAR, VARCHAR2 (VARCHAR), NVARCHAR2 (NCHAR VARYING)
NUM[BER] Count of rows all types
SUM Sum of non-null values NUMBER
STD Standard deviation of non-null values NUMBER
VAR[IANCE] Variance of non-null values NUMBER
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.
Table 13-3 Variables Predefined at SQL*Plus Installation
Variable Name Contains
_CONNECT_IDENTIFIER Connection identifier used to make connection, where available.
_DATE Current date, or a user defined fixed string.
_EDITOR Specifies the editor used by the EDIT command.
_O_VERSION Current version of the installed Oracle Database.
_O_RELEASE Full release number of the installed Oracle Database.
_PRIVILEGE Privilege level of the current connection.
_SQLPLUS_RELEASE Full release number of installed SQL*Plus component.
_USER User name used to make connection.
_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.
See Also:
Oracle Database SQL Reference for information on AS SYSDBA and AS SYSOPER 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
DEFINE _SQLPLUS_RELEASE = "101000100" (CHAR)
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
Term Description
n Deletes line n.
n m Deletes lines n through m.
n * Deletes line n through the current line.
n LAST Deletes line n through the last line.
*
Deletes the current line.
LAST Deletes the last line.
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.
Note:
DEL is a SQL*Plus command and DELETE is a SQL command. For more information about the SQL DELETE command, see the Oracle Database SQL Reference.
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;
/
Package created.
DESCRIBE apack
PROCEDURE APROC
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P1 CHAR IN
P2 NUMBER IN
PROCEDURE BPROC
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
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
1* SELECT DEPARTMENT_ID
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.
Note::
This command applies only to the current instance. To specify archiving for a different instance or for all instances in a Parallel Server, use the SQL command ALTER SYSTEM. For more information about using SQL commands, see the Oracle Database SQL Reference.
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
2* C/OLD/NEW/
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 columndisplay 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, <, >, " and &, 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".
Table 13-1 Number Formats
Element Examples Description
, (comma) 9,999 Displays a comma in the specified position.
. (period) 99.99 Displays a period (decimal point) to separate the integral and fractional parts of a number.
$ $9999 Displays a leading dollar sign.
0 0999
9990 Displays leading zeros
Displays trailing zeros.
9 9999 Displays a value with the number of digits specified by the number of 9s. Value has a leading space if positive, a leading minus sign if negative. Blanks are displayed for leading zeroes. A zero (0) is displayed for a value of zero.
B B9999 Displays blanks for the integer part of a fixed-point number when the integer part is zero, regardless of zeros in the format model.
C C999 Displays the ISO currency symbol in the specified position.
D 99D99 Displays the decimal character to separate the integral and fractional parts of a number.
EEEE 9.999EEEE Displays value in scientific notation (format must contain exactly four "E"s).
G 9G999 Displays the group separator in the specified positions in the integral part of a number.
L L999 Displays the local currency symbol in the specified position.
MI 9999MI Displays a trailing minus sign after a negative value.
Display a trailing space after a positive value.
PR 9999PR Displays a negative value in <angle brackets>.
Displays a positive value with a leading and trailing space.
RN
rn RN
rn Displays uppercase Roman numerals.
Displays lowercase Roman numerals.
Value can be an integer between 1 and 3999.
S S9999
9999S Displays a leading minus or plus sign.
Displays a trailing minus or plus sign.
TM TM Displays the smallest number of decimal characters possible. The default is TM9. Fixed notation is used for output up to 64 characters, scientific notation for more than 64 characters. Cannot precede TM with any other element. TM can only be followed by a single 9 or E
U U9999 Displays the dual currency symbol in the specified position.
V 999V99 Displays value multiplied by 10n, where n is the number of 9's after the V.
X XXXX
xxxx Displays the hexadecimal value for the rounded value of the specified number of digits.
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';
Session altered.
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.
Table 13-2 COMPUTE Functions
Function Computes Applies to Datatypes
AVG Average of non-null values NUMBER
COU[NT] Count of non-null values all types
MIN[IMUM] Minimum value NUMBER, CHAR, NCHAR, VARCHAR2 (VARCHAR), NVARCHAR2 (NCHAR VARYING)
MAX[IMUM] Maximum value NUMBER, CHAR, NCHAR, VARCHAR2 (VARCHAR), NVARCHAR2 (NCHAR VARYING)
NUM[BER] Count of rows all types
SUM Sum of non-null values NUMBER
STD Standard deviation of non-null values NUMBER
VAR[IANCE] Variance of non-null values NUMBER
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.
Table 13-3 Variables Predefined at SQL*Plus Installation
Variable Name Contains
_CONNECT_IDENTIFIER Connection identifier used to make connection, where available.
_DATE Current date, or a user defined fixed string.
_EDITOR Specifies the editor used by the EDIT command.
_O_VERSION Current version of the installed Oracle Database.
_O_RELEASE Full release number of the installed Oracle Database.
_PRIVILEGE Privilege level of the current connection.
_SQLPLUS_RELEASE Full release number of installed SQL*Plus component.
_USER User name used to make connection.
_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.
See Also:
Oracle Database SQL Reference for information on AS SYSDBA and AS SYSOPER 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
DEFINE _SQLPLUS_RELEASE = "101000100" (CHAR)
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
Term Description
n Deletes line n.
n m Deletes lines n through m.
n * Deletes line n through the current line.
n LAST Deletes line n through the last line.
*
Deletes the current line.
- n Deletes the current line through line n.
- LAST Deletes the current line through the last line.
LAST Deletes the last line.
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.
Note:
DEL is a SQL*Plus command and DELETE is a SQL command. For more information about the SQL DELETE command, see the Oracle Database SQL Reference.
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;
/
Package created.
DESCRIBE apack
PROCEDURE APROC
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P1 CHAR IN
P2 NUMBER IN
PROCEDURE BPROC
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
No comments:
Post a Comment