SQL Plus - System Variable (Configuration)
Table of Contents
About
Sqlplus system variable alter the SqlPlus - SqlPlus Command environment settings, for example, to:
- customize HTML formatting
- enable or disable the printing of column headings
- set the number of lines per page
- set the display width for data
Articles Related
How to
Set a system variable
You set a system variable with the SET command
SET TERM OFF
Show one system variable
List all system variable
You can list the system variables with the SHOW command and the All option.
show all
appinfo is OFF and set to "SQL*Plus"
arraysize 15
autocommit OFF
autoprint OFF
autorecovery OFF
autotrace OFF
blockterminator "." (hex 2e)
btitle OFF and is the first few characters of the next SELECT statement
cmdsep OFF
colsep " "
compatibility version NATIVE
concat "." (hex 2e)
copycommit 0
COPYTYPECHECK is ON
define "&" (hex 26)
describe DEPTH 1 LINENUM OFF INDENT ON
echo OFF
editfile "afiedt.buf"
embedded OFF
escape OFF
escchar OFF
exitcommit ON
colinvisible OFF
FEEDBACK ON for 6 or more rows
flagger OFF
flush ON
heading ON
headsep "|" (hex 7c)
instance "local"
linesize 100
lno 1
loboffset 1
logsource ""
long 5000
longchunksize 80
markup HTML OFF HEAD "<style type='text/css'> body {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} p {font:10pt Arial
,Helvetica,sans-serif; color:black; background:White;} table,tr,td {font:10pt Arial,Helvetica,sans-serif; color:Black; background:#f7f7e7; p
adding:0px 0px 0px 0px; margin:0px 0px 0px 0px;} th {font:bold 10pt Arial,Helvetica,sans-serif; color:#336699; background:#cccc99; padding:0
px 0px 0px 0px;} h1 {font:16pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; border-bottom:1px solid #cccc99; ma
rgin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;-
} h2 {font:bold 10pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; margin-top:4pt; margin-bottom:0pt;} a {font:9
pt Arial,Helvetica,sans-serif; color:#663300; background:#ffffff; margin-top:0pt; margin-bottom:0pt; vertical-align:top;}</style><title>SQL*
Plus Report</title>" BODY "" TABLE "border='1' width='90%' align='center' summary='Script output'" SPOOL OFF ENTMAP ON PREFORMAT OFF
newpage 1
null "null"
numformat ""
numwidth 10
pagesize 0
PAUSE is OFF
pno 0
recsep WRAP
recsepchar " " (hex 20)
release 1201000100
repfooter OFF and is NULL
repheader OFF and is NULL
securedcol is OFF
serveroutput OFF
shiftinout INVISIBLE
showmode OFF
spool OFF
sqlblanklines OFF
sqlcase MIXED
sqlcode 0
sqlcontinue "> "
sqlnumber ON
sqlpluscompatibility 12.1.0
sqlprefix "#" (hex 23)
sqlprompt "[email protected]>"
sqlterminator ";" (hex 3b)
suffix "sql"
tab ON
termout ON
timing OFF
trimout ON
trimspool ON
ttitle OFF and is the first few characters of the next SELECT statement
underline "-" (hex 2d)
USER is "HR"
verify ON
wrap : lines will be wrapped
xmloptimizationcheck OFF
errorlogging is OFF
Storing and restoring System Variable
To store the current setting of all system variables, use the STORE command
STORE SET file_name
To restore the system variables, just start the file saved
START file_name
List
An asterisk (*) indicates the SET option is not available in iSQL*Plus.
| System Variables | Description |
|---|---|
| APPI[NFO]{ON | OFF | text} | Sets automatic registering of scripts through the DBMS_APPLICATION_INFO package. |
| arraysize | Sets the number of rows, called a batch, that SQL*Plus will fetch from the database at one time. |
| AUTO[COMMIT]{ON | OFF | IMM[EDIATE] | n} | Controls when Oracle Database commits pending changes to the database. |
| AUTOP[RINT] {ON | OFF} | Sets the automatic printing of bind variables. |
| AUTORECOVERY [ON | OFF] | ON sets the RECOVER command to automatically apply the default filenames of archived redo log files needed during recovery. |
| autotrace | Displays a report on the execution of successful SQL DML statements (SELECT, INSERT, UPDATE or DELETE). |
| BLO[CKTERMINATOR] {. | c | ON | OFF} | Sets the non-alphanumeric character used to end PL/SQL blocks to c. |
| CMDS[EP] {; | c | ON | OFF} | Sets the non-alphanumeric character used to separate multiple SQL*Plus commands entered on one line to c. |
| COLSEP { | text} | SET COLSEP determines the column separator character to be printed between column output that is rendered inside <PRE> tags. Sets the text to be printed between selected columns. |
| CON[CAT] {. | c | ON | OFF} | 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. |
| COPYC[OMMIT] {0 | n} | Controls the number of batches after which the COPY command commits changes to the database. |
| COPYTYPECHECK {ON | OFF} | Sets the suppression of the comparison of datatypes while inserting or appending to tables with the COPY command. |
| DEF[INE] {& | c | ON | OFF} | Sets the character used to prefix variables to c. |
| DESCRIBE [DEPTH {1 | n | ALL}] [LINENUM {ON | OFF}] [INDENT {ON | OFF}] | Sets the depth of the level to which you can recursively describe an object. |
| ECHO {ON | OFF} | Controls whether the START command lists each command in a script as the command is executed. |
| *SET EDITF[ILE] file_name[.ext] | Sets the default filename for the EDIT command. |
| EMB[EDDED] {ON | OFF} | Controls where on a page each report begins. |
| ESC[APE] {\ | c | ON | OFF} | Defines the character you enter as the escape character. |
| FEED[BACK] {6 | n | ON | OFF} | Displays the number of records returned by a query when a query selects at least n records. |
| FLAGGER {OFF | ENTRY | INTERMED[IATE] | FULL} | Checks to make sure that SQL statements conform to the ANSI/ISO SQL92 standard. |
| *FLUSH {ON | OFF} | Controls when output is sent to the user's display device. |
| HEA[DING] {ON | OFF} | Controls printing of column headings in reports. |
| HEADS[EP] { | | c | ON | OFF} | Defines the character you enter as the heading separator character. |
| INSTANCE [instance_path | LOCAL] | Changes the default instance for your session to the specified instance path. |
| LINESIZE {80 | n} SET LIN[ESIZE] {150 | n} | Sets the total number of characters that SQL*Plus displays on one line before beginning a new line. |
| LOBOF[FSET] {1 | n} | Sets the starting position from which CLOB and NCLOB data is retrieved and displayed. |
| LOGSOURCE [pathname] | Specifies the location from which archive logs are retrieved during recovery. |
| LONG {80 | n} | Sets maximum width (in bytes) for displaying LONG, CLOB, NCLOB and XMLType values; and for copying LONG values. |
| LONGC[HUNKSIZE] {80 | n} | Sets the size (in bytes) of the increments in which SQL*Plus retrieves a LONG, CLOB, NCLOB or XMLType value. |
| MARK[UP] HTML [ON | OFF] [HEAD text] [BODY text] [TABLE text] [ENTMAP {ON | OFF}] [SPOOL {ON | OFF}] [PRE[FORMAT] {ON | OFF}] | Outputs HTML marked up text, which is the output used by iSQL*Plus. |
| NEWP[AGE] {1 | n | NONE} | Sets the number of blank lines to be printed from the top of each page to the top title. |
| NULL text | Sets the text that represents a null value in the result of a SQL SELECT command. |
| NUMF[ORMAT] format | Sets the default format for displaying numbers. |
| NUM[WIDTH] {10 | n} | Sets the default width for displaying numbers. |
| PAGES[IZE] {14 | n} | Sets the number of lines in each page. |
| PAU[SE] {ON | OFF | text} | Enables you to control scrolling of your terminal when running reports. |
| RECSEP {WR[APPED] | EA[CH] | OFF} | RECSEP tells SQL*Plus where to make the record separation. |
| RECSEPCHAR { | c} | Display or print record separators. |
| SERVEROUT[PUT] {ON | OFF} [SIZE {n | UNL[IMITED]}] [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. |
| *SHIFT[INOUT] {VIS[IBLE] | INV[ISIBLE]} | Enables correct alignment for terminals that display shift characters. |
| *SHOW[MODE] {ON | OFF} | Controls whether SQL*Plus lists the old and new settings of a SQL*Plus system variable when you change the setting with SET. |
| *SQLBL[ANKLINES] {ON | OFF} | Controls whether SQL*Plus puts blank lines within a SQL command or script. |
| SQLC[ASE] {MIX[ED] | LO[WER] | UP[PER]} | Converts the case of SQL commands and PL/SQL blocks just prior to execution. |
| *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] {ON | OFF} | Sets the prompt for the second and subsequent lines of a SQL command or PL/SQL block. |
| SQLPLUSCOMPAT[IBILITY] {x.y[.z]} | Sets the behavior or output format of VARIABLE to that of the release or version specified by x.y[.z]. |
| *SQLPRE[FIX] {# | c} | Sets the SQL*Plus prefix character. |
| *SQLP[ROMPT] {SQL> | text} | Sets the SQL*Plus command prompt. |
| SQLT[ERMINATOR] {; | c | ON | OFF} | Sets the character used to end and execute SQL commands to c. |
| *SUF[FIX] {SQL | text} | Sets the default file that SQL*Plus uses in commands that refer to scripts. |
| *tab | Determines how SQL*Plus formats white space in terminal output. |
| *TERMOUT {ON | OFF} | Controls the display of output generated by commands executed from a script. |
| *TI[ME] {ON | OFF} | Controls the display of the current time. |
| TIMI[NG] {ON | OFF} | Controls the display of timing statistics. |
| *TRIM[OUT] {ON | OFF} | Determines whether SQL*Plus puts trailing blanks at the end of each displayed line. |
| *TRIMS[POOL] {ON | OFF} | Determines whether SQL*Plus puts trailing blanks at the end of each spooled line. |
| UND[ERLINE] {- | c | ON | OFF} | Sets the character used to underline column headings in SQL*Plus reports to c. |
| VER[IFY] {ON | OFF} | 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. |
| WRAP {ON | OFF} | Controls whether SQL*Plus truncates the display of a SELECTed row if it is too long for the current line width. |
| XQUERY BASEURI {text} | Defines the base URI to use. This is useful to change the prefix of the file to access when writing generic XQuery expressions. |
| XQUERY ORDERING {UNORDERED | ORDERED | DEFAULT} | Controls the ordering of results from an XQuery. |
| XQUERY NODE {BYVALUE | BYREFERENCE | DEFAULT} | Sets the preservation mode for notes created or returned. |
| XQUERY CONTEXT {text} | Specifies an XQuery context item which can be either a node or a value. |