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. | |
| For more information on the DBMS_APPLICATION_INFO package, see "Registering Applications" in the Oracle7 Server Tuning manual. |
| 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"![[*]](jump.gif) 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 (;). |
| COLSEP { |text} | 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. |
| EDITF[ILE] file_name[.ext] | 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 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.
| 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: |
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 wiSQLCONTINUE
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.