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.

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

@@ (double "at" sign)

Purpose

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.

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).

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.

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:

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.

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.

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).

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.

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.

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.)

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

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'

CHANGE

Purpose

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.

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/

CLEAR

Purpose

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

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 specifiedalias 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.

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.

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).

Displays upper- or lowercase Roman numerals. Value can be an integer between 1 and 3999.

DATE

DATE

Displays value as a date in MM/DD/YY format; used to format NUMBER columns that represent Julian dates.

Table 6 - 1. Number Formats

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.

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.

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.

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

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.

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.

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.

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.

Represent the Oracleusername/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.

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.

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.

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.

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.

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

If 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.

DISCONNECT

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.

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.

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

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.

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.

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.

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

HELP

Purpose:

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

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.

INPUT

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.

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.

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.

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:

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:

RUNFORM

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.

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.

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.

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.

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.

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.

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

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.

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.

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.

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.

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.

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.

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.

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"

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

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.

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.

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.

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.

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.

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.

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

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.

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:

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.