| Command | Description |
|---|---|
|
| Runs SQL*Plus statements in the specified script. The script can be called from the local file system or from a web server. |
|
| Runs a script. This command is similar to the @ (at sign) command It is useful for running nested scripts because it looks for the specified script in the same path as the calling script. |
|
| Executes the SQL command or PL/SQL block. |
|
| Reads a line of input and stores it in a given substitution variable. |
|
| Adds specified text to the end of the current line in the buffer. |
|
| Displays information about redo log files. |
|
| Specifies display characteristics for a given attribute of an Object Type column, and lists the current display characteristics for a single attribute or all attributes. |
|
| Specifies where and how formatting will change in a report, or lists the current break definition. |
|
| Places and formats a specified title at the bottom of each report page, or lists the current BTITLE definition. |
|
| Changes text on the current line in the buffer. |
|
| Resets or erases the current clause or setting for the specified option, such as BREAKS or COLUMNS. |
|
| Specifies display characteristics for a given column, or lists the current display characteristics for a single column or for all columns. |
|
| Calculates and prints summary lines, using various standard computations, on subsets of selected rows, or lists all COMPUTE definitions. |
|
| Connects a given user to Oracle Database. |
|
| Copies results from a query to a table in the same or another database. |
|
| Specifies a substitution variable and assigns it a CHAR value, or lists the value and variable type of a single variable or all variables. |
|
| Deletes one more lines of the buffer. |
|
| Lists the column definitions for the specified table, view, or synonym or the specifications for the specified function procedure. |
|
| Commits pending changes to the database and logs the current user off Oracle Database, but does not exit SQL*Plus. |
|
| Invokes an operating system text editor on the contents of the specified file or on the contents of the buffer. |
|
| Executes a single PL/SQL statement. |
|
| Terminates SQL*Plus and returns control to the operating system. |
|
| Loads an operating system file into the buffer. |
|
| Accesses the SQL*Plus command-line help system. |
|
| Executes an operating system command without leaving SQL*Plus. |
|
| Adds one or more new lines after the current line in the buffer. |
|
| Lists one or more lines of the buffer. |
|
| Enables a password to be changed without echoing the password on an input device. |
|
| Displays the specified text, then waits for the user to press Return. |
|
| Displays the current value of a bind variable. |
|
| Sends the specified message to the user's screen. |
|
| Terminates SQL*Plus and returns control to the operating system QUIT is identical to EXIT. |
|
| Performs media recovery on one or more tablespaces, one or more datafiles, or the entire database. |
|
| Begins a comment in a script. |
|
| Places and formats a specified report footer at the bottom of each report, or lists the current REPFOOTER definition. |
|
| Places and formats a specified report header at the top of each report, or lists the current REPHEADER definition. |
|
| Lists and runs the SQL command or PL/SQL block currently stored in the SQL buffer. |
|
| Saves the contents of the buffer in an operating system file (a script). |
|
| Sets a system variable to alter the SQL*Plus environment for your current session. |
|
| Shows the value of a SQL*Plus system variable or the current SQL*Plus environment. |
|
| Shuts down a currently running Oracle Database instance. |
|
| Stores query results in an operating system file and, optionally, sends the file to a printer. |
|
| Runs the SQL statements in the specified script. The script can be called from a local file system or a web server in SQL*Plus command-line. |
|
| Starts an Oracle Database instance and optionally mounts and opens a database. |
|
| Saves attributes of the current SQL*Plus environment in an operating system script. |
|
| Records timing data for an elapsed period of time, lists the current timer's title and timing data, or lists the number of active timers. |
|
| Places and formats a specified title at the top of each report page, or lists the current TTITLE definition. |
|
| Deletes one or more substitution variables that you defined either explicitly (with the DEFINE command) or implicitly (with an argument to the START command). |
|
| Declares a bind variable that can be referenced in PL/SQL. |
|
| Exits SQL*Plus if an operating system command generates an error. |
|
| Exits SQL*Plus if a SQL command or PL/SQL block generates an error. |
|
| Runs an XQuery 1.0 statement. |
Syntax
REPH[EADER] [PAGE] [printspec [text | variable] ...] | [ON | OFF]
where printspec represents one or more of the following clauses used to place and format the text:
COL nS[KIP] [n]TAB nLE[FT]CE[NTER]R[IGHT]BOLD FORMAT text
Places and formats a specified report header at the top of each report, or lists the current REPHEADER definition.
Enter REPHEADER with no clauses to list the current REPHEADER definition.
Terms
These terms and clauses also apply to the REPFOOTER command.
PAGE
Begins a new page after printing the specified report header or before printing the specified report footer.
text
The report header or footer text. Enter text in single quotes if you want to place more than one word on a single line. The default is NULL.
variable
A substitution variable or any of the following system-maintained values. SQL.LNO is the current line number, SQL.PNO is the current page number, SQL.CODE is the current error code, SQL.RELEASE is the current Oracle Database release number, and SQL.USER is the current username.
To print one of these values, reference the appropriate variable in the report header or footer. You can use the FORMAT clause to format variable.
OFF
Turns the report header or footer off (suppresses its display) without affecting its definition.
COL n
Indents to column n of the current line (backward if column n has been passed). Column in this context means print position, not table column.
S[KIP] [n]
Skips to the start of a new line n times; if you omit n, one time; if you enter zero for n, backward to the start of the current line.
TAB n
Skips forward n columns (backward if you enter a negative value for n). Column in this context means print position, not table column.
LE[FT] CE[NTER] R[IGHT]
Left-align, center, and right-align data on the current line respectively. SQL*Plus aligns following data items as a group, up to the end of the printspec or the next LEFT, CENTER, RIGHT, or COL command. CENTER and RIGHT use the SET LINESIZE value to calculate the position of the data item that follows.
BOLD
Prints data in bold print. SQL*Plus represents bold print on your terminal by repeating the data on three consecutive lines. On some operating systems, SQL*Plus may instruct your printer to print bold text on three consecutive lines, instead of bold.
FORMAT text
Specifies a format model that determines the format of data items up to the next FORMAT clause or the end of the command. The format model must be a text constant such as A10 or $999. See COLUMN for more information on formatting and valid format models.
If the datatype of the format model does not match the datatype of a given data item, the FORMAT clause has no effect on that item.
If no appropriate FORMAT model precedes a given data item, SQL*Plus prints NUMBER values according to the format specified by SET NUMFORMAT or, if you have not used SET NUMFORMAT, the default format. SQL*Plus prints DATE values using the default format.
Usage
If you do not enter a printspec clause before the text or variables, REPHEADER left justifies the text or variables.
You can use any number of constants and variables in a printspec. SQL*Plus displays the constants and variables in the order you specify, positioning and formatting each constant or variable as specified by the printspec clauses that precede it.
Examples
To define "EMPLOYEE LISTING REPORT" as a report header on a separate page, and to center it, enter:
REPHEADER PAGE CENTER 'EMPLOYEE LISTING REPORT' TTITLE RIGHT 'Page: ' FORMAT 999 SQL.PNO SELECT LAST_NAME, SALARY FROM EMP_DETAILS_VIEW WHERE SALARY > 12000;
Page: 1
EMPLOYEE LISTING REPORT
Page: 2
LAST_NAME SALARY
------------------------- ----------
King 24000
Kochhar 17000
De Haan 17000
Russell 14000
Partners 13500
Hartstein 13000
----------
sum 98500
6 rows selected.
|
To suppress the report header without changing its definition, enter:
REPHEADER OFF
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.
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_FLOAT is a floating-point number that conforms substantially with the Institute for Electrical and Electronics Engineers (IEEE) Standard for Binary Floating-Point Arithmetic, IEEE Standard 754-1985.
BINARY_DOUBLE
Makes the variable a BINARY_DOUBLE datatype. BINARY_DOUBLE is a floating-point number that conforms substantially with the Institute for Electrical and Electronics Engineers (IEEE) Standard for Binary Floating-Point Arithmetic, IEEE Standard 754-1985.
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 Language 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.
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: '
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.
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]
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
Syntax
@@{url | file_name[.ext] } [arg...]
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. The @@ command functions similarly to @ and 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.
arg...
Represent data items you wish to pass to parameters in the script. If you enter one or more arguments, SQL*Plus substitutes the values into the parameters (&1, &2, and so forth) in the script. The first argument replaces each occurrence of &1, the second replaces each occurrence of &2, and so forth.
The @@ command defines the parameters with the values given by the arguments. If you run the script again in this session, you can enter new arguments or omit the arguments to use the current values. For more information on using parameters, see Using Substitution Variables.
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.
This chapter explains how to format your query results to produce a finished report. This chapter does not discuss HTML output, but covers the following topics:
Read this chapter while sitting at your computer and try out the examples shown. Before beginning, make sure you have access to the HR sample schema described in SQL*Plus Quick Start.
Through the SQL*Plus COLUMN command, you can change the column headings and reformat the column data in your query results.
When displaying column headings, you can either use the default heading or you can change it using the COLUMN command. The following sections describe how default headings are derived and how to alter them using the COLUMN command. See the COLUMN command for more details.
SQL*Plus uses column or expression names as default column headings when displaying query results. Column names are often short and cryptic, however, and expressions can be hard to understand.
You can define a more useful column heading with the HEADING clause of the COLUMN command, in the following format:
COLUMN column_name HEADING column_heading
Example 6-1 Changing a Column Heading
To produce a report from EMP_DETAILS_VIEW with new headings specified for LAST_NAME, SALARY, and COMMISSION_PCT, enter the following commands:
COLUMN LAST_NAME HEADING 'LAST NAME' COLUMN SALARY HEADING 'MONTHLY SALARY' COLUMN COMMISSION_PCT HEADING COMMISSION SELECT LAST_NAME, SALARY, COMMISSION_PCT FROM EMP_DETAILS_VIEW WHERE JOB_ID='SA_MAN';
LAST NAME MONTHLY SALARY COMMISSION ------------------------- -------------- ---------- Russell 14000 .4 Partners 13500 .3 Errazuriz 12000 .3 Cambrault 11000 .3 Zlotkey 10500 .2 |
|
Note: The new headings will remain in effect until you enter different headings, reset each column's format, or exit from SQL*Plus. |
To change a column heading to two or more words, enclose the new heading in single or double quotation marks when you enter the COLUMN command. To display a column heading on more than one line, use a vertical bar (|) where you want to begin a new line. (You can use a character other than a vertical bar by changing the setting of the HEADSEP variable of the SET command. See the SET command for more information.)
Example 6-2 Splitting a Column Heading
To give the columns SALARY and LAST_NAME the headings MONTHLY SALARY and LAST NAME respectively, and to split the new headings onto two lines, enter
COLUMN SALARY HEADING 'MONTHLY|SALARY' COLUMN LAST_NAME HEADING 'LAST|NAME'
Now rerun the query with the slash (/) command:
/
LAST MONTHLY NAME SALARY COMMISSION ------------------------- ---------- ---------- Russell 14000 .4 Partners 13500 .3 Errazuriz 12000 .3 Cambrault 11000 .3 Zlotkey 10500 .2 |
Example 6-3 Setting the Underline Character
To change the character used to underline headings to an equal sign and rerun the query, enter the following commands:
SET UNDERLINE = /
LAST MONTHLY NAME SALARY COMMISSION ========================= ========== ========== Russell 14000 .4 Partners 13500 .3 Errazuriz 12000 .3 Cambrault 11000 .3 Zlotkey 10500 .2 |
Now change the underline character back to a dash:
SET UNDERLINE '-'
|
Note: You must enclose the dash in quotation marks; otherwise, SQL*Plus interprets the dash as a hyphen indicating that you wish to continue the command on another line. |
When displaying NUMBER columns, you can either accept the SQL*Plus default display width or you can change it using the COLUMN command. Later sections describe the default display and how you can alter it with the COLUMN command. The format model will stay in effect until you enter a new one, reset the column's format with
COLUMN column_name CLEAR
or exit from SQL*Plus.
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 FORMAT, then the column's width will always be at least the value of SET NUMWIDTH.
SQL*Plus normally displays numbers with as many digits as are required for accuracy, up to a standard display width determined by the value of the NUMWIDTH variable of the SET command (normally 10). If a number is larger than the value of SET NUMWIDTH, SQL*Plus rounds the number up or down to the maximum number of characters allowed if possible, or displays hashes if the number is too large.
You can choose a different format for any NUMBER column by using a format model in a COLUMN command. A format model is a representation of the way you want the numbers in the column to appear, using 9s to represent digits.
The COLUMN command identifies the column you want to format and the model you want to use, as shown:
COLUMN column_name FORMAT model
Use format models to add commas, dollar signs, angle brackets (around negative values), and leading zeros to numbers in a given column. You can also round the values to a given number of decimal places, display minus signs to the right of negative values (instead of to the left), and display values in exponential notation.
To use more than one format model for a single column, combine the desired models in one COLUMN command (see Example 6-4). See COLUMN for a complete list of format models and further details.
Example 6-4 Formatting a NUMBER Column
To display SALARY with a dollar sign, a comma, and the numeral zero instead of a blank for any zero values, enter the following command:
COLUMN SALARY FORMAT $99,990
Now rerun the current query:
/
LAST MONTHLY NAME SALARY COMMISSION ------------------------- -------- ---------- Russell $14,000 .4 Partners $13,500 .3 Errazuriz $12,000 .3 Cambrault $11,000 .3 Zlotkey $10,500 .2 |
Use a zero in your format model, as shown, when you use other formats such as a dollar sign and wish to display a zero in place of a blank for zero values.
When displaying datatypes, you can either accept the SQL*Plus default display width or you can change it using the COLUMN command. The format model will stay in effect until you enter a new one, reset the column's format with
COLUMN column_name CLEAR
or exit from SQL*Plus. Datatypes, in this manual, include the following types:
The default width of datatype columns is the width of the column in the database. The column width of a LONG, BLOB, BFILE, CLOB, NCLOB or XMLType defaults to the value of SET LONGCHUNKSIZE or SET LONG, whichever is the smaller.
The default width and format of unformatted DATE columns in SQL*Plus is determined by the database NLS_DATE_FORMAT parameter. Otherwise, the default format width is A9. See the FORMAT clause of the COLUMN command for more information on formatting DATE columns.
Left justification is the default for datatypes.
You can change the displayed width of a datatype or DATE, by using the COLUMN command with a format model consisting of the letter A (for alphanumeric) followed by a number representing the width of the column in characters.
Within the COLUMN command, identify the column you want to format and the model you want to use:
COLUMN column_name FORMAT model
If you specify a width shorter than the column heading, SQL*Plus truncates the heading. See the COLUMN command for more details.
Example 6-5 Formatting a Character Column
To set the width of the column LAST_NAME to four characters and rerun the current query, enter
COLUMN LAST_NAME FORMAT A4 /
LAST MONTHLY NAME SALARY COMMISSION ---- -------- ---------- Russ $14,000 .4 ell Part $13,500 .3 ners Erra $12,000 .3 zuri z LAST MONTHLY NAME SALARY COMMISSION ---- -------- ---------- Camb $11,000 .3 raul t Zlot $10,500 .2 key |
If the WRAP variable of the SET command is set to ON (its default value), the employee names wrap to the next line after the fourth character, as shown in Example 6-5, "Formatting a Character Column". If WRAP is set to OFF, the names are truncated (cut off) after the fourth character.
The system variable WRAP controls all columns; you can override the setting of WRAP for a given column through the WRAPPED, WORD_WRAPPED, and TRUNCATED clauses of the COLUMN command. See the COLUMN command for more information on these clauses. You will use the WORD_WRAPPED clause of COLUMN later in this chapter.
NCLOB, BLOB, BFILE or multibyte CLOB columns cannot be formatted with the WORD_WRAPPED option. If you format an NCLOB, BLOB, BFILE or multibyte CLOB column with COLUMN WORD_WRAPPED, the column data behaves as though COLUMN WRAPPED was applied instead.
|
Note: The column heading is truncated regardless of the setting of WRAP or any COLUMN command clauses. |
Now return the column to its previous format:
COLUMN LAST_NAME FORMAT A10
Example 6-6 Formatting an XMLType Column
Before illustrating how to format an XMLType column, you must create a table with an XMLType column definition, and insert some data into the table. You can create an XMLType column like any other user-defined column. To create a table containing an XMLType column, enter
CREATE TABLE warehouses ( warehouse_id NUMBER(3), warehouse_spec SYS.XMLTYPE, warehouse_name VARCHAR2 (35), location_id NUMBER(4));
To insert a new record containing warehouse_id and warehouse_spec values into the new warehouses table, enter
INSERT into warehouses (warehouse_id, warehouse_spec)
VALUES (100, sys.XMLTYPE.createXML(
'<Warehouse whNo="100">
<Building>Owned</Building>
</Warehouse>'));
To set the XMLType column width to 20 characters and then select the XMLType column, enter
COLUMN Building FORMAT A20
SELECT
w.warehouse_spec.extract('/Warehouse/Building/text()').getStringVal()
"Building"
FROM warehouses w;
Building -------------------- Owned |
For more information about the createXML, extract, text and getStringVal functions, and about creating and manipulating XMLType data, see Oracle Database PL/SQL Packages and Types Reference.
When you want to give more than one column the same display attributes, you can reduce the length of the commands you must enter by using the LIKE clause of the COLUMN command. The LIKE clause tells SQL*Plus to copy the display attributes of a previously defined column to the new column, except for changes made by other clauses in the same command.
Example 6-7 Copying a Column's Display Attributes
To give the column COMMISSION_PCT the same display attributes you gave to SALARY, but to specify a different heading, enter the following command:
COLUMN COMMISSION_PCT LIKE SALARY HEADING BONUS
Rerun the query:
/
LAST MONTHLY NAME SALARY BONUS ---------- -------- -------- Russell $14,000 $0 Partners $13,500 $0 Errazuriz $12,000 $0 Cambrault $11,000 $0 Zlotkey $10,500 $0 |
To list the current display attributes for a given column, use the COLUMN command followed by the column name only, as shown:
COLUMN column_name
To list the current display attributes for all columns, enter the COLUMN command with no column names or clauses after it:
COLUMN
To reset the display attributes for a column to their default values, use the CLEAR clause of the COLUMN command as shown:
COLUMN column_name CLEAR
Example 6-8 Resetting Column Display Attributes to their Defaults
To reset all column display attributes to their default values, enter:
CLEAR COLUMNS
columns cleared |
You can suppress and restore the display attributes you have given a specific column. To suppress a column's display attributes, enter a COLUMN command in the following form:
COLUMN column_name OFF
OFF tells SQL*Plus to use the default display attributes for the column, but does not remove the attributes you have defined through the COLUMN command. To restore the attributes you defined through COLUMN, use the ON clause:
COLUMN column_name ON
As you have seen, by default SQL*Plus wraps column values to additional lines when the value does not fit the column width. If you want to insert a record separator (a line of characters or a blank line) after each wrapped line of output (or after every row), use the RECSEP and RECSEPCHAR variables of the SET command.
RECSEP determines when the line of characters is printed; you set RECSEP to EACH to print after every line, to WRAPPED to print after wrapped lines, and to OFF to suppress printing. The default setting of RECSEP is WRAPPED.
RECSEPCHAR sets the character printed in each line. You can set RECSEPCHAR to any character.
You may wish to wrap whole words to additional lines when a column value wraps to additional lines. To do so, use the WORD_WRAPPED clause of the COLUMN command as shown:
COLUMN column_name WORD_WRAPPED
Example 6-9 Printing a Line of Characters after Wrapped Column Values
To print a line of dashes after each wrapped column value, enter the commands:
SET RECSEP WRAPPED SET RECSEPCHAR "-"
Finally, enter the following query:
SELECT LAST_NAME, JOB_TITLE, CITY FROM EMP_DETAILS_VIEW WHERE SALARY>12000;
Now restrict the width of the column JOB_TITLE and tell SQL*Plus to wrap whole words to additional lines when necessary:
COLUMN JOB_TITLE FORMAT A20 WORD_WRAPPED
Run the query:
/
LAST_NAME JOB_TITLE CITY
------------------------- -------------------- --------
King President Seattle
Kochhar Administration Vice Seattle
President
-------------------------------------------------------
De Haan Administration Vice Seattle
President
-------------------------------------------------------
Russell Sales Manager Oxford
Partners Sales Manager Oxford
Hartstein Marketing Manager Toronto
6 rows selected.
|
If you set RECSEP to EACH, SQL*Plus prints a line of characters after every row (after every department, for the above example).
Before continuing, set RECSEP to OFF to suppress the printing of record separators:
SET RECSEP OFF
When you use an ORDER BY clause in your SQL SELECT command, rows with the same value in the ordered column (or expression) are displayed together in your output. You can make this output more useful to the user by using the SQL*Plus BREAK and COMPUTE commands to create subsets of records and add space or summary lines after each subset.
The column you specify in a BREAK command is called a break column. By including the break column in your ORDER BY clause, you create meaningful subsets of records in your output. You can then add formatting to the subsets within the same BREAK command, and add a summary line (containing totals, averages, and so on) by specifying the break column in a COMPUTE command.
SELECT DEPARTMENT_ID, LAST_NAME, SALARY FROM EMP_DETAILS_VIEW WHERE SALARY > 12000 ORDER BY DEPARTMENT_ID;
DEPARTMENT_ID LAST_NAME SALARY
------------- ------------------------- ----------
20 Hartstein 13000
80 Russell 14000
80 Partners 13500
90 King 24000
90 Kochhar 17000
90 De Haan 17000
6 rows selected.
|
To make this report more useful, you would use BREAK to establish DEPARTMENT_ID as the break column. Through BREAK you could suppress duplicate values in DEPARTMENT_ID and place blank lines or begin a new page between departments. You could use BREAK in conjunction with COMPUTE to calculate and print summary lines containing the total salary for each department and for all departments. You could also print summary lines containing the average, maximum, minimum, standard deviation, variance, or row count.
The BREAK command suppresses duplicate values by default in the column or expression you name. Thus, to suppress the duplicate values in a column specified in an ORDER BY clause, use the BREAK command in its simplest form:
BREAK ON break_column
Example 6-10 Suppressing Duplicate Values in a Break Column
To suppress the display of duplicate department numbers in the query results shown, enter the following commands:
BREAK ON DEPARTMENT_ID;
For the following query (which is the current query stored in the buffer):
SELECT DEPARTMENT_ID, LAST_NAME, SALARY FROM EMP_DETAILS_VIEW WHERE SALARY > 12000 ORDER BY DEPARTMENT_ID;
DEPARTMENT_ID LAST_NAME SALARY
------------- ------------------------- ----------
20 Hartstein 13000
80 Russell 14000
Partners 13500
90 King 24000
Kochhar 17000
De Haan 17000
6 rows selected.
|
You can insert blank lines or begin a new page each time the value changes in the break column. To insert n blank lines, use the BREAK command in the following form:
BREAK ON break_column SKIP n
To skip a page, use the command in this form:
BREAK ON break_column SKIP PAGE
Example 6-11 Inserting Space when a Break Column's Value Changes
To place one blank line between departments, enter the following command:
BREAK ON DEPARTMENT_ID SKIP 1
Now rerun the query:
/
DEPARTMENT_ID LAST_NAME SALARY
------------- ------------------------- ----------
20 Hartstein 13000
80 Russell 14000
Partners 13500
90 King 24000
Kochhar 17000
De Haan 17000
6 rows selected.
|
You may wish to insert blank lines or a blank page after every row. To skip n lines after every row, use BREAK in the following form:
BREAK ON ROW SKIP n
To skip a page after every row, use
BREAK ON ROW SKIP PAGE
|
Note: SKIP PAGE does not cause a physical page break character to be generated unless you have also specified NEWPAGE 0. |
Suppose you have more than one column in your ORDER BY clause and wish to insert space when each column's value changes. Each BREAK command you enter replaces the previous one. Thus, if you want to use different spacing techniques in one report or insert space after the value changes in more than one ordered column, you must specify multiple columns and actions in a single BREAK command.
Example 6-12 Combining Spacing Techniques
Type the following:
SELECT DEPARTMENT_ID, JOB_ID, LAST_NAME, SALARY FROM EMP_DETAILS_VIEW WHERE SALARY>12000 ORDER BY DEPARTMENT_ID, JOB_ID;
Now, to skip a page when the value of DEPARTMENT_ID changes and one line when the value of JOB_ID changes, enter the following command:
BREAK ON DEPARTMENT_ID SKIP PAGE ON JOB_ID SKIP 1
To show that SKIP PAGE has taken effect, create a TTITLE with a page number:
TTITLE COL 35 FORMAT 9 'Page:' SQL.PNO
Run the new query to see the results:
Page: 1
DEPARTMENT_ID JOB_ID LAST_NAME SALARY
------------- ---------- ------------------------- ----------
20 MK_MAN Hartstein 13000
Page: 2
DEPARTMENT_ID JOB_ID LAST_NAME SALARY
------------- ---------- ------------------------- ----------
80 SA_MAN Russell 14000
Partners 13500
Page: 3
DEPARTMENT_ID JOB_ID LAST_NAME SALARY
------------- ---------- ------------------------- ----------
90 AD_PRES King 24000
AD_VP Kochhar 17000
De Haan 17000
6 rows selected.
|
Before continuing, turn off the top title display without changing its definition:
TTITLE OFF
You can list your current break definition by entering the BREAK command with no clauses:
BREAK
You can remove the current break definition by entering the CLEAR command with the BREAKS clause:
CLEAR BREAKS
You may wish to place the command CLEAR BREAKS at the beginning of every script to ensure that previously entered BREAK commands will not affect queries you run in a given file.
If you organize the rows of a report into subsets with the BREAK command, you can perform various computations on the rows in each subset. You do this with the functions of the SQL*Plus COMPUTE command. Use the BREAK and COMPUTE commands together in the following forms:
BREAK ON break_column COMPUTE function LABEL label_name OF column column column ... ON break_column
You can include multiple break columns and actions, such as skipping lines in the BREAK command, as long as the column you name after ON in the COMPUTE command also appears after ON in the BREAK command. To include multiple break columns and actions in BREAK when using it in conjunction with COMPUTE, use these commands in the following forms:
BREAK ON break_column_1 SKIP PAGE ON break_column_2 SKIP 1 COMPUTE function LABEL label_name OF column column column ... ON break_column_2
The COMPUTE command has no effect without a corresponding BREAK command.
You can COMPUTE on NUMBER columns and, in certain cases, on all types of columns. For more information see the COMPUTE command.
The following table lists compute functions and their effects
Table 6-1 Compute Functions
| Function... | Computes the... |
|---|---|
SUM | |
MINIMUM |
Minimum value in the column. |
MAXIMUM |
Maximum value in the column. |
AVG |
Average of the values in the column. |
STD |
Standard deviation of the values in the column. |
VARIANCE |
Variance of the values in the column. |
COUNT |
Number of non-null values in the column. |
NUMBER |
Number of rows in the column. |
The function you specify in the COMPUTE command applies to all columns you enter after OF and before ON. The computed values print on a separate line when the value of the ordered column changes.
Labels for ON REPORT and ON ROW computations appear in the first column; otherwise, they appear in the column specified in the ON clause.
You can change the compute label by using COMPUTE LABEL. If you do not define a label for the computed value, SQL*Plus prints the unabbreviated function keyword.
The compute label can be suppressed by using the NOPRINT option of the COLUMN command on the break column. See the COMPUTE command for more details. If you use the NOPRINT option for the column on which the COMPUTE is being performed, the COMPUTE result is also suppressed.
Example 6-13 Computing and Printing Subtotals
To compute the total of SALARY by department, first list the current BREAK definition:
BREAK
which displays current BREAK definitions:
break on DEPARTMENT_ID page nodup
on JOB_ID skip 1 nodup
|
Now enter the following COMPUTE command and run the current query:
COMPUTE SUM OF SALARY ON DEPARTMENT_ID /
DEPARTMENT_ID JOB_ID LAST_NAME SALARY
------------- ---------- ------------------------- ----------
20 MK_MAN Hartstein 13000
************* ********** ----------
sum 13000
DEPARTMENT_ID JOB_ID LAST_NAME SALARY
------------- ---------- ------------------------- ----------
80 SA_MAN Russell 14000
Partners 13500
************* ********** ----------
sum 27500
DEPARTMENT_ID JOB_ID LAST_NAME SALARY
------------- ---------- ------------------------- ----------
90 AD_PRES King 24000
AD_VP Kochhar 17000
De Haan 17000
************* ********** ----------
sum 58000
6 rows selected.
|
To compute the sum of salaries for departments 10 and 20 without printing the compute label:
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 SALARY>12000 ORDER BY DEPARTMENT_ID;
DEPARTMENT_ID LAST_NAME SALARY
------------- ------------------------- ----------
20 Hartstein 13000
----------
13000
80 Russell 14000
80 Partners 13500
----------
27500
90 King 24000
90 Kochhar 17000
90 De Haan 17000
----------
58000
6 rows selected.
|
To compute the salaries just at the end of the report:
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 SALARY>12000 ORDER BY DEPARTMENT_ID;
DEPARTMENT_ID LAST_NAME SALARY
------------- ------------------------- ----------
20 Hartstein 13000
80 Russell 14000
80 Partners 13500
90 King 24000
90 Kochhar 17000
90 De Haan 17000
----------
98500
6 rows selected.
|
When you establish the format of a NUMBER column, you must allow for the size of the sums included in the report.
You can calculate and print summary lines based on all values in a column by using BREAK and COMPUTE in the following forms:
BREAK ON REPORT COMPUTE function LABEL label_name OF column column column ... ON REPORT
Example 6-14 Computing and Printing a Grand Total
To calculate and print the grand total of salaries for all sales people and change the compute label, first enter the following BREAK and COMPUTE commands:
BREAK ON REPORT COMPUTE SUM LABEL TOTAL OF SALARY ON REPORT
Next, enter and run a new query:
SELECT LAST_NAME, SALARY FROM EMP_DETAILS_VIEW WHERE JOB_ID='SA_MAN';
LAST_NAME SALARY
------------------------- ----------
Russell 14000
Partners 13500
Errazuriz 12000
Cambrault 11000
Zlotkey 10500
----------
TOTAL 61000
|
To print a grand total (or grand average, grand maximum, and so on) in addition to subtotals (or sub-averages, and so on), include a break column and an ON REPORT clause in your BREAK command. Then, enter one COMPUTE command for the break column and another to compute ON REPORT:
BREAK ON break_column ON REPORT COMPUTE function LABEL label_name OF column ON break_column COMPUTE function LABEL label_name OF column ON REPORT
You can compute and print the same type of summary value on different columns. To do so, enter a separate COMPUTE command for each column.
Example 6-15 Computing the Same Type of Summary Value on Different Columns
To print the total of salaries and commissions for all sales people, first enter the following COMPUTE command:
COMPUTE SUM OF SALARY COMMISSION_PCT ON REPORT
You do not have to enter a BREAK command; the BREAK you entered in Example 6-14, "Computing and Printing a Grand Total" is still in effect. Now, change the first line of the select query to include COMMISSION_PCT:
1
1* SELECT LAST_NAME, SALARY |
APPEND , COMMISSION_PCT;
Finally, run the revised query to see the results:
/
LAST_NAME SALARY COMMISSION_PCT
------------------------- ---------- --------------
Russell 14000 .4
Partners 13500 .3
Errazuriz 12000 .3
Cambrault 11000 .3
Zlotkey 10500 .2
---------- --------------
sum 61000 1.5
|
You can also print multiple summary lines on the same break column. To do so, include the function for each summary line in the COMPUTE command as follows:
COMPUTE function LABEL label_name function LABEL label_name function LABEL label_name ... OF column ON break_column
If you include multiple columns after OF and before ON, COMPUTE calculates and prints values for each column you specify.
Example 6-16 Computing Multiple Summary Lines on the Same Break Column
To compute the average and sum of salaries for the sales department, first enter the following BREAK and COMPUTE commands:
BREAK ON DEPARTMENT_ID COMPUTE AVG SUM OF SALARY ON DEPARTMENT_ID
Now, enter and run the following query:
SELECT DEPARTMENT_ID, LAST_NAME, SALARY FROM EMP_DETAILS_VIEW WHERE DEPARTMENT_ID = 30 ORDER BY DEPARTMENT_ID, SALARY;
DEPARTMENT_ID LAST_NAME SALARY
------------- ------------------------- ----------
30 Colmenares 2500
Himuro 2600
Tobias 2800
Baida 2900
Khoo 3100
Raphaely 11000
************* ----------
avg 4150
sum 24900
6 rows selected.
|
You can list your current COMPUTE definitions by entering the COMPUTE command with no clauses:
COMPUTE
Example 6-17 Removing COMPUTE Definitions
To remove all COMPUTE definitions and the accompanying BREAK definition, enter the following commands:
CLEAR BREAKS
breaks cleared |
CLEAR COMPUTES
computes cleared |
You may wish to place the commands CLEAR BREAKS and CLEAR COMPUTES at the beginning of every script to ensure that previously entered BREAK and COMPUTE commands will not affect queries you run in a given file.
The word page refers to a screen full of information on your display or a page of a spooled (printed) report. You can place top and bottom titles on each page, set the number of lines per page, and determine the width of each line.
The word report refers to the complete results of a query. You can also place headers and footers on each report and format them in the same way as top and bottom titles on pages.
As you have already seen, you can set a title to display at the top of each page of a report. You can also set a title to display at the bottom of each page. The TTITLE command defines the top title; the BTITLE command defines the bottom title.
You can also set a header and footer for each report. The REPHEADER command defines the report header; the REPFOOTER command defines the report footer.
A TTITLE, BTITLE, REPHEADER or REPFOOTER command consists of the command name followed by one or more clauses specifying a position or format and a CHAR value you wish to place in that position or give that format. You can include multiple sets of clauses and CHAR values:
TTITLE position_clause(s) char_value position_clause(s) char_value ... BTITLE position_clause(s) char_value position_clause(s) char_value ... REPHEADER position_clause(s) char_value position_clause(s) char_value ... REPFOOTER position_clause(s) char_value position_clause(s) char_value ...
For descriptions of all TTITLE, BTITLE, REPHEADER and REPFOOTER clauses, see the TTITLE command and the REPHEADER command.
Example 6-18 Placing a Top and Bottom Title on a Page
To put titles at the top and bottom of each page of a report, enter
TTITLE CENTER - "ACME SALES DEPARTMENT PERSONNEL REPORT" BTITLE CENTER "COMPANY CONFIDENTIAL"
Now run the current query:
/
ACME SALES DEPARTMENT PERSONNEL REPORT
DEPARTMENT_ID LAST_NAME SALARY
------------- ------------------------- ----------
30 Colmenares 2500
30 Himuro 2600
30 Tobias 2800
30 Baida 2900
30 Khoo 3100
30 Raphaely 11000
COMPANY CONFIDENTIAL
6 rows selected.
|
Example 6-19 Placing a Header on a Report
To put a report header on a separate page, and to center it, enter
REPHEADER PAGE CENTER 'PERFECT WIDGETS'
Now run the current query:
/
which displays the following two pages of output, with the new REPHEADER displayed on the first page:
ACME SALES DEPARTMENT PERSONNEL REPORT
PERFECT WIDGETS
pf COMPANY CONFIDENTIAL
ACME SALES DEPARTMENT PERSONNEL REPORT
DEPARTMENT_ID LAST_NAME SALARY
------------- ------------------------- ----------
30 Colmenares 2500
30 Himuro 2600
30 Tobias 2800
30 Baida 2900
30 Khoo 3100
30 Raphaely 11000
COMPANY CONFIDENTIAL
6 rows selected.
|
To suppress the report header without changing its definition, enter
REPHEADER OFF
The report in the preceding exercises might look more attractive if you give the company name more emphasis and place the type of report and the department name on either end of a separate line. It may also help to reduce the line size and thus center the titles more closely around the data.
You can accomplish these changes by adding some clauses to the TTITLE command and by resetting the system variable LINESIZE, as the following example shows.
You can format report headers and footers in the same way as BTITLE and TTITLE using the REPHEADER and REPFOOTER commands.
Example 6-20 Positioning Title Elements
To redisplay the personnel report with a repositioned top title, enter the following commands:
TTITLE CENTER 'A C M E W I D G E T' SKIP 1 - CENTER ==================== SKIP 1 LEFT 'PERSONNEL REPORT' - RIGHT 'SALES DEPARTMENT' SKIP 2 SET LINESIZE 60 /
A C M E W I D G E T
====================
PERSONNEL REPORT SALES DEPARTMENT
DEPARTMENT_ID LAST_NAME SALARY
------------- ------------------------- ----------
30 Colmenares 2500
30 Himuro 2600
30 Tobias 2800
30 Baida 2900
30 Khoo 3100
30 Raphaely 11000
COMPANY CONFIDENTIAL
6 rows selected.
|
The LEFT, RIGHT, and CENTER clauses place the following values at the beginning, end, and center of the line. The SKIP clause tells SQL*Plus to move down one or more lines.
Note that there is no longer any space between the last row of the results and the bottom title. The last line of the bottom title prints on the last line of the page. The amount of space between the last row of the report and the bottom title depends on the overall page size, the number of lines occupied by the top title, and the number of rows in a given page. In the above example, the top title occupies three more lines than the top title in the previous example. You will learn to set the number of lines per page later in this chapter.
To always print n blank lines before the bottom title, use the SKIP n clause at the beginning of the BTITLE command. For example, to skip one line before the bottom title in the example above, you could enter the following command:
BTITLE SKIP 1 CENTER 'COMPANY CONFIDENTIAL'
You can use the COL clause in TTITLE or BTITLE to indent the title element a specific number of spaces. For example, COL 1 places the following values in the first character position, and so is equivalent to LEFT, or an indent of zero. COL 15 places the title element in the 15th character position, indenting it 14 spaces.
Example 6-21 Indenting a Title Element
To print the company name left-aligned with the report name indented five spaces on the next line, enter
TTITLE LEFT 'ACME WIDGET' SKIP 1 - COL 6 'SALES DEPARTMENT PERSONNEL REPORT' SKIP 2
Now rerun the current query to see the results:
/
ACME WIDGET
SALES DEPARTMENT PERSONNEL REPORT
DEPARTMENT_ID LAST_NAME SALARY
------------- ------------------------- ----------
30 Colmenares 2500
30 Himuro 2600
30 Tobias 2800
30 Baida 2900
30 Khoo 3100
30 Raphaely 11000
COMPANY CONFIDENTIAL
6 rows selected.
|
If you need to enter a title greater than 500 characters in length, you can use the SQL*Plus command DEFINE to place the text of each line of the title in a separate substitution variable:
DEFINE LINE1 = 'This is the first line...' DEFINE LINE2 = 'This is the second line...' DEFINE LINE3 = 'This is the third line...'
Then, reference the variables in your TTITLE or BTITLE command as follows:
TTITLE CENTER LINE1 SKIP 1 CENTER LINE2 SKIP 1 - CENTER LINE3
You can display the current page number and other system-maintained values in your title by entering a system value name as a title element, for example:
TTITLE LEFT system-maintained_value_name
There are five system-maintained values you can display in titles, the most commonly used of which is SQL.PNO (the current page number). See TTITLE for a list of system-maintained values you can display in titles.
Example 6-22 Displaying the Current Page Number in a Title
To display the current page number at the top of each page, along with the company name, enter the following command:
TTITLE LEFT 'ACME WIDGET' RIGHT 'PAGE:' SQL.PNO SKIP 2
Now rerun the current query:
/
ACMEWIDGET PAGE: 1
DEPARTMENT_ID LAST_NAME SALARY
------------- ------------------------- ----------
30 Colmenares 2500
30 Himuro 2600
30 Tobias 2800
30 Baida 2900
30 Khoo 3100
30 Raphaely 11000
COMPANY CONFIDENTIAL
6 rows selected.
|
Note that SQL.PNO has a format ten spaces wide. You can change this format with the FORMAT clause of TTITLE (or BTITLE).
Example 6-23 Formatting a System-Maintained Value in a Title
To close up the space between the word PAGE: and the page number, reenter the TTITLE command as shown:
TTITLE LEFT 'ACME WIDGET' RIGHT 'PAGE:' FORMAT 999 - SQL.PNO SKIP 2
Now rerun the query:
/
ACME WIDGET 'PAGE:' 1
DEPARTMENT_ID LAST_NAME SALARY
------------- ------------------------- ----------
30 Colmenares 2500
30 Himuro 2600
30 Tobias 2800
30 Baida 2900
30 Khoo 3100
30 Raphaely 11000
COMPANY CONFIDENTIAL
6 rows selected.
|
To list a page title definition, enter the appropriate title command with no clauses:
TTITLE BTITLE
To suppress a title definition, enter:
TTITLE OFF BTITLE OFF
These commands cause SQL*Plus to cease displaying titles on reports, but do not clear the current definitions of the titles. You may restore the current definitions by entering:
TTITLE ON BTITLE ON
You may wish to create a master/detail report that displays a changing master column value at the top of each page with the detail query results for that value underneath. You can reference a column value in a top title by storing the desired value in a variable and referencing the variable in a TTITLE command. Use the following form of the COLUMN command to define the variable:
COLUMN column_name NEW_VALUE variable_name
You must include the master column in an ORDER BY clause and in a BREAK command using the SKIP PAGE clause.
Example 6-24 Creating a Master/Detail Report
Suppose you want to create a report that displays two different managers' employee numbers, each at the top of a separate page, and the people reporting to the manager on the same page as the manager's employee number. First create a variable, MGRVAR, to hold the value of the current manager's employee number:
COLUMN MANAGER_ID NEW_VALUE MGRVAR NOPRINT
Because you will only display the managers' employee numbers in the title, you do not want them to print as part of the detail. The NOPRINT clause you entered above tells SQL*Plus not to print the column MANAGER_ID.
Next, include a label and the value in your page title, enter the proper BREAK command, and suppress the bottom title from the last example:
TTITLE LEFT 'Manager: ' MGRVAR SKIP 2 BREAK ON MANAGER_ID SKIP PAGE BTITLE OFF
Finally, enter and run the following query:
SELECT MANAGER_ID, DEPARTMENT_ID, LAST_NAME, SALARY FROM EMP_DETAILS_VIEW WHERE MANAGER_ID IN (101, 201) ORDER BY MANAGER_ID, DEPARTMENT_ID;
Manager: 101
DEPARTMENT_ID LAST_NAME SALARY
------------- ------------------------- ----------
10 Whalen 4400
40 Mavris 6500
70 Baer 10000
100 Greenberg 12000
110 Higgins 12000
Manager: 201
DEPARTMENT_ID LAST_NAME SALARY
------------- ------------------------- ----------
20 Fay 6000
6 rows selected.
|
If you want to print the value of a column at the bottom of the page, you can use the COLUMN command in the following form:
COLUMN column_name OLD_VALUE variable_name
SQL*Plus prints the bottom title as part of the process of breaking to a new page—after finding the new value for the master column. Therefore, if you simply referenced the NEW_VALUE of the master column, you would get the value for the next set of details. OLD_VALUE remembers the value of the master column that was in effect before the page break began.
You can, of course, date your reports by simply typing a value in the title. This is satisfactory for ad hoc reports, but if you want to run the same report repeatedly, you would probably prefer to have the date automatically appear when the report is run. You can do this by creating a variable to hold the current date.
You can reference the predefined substitution variable _DATE to display the current date in a title as you would any other variable.
The date format model you include in your LOGIN file or in your SELECT statement determines the format in which SQL*Plus displays the date. See your Oracle Database SQL Language Reference for more information on date format models. See Modifying Your LOGIN File for more information about the LOGIN file.
You can also enter these commands interactively. See COLUMN for more information.
Typically, a page of a report contains the number of blank line(s) set in the NEWPAGE variable of the SET command, a top title, column headings, your query results, and a bottom title. SQL*Plus displays a report that is too long to fit on one page on several consecutive pages, each with its own titles and column headings. The amount of data SQL*Plus displays on each page depends on the current page dimensions.
The default page dimensions used by SQL*Plus are shown underneath:
number of lines before the top title: 1
number of lines per page, from the top title to the bottom of the page: 14
number of characters per line: 80
You can change these settings to match the size of your computer screen or, for printing, the size of a sheet of paper.
You can change the page length with the system variable PAGESIZE. For example, you may wish to do so when you print a report.
To set the number of lines between the beginning of each page and the top title, use the NEWPAGE variable of the SET command:
SET NEWPAGE number_of_lines
If you set NEWPAGE to zero, SQL*Plus skips zero lines and displays and prints a formfeed character to begin a new page. On most types of computer screens, the formfeed character clears the screen and moves the cursor to the beginning of the first line. When you print a report, the formfeed character makes the printer move to the top of a new sheet of paper, even if the overall page length is less than that of the paper. If you set NEWPAGE to NONE, SQL*Plus does not print a blank line or formfeed between report pages.
To set the number of lines on a page, use the PAGESIZE variable of the SET command:
SET PAGESIZE number_of_lines
You may wish to reduce the line size to center a title properly over your output, or you may want to increase line size for printing on wide paper. You can change the line width using the LINESIZE variable of the SET command:
SET LINESIZE number_of_characters
Example 6-25 Setting Page Dimensions
To set the page size to 66 lines, clear the screen (or advance the printer to a new sheet of paper) at the start of each page, and set the line size to 70, enter the following commands:
SET PAGESIZE 66 SET NEWPAGE 0 SET LINESIZE 70
Now enter and run the following commands to see the results:
TTITLE CENTER 'ACME WIDGET PERSONNEL REPORT' SKIP 1 - CENTER '01-JAN-2001' SKIP 2
Now run the following query:
COLUMN FIRST_NAME HEADING 'FIRST|NAME'; COLUMN LAST_NAME HEADING 'LAST|NAME'; COLUMN SALARY HEADING 'MONTHLY|SALARY' FORMAT $99,999; SELECT DEPARTMENT_ID, FIRST_NAME, LAST_NAME, SALARY FROM EMP_DETAILS_VIEW WHERE SALARY>12000;
ACME WIDGET PERSONNEL REPORT
01-JAN-2001
FIRST LAST MONTHLY
DEPARTMENT_ID NAME NAME SALARY
------------- -------------------- ------------------------- --------
90 Steven King $24,000
90 Neena Kochhar $17,000
90 Lex De Haan $17,000
80 John Russell $14,000
80 Karen Partners $13,500
20 Michael Hartstein $13,000
6 rows selected.
|
Now reset PAGESIZE, NEWPAGE, and LINESIZE to their default values:
SET PAGESIZE 14 SET NEWPAGE 1 SET LINESIZE 80
To list the current values of these variables, use the SHOW command:
SHOW PAGESIZE SHOW NEWPAGE SHOW LINESIZE
Through the SQL*Plus command SPOOL, you can store your query results in a file or print them on your computer's default printer.
Send your query results to a file when you want to edit them with a word processor before printing or include them in a letter, email, or other document.
To store the results of a query in a file—and still display them on the screen—enter the SPOOL command in the following form:
SPOOL file_name
If you do not follow the filename with a period and an extension, SPOOL adds a default file extension to the filename to identify it as an output file. The default varies with the operating system; on most hosts it is LST or LIS. The extension is not appended when you spool to system generated files such as /dev/null and /dev/stderr. See the platform-specific Oracle documentation provided for your operating system for more information.
SQL*Plus continues to spool information to the file until you turn spooling off, using the following form of SPOOL:
SPOOL OFF
When moving data between different software products, it is sometimes necessary to use a "flat" file (an operating system file with no escape characters, headings, or extra characters embedded). For example, if you do not have Oracle Net, you need to create a flat file for use with SQL*Loader when moving data from Oracle9i to Oracle Database 10g.
To create a flat file with SQL*Plus, you first must enter the following SET commands:
SET NEWPAGE 0 SET SPACE 0 SET LINESIZE 80 SET PAGESIZE 0 SET ECHO OFF SET FEEDBACK OFF SET VERIFY OFF SET HEADING OFF SET MARKUP HTML OFF SPOOL OFF
After entering these commands, you use the SPOOL command as shown in the previous section to create the flat file.
The SET COLSEP command may be useful to delineate the columns. For more information, see the SET command.
To store the results of a query in a file—and still display them on the screen—enter the SPOOL command in the following form:
SPOOL file_name
SQL*Plus stores all information displayed on the screen after you enter the SPOOL command in the file you specify.
To print query results, spool them to a file as described in the previous section. Then, instead of using SPOOL OFF, enter the command in the following form:
SPOOL OUT
SQL*Plus stops spooling and copies the contents of the spooled file to your computer's standard (default) printer. SPOOL OUT does not delete the spool file after printing.
Example 6-26 Sending Query Results to a Printer
To generate a final report and spool and print the results, create a script named EMPRPT containing the following commands.
First, use EDIT to create the script with your operating system text editor.
EDIT EMPRPT
Next, enter the following commands into the file, using your text editor:
SPOOL TEMP CLEAR COLUMNS CLEAR BREAKS CLEAR COMPUTES COLUMN DEPARTMENT_ID HEADING DEPARTMENT COLUMN LAST_NAME HEADING 'LAST NAME' COLUMN SALARY HEADING 'MONTHLY SALARY' FORMAT $99,999 BREAK ON DEPARTMENT_ID SKIP 1 ON REPORT COMPUTE SUM OF SALARY ON DEPARTMENT_ID COMPUTE SUM OF SALARY ON REPORT SET PAGESIZE 24 SET NEWPAGE 0 SET LINESIZE 70 TTITLE CENTER 'A C M E W I D G E T' SKIP 2 - LEFT 'EMPLOYEE REPORT' RIGHT 'PAGE:' - FORMAT 999 SQL.PNO SKIP 2 BTITLE CENTER 'COMPANY CONFIDENTIAL' SELECT DEPARTMENT_ID, LAST_NAME, SALARY FROM EMP_DETAILS_VIEW WHERE SALARY>12000 ORDER BY DEPARTMENT_ID; SPOOL OFF
If you do not want to see the output on your screen, you can also add SET TERMOUT OFF to the beginning of the file and SET TERMOUT ON to the end of the file. Save and close the file in your text editor (you will automatically return to SQL*Plus). Now, run the script EMPRPT:
@EMPRPT
SQL*Plus displays the output on your screen (unless you set TERMOUT to OFF), and spools it to the file TEMP:
A C M E W I D G E T
EMPLOYEE REPORT PAGE: 1
DEPARTMENT LAST NAME MONTHLY SALARY
---------- ------------------------- --------------
20 Hartstein $13,000
********** --------------
sum $13,000
80 Russell $14,000
Partners $13,500
********** --------------
sum $27,500
90 King $24,000
Kochhar $17,000
De Haan $17,000
********** --------------
sum $58,000
--------------
sum $98,500
COMPANY CONFIDENTIAL
6 rows selected.
|
This chapter explains how to generate a HTML report containing your query results. This chapter covers the following topics:
In addition to plain text output, the SQL*Plus command-line interface enables you to generate either a complete web page, or HTML output which can be embedded in a web page. You can use SQLPLUS -MARKUP "HTML ON" or SET MARKUP HTML ON SPOOL ON to produce complete HTML pages automatically encapsulated with <HTML> and <BODY> tags.
By default, data retrieved with MARKUP HTML ON is output in HTML, though you can optionally direct output to the HTML <PRE> tag so that it displays in a web browser exactly as it appears in SQL*Plus. See the SQLPLUS MARKUP Options and the SET MARKUP command for more information about these commands.
SQLPLUS -MARKUP "HTML ON" is useful when embedding SQL*Plus in program scripts. On starting, it outputs the HTML and BODY tags before executing any commands. All subsequent output is in HTML until SQL*Plus terminates.
The -SILENT and -RESTRICT command-line options may be effectively used with -MARKUP to suppress the display of SQL*Plus prompt and banner information, and to restrict the use of some commands.
SET MARKUP HTML ON SPOOL ON generates an HTML page for each subsequently spooled file. The HTML tags in a spool file are closed when SPOOL OFF is executed or SQL*Plus exits.
You can use SET MARKUP HTML ON SPOOL OFF to generate HTML output suitable for embedding in an existing web page. HTML output generated this way has no <HTML> or <BODY> tags.
During a SQL*Plus session, use the SET MARKUP command interactively to write HTML to a spool file. You can view the output in a web browser.
SET MARKUP HTML ON SPOOL ON only specifies that SQL*Plus output will be HTML encoded, it does not create or begin writing to an output file. You must use the SQL*Plus SPOOL command to start generation of a spool file. This file then has HTML tags including <HTML> and </HTML>.
When creating a HTML file, it is important and convenient to specify a .html or .htm file extension which are standard file extensions for HTML files. This enables you to easily identify the type of your output files, and also enables web browsers to identify and correctly display your HTML files. If no extension is specified, the default SQL*Plus file extension is used.
You use SPOOL OFF or EXIT to append final HTML tags to the spool file and then close it. If you enter another SPOOL filename command, the current spool file is closed as for SPOOL OFF or EXIT, and a new HTML spool file with the specified name is created.
You can use the SET MARKUP command to enable or disable HTML output as required.
Example 7-1 Creating a Report Interactively
You can create HTML output in an interactive SQL*Plus session using the SET MARKUP command. You can include an embedded style sheet, or any other valid text in the HTML <HEAD> tag. Open a SQL*Plus session and enter the following:
SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON -
HEAD "<TITLE>Department Report</TITLE> -
<STYLE type='text/css'> -
<!-- BODY {background: #FFFFC6} --> -
</STYLE>" -
BODY "TEXT='#FF00Ff'" -
TABLE "WIDTH='90%' BORDER='5'"
You use the COLUMN command to control column output. The following COLUMN commands create new heading names for the SQL query output. The first command also turns off entity mapping for the DEPARTMENT_NAME column to allow HTML hyperlinks to be correctly created in this column of the output data:
COLUMN DEPARTMENT_NAME HEADING 'DEPARTMENT' ENTMAP OFF COLUMN CITY HEADING 'CITY'
SET MARKUP HTML ON SPOOL ON enables SQL*Plus to write HTML to a spool file. The following SPOOL command triggers the writing of the <HTML> and <BODY> tags to the named file:
SPOOL report.html
After the SPOOL command, anything entered or displayed on standard output is written to the spool file, report.html.
Enter a SQL query:
SELECT '<A HREF="http://oracle.com/'||DEPARTMENT_NAME||'.html">'||DEPARTMENT_NAME||'</A>' DEPARTMENT_NAME, CITY FROM EMP_DETAILS_VIEW WHERE SALARY>12000;
Enter the SPOOL OFF command:
SPOOL OFF
The </BODY> and </HTML> tags are appended to the spool file, report.html, before it is closed.
The output from report.sql is a file, report.html, that can be loaded into a web browser. Open report.html in your web browser. It should appear something like the following:
In this example, the prompts and query text have not been suppressed. Depending on how you invoke a script, you can use SET ECHO OFF or command-line -SILENT options to do this.
The SQL*Plus commands in this example contain several items of usage worth noting:
The hyphen used to continue lines in long SQL*Plus commands.
The TABLE option to set table WIDTH and BORDER attributes.
The COLUMN command to set ENTMAP OFF for the DEPARTMENT_NAME column to enable the correct formation of HTML hyperlinks. This makes sure that any HTML special characters such as quotes and angle brackets are not replaced by their equivalent entities, ", &, < and >.
The use of quotes and concatenation characters in the SELECT statement to create hyperlinks by concatenating string and variable elements.
View the report.html source in your web browser, or in a text editor to see that the table cells for the Department column contain fully formed hyperlinks as shown:
<html>
<head>
<TITLE>Department Report</TITLE> <STYLE type="text/css">
<!-- BODY {background: #FFFFC6} --> </STYLE>
<meta name="generator" content="SQL*Plus 10.2.0.1">
</head>
<body TEXT="#FF00Ff">
SQL> SELECT '<A HREF="http://oracle.com/'
||DEPARTMENT_NAME||'.html">'||DEPARTMENT_NAME
||'</A>' DEPARTMENT_NAME, CITY
<br>
2 FROM EMP_DETAILS_VIEW
<br>
3* WHERE SALARY>12000
<br>
<p>
<table WIDTH="90%" BORDER="5">
<tr><th>DEPARTMENT</th><th>CITY</th></tr>
<tr><td><A HREF="http://oracle.com/Executive.html">Executive</A></td>
<td>Seattle</td></tr>
<tr><td><A HREF="http://oracle.com/Executive.html">Executive</A></td>
<td>Seattle</td></tr>
<tr><td><A HREF="http://oracle.com/Executive.html">Executive</A></td>
<td>Seattle</td></tr>
<tr><td><A HREF="http://oracle.com/Sales.html">Sales</A></td>
<td>Oxford</td></tr>
<tr><td><A HREF="http://oracle.com/Sales.html">Sales</A></td>
<td>Oxford</td></tr>
<tr><td><A HREF="http://oracle.com/Marketing.html">Marketing</A></td>
<td>Toronto</td></tr>
</table>
<p>
6 rows selected.<br>
SQL> spool off
<br>
</body>
</html>
Example 7-2 Creating a Report using the SQLPLUS Command
Enter the following command at the operating system prompt:
SQLPLUS -S -M "HTML ON TABLE 'BORDER="2"'" HR@Ora10g @depart.sql>depart.html
where depart.sql contains:
SELECT DEPARTMENT_NAME, CITY FROM EMP_DETAILS_VIEW WHERE SALARY>12000; EXIT
This example starts SQL*Plus with user "HR", prompts for the HR password, sets HTML ON, sets a BORDER attribute for TABLE, and runs the script depart.sql. The output from depart.sql is a web page which, in this case, has been redirected to the file depart.html using the ">" operating system redirect command (it may be different on your operating system). It could be sent to a web browser if SQL*Plus was called in a web server CGI script. See Suppressing the Display of SQL*Plus Commands in Reports for information about calling SQL*Plus from a CGI script.
Start your web browser and enter the appropriate URL to open depart.html:
The SQLPLUS command in this example contains three layers of nested quotes. From the inside out, these are:
"2" is a quoted HTML attribute value for BORDER.
'BORDER="2"' is the quoted text argument for the TABLE option.
"HTML ON TABLE 'BORDER="2"'" is the quoted argument for the -MARKUP option.
The nesting of quotes may be different in some operating systems or program scripting languages.
The SQLPLUS -SILENT option is particularly useful when used in combination with -MARKUP to generate embedded SQL*Plus reports using CGI scripts or operating system scripts. It suppresses the display of SQL*Plus commands and the SQL*Plus banner. The HTML output shows only the data resulting from your SQL query.
You can also use SET ECHO OFF to suppress the display of each command in a script that is executed with the START command.
Certain characters, <, >, " and & have a predefined meaning in HTML. In the previous example, you may have noticed that the > character was replaced by > as soon as you entered the SET MARKUP HTML ON command. To enable these characters to be displayed in your web browser, HTML provides character entities to use instead.
Table 7-1 Equivalent HTML Entities
| Character | HTML Entity | Meaning |
|---|---|---|
|
< |
< |
Start HTML tag label |
|
> |
> |
End HTML tag label |
|
" |
" |
Double quote |
|
& |
& |
Ampersand |
The web browser displays the > character, but the actual text in the HTML encoded file is the HTML entity, >. The SET MARKUP option, ENTMAP, controls the substitution of HTML entities. ENTMAP is set ON by default. It ensures that the characters <, >, " and & are always replaced by the HTML entities representing these characters. This prevents web browsers from misinterpreting these characters when they occur in your SQL*Plus commands, or in data resulting from your query.
You can set ENTMAP at a global level with SET MARKUP HTML ENTMAP ON, or at a column level with COLUMN column_name ENTMAP ON.
Syntax
ATTR[IBUTE] [type_name.attribute_name [option ...]]
where option represents one of the following clauses:
ALI[AS] aliasCLE[AR]FOR[MAT] formatLIKE {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 twenty 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
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
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.
There are eight variables defined during SQL*Plus installation. These variables only differ from user defined variables by having predefined values.
Table 12-3 Variables Predefined at SQL*Plus Installation
| Variable Name | Contains |
|---|---|
|
Connection identifier used to make connection, where available. | |
|
Current date, or a user defined fixed string. | |
|
Current version of the installed Oracle Database. | |
|
Full release number of the installed Oracle Database. | |
|
Privilege level of the current connection. | |
|
Full release number of installed SQL*Plus component. | |
|
User name used to make connection. |
Contains the connection identifier as supplied by the user to make a connection where it is available.
Contains either the current date as a dynamic variable, or a fixed string. The current date is the default and 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.
Users 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.
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
Contains the current version of the installed Oracle Database in the form:
Oracle Database 11g Release 11.2.0.1.0 - Production
Contains the full release number of the installed Oracle Database in the form:
1102000100
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 Language Reference for information on AS SYSDBA and AS SYSOPER privileges. |
Contains the full release number of the installed SQL*Plus component in the form:
1102000100
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 = "1102000100" (CHAR) |
This section describes new features of the SQL*Plus Release 11.2 and provides pointers to additional information.
SQL*Plus Release 11.2.0.2 is a superset of SQL*Plus 11.2. This section describes new features introduced in SQL*Plus Release 11.2.0.2.
SET XMLOPTIMIZATIONCHECK
A new SET command specifies that only fully optimized XML queries and DML operations are executed. It is only to assist in developing and debugging, not for production.
Syntax
TTI[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 top of each report page. Enter TTITLE with no clauses to list its current definition. The old form of TTITLE is used if only a single word or string in quotes follows the TTITLE command.
See TTI[TLE] text (obsolete old form) for a description of the old form of TTITLE.
Terms
These terms and clauses also apply to the BTITLE command.
text
The title text. Enter text in single quotes if you want to place more than one word on a single line.
variable
A substitution variable or any of the following system-maintained values, SQL.LNO (the current line number), SQL.PNO (the current page number), SQL.RELEASE (the current Oracle Database release number), SQL.SQLCODE (the current error code), or SQL.USER (the current username).
To print one of these values, reference the appropriate variable in the title. You can format variable with the FORMAT clause.
SQL*Plus substitution variables (& variables) are expanded before TTITLE is executed. The resulting string is stored as the TTITLE text. During subsequent execution for each page of results, the expanded value of a variable may itself be interpreted as a substitution variable with unexpected results.
You can avoid this double substitution in a TTITLE 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 TTITLE, enclose it in quotes so that it is only substituted once.
OFF
Turns the title off (suppresses its display) without affecting its definition.
ON
Turns the title on (restores its display). When you define a top title, SQL*Plus automatically sets TTITLE to ON.
COL n
Indents to column n of the current line (backward if column n has been passed). Here "column" means print position, not table column.
S[KIP] [n]
Skips to the start of a new line n times; if you omit n, one time; if you enter zero for n, backward to the start of the current line.
TAB n
Skips forward n columns (backward if you enter a negative value for n). "Column" in this context means print position, not table column.
LE[FT] | CE[NTER] | R[IGHT]
Left-align, center, and right-align data on the current line respectively. SQL*Plus aligns following data items as a group, up to the end of the printspec or the next LEFT, CENTER, RIGHT, or COL command. CENTER and RIGHT use the SET LINESIZE value to calculate the position of the data item that follows.
BOLD
Prints data in bold print. SQL*Plus represents bold print on your terminal by repeating the data on three consecutive lines. On some operating systems, SQL*Plus may instruct your printer to print bold text on three consecutive lines, instead of bold.
FORMAT text
Specifies a format model that determines the format of following data items, up to the next FORMAT clause or the end of the command. The format model must be a text constant such as A10 or $999. See the COLUMN command for more information on formatting and valid format models.
If the datatype of the format model does not match the datatype of a given data item, the FORMAT clause has no effect on that item.
If no appropriate FORMAT model precedes a given data item, SQL*Plus prints NUMBER values using the format specified by SET NUMFORMAT or, if you have not used SET NUMFORMAT, the default format. SQL*Plus prints DATE values according to the default format.
Enter TTITLE with no clauses to list the current TTITLE definition.
Usage
If you do not enter a printspec clause before the first occurrence of text, TTITLE left justifies the text. SQL*Plus interprets TTITLE in the new form if a valid printspec clause (LEFT, SKIP, COL, and so on) immediately follows the command name.
See COLUMN for information on printing column and DATE values in the top title.
You can use any number of constants and variables in a printspec. SQL*Plus displays them in the order you specify them, positioning and formatting each constant or variable as specified by the printspec clauses that precede it.
The length of the title you specify with TTITLE cannot exceed 2400 characters.
The continuation character (a hyphen) will not be recognized inside a single-quoted title text string. To be recognized, the continuation character must appear outside the quotes, as follows:
TTITLE CENTER 'Summary Report for' - > 'the Month of May'
Examples
To define "Monthly Analysis" as the top title and to left-align it, to center the date, to right-align the page number with a three-digit format, and to display "Data in Thousands" in the center of the next line, enter
TTITLE LEFT 'Monthly Analysis' CENTER '01 Jan 2003' - RIGHT 'Page:' FORMAT 999 SQL.PNO SKIP CENTER - 'Data in Thousands'
Monthly Analysis 01 Jan 2003 Page: 1
Data in Thousands
|
To suppress the top title display without changing its definition, enter
TTITLE OFF
Syntax
PAU[SE] [text]
where text represents the text you wish to display.
Displays the specified text then waits for the user to press RETURN.
Enter PAUSE followed by no text to display two empty lines.
Usage
Because PAUSE always waits for the user's response, it is best to use a message that tells the user explicitly to press [Return].
PAUSE reads input from the terminal (if a terminal is available) even when you have designated the source of the command input as a file.
See SET PAU[SE] {ON | OFF | text} for information on pausing between pages of a report.
Examples
To print "Adjust paper and press RETURN to continue." and to have SQL*Plus wait for the user to press [Return], you might include the following PAUSE command in a script:
SET PAUSE OFF PAUSE Adjust paper and press RETURN to continue. SELECT ...
SQL*Plus Instant Client is a standalone product with all the functionality of SQL*Plus command-line. It connects to existing remote Oracle databases, but does not include its own database. It is easy to install and uses significantly less disk space than the full Oracle Database Client installation required to use SQL*Plus command-line.
SQL*Plus Instant Client is available on platforms that support the OCI Instant Client. See the Oracle Call Interface Programmer's Guide for more information on the OCI Instant Client.
To install SQL*Plus Instant Client, you need two packages:
SQL*Plus Instant Client package.
Either the Basic OCI Instant Client package, or the lightweight OCI Instant Client package.
SQL*Plus Instant Client can be installed in two ways:
Download the packages from the Oracle Technology Network (OTN).
Copy the same files that are in the packages from an Oracle Database 11g Client Administrator installation.
Both the SQL*Plus and OCI packages must be from the same Oracle Database version, for example, 11.1.0.6.
SQL*Plus Instant Client using the Basic OCI package works with any NLS_LANG setting supported by the Oracle Database. It supports all character sets and language settings available in the Oracle Database.
SQL*Plus Instant Client using the lightweight OCI package displays error messages in English only and supports only specific character sets. It is significantly smaller than SQL*Plus Instant Client using the Basic OCI package.
Valid values for NLS_LANG parameters with the lightweight Instant Client are:
language can be any valid language supported by the Oracle Database, however, error messages are only reported in English.
territory can be any valid territory supported by the Oracle Database.
charset is one of the following character sets:
US7ASCII
WE8DEC
WE8MSWIN1252
WE8ISO8859P1
UTF8
AL16UTF16
AL32UTF8
For example:
NLS_LANG=AMERICAN_AMERICA.UTF8
See the Oracle Database Globalization Support Guide, and NLS_LANG Environment Variable for more information about NLS settings.
The OTN downloads for Linux are RPM packages. The OTN downloads for UNIX and Windows are zip files.
The SQL*Plus Instant Client package should never be installed on an ORACLE_HOME.
Download the RPM packages containing the SQL*Plus Instant Client package, and the OCI package from the OTN Instant Client page at http://www.oracle.com/technology/tech/oci/instantclient/instantclient.html. Both packages must be from the same version such as 11.1.0.6.
Use rpm -i for the initial install of the RPM packages, or rpm -u to upgrade to a newer version of the packages.
Configure SQL*Plus Instant Client. See Configuring SQL*Plus Instant Client.
Download the zip files containing the SQL*Plus Instant Client package, and the OCI package from the OTN Instant Client page at http://www.oracle.com/technology/tech/oci/instantclient/instantclient.html. Both packages must be from the same version such as 11.1.0.6.
Create a new directory, for example, /home/instantclient11_2 on UNIX or c:\instantclient11_2 on Windows.
Unzip the two packages into the new directory.
Configure SQL*Plus Instant Client. See Configuring SQL*Plus Instant Client.
Tables E-1, E-2 and E-3 list the required files from each of the SQL*Plus and OCI packages. The files from only one of the OCI packages are required. Other files installed that are not listed here can be ignored, or can be removed to save disk space.
Table D-1 Instant Client Files in the SQL*Plus Package
| Linux and UNIX | Windows | Description |
|---|---|---|
|
sqlplus |
sqlplus.exe |
SQL*Plus executable |
|
libsqlplus.so |
not applicable |
SQL*Plus library |
|
libsqlplusic.so |
orasqlplusic11.dll |
SQL*Plus data shared library |
Run the installer on the Oracle Database 11g Client Release media and choose the Administrator option.
Create a new directory, for example, /home/instantclient11_2 on UNIX and Linux, or c:\instantclient11_2 on Windows.
Copy the SQL*Plus Instant Client and the OCI Instant Client files to the new directory. All files must be copied from the same ORACLE_HOME.
See Installing SQL*Plus Instant Client on UNIX or Linux or Installing SQL*Plus Instant Client on Windows for a list of the files to copy.
Configure SQL*Plus Instant Client. See Configuring SQL*Plus Instant Client.
To install SQL*Plus Instant Client using the Basic OCI package on UNIX and Linux, copy the following files:
$ORACLE_HOME/instantclient/libociei.so $ORACLE_HOME/lib/libclntsh.so.11.1 $ORACLE_HOME/lib/libnnz11.so $ORACLE_HOME/lib/libsqlplus.so $ORACLE_HOME/lib/libsqlplusic.so $ORACLE_HOME/bin/sqlplus
To install SQL*Plus Instant Client using the lightweight OCI package on UNIX and Linux, copy the following files:
$ORACLE_HOME/instantclient/light/libociicus.so $ORACLE_HOME/lib/libclntsh.so.11.1 $ORACLE_HOME/lib/libnnz11.so $ORACLE_HOME/lib/libsqlplus.so $ORACLE_HOME/lib/libsqlplusic.so $ORACLE_HOME/bin/sqlplus
To install SQL*Plus Instant Client using the Basic OCI package on Windows, copy the following files:
ORACLE_HOME\instantclient\oraociei11.dll ORACLE_HOME\bin\oci.dll ORACLE_HOME\bin\orannzsbb11.dll ORACLE_HOME\bin\orasqlplusic11.dll ORACLE_HOME\bin\sqlplus.exe
To install SQL*Plus Instant Client using the lightweight OCI package on Windows, copy the following files:
ORACLE_HOME\instantclient\light\oraociicus11.dll ORACLE_HOME\bin\oci.dll ORACLE_HOME\bin\orannzsbb11.dll ORACLE_HOME\bin\orasqlplusic11.dll ORACLE_HOME\bin\sqlplus.exe
The SQL*Plus Instant Client executable should only be used with the matching version of the OCI Instant Client.
Note that no ORACLE_HOME or ORACLE_SID environment variables need to be set.
The RPMs downloaded from OTN install into Oracle specific sub-directories in the /usr file system. The sub-directory structure enables multiple versions of Instant Client to be available.
Add the name of the directory containing the Instant Client libraries to LD_LIBRARY_PATH. Remove any other Oracle directories.
For example, to set LD_LIBRARY_PATH on Solaris in the Bourne or Korn shells:
LD_LIBRARY_PATH=/usr/lib/oracle/11.2.0.1/client/lib:${LD_LIBRARY_PATH}
export LD_LIBRARY_PATH
Make sure the sqlplus executable installed from the RPM is the first found in your PATH. To test, enter which sqlplus which should return /usr/bin/sqlplus. If not, remove any other Oracle directories from PATH, or put /usr/bin before other SQL*Plus executables in PATH, or use an absolute or relative path to start SQL*Plus.
For example, to set PATH in the bash shell:
PATH=/usr/bin:${PATH}
export PATH
If you install multiple versions of SQL*Plus, you may need to change the symbolic link /usr/bin/sqlplus to the version of SQL*Plus matching the libraries in LD_LIBRARY_PATH. For 11.2.0.1, /usr/bin/sqlplus is a symbolic link to the SQL*Plus binary at /usr/lib/oracle/11.2.0.1/client/bin/sqlplus.
Set Oracle globalization variables required for your locale. A default locale will be assumed if no variables are set. See the Oracle Database Globalization Support Guide for more information.
For example:
NLS_LANG=AMERICAN_AMERICA.UTF8 export NLS_LANG
Add the name of the directory containing the Instant Client files to the appropriate shared library path LD_LIBRARY_PATH, LIBPATH or SHLIB_PATH. Remove any other Oracle directories.
For example on Solaris in the Bourne or Korn shells:
LD_LIBRARY_PATH=/home/instantclient11_2:${LD_LIBRARY_PATH}
export LD_LIBRARY_PATH
Add the directory containing the Instant Client files to the PATH environment variable. If it is not set, then an absolute or relative path must be used to start SQL*Plus. Remove any other Oracle directories from PATH. For example:
PATH=/home/instantclient11_2:${PATH}
export PATH
Set Oracle globalization variables required for your locale. A default locale will be assumed if no variables are set. See the Oracle Database Globalization Support Guide for more information.
For example:
NLS_LANG=AMERICAN_AMERICA.UTF8 export NLS_LANG
The environment may be configured using SET commands in a Windows command prompt or made permanent by setting Environment Variables in System Properties.
For example, to set environment variables in Windows 2000 using System Properties, open System from the Control Panel, click the Advanced tab and then click Environment Variables.
Add the directory containing the Instant Client files to the PATH system environment variable. Remove any other Oracle directories from PATH.
For example, add c:\instantclient11_2 to the beginning of PATH.
Set Oracle globalization variables required for your locale. A default locale will be assumed if no variables are set. See the Oracle Database Globalization Support Guide for more information. For example, to set NLS_LANG for a Japanese environment, create a user environment variable NLS_LANG set to JAPANESE_JAPAN.JA16EUC.
If you have installed the lightweight Instant Client, see Lightweight Instant Client for information about supported NLS_LANG settings.
SQL*Plus Instant Client is always 'remote' from any database server. To connect to a database you must specify the database using an Oracle Net connection identifier.
An example using an Easy Connection identifier to connect to the HR schema in the MYDB database running on mymachine is:
sqlplus hr/your_password@\"//mymachine.mydomain:port/MYDB\"
Alternatively you can use a Net Service Name:
sqlplus hr/your_password@MYDB
Net Service Names can be stored in a number of places, including LDAP. The use of LDAP is recommended to take advantage of the new features of Oracle Database 11g. See the Oracle Database Net Services Reference for more information.
If you want to use Net Service Names configured in a local Oracle Net tnsnames.ora file, then set the environment variable TNS_ADMIN to the directory containing the tnsnames.ora file. For example, on UNIX, if your tnsnames.ora file is in /home/user1 and it defines the Net Service Name MYDB2:
TNS_ADMIN=/home/user1 export TNS_ADMIN sqlplus hr@MYDB2
If TNS_ADMIN is not set, then an operating system dependent set of directories is examined to find tnsnames.ora. This search path includes looking in the directory specified by the ORACLE_HOME environment variable for network/admin/tnsnames.ora. This is the only reason to set the ORACLE_HOME environment variable for SQL*Plus Instant Client. If ORACLE_HOME is set when running Instant Client applications, it must be set to a directory that exists.
This example assumes the ORACLE_HOME environment variable is set, and the $ORACLE_HOME/network/admin/tnsnames.ora or ORACLE_HOME\network\admin\tnsnames.ora file defines the Net Service Name MYDB3:
sqlplus hr@MYDB3
The TWO_TASK (on UNIX) or LOCAL (on Windows) environment variable can be set to a connection identifier. This removes the need to explicitly enter the connection identifier whenever a connection is made in SQL*Plus or SQL*Plus Instant Client. This UNIX example connects to the database known as MYDB4:
TNS_ADMIN=/home/user1 export TNS_ADMIN TWO_TASK=MYDB4 export TWO_TASK sqlplus hr
On Windows, TNS_ADMIN and LOCAL may be set in the System Properties. See Configuring SQL*Plus Instant Client on Windows.
To connect AS SYSDBA or AS SYSOPER to perform DBA tasks, you need to set up an Oracle password file on the database server using the database's orapwd utility. Once this is configured, your SQL*Plus Instant Client connection string might look something like:
sqlplus sys@MYDB AS SYSDBA
See the Oracle Database Administrator's Guide for information on Oracle password files.
The SQL*Plus Instant Client package can be removed separately from the OCI Instant Client. After uninstalling the SQL*Plus Instant Client package, the remaining OCI Instant Client libraries enable custom written OCI programs or third party database utilities to connect to a database.
For installations on Linux from RPM packages, use rpm -e only on the SQL*Plus Instant Client package
or
For installations on UNIX and Windows, and installations on Linux from the Client Release media, manually remove the following SQL*Plus specific files:
Reset environment variables and remove tnsnames.ora if necessary.
For installations on Linux from RPM packages, use rpm -qa to find the SQL*Plus Instant Client and Basic OCI package names and run rpm -e to remove them
or
For installations on UNIX and Windows, and installations on Linux from the Client Release media, manually delete the directory containing the SQL*Plus executable and Oracle libraries.
See Installing SQL*Plus Instant Client from the 11g Client Release Media for a list of the files you copied to the directory.
Reset environment variables such as PATH, SQLPATH, LD_LIBRARY_PATH and TNS_ADMIN.
Remove tnsnames.ora if necessary.
Syntax
PRINT [variable ...]
where variable ... represents names of bind variables whose values you want to display.
Displays the current values of bind variables. For more information on bind variables, see your Oracle Database PL/SQL Language Reference.
Enter PRINT with no variables to print all bind variables.
Usage
Bind variables are created using the VARIABLE command. See VARIABLE for more information and examples.
You can control the formatting of the PRINT output just as you would query output. For more information, see the formatting techniques described in Formatting SQL*Plus Reports.
To automatically display bind variables referenced in a successful PL/SQL block or used in an EXECUTE command, use the AUTOPRINT clause of the SET command. See SET for more information.
Examples
The following example illustrates a PRINT command:
VARIABLE n NUMBER BEGIN :n := 1; END; /
PL/SQL procedure successfully completed. |
PRINT n
N ---------- 1 |
Syntax
CONN[ECT] [{logon | / | proxy} [AS {SYSOPER | SYSDBA | SYSASM}] [edition=value]]
where logon has the syntax:
username[/password] [@connect_identifier]
where proxy has the syntax:
proxyuser[username] [/password] [@connect_identifier]
|
Note: The brackets around username in proxy are required syntax, not an indication of an optional term. For example, to connect toscott through proxy user hr with password welcome1.
CONNECT hr[scott]/welcome1 |
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.
|
Warning: Including your password in plain text is a security risk. You can avoid this risk by omitting the password, and entering it only when the system prompts for it. |
To connect to a database using an enterprise user proxy, you must first configure the proxy. For information about configuring an enterprise user proxy, see the Oracle Database Enterprise User Security Administrator's Guide.
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 password, SQL*Plus prompts you for password. When prompting, SQL*Plus does not display password on your terminal screen.
See the PASSWORD command for information about changing your password in SQL*Plus.
An Oracle Net connect identifier. The exact syntax depends on the Oracle Net configuration. For more information, refer to the Oracle Net manual 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.
The value for the Oracle Session Edition. An edition enables two or more versions of an object in a database. It provides a staging area where changed objects can be loaded into the database, compiled, and executed during uptime. This is particularly useful to reduce downtime associated with patching an application. edition=value overrides any edition value specified in the ORA_EDITION environment variable. For more detailed information, see Oracle Database Advanced Application Developer's Guide.
/ (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.
In SQL*Plus command line, where applications use password credentials to connect to databases, it is possible to store the credentials in a client-side Oracle wallet. When you configure a client to use the Oracle wallet, applications can use the following syntax to connect to databases that use password authentication:
CONNECT /@database_alias
For information about configuring your client to use secure external password store and for information about managing credentials in it, see the Oracle Database Security Guide.
AS {SYSOPER | SYSDBA | SYSASM}
The AS clause enables privileged connections by users who have been granted SYSOPER, SYSDBA or SYSASM system privileges. You can use any one 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 Language Reference.
Examples
To connect across Oracle Net with username HR, to the database known by the Oracle Net alias as FLEETDB, enter
CONNECT HR@FLEETDB
For more information about setting up your password file, refer to the Oracle Database Administrator's Guide.
To connect to an instance on the current node as a privileged user named HR, enter
CONNECT HR AS SYSDBA
To connect to an instance on the current node as a privileged default user, enter
CONNECT / AS SYSDBA
This chapter contains descriptions of the SQL*Plus commands listed alphabetically. Each description contains the following parts:
| Section | Description |
|---|---|
| Syntax | Shows how to enter the command and provides a brief description of the basic uses of the command. |
| Terms | Describes the function of each term or clause appearing in the syntax. |
| Usage | Provides additional information on uses of the command and on how the command works. |
| Examples | Gives one or more examples of the command. |
You can continue a long SQL*Plus command by typing a hyphen at the end of the line and pressing Return. If you wish, you can type a space before typing the hyphen. SQL*Plus displays a right angle-bracket (>) as a prompt for each additional line.
You do not need to end a SQL*Plus command with a semicolon. When you finish entering the command, you can press Return. If you wish, however, you can enter a semicolon at the end of a SQL*Plus command.
This chapter provides information about how to tune SQL*Plus for better performance. It discusses the following topics:
For information about tuning Oracle Database, see the Oracle Database Performance Tuning Guide.
You can automatically get a report on the execution path used by the SQL optimizer and the statement execution statistics. The report is generated after successful SQL DML (that is, SELECT, DELETE, UPDATE and INSERT) statements. It is useful for monitoring and tuning the performance of these statements.
SQL*Plus report output may differ for DML if dynamic sampling is in effect.
You can control the report by setting the AUTOTRACE system variable.
| Autotrace Setting | Result |
|---|---|
| SET AUTOTRACE OFF | No AUTOTRACE report is generated. This is the default. |
| SET AUTOTRACE ON EXPLAIN | The AUTOTRACE report shows only the optimizer execution path. |
| SET AUTOTRACE ON STATISTICS | The AUTOTRACE report shows only the SQL statement execution statistics. |
| SET AUTOTRACE ON | The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics. |
| SET AUTOTRACE TRACEONLY | Like SET AUTOTRACE ON, but suppresses the printing of the user's query output, if any. If STATISTICS is enabled, query data is still fetched, but not printed. |
To use this feature, you must create a PLAN_TABLE table in your schema and then have the PLUSTRACE role granted to you. DBA privileges are required to grant the PLUSTRACE role. For information on how to grant a role and how to create the PLAN_TABLE table, see the Oracle Database SQL Language Reference.
For more information about the roles and the PLAN_TABLE, see the Oracle Database SQL Language Reference and the AUTOTRACE variable of the SET command.
Example 8-1 Creating a PLAN_TABLE
Run the following commands from your SQL*Plus session to create the PLAN_TABLE in the HR schema:
CONNECT HR @$ORACLE_HOME/rdbms/admin/utlxplan.sql
Table created. |
Example 8-2 Creating the PLUSTRACE Role
Run the following commands from your SQL*Plus session to create the PLUSTRACE role and grant it to the DBA:
CONNECT / AS SYSDBA @$ORACLE_HOME/sqlplus/admin/plustrce.sql drop role plustrace;
Role dropped. |
create role plustrace;
Role created. |
grant plustrace to dba with admin option;
Grant succeeded. |
Example 8-3 Granting the PLUSTRACE Role
Run the following commands from your SQL*Plus session to grant the PLUSTRACE role to the HR user:
CONNECT / AS SYSDBA GRANT PLUSTRACE TO HR;
Grant succeeded. |
The Execution Plan shows the SQL optimizer's query execution path. Execution Plan output is generated using EXPLAIN PLAN and DBMS_XPLAN.
For information about interpreting the output of DBMS_XPLAN, see the Oracle Database Performance Tuning Guide.
The statistics are recorded by the server when your statement executes and indicate the system resources required to execute your statement. The results include the following statistics.
| Database Statistic Name | Description |
|---|---|
| recursive calls | Number of recursive calls generated at both the user and system level. Oracle Database maintains tables used for internal processing. When Oracle Database needs to make a change to these tables, it internally generates an internal SQL statement, which in turn generates a recursive call. |
| db block gets | Number of times a CURRENT block was requested. |
| consistent gets | Number of times a consistent read was requested for a block |
| physical reads | Total number of data blocks read from disk. This number equals the value of "physical reads direct" plus all reads into buffer cache. |
| redo size | Total amount of redo generated in bytes |
| bytes sent through SQL*Net to client | Total number of bytes sent to the client from the foreground processes. |
| bytes received through SQL*Net from client | Total number of bytes received from the client over Oracle Net. |
| SQL*Net round-trips to/from client | Total number of Oracle Net messages sent to and received from the client |
| sorts (memory) | Number of sort operations that were performed completely in memory and did not require any disk writes |
| sorts (disk) | Number of sort operations that required at least one disk write |
| rows processed | Number of rows processed during the operation |
The client referred to in the statistics is SQL*Plus. Oracle Net refers to the generic process communication between SQL*Plus and the server, regardless of whether Oracle Net is installed. You cannot change the default format of the statistics report.
For a more complete list of database statistics, see the Oracle Database Reference. For more information about the statistics and how to interpret them, see Chapter 3, "Gathering Optimizer Statistics" in the Oracle Database Performance Tuning Guide.
Example 8-4 Tracing Statements for Performance Statistics and Query Execution Path
If the SQL buffer contains the following statement:
SELECT E.LAST_NAME, E.SALARY, J.JOB_TITLE FROM EMPLOYEES E, JOBS J WHERE E.JOB_ID=J.JOB_ID AND E.SALARY>12000;
The statement can be automatically traced when it is run:
SET AUTOTRACE ON /
LAST_NAME SALARY JOB_TITLE
------------------------- ---------- -----------------------------------
King 24000 President
De Haan 17000 Administration Vice President
Kochhar 17000 Administration Vice President
Partners 13500 Sales Manager
Russell 14000 Sales Manager
Hartstein 13000 Marketing Manager
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2988506077
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 360 | 6 (17)| 00:00:01 |
|* 1 | HASH JOIN | | 6 | 360 | 6 (17)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| EMPLOYEES| 6 | 204 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| JOBS | 19 | 494 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."JOB_ID"="J"."JOB_ID")
2 - filter("E"."SALARY">12000)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
706 bytes sent via SQL*Net to client
496 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed
|
Example 8-5 Tracing Statements Without Displaying Query Data
To trace the same statement without displaying the query data, enter:
SET AUTOTRACE TRACEONLY /
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2988506077
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 360 | 6 (17)| 00:00:01 |
|* 1 | HASH JOIN | | 6 | 360 | 6 (17)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| EMPLOYEES| 6 | 204 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| JOBS | 19 | 494 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."JOB_ID"="J"."JOB_ID")
2 - filter("E"."SALARY">12000)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
706 bytes sent via SQL*Net to client
496 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed
|
This option is useful when you are tuning a large query, but do not want to see the query report.
|
Note: Your output may vary depending on the server version and configuration. |
Use the SQL*Plus TIMING command to collect and display data on the amount of computer resources used to run one or more commands or blocks. TIMING collects data for an elapsed period of time, saving the data on commands run during the period in a timer.
See the TIMING command, and Tracing Statements for information about using AUTOTRACE to collect statistics.
To delete all timers, enter CLEAR TIMING.
When you trace a statement in a parallel or distributed query, the Execution Plan output depends on the statement you use.
Example 8-6 Tracing Statements With Parallel Query Option
To trace a parallel query running the parallel query option:
create table D2_t1 (unique1 number) parallel - (degree 6);
Table created. |
create table D2_t2 (unique1 number) parallel - (degree 6);
Table created. |
create unique index d2_i_unique1 on d2_t1(unique1);
Index created. |
set long 500 longchunksize 500 SET AUTOTRACE ON EXPLAIN SELECT /*+ INDEX(B,D2_I_UNIQUE1) USE_NL(B) ORDERED - */ COUNT (A.UNIQUE1) FROM D2_T2 A, D2_T1 B WHERE A.UNIQUE1 = B.UNIQUE1;
Execution Plan
----------------------------------------------------------
Plan hash value: 107954098
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |Bytes| Cost(%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 1 (0)| 00:00:01 | | | |
| 1 | SORT AGGREGATE | | 1 | 26 | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001| 1 | 26 | | | Q1,01 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 26 | | | Q1,01 | PCWP | |
| 5 | NESTED LOOPS | | 1 | 26 | 1 (0)| 00:00:01 | Q1,01 | PCWP | |
| 6 | PX RECEIVE | | | | | | Q1,01 | PCWP | |
| 7 | PX SEND BROADCAST | :TQ10000| | | | | Q1,00 | P->P | BROADCAST |
| 8 | PX BLOCK ITERATOR | | 1 | 13 | 0 (0)| 00:00:01 | Q1,00 | PCWC | |
| 9 | TABLE ACCESS FULL| D2_T2 | 1 | 13 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
| 10 | PX BLOCK ITERATOR | | 1 | 13 | 2 (0)| 00:00:01 | Q1,01 | PCWC | |
|* 11 | TABLE ACCESS FULL | D2_T1 | 1 | 13 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
11 - filter("A"."UNIQUE1"="B"."UNIQUE1")
Note
-----
- dynamic sampling used for this statement
|
Example 8-7 To monitor disk reads and buffer gets.
SET AUTOTRACE TRACEONLY STATISTICS
The following shows typical results:
Statistics
----------------------------------------------------------
467 recursive calls
27 db block gets
147 consistent gets
20 physical reads
4548 redo size
502 bytes sent via SQL*Net to client
496 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
14 sorts (memory)
0 sorts (disk)
1 rows processed
|
If consistent gets or physical reads are high relative to the amount of data returned, it indicates that the query is expensive and needs to be reviewed for optimization. For example, if you are expecting less than 1,000 rows back and consistent gets is 1,000,000 and physical reads is 10,000, further optimization is needed.
|
Note: You can also monitor disk reads and buffer gets using V$SQL or TKPROF. |
Execution Plan output from Oracle Database 9i Release 2 (9.2) or earlier is different.
Each line of the Execution Plan has a sequential line number. SQL*Plus also displays the line number of the parent operation.
The Execution Plan consists of four columns displayed in the following order:
| Column Name | Description |
|---|---|
ID_PLUS_EXP | Shows the line number of each execution step. |
PARENT_ID_PLUS_EXP | Shows the relationship between each step and its parent. This column is useful for large reports. |
PLAN_PLUS_EXP | Shows each step of the report. |
OBJECT_NODE_PLUS_EXP | Shows database links or parallel query servers used. |
The format of the columns may be altered with the COLUMN command. For example, to stop the PARENT_ID_PLUS_EXP column being displayed, enter
COLUMN PARENT_ID_PLUS_EXP NOPRINT
The Execution Plan output is generated using the EXPLAIN PLAN command.
When you trace a statement in a parallel or distributed query, the Execution Plan shows the cost based optimizer estimates of the number of rows (the cardinality). In general, the cost, cardinality and bytes at each node represent cumulative results. For example, the cost of a join node accounts for not only the cost of completing the join operations, but also the entire costs of accessing the relations in that join.
Lines marked with an asterisk (*) denote a parallel or remote operation. Each operation is explained in the second part of the report. See the Oracle Database Performance Tuning Guide for more information on parallel and distributed operations.
The second section of this report consists of three columns displayed in the following order
| Column Name | Description |
|---|---|
ID_PLUS_EXP | Shows the line number of each execution step. |
OTHER_TAG_PLUS_EXP | Describes the function of the SQL statement in the OTHER_PLUS_EXP column. |
OTHER_PLUS_EXP | Shows the text of the query for the parallel server or remote database. |
The format of the columns may be altered with the COLUMN command.
Most performance benefit comes from tuning SQL queries executed in a script. This is done with tools like SQL*Plus's AUTOTRACE command. It involves restructuring queries to make best use of the Oracle Database SQL optimizer. For information about Tuning SQL statements, see the Oracle Database Performance Tuning Guide.
The performance gains made by tuning SQL*Plus-specific commands are smaller, but could be important for some applications. The following system variables and commands can influence SQL*Plus performance.
COLUMN NOPRINT turns off screen output and printing of the column heading and all values selected for the column.
It is better to remove an unneeded column from a SELECT then it is to use COLUMN NOPRINT to stop it displaying. Removing the column from the query means the SQL engine does not need to process it, or need to transfer the column data back to SQL*Plus.
Sets automatic registering of scripts through the DBMS_APPLICATION_INFO package. Setting APPINFO OFF prevents administrators monitoring the performance and resource usage of scripts.
If many SQL scripts are being called, then turning APPINFO OFF stops internal SQL*Plus calls to the database DBMS_APPLICATION_INFO package.
Sets the number of rows that SQL*Plus will fetch from the database at one time. Valid values are 1 to 5000.
The effectiveness of setting ARRAYSIZE depends on how well Oracle Database fills network packets and your network latency and throughput. In recent versions of SQL*Plus and Oracle Database, ARRAYSIZE may have little effect. Overlarge sizes can easily take more SQL*Plus memory which may decrease overall performance.
SET DEFINE OFF disables the parsing of commands to replace substitution variables with their values.
SET FLUSH OFF enables the operating system to buffer output. ON disables buffering and flushes output to the screen. Any benefit from setting FLUSH either ON or OFF depends on your operating system and data. The gain may be marginal.
Use OFF only when you run a script that does not require user interaction and whose output you do not need to see until the script finishes running.
SET LINESIZE sets the total number of characters that SQL*Plus displays on one line before beginning a new line.
Keep LINESIZE as small as possible to avoid extra memory allocations and memory copying.
However, if LINESIZE is too small, columns that cannot fit next to each other are put on separate lines. This may reduce performance significantly.
SET LONGCHUNKSIZE sets the size of the increments SQL*Plus uses to retrieve a BLOB, BFILE, CLOB, LONG, NCLOB or XMLType value.
Experiment with different sizes if LONGS or LOBs are being fetched.
Sets the number of lines on each page of output.
Increase PAGESIZE to avoid printing headings frequently, or set it to 0 to prevent headings being displayed.
SET SERVEROUTPUT OFF suppresses the display output (DBMS_OUTPUT.PUT_LINE) of stored procedures or PL/SQL blocks in SQL*Plus.
Setting SERVEROUTPUT OFF stops internal SQL*Plus calls to the DBMS_OUTPUT package done after user SQL statements.
Sets the SQL*Plus command prompt.
Use the default prompt, "SQL> " to stop variable substitution occurring each time the prompt is displayed.
Determines how SQL*Plus formats white space in terminal output.
Setting TAB ON causes multiple spaces to be compressed in terminal output. Unless this significantly reduces the written data, the processing required may marginally outweigh any benefit.
SET TERMOUT OFF suppresses the display so that you can spool output from a script without seeing it on the screen.
If both spooling to file and writing to terminal are not required, use SET TERMOUT OFF in SQL scripts to disable terminal output.
SET TRIMOUT ON or SET TRIMSPOOL ON removes trailing blanks at the end of each displayed or spooled line.
Setting these variables ON can reduce the amount of data written. However, if LINESIZE is optimal, it may be faster to set the variables OFF. The SQL*Plus output line is blank filled throughout the query processing routines so removing the spaces could take extra effort.
Deletes substitution variables that you defined either explicitly (with the DEFINE command) or implicitly (with an argument to the START command or COLUMN NEW_VAL|OLD_VAL).
Use the UNDEFINE command to remove unnecessary substitution variables. This can reduce the time taken for any operation that uses '&', new_value or old_value variables.
This chapter describes the available methods for controlling access to database tables, and SQL*Plus commands. It covers the following topics:
SQL*Plus uses the PRODUCT_USER_PROFILE (PUP) table, a table in the SYSTEM account, to provide product-level security that supplements the user-level security provided by the SQL GRANT and REVOKE commands and user roles.
DBAs can use the PUP table to disable certain SQL and SQL*Plus commands in the SQL*Plus environment on a per-user basis. SQL*Plus—not Oracle Database—enforces this security. DBAs can even restrict access to the GRANT, REVOKE, and SET ROLE commands to control users' ability to change their database privileges.
SQL*Plus reads restrictions from the PUP table when a user logs in to SQL*Plus and maintains those restrictions for the duration of the session. Changes to the PUP table will only take effect the next time the affected users log in to SQL*Plus.
When SYSTEM, SYS, or a user authenticating with SYSDBA or SYSOPER privileges connects or logs in, SQL*Plus does not read the PUP table. Therefore, no restrictions apply to these users.
The PUP table applies only to the local database. If accessing objects on a remote database through a database link, the PUP table for the remote database does not apply. The remote database cannot extract the username and password from the database link in order to determine that user's profile and privileges.
You can create the PUP table by running the script named PUPBLD with the extension SQL as SYSTEM. The exact format of the file extension and the location of the file are system dependent. See your DBA for more information.
|
Note: If the table is created incorrectly, all users other than privileged users will see a warning when connecting to Oracle Database that the PUP table information is not loaded. |
The PUP table has the following columns:
PRODUCT NOT NULL VARCHAR2 (30) USERID VARCHAR2(30) ATTRIBUTE VARCHAR2(240) SCOPE VARCHAR2(240) NUMERIC_VALUE NUMBER(15,2) CHAR_VALUE VARCHAR2(240) DATE_VALUE DATE LONG_VALUE LONG
The following list describes each column in the PUP table:
| PUP Column | Description |
|---|---|
| PRODUCT | Must contain the product name (in this case "SQL*Plus"). You cannot enter wildcards or NULL in this column. |
| USERID | Must contain the username (uppercase) of the user for whom you wish to disable the command. To disable the command for more than one user, use SQL wild cards (%) or make multiple entries. Thus, all of the following entries are valid:
|
| ATTRIBUTE | Must contain the name (in uppercase) of the SQL, SQL*Plus, or PL/SQL command to disable (for example, RUN). If you are disabling a role, it must contain the character string "ROLES". You cannot enter a wildcard. See PUP Table Administration for a list of SQL and SQL*Plus commands you can disable. See Creating and Controlling Roles for information on how to disable a role. |
| SCOPE | Not used, it is recommended that you enter NULL. Other products may store specific file restrictions or other data in this column. |
| NUMERIC_VALUE | Not used, it is recommended that you enter NULL. Other products may store numeric values in this column. |
| CHAR_VALUE | Must contain the character string "DISABLED" to disable a SQL, SQL*Plus, or PL/SQL command. If you are disabling a role, it must contain the name of the role you wish to disable. You cannot use a wildcard. See Disabling Commands with SQLPLUS -RESTRICT for information on disabling a role. |
| DATE_VALUE | Not used, it is recommended that you enter NULL. Other products may store DATE values in this column. |
| LONG_VALUE | Not used, it is recommended that you enter NULL. Other products may store LONG values in this column. |
The DBA username SYSTEM owns and has all privileges on the PUP table. Other Oracle Database usernames should have only SELECT access to this table, which enables a view of restrictions for that username and those restrictions assigned to PUBLIC. The script PUPBLD.SQL, when run, grants SELECT access on the PUP table to PUBLIC.
To disable a SQL or SQL*Plus command for a given user, insert a row containing the user's username in the Userid column, the command name in the Attribute column, and DISABLED in the Char_Value column. The Scope, Numeric_Value, and Date_Value columns should contain NULL. For example:
PRODUCT USERID ATTRIBUTE SCOPE NUMBERIC CHAR DATE LONG
VALUE VALUE VALUE VALUE
------- ------ --------- ----- -------- ------ ----- -----
SQL*Plus HR HOST DISABLED
SQL*Plus % INSERT DISABLED
SQL*Plus % UPDATE DISABLED
SQL*Plus % DELETE DISABLED
To re-enable commands, delete the row containing the restriction.
| SQL*Plus Commands That Can Be Disabled | |||
|---|---|---|---|
| ACCEPT | DEFINE | PASSWORD | SHUTDOWN |
| APPEND | DEL | PAUSE | SPOOL |
| ARCHIVE LOG | DESCRIBE | START (@, @@) | |
| ATTRIBUTE | DISCONNECT | PROMPT | STARTUP |
| BREAK | EDIT | RECOVER | STORE |
| BTITLE | EXECUTE | REMARK | TIMING |
| CHANGE | EXIT/QUIT | REPFOOTER | TTITLE |
| CLEAR | GET | REPHEADER | UNDEFINE |
| COLUMN | HELP (?) | RUN | VARIABLE |
| COMPUTE | HOST | SAVE | WHENEVER OSERROR |
| CONNECT | INPUT | SET | WHENEVER SQLERROR |
| COPY | LIST (;) | SHOW | XQUERY |
You can disable the following PL/SQL commands:
|
Note:
|
Example 9-1 Setting Restrictions in the PUP Table
This is an example of how to insert a row into the PUP table to restrict the user HR from using the SELECT statement:
Log in as SYSTEM with the command
SQLPLUS SYSTEM
Insert a row into the PUP table with the command:
INSERT INTO PRODUCT_USER_PROFILE
VALUES ('SQL*Plus', 'HR', 'SELECT', NULL, NULL, 'DISABLED', NULL, NULL);
Connect as HR and try to SELECT something:
CONNECT HR SELECT * FROM EMP_DETAILS_VIEW;
This command causes the following error message:
SP2-0544: Command SELECT disabled in Product User Profile |
To delete this row and remove the restriction from the user HR, CONNECT again as SYSTEM and enter:
DELETE FROM PRODUCT_USER_PROFILE WHERE USERID = 'HR';
You can use SQL commands to create and control access to roles to provide security for your database tables. By creating a role and then controlling who has access to it, you can ensure that only certain users have access to particular database privileges.
Roles are created and used with the SQL CREATE, GRANT, and SET commands:
To create a role, you use the CREATE command. You can create roles with or without passwords.
To grant access to roles, you use the GRANT command. In this way, you can control who has access to the privileges associated with the role.
To access roles, you use the SET ROLE command. If you created the role with a password, the user must know the password in order to access the role.
For more information about roles, see your Oracle Database SQL Language Reference, your Oracle Database Administrator's Guide, and your Oracle Database Concepts manual.
From SQL*Plus, users can submit any SQL command. In certain situations, this can cause security problems. Unless you take proper precautions, a user could use SET ROLE to access privileges obtained through an application role. With these privileges, they might issue SQL statements from SQL*Plus that could wrongly change database tables.
To prevent application users from accessing application roles in SQL*Plus, you can use the PUP table to disable the SET ROLE command. You also need to disable the BEGIN and SQL*Plus EXECUTE commands to prevent application users setting application roles through a PL/SQL block. This gives a SQL*Plus user only those privileges associated with the roles enabled when they started SQL*Plus. For more information about the creation and usage of user roles, see your Oracle Database SQL Language Reference and Oracle Database Administrator's Guide.
To disable a role for a given user, insert a row in the PUP table containing the user's username in the Userid column, "ROLES" in the Attribute column, and the role name in the Char_Value column.
|
Note: When you enter "PUBLIC" or "%" for the Userid column, you disable the role for all users. You should only use "%" or "PUBLIC" for roles which are granted to "PUBLIC". If you try to disable a role that has not been granted to a user, none of the roles for that user are disabled. |
The Scope, Numeric_Value, and Date_Value columns should contain NULL. For example:
PRODUCT USERID ATTRIBUTE SCOPE NUMERIC CHAR DATE LONG
VALUE VALUE VALUE VALUE
------- ------ --------- ----- -------- ------ ----- -----
SQL*Plus HR ROLES ROLE1
SQL*Plus PUBLIC ROLES ROLE2
During login, these table rows are translated into the command
SET ROLE ALL EXCEPT ROLE1, ROLE2
To ensure that the user does not use the SET ROLE command to change their roles after login, you can disable the SET ROLE command.
To re-enable roles, delete the row containing the restriction.
See Disabling SET ROLE for more information.
Like the Product User Profile table, the RESTRICT option enables you to disable certain commands that interact with the operating system. However, commands disabled with the -RESTRICT option are disabled even when no connection to a server exists, and remain disabled until SQL*Plus terminates.
The following table shows which commands are disabled in each restriction level.
| Command | Level 1 | Level 2 | Level 3 |
|---|---|---|---|
| EDIT | disabled | disabled | disabled |
| GET | disabled | ||
| HOST | disabled | disabled | disabled |
| SAVE | disabled | disabled | |
| SPOOL | disabled | disabled | |
| START | disabled | ||
| STORE | disabled | disabled |
|
Note:
|
For more information about the RESTRICT option, see the SQLPLUS RESTRICT Option.
Some operating systems allow any user to see what programs are being run. If the display also shows command-line arguments, it may be possible to view the usernames and passwords of other SQL*Plus users.
For example, on many UNIX or Li\nux systems the ps command shows program arguments. To stop passwords being displayed depends on how you use SQL*Plus.
To run SQL*Plus interactively, always wait for SQL*Plus to prompt for connection information, particularly your password.
To run a batch SQL script from a UNIX shell script, set environment variables MYUSERNAME and MYPASSWORD to the appropriate values. Run a shell script containing:
sqlplus /nolog <<EOF connect $MYUSERNAME/$MYPASSWORD select ... EOF
To run a batch SQL script, hard code the username and password as the first line of the SQL script. Then call the script with:
sqlplus @myscript.sql
When SQL*Plus is started like this, it uses the first line of the script as the username/password@connection_identifier string.
Avoid storing your username and password in files or scripts. If you do store your username and password in a file or script, ensure that the file or script is secured from non-authorized access.
This chapter helps you learn the basics of using SQL*Plus. It has the following topics:
Unless stated otherwise, descriptions of commands are applicable to all user interfaces.
In the command-line, type commands at the SQL*Plus prompt and press Return to execute them.
Usually, you separate the words in a command with a space or a tab. You can use additional spaces or tabs between words to make your commands more readable.
Case sensitivity is operating system specific. For the sake of clarity, all table names, column names, and commands in this guide appear in capital letters.
You can enter three kinds of commands:
SQL commands, for working with information in the database
PL/SQL blocks, also for working with information in the database
SQL*Plus commands, for formatting query results, setting options, and editing and storing SQL commands and PL/SQL blocks
The manner in which you continue a command on additional lines, end a command, or execute a command differs depending on the type of command you wish to enter and run. Examples of how to run and execute these types of commands are found on the following pages.
The SQL buffer stores the most recently entered SQL command or PL/SQL block (but not SQL*Plus commands). The command or block remains in the buffer until replaced by the next SQL command or PL/SQL block. You can view the buffer contents with the LIST command.
You can execute the command or block in the SQL buffer using the RUN or /(slash) commands. RUN displays the command or block in the buffer before executing it. /(slash) executes the command or block in the buffer without displaying it first. For information about editing a command or block stored in the buffer see Editing Scripts in SQL*Plus Command-Line.
SQL*Plus does not store SQL*Plus commands, or the semicolon or slash characters you type to execute a command in the SQL buffer.
To see the definitions of each column in a given table or view, use the SQL*Plus DESCRIBE command.
Example 4-1 Using the DESCRIBE Command
To list the column definitions of the columns in the sample 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) |
|
Note: DESCRIBE accesses information in the Oracle Database data dictionary. You can also use SQL SELECT commands to access this and other information in the database. See your Oracle Database SQL Language Reference for details. |
To see the definition of a function or procedure, use the SQL*Plus DESCRIBE command.
Example 4-2 Using the DESCRIBE Command
To create and list the definition of a function called AFUNC, enter
create or replace function afunc (f1 varchar2, f2 number) return number as
begin
if (length(f1) > f2) then
return 1;
else
return 0;
end if;
end;
/
FUNCTION created. |
DESCRIBE afunc
FUNCTION afunc RETURNS NUMBER Argument Name Type In/Out Default? --------------- -------- -------- --------- F1 VARCHAR2 IN F2 NUMBER IN |
The SQL command language enables you to manipulate data in the database. See your Oracle Database SQL Language Reference for information on individual SQL commands.
Example 4-3 Entering a SQL Command
In this example, you will enter and execute a SQL command to display the employee number, name, job, and salary of each employee in the EMP_DETAILS_VIEW view.
At the command prompt, enter the first line of the command:
SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID, SALARY
If you make a mistake, use Backspace to erase it and re-enter. When you are done, press Return to move to the next line.
SQL*Plus displays a "2", the prompt for the second line. Enter the second line of the command:
FROM EMP_DETAILS_VIEW WHERE SALARY > 12000;
The semicolon (;) means that this is the end of the command. Press Return or click Execute. SQL*Plus processes the command and displays the results:
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
----------- ------------------------- ---------- --------------
100 King AD_PRES $24,000
101 Kochhar AD_VP $17,000
102 De Haan AD_VP $17,000
145 Russell SA_MAN $14,000
146 Partners SA_MAN $13,500
201 Hartstein MK_MAN $13,000
6 rows selected.
|
After displaying the results and the number of rows retrieved, SQL*Plus command-line displays the command prompt again. If you made a mistake and therefore did not get the results shown, re-enter the command.
The headings may be repeated in your output, depending on the setting of a system variable called PAGESIZE. Sometimes, the result from a query will not fit the available page width. You can use the system variable, LINESIZE, to set the width of the output in characters. See Setting Page Dimensions. Typically, LINESIZE is set to 80 in command-line. Whether you see the message stating the number of records retrieved depends on the setting of the system variable, FEEDBACK. See System Variables that Affect How Commands Run for more information.
Just as spoken language has syntax rules that govern the way we assemble words into sentences, SQL*Plus has syntax rules that govern how you assemble words into commands. You must follow these rules if you want SQL*Plus to accept and execute your commands.
You can divide your SQL command into separate lines at any points you wish, as long as individual words are not split. Thus, you can enter the query you entered in Example 4-3, "Entering a SQL Command" on three lines:
SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID FROM EMP_DETAILS_VIEW WHERE SALARY>12000;
In this guide, you will find most SQL commands divided into clauses, one clause on each line. In Example 4-3, "Entering a SQL Command", for instance, the SELECT and FROM clauses were placed on separate lines. Many people find this clearly visible structure helpful, but you may choose whatever line division makes commands most readable to you.
You can end a SQL command in one of three ways:
with a semicolon (;)
with a slash (/) on a line by itself
with a blank line
A semicolon (;) tells SQL*Plus that you want to run the command. Type the semicolon at the end of the last line of the command, as shown in Example 4-3, "Entering a SQL Command", and press Return or click Execute. SQL*Plus processes the command and also stores the command in the SQL buffer. See The SQL Buffer for details. If you mistakenly press Return before typing the semicolon, SQL*Plus prompts you with a line number for the next line of your command. Type the semicolon and press Return again or click Execute to run the command.
A slash (/) on a line by itself also tells SQL*Plus that you wish to run the command. Press Return at the end of the last line of the command. SQL*Plus prompts you with another line number. Type a slash and press Return again or click Execute. SQL*Plus executes the command and stores it in the buffer.
A blank line in a SQL statement or script tells SQL*Plus that you have finished entering the command, but do not want to run it yet. Press Return at the end of the last line of the command. SQL*Plus prompts you with another line number.
|
Note: You can change the way blank lines appear and behave in SQL statements using the SET SQLBLANKLINES command. For more information about changing blank line behavior, see the SET command. |
To execute commands this way, press Return again; SQL*Plus now prompts you with the SQL*Plus command prompt. SQL*Plus does not execute the command, but stores it in the SQL buffer. See The SQL Buffer for details. If you subsequently enter another SQL command, SQL*Plus overwrites the previous command in the buffer.
You can also use PL/SQL subprograms (called blocks) to manipulate data in the database. See your Oracle Database PL/SQL Language Reference for information on individual PL/SQL statements.
SQL*Plus treats PL/SQL subprograms in the same manner as SQL commands, except that a semicolon (;) or a blank line does not terminate and execute a block. Terminate PL/SQL subprograms by entering a period (.) by itself on a new line. You can also terminate and execute a PL/SQL subprogram by entering a slash (/) by itself on a new line.
You enter the mode for entering PL/SQL statements when:
You type DECLARE or BEGIN. After you enter PL/SQL mode in this way, type the remainder of your PL/SQL subprogram.
You type a SQL command (such as CREATE PROCEDURE) that creates a stored procedure. After you enter PL/SQL mode in this way, type the stored procedure you want to create.
SQL*Plus stores the subprograms you enter in the SQL buffer. Execute the current subprogram with a RUN or slash (/) command. A semicolon (;) is treated as part of the PL/SQL subprogram and will not execute the command.
SQL*Plus sends the complete PL/SQL subprogram to Oracle Database for processing (as it does SQL commands). See your Oracle Database PL/SQL Language Reference for more information.
You might enter and execute a PL/SQL subprogram as follows:
DECLARE
x NUMBER := 100;
BEGIN
FOR i IN 1..10 LOOP
IF MOD (i, 2) = 0 THEN --i is even
INSERT INTO temp VALUES (i, x, 'i is even');
ELSE
INSERT INTO temp VALUES (i, x, 'i is odd');
END IF;
x := x + 100;
END LOOP;
END;
.
/
Stored procedures are PL/SQL functions, packages, or procedures. To create stored procedures, you use the following SQL CREATE commands:
CREATE FUNCTION
CREATE LIBRARY
CREATE PACKAGE
CREATE PACKAGE BODY
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
Entering any of these commands places you in PL/SQL mode, where you can enter your PL/SQL subprogram. For more information, see Running PL/SQL Blocks. When you are done typing your PL/SQL subprogram, enter a period (.) on a line by itself to terminate PL/SQL mode. To run the SQL command and create the stored procedure, you must enter RUN or slash (/). A semicolon (;) will not execute these CREATE commands.
When you use CREATE to create a stored procedure, a message appears if there are compilation errors. To view these errors, you use SHOW ERRORS. For example:
SHOW ERRORS PROCEDURE ASSIGNVL
See SHOW for more information.
To execute a PL/SQL statement that references a stored procedure, you can use the SQL*Plus EXECUTE command. EXECUTE runs the PL/SQL statement that you enter immediately after the command. For example:
EXECUTE EMPLOYEE_MANAGEMENT.NEW_EMP('BLAKE')
See EXECUTE for more information.
You can use SQL*Plus commands to manipulate SQL commands and PL/SQL blocks and to format and print query results. SQL*Plus treats SQL*Plus commands differently than SQL commands or PL/SQL blocks.
To speed up command entry, you can abbreviate many SQL*Plus commands. For information on and abbreviations of all SQL*Plus commands, see SQL*Plus Command Reference.
Example 4-4 Entering a SQL*Plus Command
This example shows how you might enter a SQL*Plus command to change the format used to display the column SALARY of the sample view, EMP_DETAILS_VIEW.
Enter this SQL*Plus command:
COLUMN SALARY FORMAT $99,999 HEADING 'MONTHLY SALARY'
If you make a mistake, use Backspace to erase it and re-enter. When you have entered the line, press Return. SQL*Plus notes the new format and displays the SQL*Plus command prompt again, ready for a new command.
Enter the following query and press Return to run it:
SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID, SALARY FROM EMP_DETAILS_VIEW WHERE SALARY > 12000;
EMPLOYEE_ID LAST_NAME JOB_ID MONTHLY SALARY
----------- ------------------------- ---------- --------------
100 King AD_PRES $24,000
101 Kochhar AD_VP $17,000
102 De Haan AD_VP $17,000
145 Russell SA_MAN $14,000
146 Partners SA_MAN $13,500
201 Hartstein MK_MAN $13,000
6 rows selected.
|
The COLUMN command formatted the column SALARY with a dollar sign ($) and a comma (,) and gave it a new heading.
SQL*Plus commands have a different syntax from SQL commands or PL/SQL blocks.
You do not need to end a SQL*Plus command with a semicolon. When you finish entering the command, you can just press Return or click Execute. There is no need to end a SQL*Plus command with a semicolon.
You can continue a long SQL*Plus command by typing a hyphen at the end of the line and pressing Return. If you wish, you can type a space before typing the hyphen. SQL*Plus displays a right angle-bracket (>) as a prompt for each additional line.
For example:
COLUMN SALARY FORMAT $99,999 - HEADING 'MONTHLY SALARY'
Since SQL*Plus identifies the hyphen as a continuation character, entering a hyphen within a SQL statement is ignored by SQL*Plus. SQL*Plus does not identify the statement as a SQL statement until after the input processing has joined the lines together and removed the hyphen. For example, entering the following:
SELECT 200 - 100 FROM DUAL;
returns the error:
SELECT 200 100 FROM DUAL
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
|
To ensure that the statement is interpreted correctly, reposition the hyphen from the end of the first line to the beginning of the second line.
The SQL*Plus SET command controls many variables—called SET variables or system variables—which affect the way SQL*Plus runs your commands. System variables control a variety of conditions within SQL*Plus, including default column widths for your output, whether SQL*Plus displays the number of records selected by a command, and your page size.
The examples in this guide are based on running SQL*Plus with the system variables at their default settings. Depending on the settings of your system variables, your output may appear slightly different than the output shown in the examples. (Your settings might differ from the default settings if you have a SQL*Plus LOGIN file on your computer.)
See the SET command for more information on system variables and their default settings. See SQL*Plus Configuration and SQLPLUS Program Syntax for details on the SQL*Plus LOGIN file.
To list the current setting of a system variable, enter SHOW followed by the variable name. See the SHOW command for information on other items you can list with SHOW.
Suppose you have displayed the first page of a 50 page report and decide you do not need to see the rest of it. Press Cancel, the system's interrupt character, which is usually CTRL+C. SQL*Plus stops the display.
|
Note: Pressing Cancel does not stop the printing of a file that you have sent to a printer with the OUT clause of the SQL*Plus SPOOL command. (You will learn about printing query results in Formatting SQL*Plus Reports.) You can stop the printing of a file through your operating system. For more information, see your operating system's installation and user's guide. |
You can execute an operating system command from the SQL*Plus command prompt. This is useful when you want to perform a task such as listing existing operating system files.
To run an operating system command, enter the SQL*Plus command HOST followed by the operating system command. For example, this SQL*Plus command runs the command, DIRECTORY *.SQL:
HOST DIRECTORY *.SQL
When the command finishes running, the SQL*Plus command prompt appears again.
|
Note: Operating system commands entered from a SQL*Plus session using the HOST command do not affect the current SQL*Plus session. For example, setting an operating system environment variable does not affect the current SQL*Plus session, but may affect SQL*Plus sessions started subsequently.You can suppress access to the HOST command. For more information about suppressing the HOST command see SQL*Plus Security. |
You can use the PAUSE system variable to stop and examine the contents of the screen after each page during the display of a long report, or during the display of a table definition with many columns.
You can use SET PAUSE to pause output after displaying each screen of a query or report. See SET PAU[SE] {ON | OFF | text} for more information.
You can specify changes you wish to make to the information stored in the database using the SQL Database Manipulation Language (DML) commands UPDATE, INSERT, and DELETE—which can be used independently or within a PL/SQL block. These changes are not made permanent until you enter a SQL COMMIT command or a SQL Database Control Language (DCL) or Database Definition Language (DDL) command (such as CREATE TABLE), or use the autocommit feature. The SQL*Plus autocommit feature causes pending changes to be committed after a specified number of successful SQL DML transactions. (A SQL DML transaction is either an UPDATE, INSERT, or DELETE command, or a PL/SQL block.)
You control the autocommit feature with the SQL*Plus AUTOCOMMIT system variable. Regardless of the AUTOCOMMIT setting, changes are committed when you exit SQL*Plus successfully.
Example 4-5 Turning Autocommit On
To turn the autocommit feature on, enter
SET AUTOCOMMIT ON
Alternatively, you can enter the following to turn the autocommit feature on:
SET AUTOCOMMIT IMMEDIATE
Until you change the setting of AUTOCOMMIT, SQL*Plus automatically commits changes from each SQL DML command that specifies changes to the database. After each autocommit, SQL*Plus displays the following message:
COMMIT COMPLETE |
When the autocommit feature is turned on, you cannot roll back changes to the datambase.
To commit changes to the database after a number of SQL DML commands, for example, 10, enter
SET AUTOCOMMIT 10
SQL*Plus counts SQL DML commands as they are executed and commits the changes after each 10th SQL DML command.
|
Note: For this feature, a PL/SQL block is regarded as one transaction, regardless of the actual number of SQL commands contained within it. |
To turn the autocommit feature off again, enter the following command:
SET AUTOCOMMIT OFF
To confirm that AUTOCOMMIT is now set to OFF, enter the following SHOW command:
SHOW AUTOCOMMIT
AUTOCOMMIT OFF |
See SET AUTO[COMMIT]{ON | OFF | IMM[EDIATE] | n} for more information.
If SQL*Plus detects an error in a command, it displays an error message. See SQL*Plus Error Messages for a list of SQL*Plus error messages.
Example 4-6 Interpreting an Error Message
If you attempt to execute a file that does not exist or is unavailable by entering:
START EMPLYYES.SQL
An error message indicates that the table does not exist:
SP2-0310: unable to open file "emplyyes.sql" |
You will often be able to figure out how to correct the problem from the message alone. If you need further explanation, take one of the following steps to determine the cause of the problem and how to correct it:
If the error is a numbered error beginning with the letters "SP2", look up the SQL*Plus message in SQL*Plus Error Messages.
If the error is a numbered error beginning with the letters "CPY" look up the SQL*Plus COPY command message in COPY Command Messages.
If the error is a numbered error beginning with the letters "ORA", look up the Oracle Database message in the Oracle Database Error Messages guide or in the platform-specific Oracle documentation provided for your operating system.
If the error is a numbered error beginning with the letters "PLS", look up the Oracle Database message in the Oracle Database PL/SQL Language Reference.
If the error is unnumbered, look up correct syntax for the command that generated the error in SQL*Plus Command Reference for a SQL*Plus command, in the Oracle Database SQL Language Reference for a SQL command, or in the Oracle Database PL/SQL Language Reference for a PL/SQL block. Otherwise, contact your DBA.
This appendix lists error messages with prefixes SP2- and CPY- generated by SQL*Plus:
For error messages with prefixes such as ORA-, TNS- and PLS- generated by Oracle Database, see the Oracle Database Error Messages guide.
Remove extra white space and comments.
Re-code to use fewer commands and/or shorter variable names.
Place sections of the block into stored (or packaged) procedures, and then call these procedures from the block.
command filename[.ext] [CR[EATE]|REP[LACE]|AP[PEND]]
where command can be SAVE, SPOOL or STORE SET
To use the command keywords CREATE, REPLACE, APPEND or one of their abbreviations as the filename, or to use the word FILE as the filename, you must enclose it in single quotes.
An error was encountered when creating the destination file.
A directory name specified in the SAVE statement was not found.
A system error made it impossible to open the file.
Check that the destination is valid and that there is sufficient space on the destination device.
Check the statement for a typing mistake in the directory name. Then issue the statement again after correcting the directory name.
An error was encountered when creating the destination file.
A directory name specified in the SAVE statement was not found.
A system error made it impossible to open the file.
Check that the destination is valid and that there is sufficient space on the destination device.
Check the statement for a typing mistake in the directory name. Then issue the statement again after correcting the directory name.
The file was not found under the specified name in the specified location.
File lacked the necessary privileges to open the file.
A system error made it impossible to open the file.
Make sure the file name specified is stored in the appropriate directory.
Make sure that the file has the privileges necessary for access. If it does not then change privileges accordingly.
Consult operating system documentation or contact the System Administrator.
sqlplus system @hlpbld.sql helpus.sql
Insufficient privileges to create a file.
A system error made it impossible to create a file.
Change privileges to allow creation of the file.
Consult the operating system documentation or contact the System Administrator.
This message is hard coded (in English) in the SQL*Plus source code so it can be displayed on message file error. It could never be read from this message file because the error occurs only when the message files cannot be opened. This entry in the message file is for documentation purposes only.
On Windows if the environment variable called SQLPLUS is set, it must contain the directory name of the SQL*Plus message files, for example ORACLE_HOME\sqlplus\mesg.
Also check that the file sp1XX.msb is in the $ORACLE_HOME/sqlplus/mesg or ORACLE_HOME\sqlplus\mesg directory. The "XX" stands for the country prefix associated with your NLS_LANG environment variable. SQL*Plus reads only one of the sp1XX.msb files. For example sp1ja.msb is read if NLS_LANG is JAPANESE_JAPAN.JA16EUC. If NLS_LANG is not set, then the default (English language) sp1us.msb is used. Check that the appropriate file is of non-zero size and that the file permissions allow it to be read. Note that ".msb" files are binary. The contents may be meaningless when viewed or printed. If you are unsure which language file is being used, unset NLS_LANG and run SQL*Plus to verify it can read the sp1us.msb file.
User's Guide and Reference
Release 11.2
E16604-02
August 2010
SQL*Plus User's Guide and Reference, Release 11.2
E16604-02
Copyright © 1996, 2010, Oracle and/or its affiliates. All rights reserved.
Primary Author: Simon Watt
Contributor: Luan Nim, Andrei Souleimanian, Senthilprabhu Dhamotharan, Mahantesh Savanur
This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.
The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.
If this software or related documentation is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, the following notice is applicable:
U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data delivered to U.S. Government customers are "commercial computer software" or "commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, the use, duplication, disclosure, modification, and adaptation shall be subject to the restrictions and license terms set forth in the applicable Government contract, and, to the extent applicable by the terms of the Government contract, the additional rights set forth in FAR 52.227-19, Commercial Computer Software License (December 2007). Oracle USA, Inc., 500 Oracle Parkway, Redwood City, CA 94065.
This software is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications which may create a risk of personal injury. If you use this software in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure the safe use of this software. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software in dangerous applications.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
This software and documentation may provide access to or information on content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services.
Syntax
L[IST] [n | n m | n * | n LAST | * | * n | * LAST | LAST]
Lists one or more lines of the SQL buffer.
The buffer has no command history list and does not record SQL*Plus commands. In SQL*Plus command-line you can also use ";" to list all the lines in the SQL buffer.
Terms
Enter LIST with no clauses, or ";" to list all lines. The last line listed becomes the new current line (marked by an asterisk).
Examples
To list the contents of the buffer, enter
LIST
or enter
;
1 SELECT LAST_NAME, DEPARTMENT_ID, JOB_ID 2 FROM EMP_DETAILS_VIEW 3 WHERE JOB_ID = 'SH_CLERK' 4* ORDER BY DEPARTMENT_ID |
The asterisk indicates that line 4 is the current line.
To list the second line only, enter
LIST 2
The second line is displayed:
2* FROM EMP_DETAILS_VIEW |
To list from the current line (now line 2) to the last line, enter
LIST * LAST
You will then see this:
2 FROM EMP_DETAILS_VIEW 3 WHERE JOB_ID = 'SH_CLERK' 4* ORDER BY DEPARTMENT_ID |
Syntax
HO[ST] [command]
where command represents an operating system command.
Executes an operating system command without leaving SQL*Plus.
Enter HOST without command to display an operating system prompt. You can then enter multiple operating system commands. For information on returning to SQL*Plus, refer to the platform-specific Oracle documentation provided for your operating system.
|
Note: Operating system commands entered from a SQL*Plus session using the HOST command do not affect the current SQL*Plus session. For example, setting an operating system environment variable only affects SQL*Plus sessions started subsequently.You can disable HOST. For more information about disabling HOST, see SQL*Plus Security. |
Usage
In some operating systems, you can use a character in place of HOST such as "$" in Windows or "!" in UNIX, or you may not have access to the HOST command. See the platform-specific Oracle documentation provided for your operating system or ask your DBA for more information.
On some platforms, an _RC substitution variable may be created with a HOST return value that is operation system dependent. It is recommended that you do not use the _RC substitution variable in scripts as it is not portable.
SQL*Plus removes the SQLTERMINATOR (a semicolon by default) before the HOST command is issued. A workaround for this is to add another SQLTERMINATOR. See SET SQLT[ERMINATOR] {; | c | ON | OFF} for more information.
Examples
To execute a UNIX operating system command, ls *.sql, enter
HOST ls *.sql
To execute a Windows operating system command, dir *.sql, enter
HOST dir *.sql
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
See the TTITLE command 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'
Syntax
SHO[W] option
where option represents one of the following terms or clauses:
system_variable ALL BTI[TLE]ERR[ORS] [ { FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY | TRIGGER | VIEW | TYPE | TYPE BODY | DIMENSION | JAVA CLASS } [schema.]name] LNO PARAMETERS [parameter_name] PNO RECYC[LEBIN] [original_name] REL[EASE] REPF[OOTER] REPH[EADER] SGA SPOO[L] SPPARAMETERS [parameter_name SQLCODE TTI[TLE] USER XQUERY
Shows the value of a SQL*Plus system variable or the current SQL*Plus environment. SHOW SGA requires a DBA privileged login.
Terms
system_variable
Represents any system variable set by the SET command.
ALL
Lists the settings of all SHOW options, except ERRORS and SGA, in alphabetical order.
BTI[TLE]
Shows the current BTITLE definition.
ERR[ORS] [{FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY | TRIGGER | VIEW | TYPE | TYPE BODY | DIMENSION | JAVA CLASS} [schema.]name]
Shows the compilation errors of a stored procedure (includes stored functions, procedures, and packages). After you use the CREATE command to create a stored procedure, a message is displayed if the stored procedure has any compilation errors. To see the errors, you use SHOW ERRORS.
When you specify SHOW ERRORS with no arguments, SQL*Plus shows compilation errors for the most recently created or altered stored procedure. When you specify the type (function, procedure, package, package body, trigger, view, type, type body, dimension, or java class) and the name of the PL/SQL stored procedure, SQL*Plus shows errors for that stored procedure. For more information on compilation errors, see your PL/SQL User's Guide and Reference.
schema contains the named object. If you omit schema, SHOW ERRORS assumes the object is located in your current schema.
SHOW ERRORS output displays the line and column number of the error (LINE/COL) as well as the error itself (ERROR). LINE/COL and ERROR have default widths of 8 and 65, respectively. You can use the COLUMN command to alter the default widths.
LNO
Shows the current line number (the position in the current page of the display and/or spooled output).
PARAMETERS [parameter_name]
Displays the current values for one or more initialization parameters. You can use a string after the command to see a subset of parameters whose names include that string. For example, if you enter:
SHOW PARAMETERS COUNT
NAME TYPE VALUE ------------------------------ ----- ----- db_file_multiblock_read_count integer 12 spin_count integer 0 |
The SHOW PARAMETERS command, without any string following the command, displays all initialization parameters.
Your output may vary depending on the version and configuration of the Oracle Database server to which you are connected. You need SELECT ON V_$PARAMETER object privileges to use the PARAMETERS clause, otherwise you will receive a message
ORA-00942: table or view does not exist
PNO
Shows the current page number.
RECYC[LEBIN] [original_name]
Shows objects in the recycle bin that can be reverted with the FLASHBACK BEFORE DROP command. You do not need to remember column names, or interpret the less readable output from the query:
SELECT * FROM USER_RECYCLEBIN
The query returns four columns displayed in the following order:
| Column Name | Description |
|---|---|
ORIGINAL NAME | Shows the original name used when creating the object. |
RECYCLEBIN NAME | Shows the name used to identify the object in the recyclebin. |
OBJECT TYPE | Shows the type of the object. |
DROP TIME | Shows the time when the object was dropped. |
The output columns can be formatted with the COLUMN command.
For DBAs, the command lists their own objects as they have their own user_recyclebin view.
REL[EASE]
Shows the release number of Oracle Database that SQL*Plus is accessing.
REPF[OOTER]
Shows the current REPFOOTER definition.
REPH[EADER]
Shows the current REPHEADER definition.
SPOO[L]
Shows whether output is being spooled.
SGA
Displays information about the current instance's System Global Area. You need SELECT ON V_$SGA object privileges otherwise you will receive a message
ORA-00942: table or view does not exist
SPPARAMETERS [parameter_name]
As for SHOW PARAMETERS except that SHOW SPPARAMETERS displays current values for initialization parameters for all instances. You can use a string after the command to see a subset of parameters whose names include that string.
The SHOW SPPARAMETERS command, without any string following the command, displays all initialization parameters for all instances.
Your output may vary depending on the version and configuration of the Oracle Database server to which you are connected. You need SELECT ON V_$PARAMETER object privileges to use the SPPARAMETERS clause.
SQLCODE
Shows the value of SQL.SQLCODE (the SQL return code of the most recent operation).
TTI[TLE]
Shows the current TTITLE definition.
USER
Shows the username you are currently using to access SQL*Plus. If you connect as "/ AS SYSDBA", then the SHOW USER command displays
USER is "SYS"
XQUERY
Shows the current values of the XQUERY settings, BASEURI, CONTEXT, NODE and ORDERING.
xquery BASEURI "public/scott" CONTEXT "doc("test.xml")" NODE byreference ORDERING ordered
The following output is displayed when no values are set:
xquery BASEURI "" CONTEXT "" NODE default ORDERING default
Examples
To display information about the SGA, enter
SHOW SGA
Total System Global Area 7629732 bytes Fixed Size 60324 bytes Variable Size 6627328 bytes Database Buffers 409600 bytes Redo Buffers 532480 bytes |
The following example illustrates how to create a stored procedure and then show its compilation errors:
CONNECT SYSTEM/MANAGER CREATE PROCEDURE HR.PROC1 AS BEGIN :P1 := 1; END; /
Warning: Procedure created with compilation errors. |
SHOW ERRORS PROCEDURE PROC1
NO ERRORS. |
SHOW ERRORS PROCEDURE HR.PROC1
Errors for PROCEDURE HR PROC1: LINE/COL ERROR -------------------------------------------------------- 3/3 PLS-00049: bad bind variable 'P1' |
To show whether AUTORECOVERY is enabled, enter
SHOW AUTORECOVERY
AUTORECOVERY ON |
To display the connect identifier for the default instance, enter
SHOW INSTANCE
INSTANCE "LOCAL" |
To display the location for archive logs, enter
SHOW LOGSOURCE
LOGSOURCE "/usr/oracle90/dbs/arch" |
To display objects that can be reverted with the FLASHBACK commands where CJ1 and ABC were objects dropped, enter:
SHOW RECYCLEBIN
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME -------------- ------------------ ------------ -------------------- CJ1 RB$$29458$TABLE$0 TABLE 2003-01-22:14:54:07 ABC RB$$29453$TABLE$0 TABLE 2003-01-20:18:50:29 |
To restore CJ1, enter
FLASHBACK TABLE CJ1 TO BEFORE DROP;
Globalization support enables the storing, processing and retrieval of data in native languages. The languages that can be stored in an Oracle database are encoded by Oracle Database-supported character sets. Globalization support ensures that database utilities, error messages, sort order, and date, time, monetary, numeric, and calendar conventions adjust to the native language and locale.
Topics:
For more information on globalization support, see the Oracle Technology Network globalization notes at http://otn.oracle.com/tech/globalization/, and see the Oracle Database Globalization Support Guide
SQL*Plus supports multiple languages through the NLS_LANG environment variable. To display another language in SQL*Plus, before starting SQL*Plus you must configure:
NLS_LANG in the SQL*Plus client environment.
The Oracle Database during installation.
The NLS_LANG environment variable has three components, each controlling a subset of the globalization features.
Your operating system and keyboard must be able to support the character set you have chosen. You may need to install additional support software. For more information about NLS_LANG, and software support, see the Oracle Database Globalization Support Guide.
Setting up locale specific behavior on the SQL*Plus client is achieved with the use of NLS parameters. These parameters may be specified in a number of ways, including as an initialization parameter on the server. For settings that control the behavior of the server, see the Oracle Database Globalization Support Guide.
NLS_LANG has the syntax:
NLS_LANG = language_territory.charset
where language specifies the conventions to use for Oracle Database messages, sorting order, day and month names. For example, to receive messages in Japanese, set language to JAPANESE. If language is not set, it defaults to AMERICAN.
where territory specifies the convention for default dates, and for monetary, and numeric formats. For example to use the Japanese territory format, set territory to JAPAN. If territory is not set, the default value is derived from the language value, and so is set to AMERICA.
where, in SQL*Plus command-line, charset specifies the character set encoding used by SQL*Plus for data processing, and is generally suited to that of the users terminal. Illogical combinations can be set, but will not work. For example, Japanese cannot be supported using a Western European character set such as:
NLS_LANG=JAPANESE_JAPAN.WE8DEC
However, Japanese could be supported with the Unicode character set. For example:
NLS_LANG=JAPANESE_JAPAN.UTF8
You can view the NLS_LANG setting by entering the SELECT command:
SELECT * FROM NLS_SESSION_PARAMETERS;
The NLS_TERRITORY and NLS_LANGUAGE values correspond to the language and territory components of the NLS_LANG variable.
You can also obtain a list of valid values for the NLS_SORT, NLS_LANGUAGE, NLS_TERRITORY and NLS_CHARACTERSET by querying the NLS dynamic performance view table V$NLS_VALID_VALUES.
You can set the NLS_LANG environment variable to control globalization features.
Example 11-1 Configuring Japanese Support in SQL*Plus on Windows
Ensure you have exited your current SQL*Plus session.
Open System from Start > Settings > Control Panel.
Click the Advanced tab and select Environment Variables.
Create a new environment variable, NLS_LANG, with a value of Japanese_Japan.JA16SJIS.
You may need to restart Windows for this setting to take effect.
Example 11-2 Configuring Japanese Support in SQL*Plus on UNIX
Ensure you have exited your current SQL*Plus session.
Set the NLS_LANG variable using either set or setenv depending on the UNIX shell you are using. For example, in csh, you would enter:
setenv NLS_LANG Japanese_Japan.UTF8
or
setenv NLS_LANG Japanese_Japan.JA16SJIS
or
setenv NLS_LANG Japanese_Japan.JA16EUC
The locale setting of your UNIX terminal determines the exact value of the NLS_LANG parameter. For more information on the NLS_LANG setting, see the Oracle Database Globalization Support Guide.
Example 11-3 Configuring Japanese Support in Oracle Database
To store data in the Japanese character set using UTF-8 character encoding, ensure that the Oracle database has been created with the AL32UTF8 character set. See your Oracle Database Installation Guide for information about creating your database in a character set other than US7ASCII.
Syntax
DEL [n | n m | n * | n LAST | * | * n | * LAST | LAST]
Deletes one or more lines of the buffer.
SQL*Plus commands are not stored in the buffer. There is no history of commands previously entered in the buffer.
Terms
Enter DEL with no clauses to delete the current line of the buffer.
Usage
DEL makes the following line of the buffer (if any) the current line. You can enter DEL several times to delete several consecutive lines.
|
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 Language 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 |
Syntax
XQUERY xquery_statement
The SQL*Plus XQUERY command enables you to perform an XQuery 1.0 query on a specified database. XQUERY is supported on Oracle Database 10g (Release 2) and later versions. Attempting to use XQUERY on an earlier version of the Oracle Database gives the error:
SP2-614 Server version too low
Terms
xquery_statement
Specifies the XQuery statement you want to run. The statement is entered with standard XQuery syntax. The XQUERY statement is terminated with a forward slash, '/'.
Usage
Prefix your XQuery statement with the SQL*Plus command, XQUERY, and terminate the XQUERY command with a slash (/). XQUERY is a SQL*Plus keyword. If XQueries are executed in other tools, the keyword may not be needed.
XML output from the XQUERY command is displayed as native XML according to the active SET command options. SET LONG typically needs to be set. It may be useful to consider the following settings:
Linesize for rows longer than the default 80 characters (SET LINESIZE).
LOB, LONG and XML Type Size for rows longer than the default 80 characters (SET LONG).
Output Page Setup to match output (SET PAGESIZE).
Display Headings to repress the "Result Sequence" column heading (SET HEADING OFF).
The XQUERY command requires an active database connection. The command will not work with SQLPLUS /NOLOG.
Bind variables are not supported in the XQUERY command.
There are four SET commands specific to the XQUERY command. The SHOW XQUERY command gives the status of these settings. They are:
Table 12-7 XQUERY SET commands
| SET Command |
|---|
|
SET XQUERY BASEURI |
|
SET XQUERY ORDERING |
|
SET XQUERY NODE |
|
SET XQUERY CONTEXT |
For more information about the SET XQUERY commands, see :
Examples
The XQuery statement in the following script queries the EMP_DETAILS_VIEW view of the HR schema:
set long 160
set linesize 160
xquery for $i in ora:view("EMP_DETAILS_VIEW") return $i
/
Result Sequence ------------------------------------------------------------------------------------------- <ROW><EMPNO>7369</EMPNO><ENAME>SMITH</ENAME><JOB>CLERK</JOB><MGR>7902</MGR><HIREDATE>17- DEC-80</HIREDATE><SAL>800</SAL><DEPTNO>20</DEPTNO></ROW> 14 item(s) selected. |
This chapter provides a brief overview of the database administration tools available in SQL*Plus, and discusses the following topics:
This chapter is intended for use by database administrators. You must have database administrator privileges to use this functionality.
For more information on database administration, see the Oracle Database Concepts manual.
Special operations such as starting up or shutting down a database are performed by a database administrator (DBA). The DBA has certain privileges that are not assigned to normal users. The commands outlined in this chapter would normally be used by a DBA.
For more information about security and roles in SQL*Plus, see SQL*Plus Security.
An Oracle database may not always be available to all users. To open or close a database, or to start up or shut down an instance, you must have DBA privileges or be connected as SYSOPER or SYSDBA. Other users cannot change the current status of an Oracle database.
Starting a database involves three steps:
Starting an instance
An instance controls the background processes and the allocation of memory area to access an Oracle database.
Mounting the database
Mounting the database associates it with a previously started instance.
Opening the database
Opening the database makes it available for normal database operations.
For more information about database startup, see the Oracle Database Concepts guide. For more information about starting a database, see the STARTUP command.
Example 10-1 Starting an Instance
To start an Oracle Database instance, without mounting the database, enter
STARTUP NOMOUNT
Example 10-2 Mounting the Database
To start an instance, mount the database, but leave the database closed, enter
STARTUP MOUNT
Example 10-3 Opening the Database
To start an instance using the Oracle Database Server parameter file INITSALES.ORA, mount and open the database named SALES, and restrict access to database administrators, enter
STARTUP OPEN sales PFILE=INITSALES.ORA RESTRICT
where SALES is the database name specified in the DB_NAME parameter in the INITSALES.ORA parameter file.
Shutting down a database involves three steps:
Closing the database
When a database is closed, all database and recovery data in the SGA are written to the datafiles and redo log files, and all online datafiles are closed.
Dismounting the database
Dismounting the database disassociates the database from an instance and closes the control files of the database.
Shutting down the instance
Shutting down an instance reclaims the SGA from memory and terminates the background Oracle Database processes that constitute an Oracle Database instance.
For more information about database shutdown, see the Oracle Database Concepts guide. For information about stopping a database, see the SHUTDOWN command.
Example 10-4 Shutting Down the Database
To shut down the database normally after it has been opened and mounted, enter
SHUTDOWN
|
Every Oracle database has a set of two or more redo log files. The set of redo log files for a database is collectively referred to as the database's redo log.
The redo log is used to record changes made to data. If, for example, there is a database failure, the redo log is used to recover the database. To protect against a failure involving the redo log itself, Oracle Database has a mirrored redo log so that two or more copies of the redo log can be maintained on different disks.
Operating a database in ARCHIVELOG mode enables the archiving of the online redo log.
The SQL ALTER SYSTEM command enables a complete recovery from disk failure as well as instance failure, because all changes made to the database are permanently saved in an archived redo log.
For more information about redo log files and database archiving modes, see the Oracle Database Concepts manual, and the ARCHIVE LOG command.
To list the details of the current log file being archived, enter
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 |
If a damaged database is in ARCHIVELOG mode, it is a candidate for either complete media recovery or incomplete media recovery operations. To begin media recovery operations use the RECOVER command. For more information about recovering data, see the RECOVER command.
In order to begin recovery operations, you must have DBA privileges.
To recover the database up to a specified time using a control backup file, enter
RECOVER DATABASE UNTIL TIME '1998-11-23:12:47:30'- USING BACKUP CONTROLFILE
To recover two offline tablespaces, enter
RECOVER TABLESPACE ts1, ts2
Make sure that the tablespaces you are interested in recovering have been taken offline, before proceeding with recovery for those tablespaces.
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. The @ command functions similarly to @@ and 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 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, SQL*Plus searches for a file with that filename and extension in the current default directory. If SQL*Plus does not find the file in the current directory, it searches a system-dependent path to find it. 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.
arg...
Represent data items you wish to pass to parameters in the script. If you enter one or more arguments, SQL*Plus substitutes the values into the parameters (&1, &2, and so forth) in the script. The first argument replaces each occurrence of &1, the second replaces each occurrence of &2, and so forth.
The @ command defines the parameters with the values given by the arguments; if you run the script again in this session, you can enter new arguments or omit the arguments to use the current values. For more information on using parameters, see Using Substitution Variables.
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
Syntax
PASSW[ORD] [username]
where username specifies the user. If omitted, username defaults to the current user.
Enables you to change a password without echoing it on an input device.
Usage
To change the password of another user, you must have been granted the appropriate privilege. See CONNECT for more information about changing your password.
Examples
If you want to change your current password, enter
PASSWORD Changing password for your_password Old password: your_password New password: new_password Retype new password: new_password Password changed |
If you are logged on as a DBA, and want to change the password for user johnw (currently identified by johnwpass) to johnwnewpass
PASSWORD johnw Changing password for johnw New password: johnwnewpass Retype new password: johnwnewpass Password changed |
Passwords are not echoed to the screen, they are shown here for your convenience.
Syntax
STA[RT] {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.
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]
The script you wish to execute. The file can contain any command that you can run interactively.
If you do not specify an extension, SQL*Plus assumes the default command-file extension (normally SQL). See SET SUF[FIX] {SQL | text} for information on changing the default extension.
When you enter START file_name.ext, SQL*Plus searches for a file with the filename and extension you specify in the current default directory. If SQL*Plus does not find such a file, SQL*Plus will search a system-dependent path to find the file. Some operating systems may not support the path search. See the platform-specific Oracle documentation provided for your operating system for specific information related to your operating system environment.
arg ...
Data items you wish to pass to parameters in the script. If you enter one or more arguments, SQL*Plus substitutes the values into the parameters (&1, &2, and so forth) in the script. The first argument replaces each occurrence of &1, the second replaces each occurrence of &2, and so on.
The START command defines the parameters with the values of the arguments; if you START the script again in this session, you can enter new arguments or omit the arguments to use the old values.
See Defining Substitution Variables and Using Substitution Variables for more information on using parameters.
Usage
All previous settings like COLUMN command settings stay in effect when the script starts. If the script changes any setting, then this new value stays in effect after the script has finished
The @ (at sign) and @@ (double at sign) commands function similarly to START. Disabling the START command in the Product User Profile also disables the @ and @@ commands. See @ (at sign) and @@ (double at sign) for further information on these commands. See Disabling SQL*Plus, SQL, and PL/SQL Commands for more information.
The EXIT or QUIT command in a script terminates SQL*Plus.
Examples
A file named PROMOTE with the extension SQL, used to promote employees, might contain the following command:
SELECT FIRST_NAME, LAST_NAME, JOB_ID, SALARYFROM EMP_DETAILS_VIEWWHERE JOB_ID='&1' AND SALARY>&2;
To run this script, enter
START PROMOTE ST_MAN 7000
or if it is located on a web server, enter a command in the form:
START HTTP://machine_name.domain:port/PROMOTE.SQL ST_MAN 7000
Where machine_name.domain must be replaced by the host.domain name, and port by the port number used by the web server where the script is located.
The following command is executed:
SELECT LAST_NAME, LAST_NAME FROM EMP_DETAILS_VIEW WHERE JOB_ID='ST_MAN' AND SALARY>7000;
and the results displayed.
This appendix discusses the following topics:
Read this chapter while sitting at your computer and try out the example shown. Before beginning, make sure you have access to the sample tables described in SQL*Plus Quick Start.
The COPY command will be obsoleted in future releases of SQL*Plus. No new datatypes will be supported by COPY.
COPY {FROM database | TO database | FROM database TO database} {APPEND|CREATE|INSERT|REPLACE} destination_table [(column, column, column, ...)] USING query
where database has the following syntax:
username[/password]@connect_identifier
Copies data from a query to a table in the same or another database. COPY supports the following datatypes:
|
Warning: Including your password in plain text is a security risk. You can avoid this risk by omitting the password, and entering it only when the system prompts for it. |
See the following list for a description of each term or clause:
FROM database
The database that contains the data to be copied. If you omit the FROM clause, the source defaults to the database to which SQL*Plus is connected (that is, the database that other commands address). You must use a FROM clause to specify a source database other than the default. The COPY command FROM clause does not support SYSDBA or SYSOPER privileged connections.
TO database
The database containing the destination table. If you omit the TO clause, the destination defaults to the database to which SQL*Plus is connected (that is, the database that other commands address). You must use a TO clause to specify a destination database other than the default. The COPY command TO clause does not support SYSDBA or SYSOPER privileged connections.
database
Specifies username[/password] @connect_identifier of the Oracle Database source or destination database you wish to COPY FROM or COPY TO. The COPY command does not support SYSDBA or SYSOPER privileged connections. You must include a username. SQL*Plus prompts you for the password associated with the username specified in the COPY FROM or COPY TO clauses. SQL*Plus suppresses the display of your password response.
You must include the connect_identifier clause to specify the source or destination database. The exact syntax depends on the Oracle Net configuration. For more information, refer to the Oracle Net manual or contact your DBA.
APPEND
Inserts the rows from query into destination_table if the table exists. If destination_table does not exist, COPY creates it.
CREATE
Inserts the rows from query into destination_table after first creating the table. If destination_table already exists, COPY returns an error.
INSERT
Inserts the rows from query into destination_table. If destination_table does not exist, COPY returns an error. When using INSERT, the USING query must select one column for each column in destination_table.
REPLACE
Replaces destination_table and its contents with the rows from query. If destination_table does not exist, COPY creates it. Otherwise, COPY drops the existing table and replaces it with a table containing the copied data.
destination_table
Represents the table you wish to create or to which you wish to add data.
(column, column, column, ...)
Specifies the names of the columns in destination_table. You must enclose a name in double quotes if it contains lowercase letters or blanks.
If you specify columns, the number of columns must equal the number of columns selected by the query. If you do not specify any columns, the copied columns will have the same names in the destination table as they had in the source if COPY creates destination_table.
USING query
Specifies a SQL query (SELECT command) determining which rows and columns COPY copies.
To enable the copying of data between Oracle and non-Oracle databases, NUMBER columns are changed to DECIMAL columns in the destination table. Hence, if you are copying between Oracle databases, a NUMBER column with no precision will be changed to a DECIMAL(38) column. When copying between Oracle databases, you should use SQL commands (CREATE TABLE AS and INSERT) or you should ensure that your columns have a precision specified.
The SQL*Plus SET LONG variable limits the length of LONG columns that you copy. If any LONG columns contain data longer than the value of LONG, COPY truncates the data.
SQL*Plus performs a commit at the end of each successful COPY. If you set the SQL*Plus SET COPYCOMMIT variable to a positive value n, SQL*Plus performs a commit after copying every n batches of records. The SQL*Plus SET ARRAYSIZE variable determines the size of a batch.
Some operating environments require that service names be placed in double quotes.
The following command copies the entire EMPLOYEES table to a table named WESTEMPLOYEES. Note that the tables are located in two different databases. If WESTEMPLOYEES already exists, SQL*Plus replaces the table and its contents. The columns in WESTEMPLOYEES have the same names as the columns in the source table, EMPLOYEES.
COPY FROM HR@HQ TO JOHN@WEST - REPLACE WESTEMPLOYEES - USING SELECT * FROM EMPLOYEES
The following command copies selected records from EMPLOYEES to the database to which SQL*Plus is connected. SQL*Plus creates SALESMEN through the copy. SQL*Plus copies only the columns EMPLOYEE_ID and LAST_NAME, and at the destination names them EMPLOYEE_ID and SA_MAN.
COPY FROM HR@ORACLE01 - CREATE SALESMEN (EMPLOYEE_ID, SA_MAN) - USING SELECT EMPLOYEE_ID, LAST_NAME FROM EMPLOYEES - WHERE JOB_ID='SA_MAN';
Use the SQL*Plus COPY command to copy CHAR, DATE, LONG, NUMBER or VARCHAR2 data between databases and between tables on the same database. With the COPY command, you can copy data between databases in the following ways:
Copy data from a remote database to your local database.
Copy data from your local (default) database to a remote database (most systems).
Copy data from one remote database to another remote database (most systems).
|
Note: In general, the COPY command was designed to be used for copying data between Oracle and non-Oracle databases. You should use SQL commands (CREATE TABLE AS and INSERT) to copy data between Oracle databases. |
You enter the COPY command in the following form:
COPY FROM database TO database action - destination_table (column_name, column_name, - column_name ...) USING query
Here is a sample COPY command:
COPY FROM HR@BOSTONDB - TO TODD@CHICAGODB - CREATE NEWDEPT (DEPARTMENT_ID, DEPARTMENT_NAME, CITY) - USING SELECT * FROM EMP_DETAILS_VIEW
To specify a database in the FROM or TO clause, you must have a valid username and password for the local and remote databases and know the appropriate Oracle Net service names. COPY obeys Oracle Database security, so the username you specify must have been granted access to tables for you to have access to tables. For information on what databases are available to you, contact your DBA.
When you copy to your local database from a remote database, you can omit the TO clause. When you copy to a remote database from your local database, you can omit the FROM clause. When you copy between remote databases, you must include both clauses. However, including both clauses increases the readability of your scripts.
The COPY command behaves differently based on whether the destination table already exists and on the action clause you enter (CREATE in the example). See Controlling Treatment of the Destination Table for more information.
By default, the copied columns have the same names in the destination table that they have in the source table. If you want to give new names to the columns in the destination table, enter the new names in parentheses after the destination table name. If you enter any column names, you must enter a name for every column you are copying.
|
Note: To enable the copying of data between Oracle and non-Oracle databases, NUMBER columns are changed to DECIMAL columns in the destination table. Hence, if you are copying between Oracle databases, a NUMBER column with no precision will be changed to a DECIMAL(38) column. When copying between Oracle databases, you should use SQL commands (CREATE TABLE AS and INSERT) or you should ensure that your columns have a precision specified. |
The USING clause specifies a query that names the source table and specifies the data that COPY copies to the destination table. You can use any form of the SQL SELECT command to select the data that the COPY command copies.
Here is an example of a COPY command that copies only two columns from the source table, and copies only those rows in which the value of DEPARTMENT_ID is 30:
COPY FROM HR@BOSTONDB - REPLACE EMPCOPY2 - USING SELECT LAST_NAME, SALARY - FROM EMP_DETAILS_VIEW - WHERE DEPARTMENT_ID = 30
You may find it easier to enter and edit long COPY commands in scripts rather than trying to enter them directly at the command prompt.
You control the treatment of the destination table by entering one of four control clauses—REPLACE, CREATE, INSERT, or APPEND.
The REPLACE clause names the table to be created in the destination database and specifies the following actions:
If the destination table already exists, COPY drops the existing table and replaces it with a table containing the copied data.
If the destination table does not already exist, COPY creates it using the copied data.
You can use the CREATE clause to avoid accidentally writing over an existing table. CREATE specifies the following actions:
If the destination table already exists, COPY reports an error and stops.
If the destination table does not already exist, COPY creates the table using the copied data.
Use INSERT to insert data into an existing table. INSERT specifies the following actions:
If the destination table already exists, COPY inserts the copied data in the destination table.
If the destination table does not already exist, COPY reports an error and stops.
Use APPEND when you want to insert data in an existing table, or create a new table if the destination table does not exist. APPEND specifies the following actions:
If the destination table already exists, COPY inserts the copied data in the destination table.
If the table does not already exist, COPY creates the table and then inserts the copied data in it.
Example B-1 Copying from a Remote Database to Your Local Database Using CREATE
To copy HR from a remote database into a table called EMPLOYEE_COPY on your own database, enter the following command:
COPY FROM HR@BOSTONDB - CREATE EMPCOPY - USING SELECT * FROM HR
Array fetch/bind size is 15. (arraysize is 15) Will commit when done. (copycommit is 0) Maximum long size is 80. (long is 80) |
SQL*Plus then creates the table EMPLOYEE_COPY and copies the rows:
Table SALESMAN created. 5 rows selected from HR@BOSTONDB. 5 rows inserted into SALESMAN. 5 rows committed into SALESMAN at DEFAULT HOST connection. |
In this COPY command, the FROM clause directs COPY to connect you to the database with the specification BOSTONDB as HR.
Notice that you do not need a semicolon at the end of the command; COPY is a SQL*Plus command, not a SQL command, even though it contains a query. Since most COPY commands are longer than one line, you must use a line continuation hyphen (-), optionally preceded by a space, at the end of each line except the last.
The first three messages displayed by COPY show the values of SET command variables that affect the COPY operation. The most important one is LONG, which limits the length of a LONG column's value. (LONG is a datatype, similar to CHAR.) If the source table contains a LONG column, COPY truncates values in that column to the length specified by the system variable LONG.
The variable ARRAYSIZE limits the number of rows that SQL*Plus fetches from the database at one time. This number of rows makes up a batch. The variable COPYCOMMIT sets the number of batches after which COPY commits changes to the database. (If you set COPYCOMMIT to zero, COPY commits changes only after all batches are copied.) For more information on SET variables, including how to change their settings, see the SET command.
After listing the three system variables and their values, COPY tells you if a table was dropped, created, or updated during the copy. Then COPY lists the number of rows selected, inserted, and committed.
You can refer to another user's table in a COPY command by qualifying the table name with the username, just as you would in your local database, or in a query with a database link.
For example, to make a local copy of a table named DEPARTMENT owned by the username ADAMS on the database associated with the Oracle Net connect identifier BOSTONDB, you would enter
COPY FROM HR@BOSTONDB - CREATE EMPLOYEE_COPY2 - USING SELECT * FROM ADAMS.DEPARTMENT
Of course, you could get the same result by instructing COPY to log in to the remote database as ADAMS. You cannot do that, however, unless you know the password associated with the username ADAMS.
You can copy data from one table to another in a single database (local or remote). To copy between tables in your local database, specify your own username and the service name for your local database in either a FROM or a TO clause (omit the other clause):
COPY FROM HR@MYDATABASE - INSERT EMPLOYEE_COPY2 - USING SELECT * FROM EMPLOYEE_COPY
To copy between tables on a remote database, include the same username and service name in the FROM and TO clauses:
COPY FROM HR@BOSTONDB - TO HR@BOSTONDB - INSERT EMPLOYEE_COPY2 - USING SELECT * FROM EMPLOYEE_COPY
This chapter describes how to start, login, and connect to a database, how to get help, and how to exit SQL*Plus.
Specific topics discussed are:
When you start SQL*Plus, you need a username and password to login to an Oracle Database schema. Your username and password identify you as an authorized user of the Oracle Database schema.
The database administrator (DBA) is responsible for creating your database account with the necessary privileges and giving you the username and password that enables you to access your account.
Default logins are created and you are prompted for associated passwords during Oracle Database installation. Some of the default login usernames created are:
SYS
SYSTEM
HR
Logins are created and displayed in messages during Oracle Database installation.
For further information about the default logins, see the Oracle Database Administrator's Guide.
Once you have logged in, you can connect under a different username using the CONNECT command. The username and password must be valid for the database. For example, to connect the username TODD to the default database using the password FOX, you could enter
CONNECT TODD
You are prompted to enter the password, FOX.
In the command-line interface, if you omit the username and password, SQL*Plus prompts you for them. Because CONNECT first disconnects you from your current database, you will be left unconnected to any database if you use an invalid username and password in your CONNECT command.
If you log on or connect as a user whose account has expired, you are prompted to change your password before you can connect.
If an account is locked, a message is displayed and connection as this user is not permitted until the account is unlocked by your DBA.
You can use the DISCONNECT command to disconnect from a database without leaving SQL*Plus.
As a command-line alternative for large-scale deployments where applications use password credentials to connect to databases, it is possible to store such credentials in a client-side Oracle wallet. An Oracle wallet is a secure software container that is used to store authentication and signing credentials.
Storing database password credentials in a client-side Oracle wallet eliminates the need to embed usernames and passwords in application code, batch jobs, or scripts. This reduces the risk of exposing passwords in the clear in scripts and application code, and simplifies maintenance because you need not change your code each time usernames and passwords change. In addition, not having to change application code also makes it easier to enforce password management policies for these user accounts.
When you configure a client to use the external password store, applications can use the following syntax to connect to databases that use password authentication:
CONNECT /@database_alias
Note that you need not specify database login credentials in this CONNECT statement. Instead your system looks for database login credentials in the client wallet.
|
See Also: Oracle Database Administrator's Guide for information about configuring your client to use secure external password store and for information about managing credentials in it. |
In the command-line interface, if your password has expired, SQL*Plus prompts you to change it when you attempt to log in. You are logged in once you successfully change your password.
In the command-line interface, you can change your password with the PASSWORD command. See PASSWORD.
You must connect to an Oracle Database (instance) before you can query or modify data in that database. You can connect to the default database and to other databases accessible through your network. To connect to another database over a network, both databases must have Oracle Net configured, and have compatible network drivers. You must enter either a connection identifier or a net service name to connect to a database other than the default.
The connection identifier or net service name is entered:
as an argument to the SQLPLUS Program Syntax when starting a command-line session.
as an argument to the CONNECT command from a current session.
Your DBA is responsible for creating the databases you use and defining net service names for them in the tnsnames.ora file.
A net service name definition in the tnsnames.ora file has the syntax:
net_service_name= (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=port) ) (CONNECT_DATA= (SERVICE_NAME=service_name) ) )
To use a net service name (alias), it must have an entry in the tnsnames.ora file on the machine running SQL*Plus. An entry in tnsnames.ora is not required if you use a connection identifier.
Example 3-1 The tnsnames.ora entry for the sales database
SALES1 = (DESCRIPTION = (ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521) ) (CONNECT_DATA= (SERVICE_NAME=sales.us.acme.com) ) )
Example 3-2 Start a command-line session to the sales database using the net service name
SQLPLUS hr@SALES1
See the Oracle Database Net Services Reference and the Oracle Database Net Services Administrator's Guide for more information about database connections and net service name definitions.
Depending on your configuration, use the full connection identifier syntax like:
(DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=port) ) (CONNECT_DATA= (SERVICE_NAME=service_name) ) )
The SERVICE_NAME is the global database name entered during database creation. It combines a database name with a domain name. For example, the SERVICE_NAME sales.us.acme.com has a database name of sales and a domain of us.acme.com.
An INSTANCE_NAME is the name you give to the database instance during creation. It defaults to the SID you entered during database creation.
An Oracle System Identifier (SID) identifies a specific Oracle release 8.0 database instance.
You can optionally use an INSTANCE_NAME in place of the SERVICE_NAME phrase.
Use a SID in place of the SERVICE_NAME when connecting to an Oracle release 8.0 or earlier database.
The easy or abbreviated connection identifier has the syntax:
[//]host[:port][/service_name]
Example 3-4 Start a command-line session to the sales database using the easy connection identifier
sqlplus hr@\"sales-server:1521/sales.us.acme.com\"
Example 3-5 CONNECT to the sales database using the easy connection identifier
connect hr@"sales-server:1521/sales.us.acme.com"
The easy connection identifier can be used wherever you can use a full connection identifier, or a net service name. The easy syntax is less complex, and no tnsnames.ora entry is required.
In the command-line interface, it is possible to start SQL*Plus without connecting to a database. This is useful for performing some database administration tasks, writing transportable scripts, or to use SQL*Plus editing commands to write or edit scripts.
You use the /NOLOG argument to the SQLPLUS command to start a connectionless command-line session. After SQL*Plus has started you can connect to a database with the CONNECT command.
If you are connecting to a remote Oracle database, make sure your Oracle Net software is installed and working properly. For more information, see the Oracle Database Net Services Administrator's Guide.
When you start a SQL*Plus command-line session, and after a CONNECT command in that session, the site profile, glogin.sql, and the user profile file, login.sql, are processed:
After SQL*Plus starts and connects, and prior to displaying the first prompt.
After SQL*Plus starts and connects, and prior to running a script specified on the command line.
Prior to the first prompt when /NOLOG is specified on the command line and no connection is made.
The site profile file, glogin.sql is processed first, then the user profile file, login.sql.
Behavior in SQL*Plus 10.1 may be unexpected depending on the setting of SET SQLPLUSCOMPATIBILITY. For example, processing glogin.sql and login.sql after a CONNECT command only occurs with the default SQLPLUSCOMPATIBILITY setting of 10.1. For more information, see SET SQLPLUSCOMPAT[IBILITY] {x.y[.z]}.
To begin using SQL*Plus, you must first understand how to start and stop SQL*Plus.
Make sure that SQL*Plus has been installed on your computer.
Log on to the operating system (if required).
Enter the command, SQLPLUS, and press Return.
|
Note: Some operating systems expect you to enter commands in lowercase letters. If your system expects lowercase, enter the SQLPLUS command in lowercase. |
SQLPLUS
SQL*Plus displays its version number, the current date, and copyright information, and prompts you for your username (the text displayed on your system may differ slightly):
SQL*Plus: Release 11.2.0.0.0 - Production on Mon Jun 2 16:29:01 2008 (c) Copyright 1982, 2008 Oracle Corporation. All rights reserved. Enter user-name: |
Enter your username and press Return. SQL*Plus displays the prompt "Enter password:".
Enter your password and press Return again. For your protection, your password does not appear on the screen.
The process of entering your username and password is called logging in. SQL*Plus displays the version of Oracle Database to which you connected and the versions of available tools such as PL/SQL.
Next, SQL*Plus displays the SQL*Plus command prompt:
SQL>
The SQL*Plus command prompt indicates that SQL*Plus is ready to accept your commands.
If SQL*Plus does not start, you should see a message to help you correct the problem.
To access command-line help for SQL*Plus commands, type HELP or ? followed by the command name at the SQL command prompt or in the iSQL*Plus Workspace Input area. See the HELP command for more information. For example:
HELP ACCEPT
To display a list of SQL*Plus commands, type HELP followed by either TOPICS or INDEX. HELP TOPICS displays a single column list of SQL*Plus commands. HELP INDEX displays a four column list of SQL*Plus commands which fits in a standard screen. For example:
HELP INDEX
If you cannot log in to SQL*Plus because your username or password is invalid or for some other reason, SQL*Plus returns an error status equivalent to an EXIT FAILURE command. See the EXIT command for further information.
When you are done working with SQL*Plus and wish to return to the operating system, enter EXIT or QUIT at the SQL*Plus prompt, or enter the end of file character, Ctrl+D on UNIX or Ctrl+Z on Windows.
SQL*Plus displays the version of Oracle Database from which you disconnected and the versions of tools available through SQL*Plus before you return to the operating system prompt.
You use the SQLPLUS command at the operating system prompt to start command-line SQL*Plus:
SQLPLUS [ [Options] [Logon|/NOLOG] [Start] ]
where: Options has the following syntax:
-H[ELP]|-V[ERSION]
|[[-C[OMPATIBILITY] {x.y[.z]] [-L[OGON]] [-M[ARKUP] markup_option]
[-R[ESTRICT] {1|2|3}] [-S[ILENT]] ]
and markup_option has the following syntax:
HTML [ON|OFF] [HEAD text] [BODY text] [TABLE text] [ENTMAP {ON|OFF}] [SPOOL {ON|OFF}] [PRE[FORMAT] {ON|OFF}]
where Logon has the following syntax:
{username[/password][@connect_identifier]| / }
[AS {SYSOPER|SYSDBA|SYSASM}][edition=value]
where Start has the following syntax:
@{url|file_name[.ext]} [arg ...]
|
Warning: Including your password in plain text is a security risk. You can avoid this risk by omitting the password, and entering it only when the system prompts for it. |
You have the option of entering logon. If you do not specify logon but do specify start, SQL*Plus assumes that the first line of the script contains a valid logon. If neither start nor logon are specified, SQL*Plus prompts for logon information.
The following sections contain descriptions of SQLPLUS command options:
-H[ELP]
Displays the usage and syntax for the SQLPLUS command, and then returns control to the operating system.
-V[ERSION]
Displays the current version and level number for SQL*Plus, and then returns control to the operating system.
-C[OMPATIBILITY] {x.y[.z]
Sets the value of the SQLPLUSCOMPATIBILITY system variable to the SQL*Plus release specified by x.y[.z]. Where x is the version number, y is the release number, and z is the update number. For example, 9.0.1 or 10.2. For more information, see the SET SQLPLUSCOMPAT[IBILITY] {x.y[.z]}system variable.
-L[OGON]
Specifies not to reprompt for username or password if the initial connection does not succeed. This can be useful in operating system scripts that must either succeed or fail and you don't want to be reprompted for connection details if the database server is not running.
You can use the MARKUP option to generate a complete stand alone web page from your query or script. MARKUP currently supports HTML 4.0 transitional.
|
Note: Depending on your operating system, the complete markup_option clause for the SQLPLUS command may need to be contained in quotes. |
Use SQLPLUS -MARKUP HTML ON or SQLPLUS -MARKUP HTML ON SPOOL ON to produce standalone web pages. SQL*Plus will generate complete HTML pages automatically encapsulated with <HTML> and <BODY> tags. The HTML tags in a spool file are closed when SPOOL OFF is executed or SQL*Plus exits.
The -SILENT and -RESTRICT command-line options may be useful when used in conjunction with -MARKUP.
You can use MARKUP HTML ON to produce HTML output in either the <PRE> tag or in an HTML table. Output to a table uses standard HTML <TABLE>, <TR> and <TD> tags to automatically encode the rows and columns resulting from a query. Output to an HTML table is the default behavior when the HTML option is set ON. You can generate output using HTML <PRE> tags by setting PREFORMAT ON.
In SQL*Plus, use the SHOW MARKUP command to view the status of MARKUP options.
The SQLPLUS -MARKUP command has the same options and functionality as the SET MARKUP command. These options are described in this section. For other information on the SET MARKUP command, see the SET command.
HTML is a mandatory MARKUP argument which specifies that the type of output to be generated is HTML. The optional HTML arguments, ON and OFF, specify whether or not to generate HTML output. The default is OFF.
MARKUP HTML ON generates HTML output using the specified MARKUP options.
You can turn HTML output ON and OFF as required during a session. The default is OFF.
The HEAD text option enables you to specify content for the <HEAD> tag. By default, text includes a default in-line cascading style sheet and title.
If text includes spaces, it must be enclosed in quotes. SQL*Plus does not test this free text entry for HTML validity. You must ensure that the text you enter is valid for the HTML <HEAD> tag. This gives you the flexibility to customize output for your browser or special needs.
The BODY text option enables you to specify attributes for the <BODY> tag. By default, there are no attributes. If text includes spaces, it must be enclosed in quotes. SQL*Plus does not test this free text entry for HTML validity. You must ensure that the text you enter is valid for the HTML <BODY> tag. This gives you the flexibility to customize output for your browser or special needs.
The TABLE text option enables you to enter attributes for the <TABLE> tag. You can use TABLE text to set HTML <TABLE> tag attributes such as BORDER, CELLPADDING, CELLSPACING and WIDTH. By default, the <TABLE> WIDTH attribute is set to 90% and the BORDER attribute is set to 1.
If text includes spaces, it must be enclosed in quotes. SQL*Plus does not test this free text entry for HTML validity. You must ensure that the text you enter is valid for the HTML <TABLE> tag. This gives you the flexibility to customize output for your browser or special needs.
ENTMAP ON or OFF specifies whether or not SQL*Plus replaces special characters <, >, " and & with the HTML entities <, >, " and & respectively. ENTMAP is set ON by default.
You can turn ENTMAP ON and OFF as required during a session. For example, with ENTMAP OFF, SQL*Plus screen output is:
SQL>PROMPT A > B A > B
With ENTMAP ON, SQL*Plus screen output is:
SQL> PROMPT A > B A > B
As entities in the <HEAD> and <BODY> tags are not mapped, you must ensure that valid entities are used in the MARKUP HEAD and BODY options.
If entities are not mapped, web browsers may treat data as invalid HTML and all subsequent output may display incorrectly. ENTMAP OFF enables users to write their own HTML tags to customize output.
|
Note: ENTMAP only takes effect when the HTML option is set ON. For more information about using entities in your output, see the COLUMN command. |
SPOOL ON or OFF specifies whether or not SQL*Plus writes the HTML opening tags, <HTML> and <BODY>, and the closing tags, </BODY> and </HTML>, to the start and end of each file created by the SQL*Plus SPOOL filename command. The default is OFF.
You can turn SPOOL ON and OFF as required during a session.
|
Note: It is important to distinguish between the SET MARKUP HTML SPOOL option, and the SQLPLUS SPOOL filename command.The SET MARKUP HTML SPOOL ON option enables the writing of the <HTML> tag to the spool file. The spool file is not created, and the header and footer tags enabled by the SET MARKUP HTML SPOOL ON option are not written to the spool file until you issue the SQLPLUS SPOOL filename command. See the SPOOL command for more information. |
SQL*Plus writes several HTML tags to the spool file when you issue the SPOOL filename command.
When you issue any of the SQL*Plus commands: EXIT, SPOOL OFF or SPOOL filename, SQL*Plus appends the following end tags and closes the file:
</BODY> </HTML>
You can specify <HEAD> tag contents and <BODY> attributes using the HEAD and BODY options
PREFORMAT ON or OFF specifies whether or not SQL*Plus writes output to the <PRE> tag or to an HTML table.y0 The default is OFF, so output is written to a HTML table by default. You can turn PREFORMAT ON and OFF as required during a session.
|
Note: To produce report output using the HTML <PRE> tag, you must set PREFORMAT ON. For example:
or
|
When MARKUP HTML ON PREFORMAT OFF is used, commands originally intended to format paper reports have different meaning for reports intended for web tables:
PAGESIZE is the number of rows in an HTML table, not the number of lines. Each row may contain multiple lines. The TTITLE, BTITLE and column headings are repeated every PAGESIZE rows.
LINESIZE may have an effect on data if wrapping is on, or for very long data. Depending on data size, output may be generated on separate lines, which a browser may interpret as a space character.
TTITLE and BTITLE content is output to three line positions: left, center and right, and the maximum line width is preset to 90% of the browser window. These elements may not align with the main output as expected due to the way they are handled for web output. Entity mapping in TTITLE and BTITLE is the same as the general ENTMAP setting specified in the MARKUP command.
If you use a title in your output, then SQL*Plus starts a new HTML table for output rows that appear after the title. Your browser may format column widths of each table differently, depending on the width of data in each column.
SET COLSEP, RECSEP and UNDERLINE only produce output in HTML reports when PREFORMAT is ON.
Enables you to disable certain commands that interact with the operating system. This is similar to disabling the same commands in the Product User Profile (PUP) table. However, commands disabled with the -RESTRICT option are disabled even if there is no connection to a server, and remain disabled until SQL*Plus terminates.
If no -RESTRICT option is active, than all commands can be used, unless disabled in the PUP table.
If -RESTRICT 3 is used, then LOGIN.SQL is not read. GLOGIN.SQL is read but restricted commands used will fail.
-S[ILENT]
Suppresses all SQL*Plus information and prompt messages, including the command prompt, the echoing of commands, and the banner normally displayed when you start SQL*Plus. If you omit username or password, SQL*Plus prompts for them, but the prompts are not visible! Use SILENT to invoke SQL*Plus within another program so that the use of SQL*Plus is invisible to the user.
SILENT is a useful mode for creating reports for the web using the SQLPLUS -MARKUP command inside a CGI script or operating system script. The SQL*Plus banner and prompts are suppressed and do not appear in reports created using the SILENT option.
username[/password]
Represent the username and password with which you wish to start SQL*Plus and connect to Oracle Database.
|
Warning: Including your password in plain text is a security risk. You can avoid this risk by omitting the password, and entering it only when the system prompts for it. |
If you omit username and password, SQL*Plus prompts you for them. If you omit only password, SQL*Plus prompts for it. In silent mode, username and password prompts are not visible! Your username appears when you type it, but not your password.
@connect_identifier
Consists of an Oracle Net connect identifier. The exact syntax depends upon the Oracle Net configuration. For more information, refer to the Oracle Net manual or contact your DBA.
edition=value
The value for the Oracle Edition. An edition enables two or more versions of an object in a database. It provides a staging area where changed objects can be loaded into the database, compiled, and executed during uptime. This is particularly useful to reduce downtime associated with patching an application. edition=value overrides any edition value specified in the ORA_EDITION environment variable. For more detailed information, see Oracle Database Advanced Application Developer's Guide.
/
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. Note that the prefix "OPS$" can be set to any other string of text. For example, you may wish to change the settings in your INIT.ORA parameters file to LOGONname or USERIDname. See the Oracle Database Administrator's Guide for information about operating system authentication.
The AS clause enables privileged connections by users who have been granted SYSOPER, SYSDBA or SYSASM system privileges.
/NOLOG
Establishes no initial connection to Oracle Database. Before issuing any SQL commands, you must issue a CONNECT command to establish a valid logon. Use /NOLOG when you want to have a SQL*Plus script prompt for the username, password, or database specification. The first line of this script is not assumed to contain a logon.
@{url|file_name[.ext]} [arg ...]
Specifies the name of a script and arguments to run. The script can be called from the local file system or from a web server.
SQL*Plus passes the arguments to the script as if executing the file using the SQL*Plus START command. If no file suffix (file extension) is specified, the suffix defined by the SET SUFFIX command is used. The default suffix is .sql.
See the START command for more information.
The SQL*Plus (pronounced "sequel plus") User's Guide and Reference introduces SQL*Plus and its uses, and provides a description of each SQL*Plus command.
This preface contains these topics:
The SQL*Plus User's Guide and Reference is intended for business and technical users and system administrators who perform the following tasks:
Develop and run batch scripts
Format, calculate on, store, print and create web output from query results
Examine table and object definitions
Perform database administration
This document assumes a basic understanding of the SQL language. If you do not have familiarity with SQL, see the Oracle Database SQL Language Reference. If you plan to use PL/SQL in conjunction with SQL*Plus, see the Oracle Database PL/SQL Language Reference.
Our goal is to make Oracle products, services, and supporting documentation accessible to all users, including users that are disabled. To that end, our documentation includes features that make information available to users of assistive technology. This documentation is available in HTML format, and contains markup to facilitate access by the disabled community. Accessibility standards will continue to evolve over time, and Oracle is actively engaged with other market-leading technology vendors to address technical obstacles so that our documentation can be accessible to all of our customers. For more information, visit the Oracle Accessibility Program Web site at http://www.oracle.com/accessibility/.
Screen Readers and SQL*Plus
Your screen reader and SQL*Plus must be installed on the same computer to work properly.
Accessibility of Code Examples in Documentation
Screen readers may not always correctly read the code examples in this document. The conventions for writing code require that closing braces should appear on an otherwise empty line; however, some screen readers may not always read a line of text that consists solely of a bracket or brace.
Accessibility of Links to External Web Sites in Documentation
This documentation may contain links to Web sites of other companies or organizations that Oracle does not own or control. Oracle neither evaluates nor makes any representations regarding the accessibility of these Web sites.
Deaf/Hard of Hearing Access to Oracle Support Services
To reach Oracle Support Services, use a telecommunications relay service (TRS) to call Oracle Support at 1.800.223.1711. An Oracle Support Services engineer will handle technical issues and provide customer support according to the Oracle service request process. Information about TRS is available at http://www.fcc.gov/cgb/consumerfacts/trs.html, and a list of phone numbers is available at http://www.fcc.gov/cgb/dro/trsphonebk.html.
For more information, see these Oracle resources:
Oracle Database Heterogeneous Connectivity Administrator's Guide
Oracle Database installation and user's manuals for your operating system
Many of the examples in this book use the sample schemas, which are installed by default when you select the Basic Installation option with an Oracle Database installation. See Oracle Database Sample Schemas for information on how these schemas were created and how you can use them yourself.
SQL*Plus error message documentation is available in SQL*Plus Error Messages. Oracle Database error message documentation is only available in HTML. If you only have access to the Oracle Database Documentation media, you can browse the Oracle Database error messages by range. Once you find the specific range, use your browser's "find in page" feature to locate the specific message. When connected to the Internet, you can search for a specific error message using the error message search feature of the Oracle Database online documentation.
The following text conventions are used in this document:
| Convention | Meaning |
|---|---|
| boldface | Boldface type indicates graphical user interface elements associated with an action, or terms defined in text or the glossary. |
| italic | Italic type indicates book titles, emphasis, or placeholder variables for which you supply particular values. |
monospace | Monospace type indicates commands within a paragraph, URLs, code in examples, text that appears on the screen, or text that you enter. |
Syntax
GET [FILE] file_name[.ext] [LIST | NOLIST]
Loads an operating system file into the SQL buffer.
The buffer has no command history list and does not record SQL*Plus commands.
Terms
FILE
Keyword to specify that the following argument is the name of the script you want to load. This optional keyword is usually omitted.
If you want to load a script with the name file, because it is a command keyword, you need to put the name file in single quotes.
file_name[.ext]
Represents the file you wish to load (typically a script).
LIST
Lists the contents of the file after it is loaded. This is the default.
NOLIST
Usage
If you do not specify a file extension, SQL*Plus assumes the default command-file extension (normally SQL). For information on changing the default extension, see SET SUF[FIX] {SQL | text}.
If the filename you specify contains the word list or the word file, the name must be in double quotes. SQL*Plus searches for the file in the current working directory.
The operating system file should contain a single SQL statement or PL/SQL block. The statement should not be terminated with a semicolon. If a SQL*Plus command or more than one SQL statement or PL/SQL block is loaded into the SQL buffer from an operating system file, an error occurs when the RUN or slash (/) command is used to execute the buffer.
The GET command can be used to load files created with the SAVE command. See SAVE for more information.
Examples
To load a file called YEARENDRPT with the extension SQL into the buffer, enter
GET YEARENDRPT
Syntax
DESC[RIBE] {[schema.]object[@db_link]}
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.
@db_link
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 Language 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.
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.
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
the ENCRYPT keyword to indicate whether or not data in a column is encrypted
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? ------------------------------ ----------------------- ------ -------- P1 CHAR IN P2 NUMBER IN |
To create and describe the object type ADDRESS that contains the attributes STREET and CITY, enter
CREATE TYPE ADDRESS AS OBJECT
( STREET VARCHAR2(20),
CITY VARCHAR2(20)
);
/
Type created. |
DESCRIBE address
Name Null? Type ----------------------------------------- -------- ---------------------- STREET VARCHAR2(20) CITY VARCHAR2(20) |
To create and describe the object type EMPLOYEE that contains the attributes LAST_NAME, EMPADDR, JOB_ID and SALARY, enter
CREATE TYPE EMPLOYEE AS OBJECT (LAST_NAME VARCHAR2(30), EMPADDR ADDRESS, JOB_ID VARCHAR2(20), SALARY NUMBER(7,2) ); /
Type created. |
DESCRIBE employee
Name Null? Type ----------------------------------------- -------- ---------------------- LAST_NAME VARCHAR2(30) EMPADDR ADDRESS JOB_ID VARCHAR2(20) SALARY NUMBER(7,2) |
To create and describe the object type addr_type as a table of the object type ADDRESS, enter
CREATE TYPE addr_type IS TABLE OF ADDRESS; /
Type created. |
DESCRIBE addr_type
addr_type TABLE OF ADDRESS Name Null? Type ----------------------------------------- -------- ---------------------- STREET VARCHAR2(20) CITY VARCHAR2(20) |
To create and describe the object type addr_varray as a varray of the object type ADDRESS, enter
CREATE TYPE addr_varray AS VARRAY(10) OF ADDRESS; /
Type created. |
DESCRIBE addr_varray
addr_varray VARRAY(10) OF ADDRESS Name Null? Type ----------------------------------------- -------- ---------------------- STREET VARCHAR2(20) CITY VARCHAR2(20) |
To create and describe the table department that contains the columns DEPARTMENT_ID, PERSON and LOC, enter
CREATE TABLE department (DEPARTMENT_ID NUMBER, PERSON EMPLOYEE, LOC NUMBER ); /
Table created. |
DESCRIBE department
Name Null? Type ----------------------------------------- -------- ---------------------- DEPARTMENT_ID NUMBER PERSON EMPLOYEE LOC NUMBER |
To create and describe the object type rational that contains the attributes NUMERATOR and DENOMINATOR, and the METHOD rational_order, enter
CREATE OR REPLACE TYPE rational AS OBJECT (NUMERATOR NUMBER, DENOMINATOR NUMBER, MAP MEMBER FUNCTION rational_order - RETURN DOUBLE PRECISION, PRAGMA RESTRICT_REFERENCES (rational_order, RNDS, WNDS, RNPS, WNPS) ); /
CREATE OR REPLACE TYPE BODY rational AS OBJECT MAP MEMBER FUNCTION rational_order - RETURN DOUBLE PRECISION IS BEGIN RETURN NUMERATOR/DENOMINATOR; END; END; / DESCRIBE rational
Name Null? Type ------------------------------ -------- ------------ NUMERATOR NUMBER DENOMINATOR NUMBER METHOD ------ MAP MEMBER FUNCTION RATIONAL_ORDER RETURNS NUMBER |
To create a table which contains a column of XMLType, and describe it, enter
CREATE TABLE PROPERTY (Price NUMBER, Description SYS.XMLTYPE);
Table created |
DESCRIBE PROPERTY;
Name Null? Type ----------------------------------------- -------- ---------------------- PRICE NUMBER DESCRIPTION SYS.XMLTYPE |
To format the DESCRIBE output use the SET command as follows:
SET LINESIZE 80 SET DESCRIBE DEPTH 2 SET DESCRIBE INDENT ON SET DESCRIBE LINE OFF
To display the settings for the object, use the SHOW command as follows:
SHOW DESCRIBE
DESCRIBE DEPTH 2 LINENUM OFF INDENT ON |
DESCRIBE employee
Name Null? Type ----------------------------------------- -------- ---------------------- FIRST_NAME VARCHAR2(30) EMPADDR ADDRESS STREET VARCHAR2(20) CITY VARCHAR2(20) JOB_ID VARCHAR2(20) SALARY NUMBER(7,2) |
To create and describe the table des2_table which contains an encrypted column col2, enter
CREATE TABLE des2_table ( col1 VARCHAR2(10), col2 VARCHAR2(15) ENCRYPT, col3 CHAR(5), col4 CHAR(20));
Table created |
DESCRIBE des2_table;
Name Null? Type ----------------------------------------- -------- ---------------------- COL1 VARCHAR2(10) COL2 VARCHAR2(15) ENCRYPT COL3 CHAR(5) COL4 CHAR(20) |
For more information on using the CREATE TYPE command, see your Oracle Database SQL Language Reference.
For information about using the SET DESCRIBE and SHOW DESCRIBE commands, see the SET and SHOW commands.
Syntax
I[NPUT] [text]
where text represents the text you wish to add.
Adds one or more new lines of text after the current line in the buffer. The buffer has no command history list and does not record SQL*Plus commands.
To add a single line, enter the text of the line after the command INPUT, separating the text from the command with a space. To begin the line with one or more spaces, enter two or more spaces between INPUT and the first non-blank character of text.
To add several lines, enter INPUT with no text. INPUT prompts you for each line. To leave INPUT, enter a null (empty) line or a period.
Usage
If you enter a line number at the command prompt larger than the number of lines in the buffer, and follow the number with text, SQL*Plus adds the text in a new line at the end of the buffer. If you specify zero (0) for the line number and follow the zero with text, then SQL*Plus inserts the line at the beginning of the buffer (that line becomes line 1).
Examples
Assume the SQL buffer contains the following command:
SELECT LAST_NAME, DEPARTMENT_ID, SALARY, COMMISSION_PCT FROM EMP_DETAILS_VIEW
To add an ORDER BY clause to the query, enter
LIST 2
2* FROM EMP_DETAILS_VIEW |
INPUT ORDER BY LAST_NAME
LIST 2 ensures that line 2 is the current line. INPUT adds a new line containing the ORDER BY clause after the current line. The SQL buffer now contains the following lines:
1 SELECT LAST_NAME, DEPARTMENT_ID, SALARY, COMMISSION_PCT 2 FROM EMP_DETAILS_VIEW 3* ORDER BY LAST_NAME |
To add a two-line WHERE clause, enter
LIST 2
2* FROM EMP_DETAILS_VIEW |
INPUT 3 WHERE JOB_ID = 'SA_MAN' 4 AND COMMISSION_PCT=.25 5
INPUT prompts you for new lines until you enter an empty line or a period. The SQL buffer now contains the following lines:
SELECT LAST_NAME, DEPARTMENT_ID, SALARY, COMMISSION_PCT FROM EMP_DETAILS_VIEW WHERE JOB_ID = 'SA_MAN' AND COMMISSION_PCT = .25 ORDER BY LAST_NAME |
