SQL Plus - System Variable (Configuration)

Card Puncher Data Processing

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





Discover More
Card Puncher Data Processing
How you can use a bind variable in SQLPlus

This page is specifically about the usage of SQL bind variable in SQLPlus.
Card Puncher Data Processing
SQL PLUS - Array size

The array size is a configuration variable which set the fetch size. Valid values are 1 to 5000. A large value increases the efficiency of queries and subqueries that fetch many rows, but requires more...
Card Puncher Data Processing
SQL Plus - (Feed|Feedback) - Number of records returned by a query

A system variable that lets display the number of records returned by a query when a query selects at least n records.
Card Puncher Data Processing
SQL Plus - (Hea|heading)

The system variable heading Controls printing of column headings in reports.
Card Puncher Data Processing
SQL Plus - Autotrace system variable

Autotrace is a system variable that permits to set the autotrace functionality. where: OFF: No autotrace ON. All ON EXPLAIN. Only the optimizer execution path is shown. ON STATISTICS. Only...
Card Puncher Data Processing
SQL Plus - Colsep (column separator character)

The COLSEP system variable determines the column separator character to be printed between column output. Sets the text to be printed between selected columns. In ISQL Plus, column separator character...
Card Puncher Data Processing
SQL Plus - Column Headers

Column header management The system variable heading controls the visibility of the headers The system variable pagesize will suppress the headers if it's set to 0. To avoid this behaviour,...
Card Puncher Data Processing
SQL Plus - Concat

The Concat system variable sets the character you can use to terminate a substitution variable reference if you wish to immediately follow the variable with a character that SQLPlus would otherwise interpret...
Card Puncher Data Processing
SQL Plus - DEFINE

Define is: a command that permit to initialize a substitution variable of a system variable The define system variable sets the character used to prefix the substitution variables to c.
Card Puncher Data Processing
SQL Plus - Echo

A system variable that controls whether the START command lists each command ina script as the command is executed.



Share this page:
Follow us:
Task Runner