SQL Plus - System Variable (Configuration)

1 - 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

3 - How to

3.1 - Set a system variable

You set a system variable with the SET command


3.2 - Show one system variable

You can list the system variable with the SHOW command.

termout OFF

3.3 - 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
define "&" (hex 26)
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
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

3.4 - 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

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

5 - Reference

Data Science
Data Analysis
Data Science
Linear Algebra Mathematics

Powered by ComboStrap