Oracle PL/SQL/SQL Plus/Environment Variables

Common SQL*Plus Environment Variables

   <source lang="sql">

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

</source>
   
  

SHOW ALL command

   <source lang="sql">

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>

</source>