Set Commands

Sets a system variable to alter the SQL*Plus environment settings for our current session, for example: Display width for data, Turn on HTML formatting, Enabling or disabling printing of column headings, Number of lines per page.

In iSQL*Plus, we can also use the System Variables screen to set system variables. SET system_variable value where system_variable and value represent one of the following clauses:

Syntax:

SQL> SET option value
SQL> SHO[W] option

Options: most of these have an abbreviated and a long form e.g. APPINFO or APPI will do the same thing we can get a list of the set options in SQLPLUS with the command

SQL> HELP SET

Commands and Description

  • APPI[NFO]{ON|OFF|text} - Application info for performance monitor (see DBMS_APPLICATION_INFO)

  • ARRAY[SIZE] {15|n} - Fetch size (1 to 5000) the number of rows that will be retrieved in one go.

  • AUTO[COMMIT] {OFF|ON|IMM[EDIATE]|n} - Autocommit commits after each SQL command or PL/SQL block

  • AUTOP[RINT] {OFF|ON} - Automatic PRINTing of bind variables.

  • AUTORECOVERY [ON|OFF] - Configure the RECOVER command to automatically apply archived redo log files during recovery - without any user confirmation.

  • AUTOT[RACE] {OFF|ON|TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]] - Display a trace report for SELECT, INSERT, UPDATE or DELETE statements EXPLAIN shows the query execution path by performing an EXPLAIN PLAN.

  • CMDS[EP] {;|c|OFF|ON} - Change or enable command separator - default is a semicolon (;)

  • COLSEP { |text} - The text to be printed between SELECTed columns normally a space.

  • COM[PATIBILITY] {V5|V6|V7|V8|NATIVE} - Version of oracle - see also init.ora COMPATIBILITY= We can set this back by up to 2 major versions e.g. Ora 9 supports 8 and 7

  • CON[CAT] {.|c|OFF|ON} - Termination character for substitution variable reference default is a period.

  • COPYC[OMMIT] {0|n} - The COPY command will fetch n batches of data between commits. (n= 0 to 5000) the size of each fetch=ARRAYSIZE. If COPYCOMMIT = 0, COPY will commit just once - at the end.

  • DESCRIBE [DEPTH {1|n|ALL}][LINENUM {ON|OFF}][INDENT {ON|OFF}] - Sets the depth of the level to which we can recursively describe an object (1 to 50) see the DESCRIBE command

  • ECHO {OFF|ON} - Display commands as they are executed

  • EMB[EDDED] {OFF|ON} - OFF = report printing will start at the top of a new page. ON = report printing may begin anywhere on a page.

  • ESC[APE] {|c|OFF|ON} - Defines the escape character. OFF undefines. ON enables.

  • FEED[BACK] {6|n|OFF|ON} - Display the number of records returned (when rows > n ) OFF (or n=0) turns the display off ON sets n=1

  • FLAGGER {OFF|ENTRY|INTERMED[IATE]|FULL} - Checks to make sure that SQL statements conform to the ANSI/ISO SQL92 standard. non-standard constructs are flagged as errors and displayed

  • FLU[SH] {OFF|ON} - Buffer display output (OS) (no longer used in Oracle 9)

  • HEA[DING] {OFF|ON} - print column headings

  • HEADS[EP] {||c|OFF|ON} - Define the heading separator character (used to divide a column heading onto > one line.) OFF will actually print the heading separator char

  • INSTANCE [instance_path|LOCAL] - Change the default instance for our session, this command may only be issued when not already connected and requires Net8

  • LIN[ESIZE] {150|n} - Width of a line (before wrapping to the next line) Earlier versions default to 80, Oracle 9 is 150

  • LOBOF[FSET] {n|1} - Starting position from which CLOB and NCLOB data is retrieved and displayed

  • LOGSOURCE [pathname] - Change the location from which archive logs are retrieved during recovery normally taken from LOG_ARCHIVE_DEST

  • SUF[FIX] {SQL|text} - Default file extension for SQL scripts

  • TAB {OFF|ON} - Format white space in terminal output. OFF = use spaces to format white space. ON = use the TAB char. Note this does not apply to spooled output files. The default is system-dependent. Enter SHOW TAB to see the default value.

  • TERM[OUT] {OFF|ON} - OFF suppresses the display of output from a command file ON displays the output. TERMOUT OFF does not affect the output from commands entered interactively.

  • TI[ME] {OFF|ON} - Display the time at the command prompt.

  • TIMI[NG] {OFF|ON} - ON = display timing statistics for each SQL command or PL/SQL block run. OFF = suppress timing statistics

  • TRIMS[POOL] {ON|OFF} - Allows trailing blanks at the end of each spooled line. This does not affect terminal output.

  • UND[ERLINE] {-|c|ON|OFF} - Set the char used to underline column headings to c.

  • VER[IFY] {OFF|ON} - ON = list the text of a command before and after replacing substitution variables with values. OFF = don’t display the command.

  • WRA[P] {OFF|ON} - Controls whether to truncate or wrap the display of long lines. OFF = truncate ON = wrap to the next line The COLUMN command (WRAPPED and TRUNCATED clause) can override this for specific columns.

⌖ sql rdbms set-commands
← SQL * Plus
ORDER BY Clause →

Subscribe For More Content