Oracle PL/SQL/SQL Plus/Environment Variables

Common SQL*Plus Environment Variables

 
Variable                         Function                                                                  Usage
ARRAY[SIZE]                      Determines the number of rows fetched from database at one time           SET ARRAY 50
AUTO[COMMIT]                     Specifies whether commits of transactions are automatic or manual         SET AUTO ON
COLSEP                           Specifies the text that you want printed in between column values         SET COLSEP
COPY[COMMIT]                     Sets the frequency of commits when using the COPY command                 SET COPY 10000
DEF[INE]{&/C/ON/OFF}             Sets the prefix character used during variable substitutions              SET DEFINE ON
ECHO {OFF/ON}                    Specifies whether echo is on or off                                       SET ECHO ON
EDITF[ILE]                       Sets the default filename when you use your default editor                SET EDITFILE draft.sql
FEED[BACK] {OFF/ON}              Specifies whether SQL*Plus shows the number of records returned           SET FEEDBACK OFF
FLUSH {OFF/ON}                   Determines whether output is buffered or flushed to the screen            SET FLUSH OFF
HEA[DING] {OFF/ON}               Specifies whether the column headers are printed or not                   SET HEAD OFF
LIN[ESIZE] {80|n}                Specifies the number of characters displayed per line                     SET LINESIZE 40
LONG {80/n}                      Specifies the maximum width of the LONG, CLOB, NCLOB, and XMLType values  SET LONG 100000
NEWP[AGE] {1/n/none}             Specifies the number of blank lines at the top of each new page           SET NEWPAGE 0
NUM[WIDTH] {10/n}                Specifies the format for displaying numbers                               SET NUM
PAGES[IZE] {24/n}                Specifies the number of lines in each page                                SET PAGESIZE 60
PAU[SE] {OFF/ON/TEXT}            Specifies the amount of output that is printed to the screen              SET PAUSE ON
SERVEROUT[PUT] {OFF/ON}[SIZE n]  Specifies whether output of PL/SQL code is shown                          SET SERVEROUTPUT ON
SQLP[ROMPT] {SQL> |TEXT}         Specifies the command prompt for SQL*Plus sessions                        SET SQLPROMPT "salapati >"
TERM[OUT] {OFF/ON}               Specifies whether command file output is displayed or not                 SET TERMOUT OFF
TI[ME] {OFF/ON}                  Displays time if set to on                                                SET TIME OFF
TIMI[NG] {OFF/ON}                Controls the display of timing for SQL commands                           SET TIMING OFF
VER[IFY] {OFF/ON}                Specifies whether SQL text is displayed after variable substitution       SET VERIFY OFF

SHOW ALL command

 
SQL> 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
FEEDBACK ON for 6 or more rows
flagger OFF
flush ON
heading ON
headsep "|" (hex 7c)
instance "local"
linesize 200
lno 8
loboffset 1
logsource ""
long 80
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; padding:0px 0px 0px 0
px; margin:0px 0px 0px 0px;} th {font:bold 10pt Arial,Helvetica,sans-serif; color:#336699; background:#cccc99; padding:0px 0px 0px 0px;} h1 {font:16pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; border-bottom:1px solid #cccc99; margin-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:9pt Arial,Helvetica,sans-serif; color:#663300; background:#ffffff; margin-top:0pt; margin-bottom:0pt; vertical-align:top;}</style><title>SQL*Plu
s Report</title>" BODY "" TABLE "border="1" width="90%" align="center" summary="Script output"" SPOOL OFF ENTMAP ON PREFORMAT OFF
newpage 1
null ""
numformat ""
numwidth 10
pagesize 14
PAUSE is OFF
pno 2
recsep WRAP
recsepchar " " (hex 20)
release 1002000100
repfooter OFF and is NULL
repheader OFF and is NULL
serveroutput OFF
shiftinout INVISIBLE
showmode OFF
spool OFF
sqlblanklines OFF
sqlcase MIXED
sqlcode 0
sqlcontinue "> "
sqlnumber ON
sqlpluscompatibility 10.2.0
sqlprefix "#" (hex 23)
sqlprompt "SQL> "
sqlterminator ";" (hex 3b)
suffix "sql"
tab ON
termout ON
timing OFF
trimout ON
trimspool OFF
ttitle OFF and is the first few characters of the next SELECT statement
underline "-" (hex 2d)
USER is "SYS"
verify ON
wrap : lines will be wrapped
SQL>