SQL*Plus User's Guide and Reference | ![]() Library |
![]() Product |
![]() Contents |
![]() Index |
Purpose
Discusses the basic use(s) of the command.
Syntax
Shows how to enter the command. Refer to Chapter 1 for an explanation of the syntax notation.
Terms and Clauses
Describes the function of each term or clause appearing in the syntax.
Usage Notes
Provides additional information on how the command works and on uses of the command.
Examples
Gives one or more examples of the command.
A summary table that lists and briefly describes SQL*Plus commands precedes the individual command descriptions.
To access online help for SQL*Plus commands, you can type HELP followed by the command name at the SQL command prompt. For example:
SQL> HELP ACCEPT
If you get a response that help is unavailable, consult your database administrator. See the HELP command for more information.
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 just press [Return]. If you wish, however, you can enter a semicolon at the end of a SQL*Plus command.
Command | Description |
@ | Runs the specified command file. |
@@ | Runs a nested command file. |
/ | Executes the SQL command or PL/SQL block. |
ACCEPT | Reads a line of input and stores it in a given user variable. |
APPEND | Adds specified text to the end of the current line in the buffer. |
BREAK | Specifies where and how formatting will change in a report, or lists the current break definition. |
BTITLE | Places and formats a specified title at the bottom of each report page, or lists the current BTITLE definition. |
CHANGE | Changes text on the current line in the buffer. |
CLEAR | Resets or erases the current value or setting for the specified option, such as BREAKS or COLUMNS. |
COLUMN | Specifies display attributes for a given column, or lists the current display attributes for a single column or for all columns. |
COMPUTE | Calculates and prints summary lines, using various standard computations, on subsets of selected rows, or lists all COMPUTE definitions. |
CONNECT | Connects a given username to Oracle. |
COPY | Copies data from a query to a table in a local or remote database. |
DEFINE | Specifies a user variable and assigns it a CHAR value, or lists the value and variable type of a single variable or all variables. |
DEL | Deletes one or more lines of the buffer. |
DESCRIBE | Lists the column definitions for the specified table, view, or synonym or the specifications for the specified function or procedure. |
DISCONNECT | Commits pending changes to the database and logs the current username off Oracle, but does not exit SQL*Plus. |
EDIT | Invokes a host operating system text editor on the contents of the specified file or on the contents of the buffer. |
EXECUTE | Executes a single PL/SQL statement. |
EXIT | Terminates SQL*Plus and returns control to the operating system. |
GET | Loads a host operating system file into the SQL buffer. |
HELP | Accesses the SQL*Plus help system. |
HOST | Executes a host operating system command without leaving SQL*Plus. |
INPUT | Adds one or more new lines after the current line in the buffer. |
LIST | Lists one or more lines of the SQL buffer. |
PAUSE | Displays an empty line followed by a line containing text, then waits for the user to press [Return], or displays two empty lines and waits for the user's response. |
Displays the current value of a bind variable. | |
PROMPT | Sends the specified message or a blank line to the user's screen. |
REMARK | Begins a comment in a command file. |
REPFOOTER | Places and formats a specified report footer at the bottom of each report, or lists the current REPFOOTER definition. |
REPHEADER | Places and formats a specified report header at the top of each report, or lists the current REPHEADER definition. |
RUN | Lists and executes the SQL command or PL/SQL block currently stored in the SQL buffer. |
RUNFORM | Invokes a SQL*Forms application from within SQL*Plus. |
SAVE | Saves the contents of the SQL buffer in a host operating system file (a command file). |
SET | Sets a system variable to alter the SQL*Plus environment for your current session. |
SHOW | Shows the value of a SQL*Plus system variable or the current SQL*Plus environment. |
SPOOL | Stores query results in an operating system file and, optionally, sends the file to a printer. |
SQLPLUS | Starts SQL*Plus from the operating system prompt. |
START | Executes the contents of the specified command file. |
STORE | Saves attributes of the current SQL*Plus environment in a host operating system file (a command file). |
TIMING | 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. |
TTITLE | Places and formats a specified title at the top of each report page, or lists the current TTITLE definition. |
UNDEFINE | Deletes one or more user variables that you defined either explicitly (with the DEFINE command) or implicitly (with an argument to the START command). |
VARIABLE | Declares a bind variable that can be referenced in PL/SQL. |
WHENEVER OSERROR | Exits SQL*Plus if an operating system command generates an error. |
WHENEVER SQLERROR | Exits SQL*Plus if a SQL command or PL/SQL block generates an error. |
Runs the specified command file.
Syntax
@ file_name[.ext] [arg...]
Terms and Clauses
Refer to the following list for a description of each term or clause:
file_name[.ext]
Represents the command file 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 the SUFFIX variable of the SET command in this chapter.
When you enter @ 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. Consult the Oracle installation and user's manual(s) 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 command file. If you enter one or more arguments, SQL*Plus substitutes the values into the parameters (&1, &2, and so forth) in the command file. 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 of the arguments; if you run the command file again in this session, you can enter new arguments or omit the arguments to use the current values.
For more information on using parameters, refer to the subsection "Passing Parameters through the START Command" under "Writing Interactive Commands".
Usage Notes
You can include in a command file any command you would normally enter interactively (typically, SQL, SQL*Plus commands, or PL/SQL blocks).
An EXIT or QUIT command used in a command file terminates SQL*Plus.
The @ command functions similarly to START.
If the START command is disabled (see "Disabling SQL*Plus, SQL, and PL/SQL Commands" in Appendix E), this will also disable the @ command. See START in this chapter for information on the START 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 the SQLTERMINATOR variable of the SET command in this chapter for more information.
Example
To run a command filenamed PRINTRPT with the extension SQL, enter
SQL> @PRINTRPT
To run a command filenamed WKRPT with the extension QRY, enter
SQL> @WKRPT.QRY
Runs a nested command file. This command is identical to the @ ("at" sign) command except that it looks for the specified command file in the same path as the command file from which it was called.
Syntax
@@ file_name[.ext]
Terms and Clauses
Refer to the following for a description of the term or clause:
file_name[.ext]
Represents the nested command file 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 the SUFFIX variable of the SET command in this chapter.
When you enter @@file_name.ext from within a command file, SQL*Plus runs file_name.ext from the same directory as the command file. When you enter @@file_name.ext interactively, SQL*Plus runs file_name.ext from the current working directory. If SQL*Plus does not find such a file, SQL*Plus searches a system-dependent path to find the file. Some operating systems may not support the path search. Consult the Oracle installation and user's manual(s) provided for your operating system for specific information related to your operating system environment.
Usage Notes
You can include in a command file any command you would normally enter interactively (typically, SQL or SQL*Plus commands).
An EXIT or QUIT command used in a command file terminates SQL*Plus.
The @@ command functions similarly to START.
If the START command is disabled, this will also disable the @@ command. See START in this chapter for further information on the START 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 the SQLTERMINATOR variable of the SET command in this chapter for more information.
Example
Suppose that you have the following command filenamed PRINTRPT:
SELECT * FROM EMP @EMPRPT @@ WKRPT
When you START PRINTRPT and it reaches the @ command, it looks for the command filenamed EMPRPT in the current working directory and runs it. When PRINTRPT reaches the @@ command, it looks for the command filenamed WKRPT in the same path as PRINTRPT and runs it.
Executes the SQL command or PL/SQL block currently stored in the SQL buffer.
Syntax
/
Usage Notes
You can enter a slash (/) at the command prompt or at a line number prompt of a multi-line command.
The slash command functions similarly to RUN, but does not list the command in the buffer on your screen.
Executing a SQL command or PL/SQL block using the slash command will not cause the current line number in the SQL buffer to change unless the command in the buffer contains an error. In that case, SQL*Plus changes the current line number to the number of the line containing the error.
Example
To see the SQL command(s) you will execute, you can list the contents of the buffer:
SQL> LIST 1* SELECT ENAME, JOB FROM EMP WHERE ENAME = 'JAMES'
Enter a slash (/) at the command prompt to execute the command in the buffer:
SQL> /
For the above query, SQL*Plus displays the following output:
ENAME JOB ---------- --------- JAMES CLERK
Reads a line of input and stores it in a given user variable.
Syntax
ACC[EPT] variable [NUM[BER]|CHAR|DATE] [FOR[MAT] format] [DEF[AULT] default] [PROMPT text|NOPR[OMPT]] [HIDE]
Terms and Clauses
Refer to the following list for a description of each term or clause:
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 datatype of variable the datatype NUMBER. If the reply does not match the datatype, ACCEPT gives an error message and prompts again.
CHAR
Makes the datatype of variable the datatype CHAR. The maximum CHAR length limit 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.
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 for a reply. The format element must be a text constant such as A10 or 9.999. See the COLUMN command in this chapter for a complete list of format elements.
Oracle date formats such as 'dd/mm/yy' are valid when the datatype is DATE. DATE without a specified format defaults to the Oracle NLS_DATE_FORMAT of the current session. See the Oracle7 Server Administrator's Guide and the SQL Language Reference Guide for information on Oracle 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 in this chapter for more information.
Examples
To display the prompt "Password: ", place the reply in a CHAR variable named PSWD, and suppress the display, enter
SQL> 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
SQL> 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 named HIRED with the format "dd/mm/yy" and a default of "01/01/94", enter
SQL> ACCEPT hired DATE FORMAT 'dd/mm/yy' DEFAULT '01/01/94'- > PROMPT 'Enter date hired: '
To display the prompt "Enter employee lastname: " and place the reply in a CHAR variable named LASTNAME, enter
SQL> ACCEPT lastname CHAR FORMAT 'A20' - > PROMPT 'Enter employee lastname: '
Adds specified text to the end of the current line in the SQL buffer.
Syntax
A[PPEND] text
Terms and Clauses
Refer to the following for a description of the term or clause:
text
Represents the text you wish to append. If you wish to separate text from the preceding characters with a space, enter two spaces between APPEND and text.
To APPEND text that ends with a semicolon, end the command with two semicolons (SQL*Plus interprets a single semicolon as an optional command terminator).
Examples
To append a space and the column name DEPT to the second line of the buffer, make that line the current line by listing the line as follows:
SQL> 2 2* FROM EMP,
Now enter APPEND:
SQL> APPEND DEPT SQL> 2 2* FROM EMP, DEPT
Notice the double space between APPEND and DEPT. The first space separates APPEND from the characters to be appended; the second space becomes the first appended character.
To append a semicolon to the line, enter
SQL> APPEND ;;
SQL*Plus appends the first semicolon to the line and interprets the second as the terminator for the APPEND command.
Specifies where and how formatting will change in a report, such as
Syntax
BRE[AK] [ON report_element [action [action]]] ...
where:
report_element
Requires the following syntax:
{column|expr|ROW|REPORT}
action
Requires the following syntax:
[SKI[P] n|[SKI[P]] PAGE] [NODUP[LICATES]|DUP[LICATES]]
Terms and Clauses
Refer to the following list for a description of each term or clause:
ON column [action [action]]
When you include action(s), specifies action(s) for SQL*Plus to take whenever a break occurs in the specified column (called the break column). (column cannot have a table or view appended to it. To achieve this, you can alias the column in the SQL statement.) A break is one of three events:
When you omit action(s), BREAK ON column suppresses printing of duplicate values in column and marks a place in the report where SQL*Plus will perform the computation you specify in a corresponding COMPUTE command.
You can specify ON column one or more times. If you specify multiple ON clauses, as in
SQL> BREAK ON DEPTNO SKIP PAGE ON JOB - > SKIP 1 ON SAL SKIP 1
the first ON clause represents the outermost break (in this case, ON DEPTNO) and the last ON clause represents the innermost break (in this case, ON SAL). SQL*Plus searches each row of output for the specified break(s), starting with the outermost break and proceeding--in the order you enter the clauses--to the innermost. In the example, SQL*Plus searches for a change in the value of DEPTNO, then JOB, then SAL.
Next, SQL*Plus executes actions beginning with the action specified for the innermost break and proceeding in reverse order toward the outermost break (in this case, from SKIP 1 for ON SAL toward SKIP PAGE for ON DEPTNO). SQL*Plus executes each action up to and including the action specified for the first occurring break encountered in the initial search.
If, for example, in a given row the value of JOB changes--but the values of DEPTNO and SAL remain the same--SQL*Plus skips two lines before printing the row (one as a result of SKIP 1 in the ON SAL clause and one as a result of SKIP 1 in the ON JOB clause).
Whenever you use ON column, you should also use an ORDER BY clause in the SQL SELECT command. Typically, the columns used in the BREAK command should appear in the same order in the ORDER BY clause (although all columns specified in the ORDER BY clause need not appear in the BREAK command). This prevents breaks from occurring at meaningless points in the report. The following SELECT command produces meaningful results:
SQL> SELECT DEPTNO, JOB, SAL, ENAME 2 FROM EMP 3 ORDER BY DEPTNO, JOB, SAL, ENAME;
All rows with the same DEPTNO print together on one page, and within that page all rows with the same JOB print in groups. Within each group of jobs, jobs with the same SAL print in groups. Breaks in ENAME cause no action because ENAME does not appear in the BREAK command.
ON expr [action [action]]
When you include action(s), specifies action(s) for SQL*Plus to take when the value of the expression changes.
When you omit action(s), BREAK ON expr suppresses printing of duplicate values of expr and marks a place in the report where SQL*Plus will perform the computation you specify in a corresponding COMPUTE command.
You can use an expression involving one or more table columns or an alias assigned to a report column in a SQL SELECT or SQL*Plus COLUMN command. If you use an expression in a BREAK command, you must enter expr exactly as it appears in the SELECT command. If the expression in the SELECT command is a+b, for example, you cannot use b+a or (a+b) in a BREAK command to refer to the expression in the SELECT command.
The information given above for ON column also applies to ON expr.
ON ROW [action [action]]
When you include action(s), specifies action(s) for SQL*Plus to take when a SQL SELECT command returns a row. The ROW break becomes the innermost break regardless of where you specify it in the BREAK command. You should always specify an action when you BREAK on a row.
ON REPORT [action]
Marks a place in the report where SQL*Plus will perform the computation you specify in a corresponding COMPUTE command. Use BREAK ON REPORT in conjunction with COMPUTE to print grand totals or other "grand" computed values.
The REPORT break becomes the outermost break regardless of where you specify it in the BREAK command.
Note that SQL*Plus will not skip a page at the end of a report, so you cannot use BREAK ON REPORT SKIP PAGE.
Refer to the following list for a description of each action:
SKI[P] n
Skips n lines before printing the row where the break occurred.
[SKI[P]] PAGE
Skips the number of lines that are defined to be a page before printing the row where the break occurred. The number of lines per page can be set via the PAGESIZE clause of the SET command. Note that PAGESIZE only changes the number of lines that SQL*Plus considers to be a page. Therefore, SKIP PAGE may not always cause a physical page break, unless you have also specified NEWPAGE 0. Note also that if there is a break after the last row of data to be printed in a report, SQL*Plus will not skip the page.
NODUP[LICATES]
Prints blanks rather than the value of a break column when the value is a duplicate of the column's value in the preceding row.
DUP[LICATES]
Prints the value of a break column in every selected row.
Enter BREAK with no clauses to list the current break definition.
Usage Notes
Each new BREAK command you enter replaces the preceding one.
To remove the BREAK command, use CLEAR BREAKS.
Example
To produce a report that prints duplicate job values, prints the average of SAL and inserts one blank line when the value of JOB changes, and additionally prints the sum of SAL and inserts another blank line when the value of DEPTNO changes, you could enter the following commands. (The example selects departments 10 and 30 and the jobs of clerk and salesman only.)
SQL> BREAK ON DEPTNO SKIP 1 ON JOB SKIP 1 DUPLICATES SQL> COMPUTE SUM OF SAL ON DEPTNO SQL> COMPUTE AVG OF SAL ON JOB SQL> SELECT DEPTNO, JOB, ENAME, SAL FROM EMP 2 WHERE JOB IN ('CLERK', 'SALESMAN') 3 AND DEPTNO IN (10, 30) 4 ORDER BY DEPTNO, JOB;
The following output results:
DEPTNO JOB ENAME SAL --------- --------- ---------- --------- 10 CLERK MILLER 1300 ********* --------- avg 1300 ********** ---------- sum 1300 30 CLERK JAMES 1045 ********* ---------- avg 1045 SALESMAN ALLEN 1760 SALESMAN MARTIN 1375 SALESMAN TURNER 1650 SALESMAN WARD 1375 ********* ---------- avg 1540 ********** ---------- sum 7205
Places and formats a specified title at the bottom of each report page or lists the current BTITLE definition.
For a description of the old form of BTITLE, see Appendix F.
Syntax
BTI[TLE] [printspec [text|variable] ...]|[OFF|ON]
Terms and Clauses
Refer to the TTITLE command for additional information on terms and clauses in the BTITLE command syntax.
Enter BTITLE with no clauses to list the current BTITLE definition.
Usage Notes
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.
Examples
To set a bottom title with CORPORATE PLANNING DEPARTMENT on the left and a date on the right, enter
SQL> BTITLE LEFT 'CORPORATE PLANNING DEPARTMENT' - > RIGHT '11 Mar 1988'
To set a bottom title with CONFIDENTIAL in column 50, followed by six spaces and a date, enter
SQL> BTITLE COL 50 'CONFIDENTIAL' TAB 6 '11 Mar 88'
Changes the first occurrence of text on the current line in the buffer.
Syntax
C[HANGE] sepchar old [sepchar [new [sepchar]]]
Terms and Clauses
Refer to the following list for a description of each term or clause:
sepchar
Represents any non-alphanumeric character such as "/" or "!". Use a sepchar that does not appear in old or new. You can omit the space between CHANGE and the first sepchar.
old
Represents the text you wish to change. CHANGE ignores case in searching for old. For example,
CHANGE /aq/aw
will find the first occurrence of "aq", "AQ", "aQ", or "Aq" and change it to "aw". SQL*Plus inserts the new text exactly as you specify it.
If old is prefixed with "...", it matches everything up to and including the first occurrence of old. If it is suffixed with "...", it matches the first occurrence of old and everything that follows on that line. If it contains an embedded "...", it matches everything from the preceding part of old through the following part of old.
new
Represents the text with which you wish to replace old. If you omit new and, optionally, the second and third sepchars, CHANGE deletes old from the current line of the buffer.
Usage Notes
You can also use CHANGE to modify a line in the buffer that has generated an Oracle error. SQL*Plus sets the buffer's current line to the line containing the error so that you can make modifications.
To re-enter an entire line, you can type the line number followed by the new contents of the line. If you specify a line number larger than the number of lines in the buffer and follow the number with text, SQL*Plus adds the text in a new line at the end of the buffer. If you specify zero ("0") for the line number and follow the zero with text, then SQL*Plus inserts the line at the beginning of the buffer (that line becomes line 1).
Examples
Assume the current line of the buffer contains the following text:
4* WHERE JOB IS IN ('CLERK','SECRETARY','RECEPTIONIST')
Enter the following command:
SQL> C /RECEPTIONIST/GUARD/
The text in the buffer changes as follows:
4* WHERE JOB IS IN ('CLERK','SECRETARY','GUARD')
Or enter the following command:
SQL> C /'CLERK',.../'CLERK')/
The original line changes to
4* WHERE JOB IS IN ('CLERK')
Or enter the following command:
SQL> C /(...)/('COOK','BUTLER')/
The original line changes to
4* WHERE JOB IS IN ('COOK','BUTLER')
You can replace the contents of an entire line using the line number. This entry
SQL> 2 FROM EMP e1
causes the second line of the buffer to be replaced with
FROM EMP e1
Note that entering a line number followed by a string will replace the line regardless of what text follows the line number. Thus,
SQL> 2 c/old/new/
will change the second line of the buffer to be
2* c/old/new/
Resets or erases the current value or setting for the specified option.
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]
Terms and Clauses
Refer to the following list for a description of each term or clause:
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 BUFFER in Appendix F).
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.
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 BUFFER in Appendix F).
TIMI[NG]
Deletes all timers created by the TIMING command.
Examples
To clear breaks, enter
SQL> CLEAR BREAKS
To clear column definitions, enter
SQL> CLEAR COLUMNS
Specifies display attributes for a given column, such as
Syntax
COL[UMN] [{column|expr} [option ...]]
where option represents one of the following clauses:
ALI[AS] alias CLE[AR] FOLD_A[FTER] FOLD_B[EFORE] FOR[MAT] format HEA[DING] text JUS[TIFY] {L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]} LIKE {expr|alias} NEWL[INE] NEW_V[ALUE] variable NOPRI[NT]|PRI[NT] NUL[L] text OLD_V[ALUE] variable ON|OFF WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]
Terms and Clauses
Enter COLUMN followed by column or expr and no other clauses to list the current display attributes for only the specified column or expression. Enter COLUMN with no clauses to list all current column display attributes.
Refer to the following list for a description of each term or clause:
{column|expr}
Identifies the data item (typically, the name of a column) in a SQL SELECT command to which the column command refers. If you use an expression in a COLUMN command, you must enter expr exactly as it appears in the SELECT command. If the expression in the SELECT command is a+b, for example, you cannot use b+a or (a+b) in a COLUMN command to refer to the expression in the SELECT command.
If you select columns with the same name from different tables, a COLUMN command for that column name will apply to both columns. That is, a COLUMN command for the column ENAME applies to all columns named ENAME that you reference in this session. COLUMN ignores table name prefixes in SELECT commands. Also, spaces are ignored unless the name is placed in double quotes.
To format the columns differently, assign a unique alias to each column within the SELECT command itself (do not use the ALIAS clause of the COLUMN command) and enter a COLUMN command for each column's alias.
ALI[AS] alias
Assigns a specified alias to a column, which can be used to refer to the column in BREAK, COMPUTE, and other COLUMN commands.
Note: A SQL*Plus alias is different from a SQL alias. See the Oracle7 Server SQL Language Reference Manual for further information on the SQL alias.
CLE[AR]
Resets the display attributes for the column to default values.
To reset the attributes for all columns, use the CLEAR COLUMNS command.
FOLD_A[FTER]
Inserts a carriage return after the column heading and after each row in the column. SQL*Plus does not insert an extra carriage return after the last column in the SELECT list.
FOLD_B[EFORE]
Inserts a carriage return before the column heading and before each row of the column. SQL*Plus does not insert an extra carriage return before the first column in the SELECT list.
Specifies the display format of the column. The format specification must be a text constant such as A10 or $9,999--not a variable.
Character Columns The default width of CHAR and VARCHAR2 (VARCHAR) columns is the width of the column in the database. SQL*Plus formats CHAR and VARCHAR2 (VARCHAR) data left-justified. If a value does not fit within the column width, SQL*Plus wraps or truncates the character string depending on the setting of SET WRAP. The width cannot exceed 32,767 or the value set with SET MAXDATA. (VARCHAR2 requires Oracle7.)
A LONG column's width defaults to the value of SET LONGCHUNKSIZE or SET LONG, whichever one is smaller.
A Trusted Oracle column of datatype MLSLABEL or RAW MLSLABEL defaults to the width defined for the column in the database or the length of the column's heading, whichever is longer. The default display width for a Trusted Oracle column of datatype ROWLABEL is 15.
To change the width of a CHAR, VARCHAR2 (VARCHAR), LONG, or Trusted Oracle column to n, use FORMAT An. (A stands for alphanumeric.) If you specify a width shorter than the column heading, SQL*Plus truncates the heading. If you specify a width for a LONG column, SQL*Plus uses the LONGCHUNKSIZE or the specified width, whichever is smaller, as the column width.
DATE Columns For Oracle7, the default width and format of unformatted DATE columns in SQL*Plus is derived from the NLS parameters in effect. Otherwise, the default width is A9. In Oracle7, the NLS parameters may be set in your database parameter file or may be environment variables or an equivalent platform-specific mechanism. They may also be specified for each session with the ALTER SESSION command. (See the documentation for the Oracle7 Server for a complete description of the NLS parameters).
When you use SQL functions like TO_CHAR, Oracle automatically allows for a very wide column.
To change the width of a DATE column to n, use the COLUMN command with FORMAT An. If you specify a width shorter than the column heading, the heading is truncated.
NUMBER Columns To change a NUMBER column's width, use FORMAT followed by an element as specified in Table 6 - 1.
The MI and PR format elements can only appear in the last position of a number format model. The S format element can only appear in the first or last position.
If a number format model does not contain the MI, S or PR format elements, negative return values automatically contain a leading negative sign and positive values automatically contain a leading space.
A number format model can contain only a single decimal character (D) or period (.), but it can contain multiple group separators (G) or commas (,). A group separator or comma cannot appear to the right of a decimal character or period in a number format model.
SQL*Plus formats NUMBER data right-justified. A NUMBER column's width equals the width of the heading or the width of the FORMAT plus one space for the sign, whichever is greater. If you do not explicitly use FORMAT, then the column's width will always be at least the value of SET NUMWIDTH.
If a value does not fit within the column width, SQL*Plus indicates overflow by displaying a pound sign (#) in place of each digit the width allows.
If a positive value is extremely large and a numeric overflow occurs when rounding a number, then the infinity sign (~) replaces the value. Likewise, if a negative value is extremely small and a numeric overflow occurs when rounding a number, then the negative infinity sign replaces the value (-~).
With all number formats, SQL*Plus rounds each value to the specified number of significant digits as set with the SET NUMWIDTH command.
HEA[DING] text
Defines a column heading. If you do not use a HEADING clause, the column's heading defaults to column or expr. If text contains blanks or punctuation characters, you must enclose it with single or double quotes. Each occurrence of the HEADSEP character (by default, '|') begins a new line. For example,
COLUMN ENAME HEADING 'Employee |Name'
would produce a two-line column heading. See the HEADSEP variable of the SET command in this chapter for information on changing the HEADSEP character.
JUS[TIFY] {L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]}
Aligns the heading. If you do not use a JUSTIFY clause, headings for NUMBER columns default to RIGHT and headings for other column types default to LEFT.
LIKE {expr|alias}
Copies the display attributes of another column or expression (whose attributes you have already defined with another COLUMN command). LIKE copies only attributes not defined by another clause in the current COLUMN command.
NEWL[INE]
Starts a new line before displaying the column's value. NEWLINE has the same effect as FOLD_BEFORE.
Specifies a variable to hold a column value. You can reference the variable in TTITLE commands. Use NEW_VALUE to display column values or the date in the top title. You must include the column in a BREAK command with the SKIP PAGE action. The variable name cannot contain a pound sign (#).
NEW_VALUE is useful for master/detail reports in which there is a new master record for each page. For master/detail reporting, you must also include the column in the ORDER BY clause. See the example at the end of this command description.
For information on displaying a column value in the bottom title, see COLUMN OLD_VALUE. Refer to TTITLE for more information on referencing variables in titles. See COLUMN FORMAT for details on formatting and valid format models.
NOPRI[NT]|PRI[NT]
Controls the printing of the column (the column heading and all the selected values). NOPRINT turns the printing of the column off. PRINT turns the printing of the column on.
NUL[L] text
Controls the text SQL*Plus displays for null values in the given column. The default is a white space. SET NULL controls the text displayed for all null values for all columns, unless overridden for a specific column by the NULL clause of the COLUMN command.
Specifies a variable to hold a column value. You can reference the variable in BTITLE commands. Use OLD_VALUE to display column values in the bottom title. You must include the column in a BREAK command with the SKIP PAGE action.
OLD_VALUE is useful for master/detail reports in which there is a new master record for each page. For master/detail reporting, you must also include the column in the ORDER BY clause.
For information on displaying a column value in the top title, see COLUMN NEW_VALUE. Refer to TTITLE for more information on referencing variables in titles.
ON|OFF
Controls the status of display attributes for a column. OFF disables the attributes for a column without affecting the attributes' definition. ON reinstates the attributes.
WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]
Specifies how SQL*Plus will treat a CHAR, VARCHAR2, LONG, or DATE string that is too wide for a column. WRAPPED wraps the string within the column bounds, beginning new lines when required. When WORD_WRAP is enabled, SQL*Plus left justifies each new line, skipping all leading whitespace (for example, returns, newline characters, tabs and spaces), including embedded newline characters. Embedded whitespace not on a line boundary is not skipped. TRUNCATED truncates the string at the end of the first line of display.
Usage Notes
You can enter any number of COLUMN commands for one or more columns. All column attributes set for each column remain in effect for the remainder of the session, until you turn the column OFF, or until you use the CLEAR COLUMN command. Thus, the COLUMN commands you enter can control a column's display attributes for multiple SQL SELECT commands.
When you enter multiple COLUMN commands for the same column, SQL*Plus applies their clauses collectively. If several COLUMN commands apply the same clause to the same column, the last one entered will control the output.
Examples
To make the ENAME column 20 characters wide and display EMPLOYEE NAME on two lines at the top, enter
SQL> COLUMN ENAME FORMAT A20 HEADING 'EMPLOYEE |NAME'
To format the SAL column so that it shows millions of dollars, rounds to cents, uses commas to separate thousands, and displays $0.00 when a value is zero, you would enter
SQL> COLUMN SAL FORMAT $9,999,990.99
To assign the alias NET to a column containing a long expression, to display the result in a dollar format, and to display <NULL> for null values, you might enter
SQL> COLUMN SAL+COMM+BONUS-EXPENSES-INS-TAX ALIAS NET SQL> COLUMN NET FORMAT $9,999,999.99 NULL '<NULL>'
Note that the example divides this column specification into two commands. The first defines the alias NET, and the second uses NET to define the format.
Also note that in the first command you must enter the expression exactly as you entered it (or will enter it) in the SELECT command. Otherwise, SQL*Plus cannot match the COLUMN command to the appropriate column.
To wrap long values in a column named REMARKS, you can enter
SQL> COLUMN REMARKS FORMAT A20 WRAP
For example:
CUSTOMER DATE QUANTITY REMARKS ---------- --------- -------- -------------------- 123 25-AUG-86 144 This order must be s hipped by air freigh t to ORD
If you replace WRAP with WORD_WRAP, REMARKS looks like this:
CUSTOMER DATE QUANTITY REMARKS ---------- --------- -------- --------------------- 123 25-AUG-86 144 This order must be shipped by air freight to ORD
If you specify TRUNCATE, REMARKS looks like this:
CUSTOMER DATE QUANTITY REMARKS ---------- --------- -------- -------------------- 123 25-AUG-86 144 This order must be s
In order to print the current date and the name of each job in the top title, enter the following. (For details on creating a date variable, see "Displaying the Current Date in Titles" under "Defining Page Titles and Dimensions".)
SQL> COLUMN JOB NOPRINT NEW_VALUE JOBVAR SQL> COLUMN TODAY NOPRINT NEW_VALUE DATEVAR SQL> BREAK ON JOB SKIP PAGE ON TODAY SQL> TTITLE CENTER 'Job Report' RIGHT DATEVAR SKIP 2 - > LEFT 'Job: ' JOBVAR SKIP 2 SQL> SELECT TO_CHAR(SYSDATE, 'MM/DD/YY') TODAY, 2 ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO 3 FROM EMP WHERE JOB IN ('CLERK', 'SALESMAN') 4 ORDER BY JOB, ENAME;
Your two page report would look similar to the following report, with "Job Report" centered within your current linesize:
Job Report 08/01/94 Job: CLERK ENAME MGR HIREDATE SAL DEPTNO ---------- ------- --------- ----------- ---------- ADAMS 7788 14-JAN-87 1100 20 JAMES 7698 03-DEC-81 950 30 MILLER 7782 23-JAN-82 1300 10 SMITH 7902 17-DEC-80 800 20
Job Report 08/01/94 Job: CLERK ENAME MGR HIREDATE SAL DEPTNO ---------- ------- --------- ----------- ---------- ALLEN 7698 20-JAN-81 1600 30 MARTIN 7698 03-DEC-81 950 30 MILLER 7782 23-JAN-82 1300 10 SMITH 7902 17-DEC-80 800 20
To change the default format of DATE columns to 'YYYY-MM-DD', you can enter
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
The following output results:
Session altered
To display the change, enter a SELECT statement, such as:
SQL> SELECT HIREDATE 2 FROM EMP 3 WHERE EMPNO = 7839;
The following output results:
HIREDATE ---------- 1981-11-17
See the Oracle7 Server SQL Language Reference Manual for information on the ALTER SESSION command.
Note that in a SELECT statement, some SQL calculations or functions, such as TO_CHAR, may cause a column to be very wide. In such cases, use the FORMAT option to alter the column width.
Calculates and prints summary lines, using various standard computations, on subsets of selected rows, or lists all COMPUTE definitions. (For details on how to create summaries, see "Clarifying Your Report with Spacing and Summary Lines".)
Syntax
COMP[UTE] [function [LAB[EL] text] ... OF {expr|column|alias} ... ON {expr|column|alias|REPORT|ROW} ...]
Terms and Clauses
Refer to the following list for a description of each term or clause:
function ...
Represents one of the functions listed in Table 6-2. If you specify more than one function, use spaces to separate the functions.
Function | Computes | Applies to Datatypes |
AVG | Average of non-null values | NUMBER |
COU[NT] | Count of non-null values | all types |
MAX[IMUM] | Maximum value | NUMBER, CHAR, VARCHAR2 (VARCHAR) |
MIN[IMUM] | Minimum value | NUMBER, CHAR, VARCHAR2 (VARCHAR) |
NUM[BER] | Count of rows | all types |
STD | Standard deviation of non-null values | NUMBER |
SUM | Sum of non-null values | NUMBER |
VAR[IANCE] | Variance of non-null values | NUMBER |
Table 6 - 2. COMPUTE Functions |
Defines the label to be printed for the computed value. If no LABEL clause is used, text defaults to the unabbreviated function keyword. If text contains spaces or punctuation, you must enclose it with single quotes. The label prints left justified and truncates to the column width or linesize, whichever is smaller. The maximum length of a label is 500 characters.
The label for the computed value appears in the break column specified. To suppress the label, use the NOPRINT option of the COLUMN command on the break column.
If you repeat a function in a COMPUTE command, SQL*Plus issues a warning and uses the first occurrence of the function.
With ON REPORT and ON ROW computations, the label appears in the first column listed in the SELECT statement. The label can be suppressed by using a NOPRINT column first in the SELECT statement. When you compute a function of the first column in the SELECT statement ON REPORT or ON ROW, then the computed value appears in the first column and the label is not displayed. To see the label, select a dummy column first in the SELECT list.
OF {expr|column|alias}...
Specifies the column(s) or expression(s) you wish to use in the computation. (column cannot have a table or view appended to it. To achieve this, you can alias the column in the SQL statement.) You must also specify these columns in the SQL SELECT command, or SQL*Plus will ignore the COMPUTE command.
If you use a SQL SELECT list alias, you must use the SQL alias in the COMPUTE command, not the column name. If you use the column name in this case, SQL*Plus will ignore the COMPUTE command.
If you do not want the computed values of a column to appear in the output of a SELECT command, specify that column in a COLUMN command with a NOPRINT clause. Use spaces to separate multiple expressions, columns, or aliases within the OF clause.
ON {expr|column|alias|REPORT|ROW} ...
Specifies the event SQL*Plus will use as a break. (column cannot have a table or view appended to it. To achieve this, you can alias the column in the SQL statement.) COMPUTE prints the computed value and restarts the computation when the event occurs (that is, when the value of the expression changes, a new ROW is fetched, or the end of the report is reached).
If multiple COMPUTE commands reference the same column in the ON clause, only the last COMPUTE command applies.
Enter COMPUTE without clauses to list all COMPUTE definitions.
Usage Notes
In order for the computations to occur, the following conditions must all be true:
Examples
To subtotal the salary for the "clerk", "analyst", and "salesman" job classifications with a compute label of "TOTAL", enter
SQL> BREAK ON JOB SKIP 1 SQL> COMPUTE SUM LABEL 'TOTAL' OF SAL ON JOB SQL> SELECT JOB, ENAME, SAL 2 FROM EMP 3 WHERE JOB IN ('CLERK', 'ANALYST', 'SALESMAN') 4 ORDER BY JOB, SAL;
The following output results:
JOB ENAME SAL --------- ---------- ---------- ANALYST SCOTT 3000 FORD 3000 ********* ---------- TOTAL 6000 CLERK SMITH 800 JAMES 950 ADAMS 1100 MILLER 1300 ********* ---------- TOTAL 4150 SALESMAN WARD 1250 MARTIN 1250 TURNER 1500 ALLEN 1600 ********* ---------- TOTAL 5600
To calculate the total of salaries less than 1,000 on a report, enter
SQL> COMPUTE SUM OF SAL ON REPORT SQL> BREAK ON REPORT SQL> COLUMN DUMMY HEADING '' SQL> SELECT ' ' DUMMY, SAL, EMPNO 2 FROM EMP 3 WHERE SAL < 1000 4 ORDER BY SAL;
The following output results:
SAL EMPNO --- ---------- ----------- 800 7369 950 7900 ---------- sum 5350
To compute the average and maximum salary for the accounting and sales departments, enter
SQL> BREAK ON DNAME SKIP 1 SQL> COMPUTE AVG LABEL 'Dept Average' - > MAX LABEL 'Dept Maximum' - > OF SAL ON DNAME SQL> SELECT DNAME, ENAME, SAL 2 FROM DEPT, EMP 3 WHERE DEPT.DEPTNO = EMP.DEPTNO 4 AND DNAME IN ('ACCOUNTING', 'SALES') 5 ORDER BY DNAME;
The following output results:
DNAME ENAME SAL -------------- ---------- ---------- ACCOUNTING CLARK 2450 KING 5000 MILLER 1300 ************** ---------- Dept Average 2916.66667 Dept Maximum 5000 SALES ALLEN 1600 WARD 1250 JAMES 950 TURNER 1500 MARTIN 1250 BLAKE 2850 ************** ---------- Dept Average 1566.66667 Dept Maximum 2850
To compute the sum of salaries for departments 10 and 20 without printing the compute label:
SQL> COLUMN DUMMY NOPRINT SQL> COMPUTE SUM OF SAL ON DUMMY SQL> BREAK ON DUMMY SKIP 1 SQL> SELECT DEPTNO DUMMY, DEPTNO, ENAME, SAL 2 FROM EMP 3 WHERE DEPTNO <= 20 4 ORDER BY DEPTNO;
SQL*Plus displays the following output:
DEPTNO ENAME SAL ---------- ---------- ---------- 10 KING 5000 10 CLARK 2450 10 MILLER 1300 ---------- 8750 20 JONES 2975 20 FORD 3000 20 SMITH 800 20 SCOTT 3000 20 ADAMS 1100 ---------- 10875
If, instead, you do not want to print the label, only the salary total at the end of the report:
SQL> COLUMN DUMMY NOPRINT SQL> COMPUTE SUM OF SAL ON DUMMY SQL> BREAK ON DUMMY SQL> SELECT NULL DUMMY, DEPTNO, ENAME, SAL 2 FROM EMP 3 WHERE DEPTNO <= 20 4 ORDER BY DEPTNO;
SQL*Plus displays the following output:
DEPTNO ENAME SAL ---------- ---------- ---------- 10 KING 5000 10 CLARK 2450 10 MILLER 1300 20 JONES 2975 20 FORD 3000 20 SMITH 800 20 SCOTT 3000 20 ADAMS 1100 ---------- 19625
Connects a given username to Oracle.
Syntax
CONN[ECT] [logon]
where:
logon
Requires the following syntax: username[/password][@database_specification]|/
Terms and Clauses
Refer to the following list for a description of each term or clause:
username [/password]
Represent the username and password with which you wish to connect to Oracle. If you omit username and password, SQL*Plus prompts you for them. If you enter a slash (/) or simply enter [Return] to the prompt for username, SQL*Plus logs you in using a default logon (see "/" below).
If you omit only password, SQL*Plus prompts you for password. When prompting, SQL*Plus does not display password on your terminal screen.
Represents a default logon using operating system authentication. You cannot enter a database_specification 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 Oracle7 Server Administrator's Guide for information about operating system authentication.
database specification
Consists of a SQL*Net connection string. The exact syntax depends upon the SQL*Net communications protocol your Oracle installation uses. For more information, refer to the SQL*Net manual appropriate for your protocol or contact your DBA. SQL*Plus does not prompt for a database specification, but uses your default database if you do not include a specification.
Usage Notes
CONNECT commits the current transaction to the database, disconnects the current username from Oracle, and reconnects with the specified username.
Examples
To connect across SQL*Net using username SCOTT and password TIGER to the database known by the SQL*Net alias as FLEETDB, enter
SQL> CONNECT SCOTT/TIGER@FLEETDB
To connect using username SCOTT, letting SQL*Plus prompt you for the password, enter
SQL> CONNECT SCOTT
Copies the data from a query to a table in a local or remote database.
Syntax
COPY {FROM username[/password]@database_specification| TO username[/password]@database_specification| FROM username[/password]@database_specification TO username[/password]@database_specification} {APPEND|CREATE|INSERT|REPLACE} destination_table [(column, column, column ...)] USING query
Terms and Clauses
Refer to the following list for a description of each term or clause:
username[/password]
Represent the Oracle username/password you wish to COPY FROM and TO. In the FROM clause, username/password identifies the source of the data; in the TO clause, username/password identifies the destination. If you do not specify password in either the FROM clause or the TO clause, SQL*Plus will prompt you for it. SQL*Plus suppresses the display of your response to these prompts.
database_specification
Consists of a SQL*Net connection string. You must include a database_specification clause in the COPY command. In the FROM clause, database_specification represents the database at the source; in the TO clause, database_specification represents the database at the destination. The exact syntax depends upon the SQL*Net communications protocol your Oracle installation uses. For more information, refer to the SQL*Net manual appropriate for your protocol or contact your DBA.
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.
FROM username[/password]@database_specification
Specifies the username, password, and 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 include a FROM clause to specify a source database other than the default.
TO username[/password]@database_specification
Specifies 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 include a TO clause to specify a destination database other than the default.
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 the 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.
Usage Notes
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 variable LONG 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 variable COPYCOMMIT to a positive value n, SQL*Plus performs a commit after copying every n batches of records. The SQL*Plus SET variable ARRAYSIZE determines the size of a batch.
Some operating environments require that database specifications be placed in double quotes.
Examples
The following command copies the entire EMP table to a table named WESTEMP. Note that the tables are located in two different databases. If WESTEMP already exists, SQL*Plus replaces the table and its contents. The columns in WESTEMP have the same names as the columns in the source table, EMP.
SQL> COPY FROM SCOTT/TIGER@HQ TO JOHN/CHROME@WEST - > REPLACE WESTEMP - > USING SELECT * FROM EMP
The following command copies selected records from EMP to the database to which SQL*Plus is connected. SQL*Plus creates SALESMEN through the copy. SQL*Plus copies only the columns EMPNO and ENAME, and at the destination names them EMPNO and SALESMAN.
SQL> COPY FROM SCOTT/TIGER@HQ - > CREATE SALESMEN (EMPNO,SALESMAN) - > USING SELECT EMPNO, ENAME FROM EMP - > WHERE JOB='SALESMAN'
Specifies a user variable and assigns it a CHAR value, or lists the value and variable type of a single variable or all variables.
Syntax
DEF[INE] [variable]|[variable = text]
Terms and Clauses
Refer to the following list for a description of each term or clause:
variable
Represents the user 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 user 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 user variables.
Usage Notes
DEFINEd variables retain their values until one of the following events occurs:
Note that you can use DEFINE to define the variable, _EDITOR, which establishes the host system editor invoked by the SQL*Plus EDIT command.
If you continue the value of a DEFINEd variable on multiple lines (using the SQL*Plus command continuation character), SQL*Plus replaces each continuation character and carriage return you enter with a space in the resulting variable. For example, SQL*Plus interprets
SQL> DEFINE TEXT = 'ONE- > TWO- > THREE'
as
SQL> DEFINE TEXT = 'ONE TWO THREE'
Examples
To assign the value MANAGER to the variable POS, type:
SQL> DEFINE POS = MANAGER
If you execute a command that contains a reference to &POS, SQL*Plus will substitute the value MANAGER for &POS and will not prompt you for a POS value.
To assign the CHAR value 20 to the variable DEPTNO, type:
SQL> DEFINE DEPTNO = 20
Even though you enter the number 20, SQL*Plus assigns a CHAR value to DEPTNO consisting of two characters, 2 and 0.
To list the definition of DEPTNO, enter
SQL> DEFINE DEPTNO DEFINE DEPTNO = "20" (CHAR)
This result shows that the value of DEPTNO is 20.
Deletes one or more lines of the buffer.
Syntax
DEL [n|n m|n *|n LAST|*|* n|* LAST|LAST]
Terms and Clauses
Refer to the following list for a description of each term or clause:
n
n m
n *
Deletes line n through the current line.
n LAST
Deletes line n through the last line.
*
* n
Deletes the current line through line n.
* LAST
Deletes the current line through the last line.
LAST
Deletes the last line.
Enter DEL with no clauses to delete the current line of the buffer.
Usage Notes
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 Oracle7 Server SQL Language Reference Manual.
Examples
Assume the SQL buffer contains the following query:
1 SELECT ENAME, DEPTNO 2 FROM EMP 3 WHERE JOB = 'SALESMAN' 4* ORDER BY DEPTNO
To make the line containing the WHERE clause the current line, you could enter
SQL> LIST 3 3* WHERE JOB = 'SALESMAN'
followed by
SQL> DEL
The SQL buffer now contains the following lines:
1 SELECT ENAME, DEPTNO 2 FROM EMP 3* ORDER BY DEPTNO
To delete the second line of the buffer, enter
SQL> DEL 2
The SQL buffer now contains the following lines:
1 SELECT ENAME, DEPTNO 2* ORDER BY DEPTNO
Lists the column definitions for the specified table, view, or synonym or the specifications for the specified function or procedure.
Syntax
DESC[RIBE] {[user.]table[@database_link_name] [column]| [user.]object[.subobject]}
Terms and Clauses
Refer to the following list for a description of each term or clause:
user
Represents the user who owns table or object. If you omit user, SQL*Plus assumes you own table or object.
table
Represents the table, view, or synonym you wish to describe.
database_link_name
Consists of the database link name corresponding to the database where table exists. For more information on which privileges allow access to another table in a different schema, refer to the Oracle7 Server SQL Language Reference Manual.
column
Represents the column in table you wish to describe.
object
Represents the function or procedure you wish to describe. If you want to describe a procedure that is in a package, object is the name of the package.
subobject
Represents the function or procedure in a package you wish to describe.
Usage Notes
The description for tables, views, and synonyms contains the following information:
The description for functions and procedures contains the following information:
To describe the table EMP, enter
SQL> DESCRIBE EMP
DESCRIBE lists the following information:
Name Null? Type ------------------------------ -------- ------------ EMPNO NOT NULL NUMBER(4) ENAME CHAR(10) JOB JOB(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2)
To describe a procedure called CUSTOMER_LOOKUP, enter
SQL> DESCRIBE customer_lookup
DESCRIBE lists the following information:
PROCEDURE customer_lookup Argument Name Type In/Out Default? ---------------------- -------- -------- --------- CUST_ID NUMBER IN CUST_NAME VARCHAR2 OUT
To describe the procedure APROC in the package APACK, enter
SQL> DESCRIBE apack.aproc
DESCRIBE lists the following information:
PROCEDURE apack.aproc
Argument Name Type In/Out Default? ---------------------- -------- -------- --------- P1 CHAR IN P2 NUMBER IN
Commits pending changes to the database and logs the current username out of Oracle, but does not exit SQL*Plus.
Syntax
DISC[ONNECT]
Usage Notes
Use DISCONNECT within a command file to prevent user access to the database when you want to log the user out of Oracle but have the user remain in SQL*Plus. Use EXIT or QUIT to log out of Oracle and return control to your host computer's operating system.
Example
Your command file might begin with a CONNECT command and end with a DISCONNECT, as shown below.
SQL> GET MYFILE 1 CONNECT ... . . . . 15* DISCONNECT
Invokes a host operating system text editor on the contents of the specified file or on the contents of the buffer.
Syntax
ED[IT] [file_name[.ext]]
Terms and Clauses
Refer to the following for a description of the term or clause:
file_name[.ext]
Represents the file you wish to edit (typically a command file).
Enter EDIT with no filename to edit the contents of the SQL buffer with the host operating system text editor.
Usage Notes
If you omit the file extension, SQL*Plus assumes the default command-file extension (normally SQL). For information on changing the default extension, see the SUFFIX variable of the SET command in this chapter.
If you specify a filename, SQL*Plus searches for the file in the current working directory. If SQL*Plus cannot find the file in the current working directory, it creates a file with the specified name.
The user variable, _EDITOR, contains the name of the text editor invoked by EDIT. You can change the text editor by changing the value of _EDITOR. See DEFINE for information about changing the value of a user variable. If _EDITOR is undefined, EDIT attempts to invoke the default host operating system editor.
EDIT alone places the contents of the SQL buffer in a file by default named AFIEDT.BUF (in your current working directory) and invokes the text editor on the contents of the file. If the file AFIEDT.BUF already exists, it is overwritten with the contents of the buffer. You can change the default filename by using the SET EDITFILE command. For more information about setting a default filename for the EDIT command, see the EDITFILE variable of the SET command in this chapter.
Note: The default file, AFIEDT.BUF, may have a different name on some operating systems.
If you do not specify a filename and the buffer is empty, EDIT returns an error message.
To leave the editing session and return to SQL*Plus, terminate the editing session in the way customary for the text editor. When you leave the editor, SQL*Plus loads the contents of the file into the buffer.
Example
To edit the file REPORT with the extension SQL using your host operating system text editor, enter
SQL> EDIT REPORT
Executes a single PL/SQL statement. The EXECUTE command is often useful when you want to execute a PL/SQL statement that references a stored procedure. For more information on PL/SQL, see your PL/SQL User's Guide and Reference.
Syntax
EXEC[UTE] statement
Terms and Clauses
Refer to the following for a description of the term or clause:
statement
Represents a PL/SQL statement.
Usage Notes
If your EXECUTE command cannot fit on one line because of the PL/SQL statement, use the SQL*Plus continuation character (a hyphen) as shown in the example below.
The length of the command and the PL/SQL statement cannot exceed the length defined by SET LINESIZE.
Examples
The following EXECUTE command assigns a value to a bind variable:
SQL> EXECUTE :n := 1
The following EXECUTE command runs a PL/SQL statement that references a stored procedure:
SQL> EXECUTE - :ID := EMP_MANAGEMENT.HIRE('BLAKE','MANAGER','KING',2990,'SALES')
Note that the value returned by the stored procedure is being placed in a bind variable, :ID. For information on how to create a bind variable, see the VARIABLE command in this chapter.
Terminates SQL*Plus and returns control to the operating system.
Syntax
{EXIT|QUIT} [SUCCESS|FAILURE|WARNING|n|variable] [COMMIT|ROLLBACK]
Terms and Clauses
Refer to the following list for a description of each term or clause:
{EXIT|QUIT}
Can be used interchangeably (QUIT is a synonym for EXIT).
n
Represents an integer you specify as the return code.
variable
Represents a user-defined or system variable (but not a bind variable), such as SQL.SQLCODE. EXIT variable exits with the value of variable as the return code.
SUCCESS
FAILURE
Exits with a return code indicating failure.
WARNING
Exits with a return code indicating warning.
COMMIT
Saves pending changes to the database before exiting.
ROLLBACK
Executes a ROLLBACK statement and abandons pending changes to the database before exiting.
EXIT with no clauses commits and exits with a value of SUCCESS.
Usage Notes
EXIT allows you to specify an operating system return code. This allows you to run SQL*Plus command files in batch mode and to detect programmatically the occurrence of an unexpected event. The manner of detection is operating system specific. See the Oracle installation and user's manual(s) provided for your operating system for details.
The key words SUCCESS, WARNING, and FAILURE represent operating-system-dependent values. On some systems, WARNING and FAILURE may be indistinguishable.
Note: SUCCESS, FAILURE, and WARNING are not reserved words.
The range of operating system return codes is also restricted on some operating systems. This limits the portability of EXIT n and EXIT variable between platforms. For example, on UNIX there is only one byte of storage for return codes; therefore, the range for return codes is limited to zero to 255.
If you make a syntax error in the EXIT options or use a non-numeric variable, SQL*Plus performs an EXIT FAILURE COMMIT.
For information on exiting conditionally, see the WHENEVER SQLERROR and WHENEVER OSERROR commands later in this chapter.
Example
The following example commits all uncommitted transactions and returns the error code of the last executed SQL command or PL/SQL block:
SQL> EXIT SQL.SQLCODE
The location of the return code depends on your system. Consult your DBA for information concerning how your operating system retrieves data from a program. See TTITLE in this chapter for more information on SQL.SQLCODE.
Loads a host operating system file into the SQL buffer.
Syntax
GET file_name[.ext] [LIS[T]|NOL[IST]]
Terms and Clauses
Refer to the following list for a description of each term or clause:
file_name[.ext]
Represents the file you wish to load (typically a command file).
LIS[T]
Lists the contents of the file.
NOL[IST]
Usage Note
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 the SUFFIX variable of the SET command in this chapter.
If part of the filename you are specifying contains the word list or the word file, you need to put the name 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 the SAVE command in this chapter for more information.
Example
To load a file called YEARENDRPT with the extension SQL into the buffer, type
SQL> GET YEARENDRPT
Accesses the SQL*Plus help system.
Syntax
HELP [topic]
Terms and Clauses
Refer to the following for a description of the term or clause:
topic
Represents a SQL*Plus help topic. This can be a SQL*Plus command (e.g., COLUMN), a SQL statement (e.g., INSERT), a PL/SQL statement (e.g., IF), or another topic in the help system (e.g., comparison operators).
Enter HELP without topic to get help on the help system.
Usage Notes
You can only enter one topic after HELP. You can abbreviate the topic (e.g., COL for COLUMN). However, if you enter only an abbreviated topic and the abbreviation is ambiguous, SQL*Plus will display help for all topics that match the abbreviation. For example, if you entered
SQL> HELP COMP
SQL*Plus would display help on COMPUTE followed by help on comparison operators.
If you get a response indicating that help is not available, consult your database administrator.
Example
To see a list of SQL*Plus commands and PL/SQL and SQL statements, enter
SQL> HELP COMMANDS
Executes a host operating system command without leaving SQL*Plus.
Syntax
HO[ST] [command]
Terms and Clauses
Refer to the following for a description of the term or clause:
command
Represents a host operating system command.
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 Oracle installation and user's manual(s) provided for your operating system.
Usage Notes
With some operating systems, you can use a "$" (VMS), "!" (UNIX), or another character instead of HOST. See the Oracle installation and user's manual(s) provided for your operating system for details.
You may not have access to the HOST command, depending on your operating system. See the Oracle installation and user's manual(s) provided for your operating system or ask your DBA for more information.
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 the SQLTERMINATOR variable of the SET command in this chapter for more information on the SQLTERMINATOR.
Example
To execute an operating system command, ls *.sql, enter
SQL> HOST ls *.sql
Adds one or more new lines of text after the current line in the buffer.
Syntax
I[NPUT] [text]
Terms and Clauses
Refer to the following for a description of the term or clause:
text
Represents the text you wish to add. 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.
Usage Notes
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:
1 SELECT ENAME, DEPTNO, SAL, COMM 2 FROM EMP
To add an ORDER BY clause to the query, enter
SQL> LIST 2 2* FROM EMP SQL> INPUT ORDER BY ENAME
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 ENAME, DEPTNO, SAL, COMM 2 FROM EMP 3* ORDER BY ENAME
To add a two-line WHERE clause, enter
SQL> LIST 2 2* FROM EMP SQL> INPUT 3 WHERE JOB = 'SALESMAN' 4 AND COMM 500 5
INPUT prompts you for new lines until you enter an empty line. The SQL buffer now contains the following lines:
1 SELECT ENAME, DEPTNO, SAL, COMM 2 FROM EMP 3 WHERE JOB = 'SALESMAN' 4 AND COMM 500 5 ORDER BY ENAME
Lists one or more lines of the SQL buffer.
Syntax
L[IST] [n|n m|n *|n LAST|*|* n|* LAST|LAST]
Terms and Clauses
Refer to the following list for a description of each term or clause:
n
n m
n *
Lists line n through the current line.
n LAST
Lists line n through the last line.
*
* n
Lists the current line through line n.
* LAST
Lists the current line through the last line.
LAST
Lists the last line.
Enter LIST with no clauses to list all lines.
Usage Notes
The last line listed becomes the new current line (marked by an asterisk).
Example
To list the contents of the buffer, enter
SQL> LIST
You will see a listing of all lines in the buffer, similar in form to the following example:
1 SELECT ENAME, DEPTNO, JOB 2 FROM EMP 3 WHERE JOB = 'CLERK' 4* ORDER BY DEPTNO
The asterisk indicates that line 4 is the current line.
To list the second line only, enter
SQL> LIST 2
You will then see this:
2* FROM EMP
To list the current line (now line 2) to the last line, enter
SQL> LIST * LAST
You will then see this:
2 FROM EMP 3 WHERE JOB = 'CLERK' 4* ORDER BY DEPTNO
Displays an empty line followed by a line containing text, then waits for the user to press [Return], or displays two empty lines and waits for the user's response.
Syntax
PAU[SE] [text]
Terms and Clauses
Refer to the following for a description of the clause or term:
text
Represents the text you wish to display.
Enter PAUSE followed by no text to display two empty lines.
Usage Notes
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.
For information on pausing between pages of a report, see the PAUSE variable of the SET command later in this chapter.
Example
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 command file:
SET PAUSE OFF PAUSE Adjust paper and press RETURN to continue. SELECT ...
Displays the current value of bind variables. For more information on bind variables, see your PL/SQL User's Guide and Reference.
Syntax
PRI[NT] [variable ...]
Terms and Clauses
Refer to the following for a description of the clause or term:
variable ...
Represents the names of the bind variables whose values you wish to display.
Enter PRINT with no variables to print all bind variables.
Usage Notes
Bind variables are created using the VARIABLE command. For more information and examples, see the VARIABLE command in this chapter.
You can control the formatting of the PRINT output just as you would query output. For more information, see the formatting techniques described.
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. For more information, see the SET command in this chapter.
Example
The following example illustrates a PRINT command:
SQL> VARIABLE n NUMBER SQL> BEGIN 2 :n := 1; 3 END; SQL> PRINT n N ---------- 1
Sends the specified message or a blank line to the user's screen.
Syntax
PROMPT [text]
Terms and Clauses
Refer to the following for a description of the term or clause:
text
Represents the text of the message you wish to display. If you omit text, PROMPT displays a blank line on the user's screen.
Usage Notes
You can use this command in command files to give information to the user.
Example
The following example shows the use of PROMPT in conjunction with ACCEPT in a command file called ASKFORDEPT. ASKFORDEPT contains the following SQL*Plus and SQL commands:
PROMPT PROMPT Please enter a valid department PROMPT For example: 10, 20, 30, 40 ACCEPT NEWDEPT NUMBER PROMPT 'DEPT:> ' SELECT DNAME FROM DEPT WHERE DEPTNO = &NEWDEPT
Assume you run the file using START or @:
SQL> @ASKFORDEPT
SQL*Plus displays the following prompts:
Please enter a valid department For example: 10, 20, 30, 40 DEPT:>
You can enter a department number at the prompt DEPT:>. By default, SQL*Plus lists the line containing &NEWDEPT before and after substitution, and then displays the department name corresponding to the number entered at the DEPT:> prompt.
Begins a comment in a command file. SQL*Plus does not interpret the comment as a command.
Syntax
REM[ARK]
Usage Notes
The REMARK command must appear at the beginning of a line, and the comment ends at the end of the line. A line cannot contain both a comment and a command.
For details on entering comments in command files using the SQL comment delimiters, /* ... */, or the ANSI/ISO comment delimiter, -- ..., refer to "Placing Comments in Command Files".
Example
The following command file contains some typical comments:
REM COMPUTE uses BREAK ON REPORT to break on end of table. BREAK ON REPORT COMPUTE SUM OF "DEPARTMENT 10" "DEPARTMENT 20" - "DEPARTMENT 30" "TOTAL BY JOB" ON REPORT REM Each column displays the sums of salaries by job for REM one of the departments 10, 20, 30. SELECT JOB, SUM( DECODE( DEPTNO, 10, SAL, 0)) "DEPARTMENT 10", SUM( DECODE( DEPTNO, 20, SAL, 0)) "DEPARTMENT 20", SUM( DECODE( DEPTNO, 30, SAL, 0)) "DEPARTMENT 30", SUM(SAL) "TOTAL BY JOB" FROM EMP GROUP BY JOB
Places and formats a specified report footer at the bottom of each report, or lists the current REPFOOTER definition.
Syntax
REPF[OOTER] [PAGE] [printspec [text|variable] ...] | [OFF|ON]
Terms and Clauses
Refer to the REPHEADER command for additional information on terms and clauses in the REPFOOTER command syntax.
Enter REPFOOTER with no clauses to list the current REPFOOTER definition.
Usage Notes
If you do not enter a printspec clause before the text or variables, REPFOOTER left justifies the text or variables.
You can use any number of constants and variables in a printspec. SQL*Plus displays the constants and variables in the order you specify them, positioning and formatting each constant or variable as specified by the printspec clauses that precede it.
Note: If SET EMBEDDED is ON, the report footer is suppressed.
Example
To define "END EMPLOYEE LISTING REPORT" as a report footer on a separate page and to center it, enter:
SQL> REPFOOTER PAGE CENTER 'END EMPLOYEE LISTING REPORT' SQL> TTITLE RIGHT 'Page: ' FORMAT 999 SQL.PNO SQL> SELECT ENAME, SAL 2 FROM EMP 3 WHERE SAL > 2000; Page: 1 ENAME SAL ---------- ---------- JONES 2975 BLAKE 2850 CLARK 2450 SCOTT 3000 KING 5000 FORD 3000 Page: 2 END EMPLOYEE LISTING REPORT
To suppress the report footer without changing its definition, enter:
SQL> REPFOOTER OFF
Places and formats a specified report header at the top of each report, or lists the current REPHEADER definition.
Syntax
REPH[EADER] [PAGE] [printspec [text|variable] ...] | [OFF|ON]
where printspec represents one or more of the following clauses used to place and format the text:
COL n S[KIP] [n] TAB n LE[FT] CE[NTER] R[IGHT] BOLD FORMAT text
Terms and Clauses
Refer to the following list for a description of each term or clause. 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.
Note: You must specify SET NEWPAGE 0 to create a physical page break using this command.
text
Represents 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
Represents a user variable or any of the following system-maintained values:
To print one of these values, reference the appropriate variable in the report header or footer. You can format variable with the FORMAT clause.
OFF
Turns the report header or footer off (suppresses its display) without affecting its definition.
ON
Turns the report header or footer on (restores its display). When you define a report header or footer, SQL*Plus automatically sets REPHEADER or REPFOOTER to ON.
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], and 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 bolded 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 COLUMN FORMAT 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 according to the default format.
Refer to the FORMAT clause of the COLUMN command in this chapter for more information on default formats.
Enter REPHEADER with no clauses to list the current REPHEADER definition.
Usage Notes
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 them, positioning and formatting each constant or variable as specified by the printspec clauses that precede it.
Example
To define "EMPLOYEE LISTING REPORT" as a report header on a separate page, and to center it, enter:
SQL> REPHEADER PAGE CENTER 'EMPLOYEE LISTING REPORT' SQL> TTITLE RIGHT 'Page: ' FORMAT 999 SQL.PNO SQL> SELECT ENAME, SAL 2 FROM EMP 3 WHERE SAL > 2000; Page: 1 EMPLOYEE LISTING REPORT Page: 2 ENAME SAL ---------- ---------- JONES 2975 BLAKE 2850 CLARK 2450 SCOTT 3000 KING 5000 FORD 3000 6 rows selected.
To suppress the report header without changing its definition, enter:
SQL> REPHEADER OFF
Lists and executes the SQL command or PL/SQL block currently stored in the SQL buffer.
Syntax
R[UN]
Usage Notes
RUN causes the last line of the SQL buffer to become the current line.
The slash command (/) functions similarly to RUN, but does not list the command in the SQL buffer on your screen.
Example
Assume the SQL buffer contains the following query:
SELECT DEPTNO FROM DEPT
To RUN the query, enter
SQL> RUN
The following output results:
1* SELECT DEPTNO FROM DEPT DEPTNO ---------- 10 20 30 40
Invokes a SQL*Forms application from within SQL*Plus.
Note: You have access to this command only if your site chose this option while installing SQL*Plus.
Syntax
RUNFORM [options] form_name
Usage Notes
The RUNFORM syntax is the same in both SQL*Plus and SQL*Forms. If you are already in SQL*Plus, you can invoke a form more quickly in this manner than by invoking a form from the system prompt because you avoid a separate Oracle logon. See your SQL*Forms Operator's Guide for details on the correct syntax.
Note that when you use RUNFORM from within SQL*Plus, you may not specify a username/password (you retain your current connection to Oracle). If you wish to use a different username/password, use the SQL*Plus CONNECT command to connect to the desired Oracle username prior to issuing the RUNFORM command.
Example
To run a form named MYFORM, enter
SQL> RUNFORM MYFORM
Saves the contents of the SQL buffer in a host operating system file (a command file).
Syntax
SAV[E] file_name[.ext] [CRE[ATE]|REP[LACE]|APP[END]]
Terms and Clauses
Refer to the following list for a description of each term or clause:
file_name[.ext]
Specifies the command file in which you wish to save the buffer's contents.
CRE[ATE]
Creates the file if the file does not exist.
REP[LACE]
Replaces the contents of an existing file. If the file does not exist, REPLACE creates the file.
APP[END]
Adds the contents of the buffer to the end of the file you specify.
Usage Notes
If you do not specify an extension, SQL*Plus assumes the default command-file extension (normally SQL). For information on changing this default extension, see the SUFFIX variable of the SET command in this chapter.
If you wish to SAVE a file under a name identical to a SAVE command clause (CREATE, REPLACE, or APPEND), you must specify a file extension.
When you SAVE the contents of the SQL buffer, SAVE adds a line containing a slash (/) to the end of the file.
If the filename you specify is the word file, you need to put the name in single quotes.
Example
To save the contents of the buffer in a filenamed DEPTSALRPT with the extension SQL, enter
SQL> SAVE DEPTSALRPT
To save the contents of the buffer in a filenamed DEPTSALRPT with the extension OLD, enter
SQL> SAVE DEPTSALRPT.OLD
Sets a system variable to alter the SQL*Plus environment for your current session, such as
SET system_variable value
where system_variable value represents a system variable followed by a value, as shown below:
APPI[NFO]{ON|OFF|text} ARRAY[SIZE] {20|n} AUTO[COMMIT] {OFF|ON|IMM[EDIATE]|n} AUTOP[RINT] {OFF|ON} AUTOT[RACE] {OFF|ON|TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]] BLO[CKTERMINATOR] {.|c} CLOSECUR[SOR] {OFF|ON} CMDS[EP] {;|c|OFF|ON} COLSEP {_|text} COM[PATIBILITY] {V6|V7|NATIVE} CON[CAT] {.|c|OFF|ON} COPYC[OMMIT] {0|n} COPYTYPECHECK {OFF|ON} CRT crt DEF[INE] {'&'|c|OFF|ON} ECHO {OFF|ON} EDITF[ILE] file_name[.ext] EMBEDDED {OFF|ON} ESC[APE] {\|c|OFF|ON} FEED[BACK] {6|n|OFF|ON} FLAGGER {OFF|ENTRY|INTERMED[IATE]|FULL} FLU[SH] {OFF|ON} HEA[DING] {OFF|ON} HEADS[EP] {||c|OFF|ON} LIN[ESIZE] {80|n} LONG {80|n} LONGC[HUNKSIZE] {80|n} MAXD[ATA] n NEWP[AGE] {1|n} NULL text NUMF[ORMAT] format NUM[WIDTH] {10|n} PAGES[IZE] {24|n} PAU[SE] {OFF|ON|text} RECSEP {WR[APPED]|EA[CH]|OFF} RECSEPCHAR {_|c} SERVEROUT[PUT] {OFF|ON} [SIZE n] [FOR[MAT] {WRA[PPED]| WOR[D_WRAPPED]|TRU[NCATED]}] SHOW[MODE] {OFF|ON} SQLC[ASE] {MIX[ED]|LO[WER]|UP[PER]} SQLCO[NTINUE] {> |text} SQLN[UMBER] {OFF|ON} SQLPRE[FIX] {#|c} SQLP[ROMPT] {SQL>|text} SQLT[ERMINATOR] {;|c|OFF|ON} SUF[FIX] {SQL|text} TAB {OFF|ON} TERM[OUT] {OFF|ON} TI[ME] {OFF|ON} TIMI[NG] {OFF|ON} TRIM[OUT] {OFF|ON} TRIMS[POOL] {ON|OFF} UND[ERLINE] {-|c|ON|OFF} VER[IFY] {OFF|ON} WRA[P] {OFF|ON}
Terms and Clauses
Refer to the following list for a description of each term, clause, or system variable:
APPI[NFO]{ON|OFF|text}
Sets automatic registering of command files through the DBMS_APPLICATION_INFO package. This enables the performance and resource usage of each command file to be monitored by your DBA. The registered name appears in the MODULE column of the V$SESSION and V$SQLAREA virtual tables. You can also read the registered name using the DBMS_APPLICATION_INFO.READ_MODULE procedure.
ON registers command files invoked by the @, @@ or START commands. OFF disables registering of command files. Instead, the current value of text is registered. Text specifies the text to register when no command file is being run or when APPINFO is OFF. The default for text is "SQL*Plus." If you enter multiple words for text, you must enclose them in quotes. The maximum length for text is limited by the DBMS_APPLICATION_INFO package.
The registered name has the format nn@xfilename where: nn is the depth level of command file; x is '<' when the command file name is truncated, otherwise, it is blank; and filename is the command file name, possibly truncated to the length allowed by the DBMS_APPLICATION_INFO package interface.
Note: To use this feature, you must have access to the DBMS_APLICATION_INFO package. Run DBMSUTIL.SQL (this name may vary depending on your operating system) as SYS to create the DBMS_APPLICATION_INFO package. DBMSUTIL.SQL is part of the Oracle7 Server product.
For more information on the DBMS_APPLICATION_INFO package, see "Registering Applications" in the Oracle7 Server Tuning manual.
Note: APPINFO is not available with TRUSTED Oracle.
ARRAY[SIZE] {20|n}
Sets the number of rows--called a batch--that SQL*Plus will fetch from the database at one time. Valid values are 1 to 5000. A large value increases the efficiency of queries and subqueries that fetch many rows, but requires more memory. Values over approximately 100 provide little added performance. ARRAYSIZE has no effect on the results of SQL*Plus operations other than increasing efficiency.
AUTO[COMMIT] {OFF|ON|IMM[EDIATE]|n}
Controls when Oracle commits pending changes to the database. ON commits pending changes to the database after Oracle executes each successful INSERT, UPDATE, or DELETE command or PL/SQL block. OFF suppresses automatic committing so that you must commit changes manually (for example, with the SQL command COMMIT). IMMEDIATE functions in the same manner as the ON option. n commits pending changes to the database after Oracle executes n successful SQL INSERT, UPDATE, or DELETE commands or PL/SQL blocks. n cannot be less than zero or greater than 2,000,000,000. The statement counter is reset to zero after successful completion of
Note: For this feature, a PL/SQL block is considered one transaction, regardless of the actual number of SQL commands contained within it.
AUTOP[RINT] {OFF|ON}
Sets the automatic PRINTing of bind variables. ON or OFF controls whether SQL*Plus automatically displays bind variables (referenced in a successful PL/SQL block or used in an EXECUTE command). For more information about displaying bind variables, see the PRINT command in this chapter.
AUTOT[RACE] {OFF|ON|TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
Displays a report on the execution of successful SQL DML statements (SELECT, INSERT, UPDATE or DELETE). The report can include execution statistics and the query execution path.
OFF does not display a trace report. ON displays a trace report. TRACEONLY displays a trace report, but does not print query data, if any. EXPLAIN shows the query execution path by performing an EXPLAIN PLAN. STATISTICS displays SQL statement statistics.
Using ON or TRACEONLY with no explicit options defaults to EXPLAIN STATISTICS.
The TRACEONLY option may be useful to suppress the query data of large queries. If STATISTICS is specified, SQL*Plus still fetches the query data from the server, however, the data is not displayed.
The AUTOTRACE report is printed after the statement has successfully completed.
Information about Execution Plans and the statistics is documented in the guide Oracle7 Server Tuning.
To use the EXPLAIN option, you must first create the table PLAN_TABLE in your schema. The description of this table is specific to the version of the database to which you are connected. Use UTLXPLAN.SQL (this name may vary depending on your operating system) to create PLAN_TABLE. UTLXPLAN.SQL is part of the Oracle7 Server product. Contact your DBA if you cannot create this table.
To access STATISTICS data, you must have access to several Dynamic Performance tables (for information about the Dynamic Performance or "V$" tables, see the Oracle7 Server documentation). Access can be granted using the role created in PLUSTRCE.SQL (this name may vary depending on your operating system). You must run PLUSTRCE.SQL as SYS and grant the role to users who will use SET AUTOTRACE. Contact your DBA to perform these steps.
When SQL*Plus produces a STATISTICS report, a second connection to the database is automatically created. This connection is closed when the STATISTICS option is set to OFF, or you log out of SQL*Plus.
The formatting of your AUTOTRACE report may vary depending on the version of the server to which you are connected and the configuration of the server.
AUTOTRACE is not available when FIPS flagging is enabled, or with TRUSTED Oracle.
See "Tracing Statements" for more information on AUTOTRACE.
BLO[CKTERMINATOR] {.|c}
Sets the non-alphanumeric character used to end PL/SQL blocks to c. To execute the block, you must issue a RUN or / (slash) command.
CLOSECUR[SOR] {OFF|ON}
Sets the cursor usage behavior. ON or OFF sets whether or not the cursor will close and reopen after each SQL statement. This feature may be useful in some circumstances to release resources in the database server.
CMDS[EP] {;|c|OFF|ON}
Sets the non-alphanumeric character used to separate multiple SQL*Plus commands entered on one line to c. ON or OFF controls whether you can enter multiple commands on a line; ON automatically sets the command separator character to a semicolon (;).
Sets the text to be printed between SELECTed columns. If the COLSEP variable contains blanks or punctuation characters, you must enclose it with single quotes. The default value for text is a single space.
In multi-line rows, the column separator does not print between columns that begin on different lines. The column separator does not appear on blank lines produced by BREAK ... SKIP n and does not overwrite the record separator. See SET RECSEP in this chapter for more information.
COM[PATIBILITY] {V6|V7|NATIVE}
Specifies the version of Oracle to which you are currently connected. Set COMPATIBILITY to V6 for Oracle Version 6 or V7 for Oracle7. Set COMPATIBILITY to NATIVE if you wish the database to determine the setting (for example, if connected to Oracle7, compatibility would default to V7). COMPATIBILITY must be correctly set for the version of Oracle to which you are connected; otherwise, you will be unable to run any SQL commands. Note that you can set COMPATIBILITY to V6 when connected to Oracle7. This enables you to run Oracle Version 6 SQL against Oracle7.
Setting COMPATIBILITY to V6 and V7 affects how SQL*Plus handles character data. Setting COMPATIBILITY to V6 causes SQL*Plus to treat CHAR column values as variable-length character strings. Setting COMPATIBILITY to V7 causes SQL*Plus to treat CHAR column values as fixed-length character strings and VARCHAR2 (VARCHAR) column values as variable-length character strings. See the Oracle7 Server documentation for a list of changes from Version 6 to Oracle7.
CON[CAT] {.|c|OFF|ON}
Sets the character you can use to terminate a substitution variable reference if you wish to immediately follow the variable with a character that SQL*Plus would otherwise interpret as a part of the substitution variable name. SQL*Plus resets the value of CONCAT to a period when you switch CONCAT on.
COPYC[OMMIT] {0|n}
Controls the number of batches after which the COPY command commits changes to the database. COPY commits rows to the destination database each time it copies n row batches. Valid values are zero to 5000. You can set the size of a batch with the ARRAYSIZE variable. If you set COPYCOMMIT to zero, COPY performs a commit only at the end of a copy operation.
COPYTYPECHECK {OFF|ON}
Sets the suppression of the comparison of datatypes while inserting or appending to tables with the COPY command. This is to facilitate copying to DB2, which requires that a CHAR be copied to a DB2 DATE.
CRT crt
Changes the default CRT file used in the SQL*Plus RUNFORM command. To return to the original default (before CRT was set), set CRT to nothing by entering two double quotes ("") for crt.
If you want to use NEW.CRT during a form invocation on a system where the default CRT is OLD.CRT, you can either invoke the form by
SQL> RUNFORM -c NEW form_name
or
SQL> SET CRT NEW SQL> RUNFORM form_name
The second method stores the CRT option so that you do not need to respecify it for subsequent RUNFORM commands during the same SQL*Plus session.
DEF[INE] {&|c|OFF|ON}
Sets the character used to prefix substitution variables to c. ON or OFF controls whether SQL*Plus will scan commands for substitution variables and replace them with their values. ON changes the value of c back to the default '&', not the most recently used character. The setting of DEFINE to OFF overrides the setting of the SCAN variable. For more information on the SCAN variable, see the SET SCAN command in Appendix F
ECHO {OFF|ON}
Controls whether the START command lists each command in a command file as the command is executed. ON lists the commands; OFF suppresses the listing.
Sets the default filename for the EDIT command. For more information about the EDIT command, see EDIT in this chapter.
You can include a path and/or file extension. For information on changing the default extension, see the SUFFIX variable of this command. The default filename and maximum filename length are operating system specific.
EMBEDDED {OFF|ON}
Controls where on a page each report begins. OFF forces each report to start at the top of a new page. ON allows a report to begin anywhere on a page. Set EMBEDDED to ON when you want a report to begin printing immediately following the end of the previously run report.
Note: When you use SET EMBEDDED ON and change the pagesize with SET PAGESIZE n, SQL*Plus finishes the current page using the existing pagesize setting and, if required, begins a new page with the new pagesize setting.
Note: When you use a BTITLE with SET EMBEDDED ON, the second and subsequent SELECT statements will always begin on a new page. This is because SQL*Plus has no input read ahead. Since SQL*Plus cannot anticipate whether you will enter another SELECT statement or, for example, EXIT, SQL*Plus has to complete processing all output from the first SELECT statement before it reads the next command. This processing includes printing the BTITLE. Therefore, given two SELECT statements, SQL*Plus prints the final BTITLE of the first SELECT statement before it processes the second. The second SELECT statement will then begin at the top of a new page.
Note: When you use a REPFOOTER with SET EMBEDDED ON, no footer will be displayed.
ESC[APE] {\|c|OFF|ON}
Defines the character you enter as the escape character. OFF undefines the escape character. ON enables the escape character. ON changes the value of c back to the default "\".
You can use the escape character before the substitution character (set through SET DEFINE) to indicate that SQL*Plus should treat the substitution character as an ordinary character rather than as a request for variable substitution.
FEED[BACK] {6|n|OFF|ON}
Displays the number of records returned by a query when a query selects at least n records. ON or OFF turns this display on or off. Turning feedback ON sets n to 1. Setting feedback to zero is equivalent to turning it OFF.
FLAGGER {OFF|ENTRY|INTERMED[IATE]|FULL}
Checks to make sure that SQL statements conform to the ANSI/ISO SQL92 standard. If any non-standard constructs are found, the Oracle Server flags them as errors and displays the violating syntax. This is the equivalent of the SQL language ALTER SESSION SET FLAGGER command.
You may execute SET FLAGGER even if you are not connected to a database. FIPS flagging will remain in effect across SQL*Plus sessions until a SET FLAGGER OFF (or ALTER SESSION SET FLAGGER = OFF) command is successful or you exit SQL*Plus.
When FIPS flagging is enabled, SQL*Plus displays a warning for the CONNECT, DISCONNECT, and ALTER SESSION SET FLAGGER commands, even if they are successful.
The SET FLAGGER and ALTER SESSION SET FLAGGER commands require Oracle7 Release 7.1 or greater.
FLU[SH] {OFF|ON}
Controls when output is sent to the user's display device. OFF allows the host operating system to buffer output. ON disables buffering.
Use OFF only when you run a command file non-interactively (that is, when you do not need to see output and/or prompts until the command file finishes running). The use of FLUSH OFF may improve performance by reducing the amount of program I/O.
HEA[DING] {OFF|ON}
Controls printing of column headings in reports. ON prints column headings in reports; OFF suppresses column headings.
Defines the character you enter as the heading separator character. The heading separator character cannot be alphanumeric or white space. You can use the heading separator character in the COLUMN command and in the old forms of BTITLE and TTITLE to divide a column heading or title onto more than one line. ON or OFF turns heading separation on or off. When heading separation is OFF, SQL*Plus prints a heading separator character like any other character. ON changes the value of c back to the default "|".
LIN[ESIZE] {80|n}
Sets the total number of characters that SQL*Plus displays on one line before beginning a new line. It also controls the position of centered and right-aligned text in TTITLE, BTITLE, REPHEADER and REPFOOTER. You can define LINESIZE as a value from 1 to a maximum that is system dependent. Refer to the Oracle installation and user's manual(s) provided for your operating system.
LONG {80|n}
Sets maximum width (in characters) for displaying and copying LONG values. For Oracle7, the maximum value of n is 2 gigabytes. For Oracle Version 6, the maximum is 32,767.
LONGC[HUNKSIZE] {80|n}
Sets the size (in characters) of the increments in which SQL*Plus retrieves a LONG value. When retrieving a LONG value, you may want to retrieve it in increments rather than all at once because of memory size restrictions. Valid values are 1 to whatever has been set with MAXDATA. LONGCHUNKSIZE applies only to Oracle7.
MAXD[ATA] n
Sets the maximum total row width that SQL*Plus can process. The default and maximum values of n are system dependent. Consult the Oracle installation and user's manual(s) provided for your operating system or your DBA for details.
Sets the number of blank lines to be printed from the top of each page to the top title. A value of zero places a formfeed at the beginning of each page (including the first page) and clears the screen on most terminals.
NULL text
Sets the text that represents a null value in the result of a SQL SELECT command. Use the NULL clause of the COLUMN command to override the setting of the NULL variable for a given column.
NUMF[ORMAT] format
Sets the default format for displaying numbers. Enter a number format for format. For number format descriptions, see the FORMAT clause of the COLUMN command in this chapter.
Sets the default width for displaying numbers. SQL*Plus rounds numbers up or down to the value of SET NUMWIDTH.
PAGES[IZE] {24|n}
Sets the number of lines in each page. You can set PAGESIZE to zero to suppress all headings, page breaks, titles, the initial blank line, and other formatting information.
Allows you to control scrolling of your terminal when running reports. ON causes SQL*Plus to pause at the beginning of each page of report output. You must press [Return] after each pause. The text you enter specifies the text to be displayed each time SQL*Plus pauses. If you enter multiple words, you must enclose text in single quotes.
You can embed terminal-dependent escape sequences in the PAUSE command. These sequences allow you to create inverse video messages or other effects on terminals that support such characteristics.
RECSEP {WR[APPED]|EA[CH]|OFF} RECSEPCHAR { |c}
Display or print record separators. A record separator consists of a single line of the RECSEPCHAR (record separating character) repeated LINESIZE times.
RECSEPCHAR defines the record separating character. A single space is the default.
RECSEP tells SQL*Plus where to make the record separation. For example, if you set RECSEP to WRAPPED, SQL*Plus prints a record separator only after wrapped lines. If you set RECSEP to EACH, SQL*Plus prints a record separator following every row. If you set RECSEP to OFF, SQL*Plus does not print a record separator.
SERVEROUT[PUT] {OFF|ON} [SIZE n] [FOR[MAT] {WRA[PPED]| WOR[D_WRAPPED]|TRU[NCATED]}]
Controls whether to display the output (that is, DBMS_OUTPUT.PUT_LINE) of stored procedures or PL/SQL blocks in SQL*Plus. OFF suppresses the output of DBMS_OUTPUT.PUT_LINE; ON displays the output.
SIZE sets the number of bytes of the output that can be buffered within the Oracle7 Server. The default for n is 2000. n cannot be less than 2000 or greater than 1,000,000.
When WRAPPED is enabled SQL*Plus wraps the server output within the line size specified by SET LINESIZE, beginning new lines when required.
When WORD_WRAPPED is enabled, each line of server output is wrapped within the line size specified by SET LINESIZE. Lines are broken on word boundaries. SQL*Plus left justifies each line, skipping all leading whitespace.
When TRUNCATED is enabled, each line of server output is truncated to the line size specified by SET LINESIZE.
For each FORMAT, every server output line begins on a new output line.
Note: The output is displayed synchronously after the stored procedure or PL/SQL block has been executed by the Oracle7 Server.
For more information on DBMS_OUTPUT.PUT_LINE, see your Oracle7 Server Application Developer's Guide.
SHOW[MODE] {OFF|ON}
Controls whether SQL*Plus lists the old and new settings of a SQL*Plus system variable when you change the setting with SET. ON lists the settings; OFF suppresses the listing. SHOWMODE ON has the same behavior as the obsolete SHOWMODE BOTH.
SQLC[ASE] {MIX[ED]|LO[WER]|UP[PER]}
Converts the case of SQL commands and PL/SQL blocks just prior to execution. SQL*Plus converts all text within the command, including quoted literals and identifiers, as follows:
SQLCASE does not change the SQL buffer itself.
SQLCO[NTINUE] {> |text}
Sets the character sequence SQL*Plus displays as a prompt after you continue a SQL*Plus command on an additional line using a hyphen (-).
SQLN[UMBER] {OFF|ON}
Sets the prompt for the second and subsequent lines of a SQL command or PL/SQL block. ON sets the prompt to be the line number. OFF sets the prompt to the value of SQLPROMPT.
SQLPRE[FIX] {#|c}
Sets the SQL*Plus prefix character. While you are entering a SQL command or PL/SQL block, you can enter a SQL*Plus command on a separate line, prefixed by the SQL*Plus prefix character. SQL*Plus will execute the command immediately without affecting the SQL command or PL/SQL block that you are entering. The prefix character must be a non-alphanumeric character.
SQLP[ROMPT] {SQL>|text}
Sets the SQL*Plus command prompt.
Sets the character used to end and execute SQL commands to c. OFF means that SQL*Plus recognizes no command terminator; you terminate a SQL command by entering an empty line. ON resets the terminator to the default semicolon (;).
Sets the default file extension that SQL*Plus uses in commands that refer to command files. SUFFIX does not control extensions for spool files.
TAB {OFF|ON}
Determines how SQL*Plus formats white space in terminal output. OFF uses spaces to format white space in the output. ON uses the TAB character. TAB settings are every eight characters. The default value for TAB is system dependent.
Note: This option applies only to terminal output. Tabs will not be placed in output files.
TERM[OUT] {OFF|ON}
Controls the display of output generated by commands executed from a command file. OFF suppresses the display so that you can spool output from a command file without seeing the output on the screen. ON displays the output. TERMOUT OFF does not affect output from commands you enter interactively.
TI[ME] {OFF|ON}
Controls the display of the current time. ON displays the current time before each command prompt. OFF suppresses the time display.
Controls the display of timing statistics. ON displays timing statistics on each SQL command or PL/SQL block run. OFF suppresses timing of each command. For information about the data SET TIMING ON displays, see the Oracle installation and user's manual(s) provided for your operating system. Refer to the TIMING command for information on timing multiple commands.
Determines whether SQL*Plus allows trailing blanks at the end of each displayed line. ON removes blanks at the end of each line, improving performance especially when you access SQL*Plus from a slow communications device. OFF allows SQL*Plus to display trailing blanks. TRIMOUT ON does not affect spooled output.
Determines whether SQL*Plus allows trailing blanks at the end of each spooled line. ON removes blanks at the end of each line. OFF allows SQL*Plus to include trailing blanks. TRIMSPOOL ON does not affect terminal output.
UND[ERLINE] {-|c|ON|OFF}
Sets the character used to underline column headings in SQL*Plus reports to c. c cannot be an alphanumeric character or a white space. ON or OFF turns underlining on or off. ON changes the value of c back to the default "-".
VER[IFY] {OFF|ON}
Controls whether SQL*Plus lists the text of a SQL statement or PL/SQL command before and after SQL*Plus replaces substitution variables with values. ON lists the text; OFF suppresses the listing.
WRA[P] {OFF|ON}
Controls whether SQL*Plus truncates the display of a SELECTed row if it is too long for the current line width. OFF truncates the SELECTed row; ON allows the SELECTed row to wrap to the next line.
Use the WRAPPED and TRUNCATED clauses of the COLUMN command to override the setting of WRAP for specific columns.
Usage Notes
SET ROLE and SET TRANSACTION are SQL commands (see the Oracle7 Server SQL Language Reference Manual for more information). When not followed by the keywords TRANSACTION or ROLE, SET is assumed to be a SQL*Plus command.
Examples
The following examples show sample uses of selected SET command variables.
APPINFO
To display the setting of APPINFO, enter:
SQL> SHOW APPINFO SQL> appinfo is ON and set to "SQL*Plus"To change the default text, enter:
SQL> SET APPI 'This is SQL*Plus' SQL> SHOW APPINFO SQL> appinfo is ON and set to "This is SQL*Plus"To make sure that registration has taken place, enter:
SQL> VARIABLE MOD VARCHAR2(50) SQL> VARIABLE ACT VARCHAR2(40) SQL> EXECUTE DBMS_APPLICATION_INFO.READ_MODULE(:MOD, :ACT); SQL> PRINT MOD MOD --------------------------------------------------- This is SQL*PlusCMDSEP
To specify a TTITLE and format a column on the same line:
SQL> SET CMDSEP + SQL> TTITLE LEFT 'SALARIES' + COLUMN SAL FORMAT $9,999 SQL> SELECT ENAME, SAL FROM EMP 2 WHERE JOB = 'CLERK';
The following output results:
SALARIES ENAME SAL ---------- ------- SMITH $800 ADAMS $1,100 JAMES $950 MILLER $1,300
COLSEP
To set the column separator to "|":
SQL> SET COLSEP '|' SQL> SELECT ENAME, JOB, DEPTNO 2 FROM EMP 3 WHERE DEPTNO = 20;
The following output results:
ENAME |JOB | DEPTNO ------------------------------- SMITH |CLERK | 20 JONES |MANAGER | 20 SCOTT |ANALYST | 20 ADAMS |CLERK | 20 FORD |ANALYST | 20
COMPATIBILITY
To run a command file, SALARY.SQL, created with Version 6 of Oracle, enter
SQL> SET COMPATIBILITY V6 SQL> START SALARY
After running the file, reset compatibility to V7 to run command files created with Oracle7:
SQL> SET COMPATIBILITY V7
Alternatively, you can add the command SET COMPATIBILITY V6 to the beginning of the command file, and reset COMPATIBILITY to V7 at the end of the file.
ESCAPE
If you define the escape character as an exclamation point (!), then
SQL> SET ESCAPE ! SQL> ACCEPT v1 PROMPT 'Enter !&1:'
displays this prompt:
Enter &1:
HEADING
To suppress the display of column headings in a report, enter
SQL> SET HEADING OFF
If you then run a SQL SELECT command,
SQL> SELECT ENAME, SAL FROM EMP 2 WHERE JOB = 'CLERK';
the following output results:
ADAMS 1100 JAMES 950 MILLER 1300
LONG
To set the maximum width for displaying and copying LONG values to 500, enter
SQL> SET LONG 500
The LONG data will wrap on your screen; SQL*Plus will not truncate until the 501st character.
LONGCHUNKSIZE
To set the size of the increments in which SQL*Plus retrieves LONG values to 100 characters, enter
SQL> SET LONGCHUNKSIZE 100
The LONG data will be retrieved in increments of 100 characters until the entire value is retrieved or the value of SET LONG is reached.
SERVEROUTPUT
To enable the display of DBMS_OUTPUT.PUT_LINE, enter
SQL> SET SERVEROUTPUT ON
The following example shows what happens when you execute an anonymous procedure with SET SERVEROUTPUT ON:
SQL> BEGIN 2 DBMS_OUTPUT.PUT_LINE('Task is complete'); 3 END; 4 / Task is complete. PL/SQL procedure successfully completed.
The following example shows what happens when you create a trigger with SET SERVEROUTPUT ON:
SQL> CREATE TRIGGER SERVER_TRIG BEFORE INSERT OR UPDATE - > OR DELETE 2 ON SERVER_TAB 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE('Task is complete.'); 5 END; 6 / Trigger created. SQL> INSERT INTO SERVER_TAB VALUES ('TEXT'); Task is complete. 1 row created.
To set the output to WORD_WRAPPED, enter
SQL> SET SERVEROUTPUT ON FORMAT WORD_WRAPPED SQL> SET LINESIZE 20 SQL> BEGIN 2 DBMS_OUTPUT.PUT_LINE('If there is nothing left to do'); 3 DBMS_OUTPUT.PUT_LINE('shall we continue with plan B?'); 4 end; 5 / If there is nothing left to do shall we continue with plan B?
To set the output to TRUNCATED, enter
SQL> SET SERVEROUTPUT ON FORMAT TRUNCATED SQL> SET LINESIZE 20 SQL> BEGIN 2 DBMS_OUTPUT.PUT_LINE('If there is nothing left to do'); 3 DBMS_OUTPUT.PUT_LINE('shall we continue with plan B?'); 4 END; 5 / If there is nothing shall we continue wi
SQLCONTINUE
To set the SQL*Plus command continuation prompt to an exclamation point followed by a space, enter
SQL> SET SQLCONTINUE '! '
SQL*Plus will prompt for continuation as follows:
SQL> TTITLE 'YEARLY INCOME' - ! RIGHT SQL.PNO SKIP 2 - ! CENTER 'PC DIVISION' SQL>
SUFFIX
To set the default command-file extension to UFI, enter
SQL> SET SUFFIX UFI
If you then enter
SQL> GET EXAMPLE
SQL*Plus will look for a filenamed EXAMPLE with an extension of UFI instead of EXAMPLE with an extension of SQL.
Shows the value of a SQL*Plus system variable or the current SQL*Plus environment.
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} [schema.]name] LABEL LNO PNO REL[EASE] REPF[OOTER] REPH[EADER] SPOO[L] SQLCODE TTI[TLE] USER
Terms and Clauses
Refer to the following list for a description of each term or clause:
system_variable
Represents any system variable set by the SET command.
ALL
Lists the settings of all SHOW options, except ERRORS and LABEL, in alphabetical order.
BTI[TLE]
Shows the current BTITLE definition.
ERR[ORS] [{FUNCTION|PROCEDURE|PACKAGE|PACKAGE BODY|TRIGGER|VIEW} [schema.]name]
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, or view) 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.
Note: You must have DBA privilege to view other schemas, or other schema's object errors.
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 alter these widths using the COLUMN command.
LABEL
Shows the security level for the current session. For more information, see your Trusted Oracle Administrator's Guide.
LNO
Shows the current line number (the position in the current page of the display and/or spooled output).
PNO
Shows the current page number.
REL[EASE]
Shows the release number of Oracle 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.
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 under which you are currently accessing SQL*Plus.
Example
To list the current LINESIZE, enter
SQL> SHOW LINESIZE
If the current linesize equals 80 characters, SQL*Plus will give the following response:
linesize 80
The following example illustrates how to create a stored procedure and then show its compilation errors:
SQL> connect system/manager SQL> create procedure scott.proc1 as SQL> begin SQL> :p1 := 1; SQL> end; SQL> /
Warning: Procedure created with compilation errors.
SQL> show errors Errors for PROCEDURE SCOTT.PROC1:
LINE/COL ERROR -------------------------------------------------------- 3/3 PLS-00049: bad bind variable 'P1'
SQL> show errors procedure proc1 No errors. SQL> show errors procedure scott.proc1 Errors for PROCEDURE SCOTT.PROC1:
LINE/COL ERROR -------------------------------------------------------- 3/3 PLS-00049: bad bind variable 'P1'
Stores query results in an operating system file and, optionally, sends the file to a printer.
Syntax
SPO[OL] [file_name[.ext]|OFF|OUT]
Terms and Clauses
Refer to the following list for a description of each term or clause:
file_name[.ext]
Represents the name of the file to which you wish to spool. SPOOL followed by file_name begins spooling displayed output to the named file. If you do not specify an extension, SPOOL uses a default extension (LST or LIS on most systems).
OFF
OUT
Stops spooling and sends the file to your host computer's standard (default) printer.
Enter SPOOL with no clauses to list the current spooling status.
Usage Notes
To spool output generated by commands in a command file without displaying the output on the screen, use SET TERMOUT OFF. SET TERMOUT OFF does not affect output from commands run interactively.
Examples
To record your displayed output in a filenamed DIARY using the default file extension, enter
SQL> SPOOL DIARY
To stop spooling and print the file on your default printer, type
SQL> SPOOL OUT
Starts SQL*Plus from the operating system prompt.
Syntax
SQLPLUS [[-S[ILENT]] [logon] [start]]|-?
where:
logon
Requires the following syntax:
username[/password] [@database_specification]|/|/NOLOG
start
Allows you to enter the name of a command file and arguments. SQL*Plus passes the arguments to the command file as though you executed the file using the SQL*Plus START command. The start clause requires the following syntax:
@file_name[.ext][arg ...]
See the START command in this chapter for more information.
Terms and Clauses
You have the option of entering logon. If you do not specify logon and do specify start, SQL*Plus assumes that the first line of the command file contains a valid logon. If neither start nor logon are specified, SQL*Plus prompts for logon information.
Refer to the following list for a description of each term or clause:
username[/password]
Represent the username and password with which you wish to start SQL*Plus and connect to Oracle. If you omit username and password, SQL*Plus prompts you for them. If you enter a slash (/) or simply enter [Return] to the prompt for username, SQL*Plus logs you in using a default logon (see "/" below).
If you omit only password, SQL*Plus prompts you for password. When prompting, SQL*Plus does not display password on your terminal screen.
Represents a default logon using operating system authentication. You cannot enter a database_specification 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 Oracle7 Server Administrator's Guide for information about operating system authentication.
/NOLOG
Establishes no initial connection to Oracle. 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 command file prompt for the username, password, or database specification. The first line of this command file is not assumed to contain a logon.
database_specification
Consists of a SQL*Net connection string. The exact syntax depends upon the SQL*Net communications protocol your Oracle installation uses. For more information, refer to the SQL*Net manual appropriate for your protocol or contact your DBA.
-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. Use SILENT to invoke SQL*Plus within another program so that the use of SQL*Plus is invisible to the user.
-?
Makes SQLPLUS display its current version and level number and then returns control to the operating system. Do not enter a space between the hyphen (-) and the question mark (?).
Usage Notes
The SQL*Plus command may be known by a different name under some operating systems, for example, plus33. See your SQL*Plus installation documentation for further information on your specific operating system.
SQL*Plus supports a Site Profile, a SQL*Plus command file created by the database administrator. This file is generally named GLOGIN with an extension of SQL. SQL*Plus executes this command file whenever any user starts SQL*Plus and SQL*Plus establishes the Oracle connection. The Site Profile allows the DBA to set up SQL*Plus environment defaults for all users at a particular site; users cannot directly access the Site Profile. The default name and location of the Site Profile depend on your system. Site Profiles are described in more detail in the Oracle installation and user's manual(s) provided for your operating system.
SQL*Plus also supports a User Profile, executed after the Site Profile. SQL*Plus searches for a filenamed LOGIN with the extension SQL in your current directory. If SQL*Plus does not find the file there, SQL*Plus will search a system-dependent path to find the file. Some operating systems may not support this path search.
If you fail to log in successfully to SQL*Plus because your username or password is invalid or some other error, SQL*Plus will return an error status equivalent to an EXIT FAILURE command. See the EXIT command in this chapter for further information.
Examples
To start SQL*Plus with username SCOTT and password TIGER, enter
SQLPLUS SCOTT/TIGER
To start SQL*Plus, as above, and to make POLICY the default database (where POLICY is a valid SQL*Net database connection string), enter
SQLPLUS SCOTT/TIGER@POLICY
To start SQL*Plus with username SCOTT and password TIGER and run a command filenamed STARTUP with the extension SQL, enter
SQLPLUS SCOTT/TIGER @STARTUP
Note the space between TIGER and @STARTUP.
Executes the contents of the specified command file.
Syntax
STA[RT] file_name[.ext] [arg ...]
Terms and Clauses
Refer to the following list for a description of each term or clause:
file_name[.ext]
Represents the command file 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). For information on changing this default extension, see the SUFFIX variable of the SET command in this chapter.
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. Consult the Oracle installation and user's manual(s) 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 command file. If you enter one or more arguments, SQL*Plus substitutes the values into the parameters (&1, &2, and so forth) in the command file. The first argument replaces each occurrence of &1, the second replaces each occurrence of &2, and so forth.
The START command DEFINEs the parameters with the values of the arguments; if you START the command file again in this session, you can enter new arguments or omit the arguments to use the old values.
For more information on using parameters, refer to the subsection "Passing Parameters through the START Command" under "Writing Interactive Commands".
Usage Notes
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 the @ and @@ commands in this chapter for further information on these commands.
The EXIT or QUIT commands in a command file terminate SQL*Plus.
Example
A filenamed PROMOTE with the extension SQL, used to promote employees, might contain the following command:
SELECT * FROM EMP WHERE MGR=&1 AND JOB='&2' AND SAL>&3;
To run this command file, enter
SQL> START PROMOTE 7280 CLERK 950
SQL*Plus then executes the following command:
SELECT * FROM EMP WHERE MGR=7280 AND JOB='CLERK' AND SAL>950;
Saves attributes of the current SQL*Plus environment in a host operating system file (a command file).
Syntax
STORE {SET} file_name[.ext] [CRE[ATE]|REP[LACE]| APP[END]]
Terms and Clauses
Refer to the following list for a description of each term or clause:
SET
Saves the values of the system variables.
Refer to the SAVE command for information on the other terms and clauses in the STORE command syntax.
Usage Notes
This command creates a command file which can be executed with the START, @ or @@ commands.
If you want to store a file under a name identical to a STORE command clause (that is, CREATE, REPLACE or APPEND), you must put the name in single quotes or specify a file extension.
Example
To store the current SQL*Plus system variables in a file named DEFAULTENV with the default command-file extension, enter
SQL> STORE SET DEFAULTENV
To append the current SQL*Plus system variables to an existing file called DEFAULTENV with the extension OLD, enter
SQL> STORE SET DEFAULTENV.OLD APPEND
Syntax
TIMI[NG] [START text|SHOW|STOP]
Terms and Clauses
Refer to the following list for a description of each term or clause:
START text
Sets up a timer and makes text the name of the timer. You can have more than one active timer by STARTing additional timers before STOPping the first; SQL*Plus nests each new timer within the preceding one. The timer most recently STARTed becomes the current timer.
SHOW
Lists the current timer's name and timing data.
STOP
Lists the current timer's name and timing data, then deletes the timer. If any other timers are active, the next most recently STARTed timer becomes the current timer.
Enter TIMING with no clauses to list the number of active timers.
Usage Notes
You can use this data to do a performance analysis on any commands or blocks run during the period.
For information about the data TIMING displays, see the Oracle installation and user's manual(s) provided for your operating system. Refer to SET TIMING ON for information on automatically displaying timing data after each SQL command or PL/SQL block you run.
To delete all timers, use the CLEAR TIMING command.
Examples
To create a timer named SQL_TIMER, enter
SQL> TIMING START SQL_TIMER
To list the current timer's title and accumulated time, enter
SQL> TIMING SHOW
To list the current timer's title and accumulated time and to remove the timer, enter
SQL> TIMING STOP
Places and formats a specified title at the top of each report page or lists the current TTITLE definition. The old form of TTITLE is used if only a single word or string in quotes follows the TTITLE command.
For a description of the old form of TTITLE, see TTITLE in Appendix F.
Syntax
TTI[TLE] [printspec [text|variable] ...]|[OFF|ON]
where printspec represents one or more of the following clauses used to place and format the text:
COL n S[KIP] [n] TAB n LE[FT] CE[NTER] R[IGHT] BOLD FORMAT text
Terms and Clauses
Refer to the following list for a description of each term or clause. These terms and clauses also apply to the BTITLE command.
text
Represents the title text. Enter text in single quotes if you want to place more than one word on a single line.
variable
Represents a user variable or any of the following system-maintained values:
To print one of these values, reference the appropriate variable in the title. You can format variable with the FORMAT clause.
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). "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], and 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 bolded 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 COLUMN FORMAT 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 according to the default format.
Refer to the FORMAT clause of the COLUMN command in this chapter for more information on default formats.
Enter TTITLE with no clauses to list the current TTITLE definition.
Usage Notes
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 NEW_VALUE 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 the constants and variables in the order you specify them, positioning and formatting each constant or variable as specified by the printspec clauses that precede it.
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 of the quotes, as follows:
SQL> 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
SQL> TTITLE LEFT 'Monthly Analysis' CENTER '11 Mar 88' - > RIGHT 'Page:' FORMAT 999 SQL.PNO SKIP CENTER - > 'Data in Thousands'
The following title results:
Monthly Analysis 11 Mar 88 Page: 1 Data in Thousands
To suppress the top title display without changing its definition, enter
SQL> TTITLE OFF
Deletes one or more user variables that you defined either explicitly (with the DEFINE command) or implicitly (with an argument to the START command).
Syntax
UNDEF[INE] variable ...
Terms and Clauses
Refer to the following for a description of the term or clause:
variable
Represents the name of the user variable you wish to delete. One or more user variables may be deleted in the same command.
Example
To undefine a user variable named POS, enter
SQL> UNDEFINE POS
To undefine two user variables named MYVAR1 and MYVAR2, enter
SQL> UNDEFINE MYVAR1 MYVAR2
Declares a bind variable that can then be referenced in PL/SQL. For more information on bind variables, see "Using Bind Variables". For more information about PL/SQL, see your PL/SQL User's Guide and Reference.
VARIABLE without arguments displays a list of all the variables declared in the session. VARIABLE followed only by a variable name lists that variable.
Syntax
VAR[IABLE] [variable [NUMBER|CHAR|CHAR (n)|VARCHAR2 (n)| REFCURSOR]]
Terms and Clauses
Refer to the following list for a description of each term or clause:
variable
Represents the name of the bind variable you wish to create.
NUMBER
Creates a variable of type NUMBER with a fixed length.
CHAR
Creates a variable of type CHAR (character) with a length of one.
CHAR (n)
Creates a variable of type CHAR with a maximum length of n, up to 255.
VARCHAR2 (n)
Creates a variable of type VARCHAR2 with a maximum length of n, up to 2000.
REFCURSOR
Creates a variable of type REF CURSOR.
Usage Notes
Bind variables may be used as parameters to stored procedures, or may be directly referenced in anonymous PL/SQL blocks.
To display the value of a bind variable created with VARIABLE, use the PRINT command. For more information, see the PRINT command in this chapter.
To automatically display the value of a bind variable created with VARIABLE, use the SET AUTOPRINT command. For more information, see the SET AUTOPRINT command in this chapter.
Bind variables cannot be used in the COPY command or SQL statements, except in PL/SQL blocks. Instead, use substitution variables.
SQL*Plus REFCURSOR bind variables may be used to reference PL/SQL 2.3 Cursor Variables, allowing PL/SQL output to be formatted by SQL*Plus. For more information on PL/SQL Cursor Variables, see your PL/SQL User's Guide and Reference.
When you execute a VARIABLE ... REFCURSOR command, SQL*Plus creates a cursor bind variable. The cursor is automatically opened by an OPEN ... FOR SELECT statement referencing the bind variable in a PL/SQL block. SQL*Plus closes the cursor after completing a PRINT statement for that bind variable, or on exit.
SQL*Plus formatting commands such as BREAK, COLUMN, COMPUTE and SET may be used to format the output from PRINTing a REFCURSOR.
A REFCURSOR bind variable may not be PRINTed more than once without re-executing the PL/SQL OPEN ... FOR statement.
Examples
The following example illustrates creating a bind variable and then setting it to the value returned by a function:
SQL> VARIABLE id NUMBER SQL> BEGIN 2 :id := emp_management.hire 3 ('BLAKE','MANAGER','KING',2990,'SALES'); 4 END;
The bind variable named id can be displayed with the PRINT command or used in subsequent PL/SQL subprograms.
The following example illustrates automatically displaying a bind variable:
SQL> SET AUTOPRINT ON SQL> VARIABLE a REFCURSOR SQL> BEGIN 2 OPEN :a FOR SELECT * FROM DEPT ORDER BY DEPTNO; 3 END; 4 / PL/SQL procedure successfully completed. DEPTNO DNAME LOC -------- ------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
In the above example, there is no need to issue a PRINT command to display the variable.
The following example creates some variables and then lists them:
SQL> VARIABLE id NUMBER SQL> VARIABLE txt CHAR (20) SQL> VARIABLE myvar REFCURSOR SQL> VARIABLE variable id datatype NUMBER variable txt datatype CHAR(20) variable myvar datatype REFCURSOR
The following example lists a single variable:
SQL> VARIABLE txt variable txt datatype CHAR(20)
The following example illustrates producing a report listing individual salaries and computing the departmental and total salary cost:
SQL> VARIABLE RC REFCURSOR 2 BEGIN 3 OPEN :RC FOR SELECT DNAME, ENAME, SAL 4 FROM EMP, DEPT 5 WHERE EMP.DEPTNO = DEPT.DEPTNO 6 ORDER BY EMP.DEPTNO, ENAME; 7 END; 8 / PL/SQL procedure successfully completed. SQL> SET PAGESIZE 100 FEEDBACK OFF SQL> TTITLE LEFT '*** Departmental Salary Bill ***' SKIP 2 SQL> COLUMN SAL FORMAT $999,990.99 HEADING 'Salary' SQL> COLUMN DNAME HEADING 'Department' SQL> COLUMN ENAME HEADING 'Employee' SQL> COMPUTE SUM LABEL 'Subtotal:' OF SAL ON DNAME SQL> COMPUTE SUM LABEL 'Total:' OF SAL ON REPORT SQL> BREAK ON DNAME SKIP 1 ON REPORT SKIP 1 SQL> PRINT RC *** Departmental Salary Bill *** Department Employee Salary -------------- ------------ ---------- ACCOUNTING CLARK $2,450.00 KING $5,000.00 MILLER $1,300.00 ************** ---------- Subtotal: $8,750.00 RESEARCH ADAMS $1,100.00 FORD $3,000.00 JONES $2,975.00 SCOTT $3,000.00 SMITH $800.00 ************** ---------- Subtotal: $10,875.00 SALES ALLEN $1,600.00 BLAKE $2,850.00 JAMES $950.00 MARTIN $1,250.00 TURNER $1,500.00 WARD $1,250.00 ************** ---------- Subtotal: $9,400.00 ---------- Total: $29,025.00
Exits SQL*Plus if an operating system error occurs (such as a file I/O error).
Syntax
WHENEVER OSERROR {EXIT [SUCCESS|FAILURE|n|variable] [COMMIT|ROLLBACK]|CONTINUE [COMMIT|ROLLBACK|NONE]}
Terms and Clauses
Refer to the following list for a description of each term or clause:
EXIT [SUCCESS|FAILURE|n|variable]
Directs SQL*Plus to exit as soon as an operating system error is detected. You can also specify that SQL*Plus return a success or failure code, the operating system failure code, or a number or variable of your choice. See EXIT in this chapter for details.
CONTINUE
COMMIT
Directs SQL*Plus to execute a COMMIT before exiting or continuing and save pending changes to the database.
ROLLBACK
Directs SQL*Plus to execute a ROLLBACK before exiting or continuing and abandon pending changes to the database.
NONE
Directs SQL*Plus to take no action before continuing.
Usage Notes
If you do not enter the WHENEVER OSERROR command, the default behavior of SQL*Plus is to continue and take no action when an operating system error occurs.
Examples
The commands in the following command file cause SQL*Plus to exit and COMMIT any pending changes if a failure occurs when writing to the output file:
WHENEVER OSERROR EXIT SQL.OSCODE COMMIT SPOOL MYLOG UPDATE EMP SET SAL = SAL*1.1 COPY TO SCOTT/TIGER@HQDB - REPLACE EMP - USING SELECT * FROM EMP SPOOL OUT SELECT SAL FROM EMP
Exits SQL*Plus if a SQL command or PL/SQL block generates an error.
Syntax
WHENEVER SQLERROR {EXIT [SUCCESS|FAILURE|WARNING|n|variable] [COMMIT|ROLLBACK]|CONTINUE [COMMIT|ROLLBACK|NONE]}
Terms and Clauses
Refer to the following list for a description of each term or clause:
EXIT [SUCCESS|FAILURE|WARNING|n|variable]
Directs SQL*Plus to exit as soon as it detects a SQL command or PL/SQL block error (but after printing the error message). SQL*Plus will not exit on a SQL*Plus error. The EXIT clause of WHENEVER SQLERROR follows the same syntax as the EXIT command. See EXIT in this chapter for details.
CONTINUE
COMMIT
Directs SQL*Plus to execute a COMMIT before exiting or continuing and save pending changes to the database.
ROLLBACK
Directs SQL*Plus to execute a ROLLBACK before exiting or continuing and abandon pending changes to the database.
NONE
Directs SQL*Plus to take no action before continuing.
Usage Notes
The WHENEVER SQLERROR command is triggered by SQL command or PL/SQL block errors, and not by SQL*Plus command errors.
If you do not enter the WHENEVER SQLERROR command, the default behavior of SQL*Plus is to continue and take no action when a SQL error occurs.
Examples
The commands in the following command file cause SQL*Plus to exit and return the SQL error code if the SQL UPDATE command fails:
SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE SQL> UPDATE EMP SET SAL = SAL*1.1
The following SQL command error causes SQL*Plus to exit and return the SQL error code:
SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE SQL> SELECT COLUMN_DOES_NOT_EXITS FROM DUAL; SELECT COLUMN_DOES_NOT_EXITS FROM DUAL * ERROR at line 1: ORA-00904: invalid column name Disconnected from Oracle.....
The following SQL command error causes SQL*Plus to exit and return the value of the variable MY_ERROR_VAR:
SQL> DEFINE MY_ERROR_VAR 99 SQL> WHENEVER SQLERROR EXIT MY_ERROR_VAR SQL> UPDATE NON_EXISTED_TABLE SET COL1 = COL1 + 1; UPDATE NON_ESISTED_TABLE SET COL1 = COL1 + 1 * ERROR at line 1: ORA-00942: table or view does not exist
Disconnected from Oracle.....
The following examples show that the WHENEVER SQLERROR command does not have any effect on SQL*Plus commands, but does on SQL commands and PL/SQL blocks:
SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE SQL> COLUMN ENAME HEADIING "EMPLOYEE NAME" Unknown COLUMN option "HEADIING" SQL> SHOW NON_EXISTED_OPTION Unknown SHOW option "NON_EXISTED_OPTION" SQL> GET NON_EXISTED_FILE.SQL Unable to open "NON_EXISTED_FILE.SQL" SQL>
The following PL/SQL block error causes SQL*Plus to exit and return the SQL error code:
SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE SQL> BEGIN 2 SELECT COLUMN_DOES_NOT_EXITS FROM DUAL; 3 END; 4 / SELECT COLUMN_DOES_NOT_EXITS FROM DUAL; * ERROR at line 2: ORA-06550: line 2, column 10: PLS-00201: identifier 'COLUMN_DOES_NOT_EXITS' must be declared ORA-06550: line 2, column 3: PL/SQL: SQL Statement ignored
Disconnected from Oracle.....
![]() ![]() Prev Next |
![]() Copyright © 1996 Oracle Corporation. All Rights Reserved. |
![]() Library |
![]() Product |
![]() Contents |
![]() Index |