SQL * Plus set command syntax

SQL * PLUS maintains system variables, also known as SET variables. It can be used to create a special environment for SQL * PLUS interaction, such as: SET
Set the display width of the NUMBER data, the NUMBER of rows per page, and the column width. Use the SET command to change these System Changes
You can also use the SHOW command to list them.

The syntax for using the set command is as follows:
SET system variable value

The system variables and their optional values are as follows:
ARRAYSIZE {20 (default) | n}
AUTOCOMMIT {OFF (default) | ON | IMMEDIATE}
BLOCKTERMINATOR {. (default) | C}
Eclipsep {; | C | OFF (default) | ON}
COMPATIBILITY {V5 | V6 | V7 | NATIVE (default )}
CONCAT {. (default) | C | OFF | ON (default )}
COPYCOMMIT {0 (default) | n}
CRT
DEFINE {& | C | OFF | ON (default )}
ECHO {OFF | ON}
EMBEDDED {OFF (default) | ON}
ESCAPE {/(default) | C | OFF (default) | ON}
FEEDBACK {6 (default) | n | OFF | ON}
FLUSH {OFF | ON (default )}
HEADING {OFF | ON (default )}
Headsep {| (default) | c | off | on (default )}
Linesize {80 (default) | n}
Long {80 (default) | n}
Longchunksize {80 (default) | n}
Maxdata n
Newpage {1 (default) | n}
Null text
Numformat
Numwidth {10 (default) | n}
Pagesize {14 (default) | n}
Pause {off (default) | on | text}
Recsep {wrapped (default) | each | off}
Recsepchar {| c}
Scan {off | on (default )}
Serveroutput {off | on} size N
Showmode {off (default) | on}
Space {1 (default) | n}
Sqlcase {mixed (default) | Lower | Upper}
SQLCONTINUE {>; (default) | text}
SQLNUMBER {OFF | ON (default )}
SQLPERFIX {# (default) | C}
SQLPROMPT {SQL>; (default) | text}
SQLTERMINATOR {; (default) | C | OFF | ON (default )}
SUFFIX {SQL (default) | text}
TAB {OFF | ON (default )}
TERMOUT {OFF | ON (default )}
TIME {OFF (default) | ON}
TIMING {OFF (default) | ON}
TRIMOUT {OFF | ON (default )}
UNDERLINE {-(default) | C | OFF | ON (default )}
VERIFY {OFF | ON (default )}
WRAP {OFF | ON (default )}

System variable description:
ARRAYSIZE {20 (default) | n} sets the number of rows in a batch, which is the number of rows that SQL * PLUS obtains from the database at a time. The valid value is 1 to 5000. A large value can improve the validity of queries and subqueries. Many Rows can be obtained, but more memory is required. when the number exceeds 1000, the effect is not very good.

AUTOCOMMIT {OFF (default) | ON | IMMEDIATE} controls the submission of ORACLE Database modifications. when you set ON, you can submit changes to the database after each SQL command or PL/SQL block is executed in ORACLE. When you set OFF, automatic submission is stopped and modifications must be submitted manually, for example, use the SQL COMMIT command. the IMMEDIATE function is the same as that of ON.

BLOCKTERMINATOR {. (default) | C} is a non-alphanumeric character used to end PL/SQL blocks. To execute blocks, you must RUN the RUN Command or/command.

Eclipsep {; | C | OFF (default) | ON} is a non-alphanumeric character used to separate multiple SQL/PLUS commands entered in one line. ON or OFF controls whether multiple commands can be entered in one line. ON will automatically set the command separator as a semicolon (. C Indicates the character.

COMPATIBILITY {V5 | V6 | V7 | NATIVE (default)} specifies the ORACLE version currently linked. if the current ORACLE version is 5, set "COMPATIBILITY" to V5; "version 6 to V6;" 7 to V7. if you want the database to decide the setting, set it to NATIVE.

CONCAT {. (default) | C | OFF | ON (default)} set end 1 to replace the character referenced by the variable. after the replacement variable reference character is aborted, it can be followed by all characters as part of the experience. Otherwise, SQL * PLUS will be interpreted as part of the replacement variable name. when the CONCAT switch is ON, SQL * PLUS can reset the value of CONCAT to point (.).

COPYCOMMIT {0 (default) | n} controls the number of batches submitted by the copy Command for database modification. after n batches of data are copied each time, the data is submitted to the target database. valid values are 0 to 5000. the ARRAYSIZE variable can be used to set the size of a batch. if COPYCOMMIT is set to 0, only one commit is executed at the end of the COPY operation.

CRT crt changes the default CRT file used by the SQL * PLUS RUNFORM command. if the CRT does not contain anything, the crt only contains ''''. if you want to use NEW. CRT (the default CRT is OLD. CRT), you can call Form in the following Form:
SQL>; RUNFORM-C NEW form name
Or
SQL>; SET CRT NEW
SQL>; RUNFORM form name
The second method stores the CRT selection, so that you do not need to specify the next time you run the RUNFORM command (in the same SQL * PLUS interaction.

DEFINE {& | C | OFF | ON (default)} specifies the character used to replace the variable. ON or OFF controls whether SQL * PLUS scans and replaces variables with their values. the setting of define on or OFF controls the setting of SCAN variables.

ECHO {OFF | ON} controls whether the START command lists every command in the command file. If it is ON, it lists the command; if it is OFF, it stops the column list.

EMBEDDED {OFF (default) | ON} controls where each report starts ON a page. when it is OFF, each report is forced to start at the top of the new page; when it is ON, a report starts to run anywhere ON the page.

ESCAPE {/(default) | C | OFF (default) | ON} is the character defined as the Escape character. if it is OFF, the Escape character does not work. when it is ON, enable the Escape character to work.

FEEDBACK {6 (default) | n | OFF | ON} displays the number of records returned by the query. ON and OFF are displayed as ON or OFF. when FEEDBACK is set to ON, it is equivalent to setting n to 1. if you set FEEDBACK to 0, it is equivalent to setting it to OFF.

FLUSH {OFF | ON (default)} controls the output to the user's display device. when it is OFF, the operating system will make buffer output; when it is ON, the buffer is not allowed. use OFF only when running command files in non-interactive mode, which can reduce program I/O always and improve performance.

HEADING {OFF | ON (default)} controls the printing of the column title in the report. If it is ON, the column title is printed in the report; if it is OFF, the column title is not printed.

HEADSEP {| (default) | C | OFF | ON (default)} defines the title delimiter. you can use the title Separator in the COLUMN command to split the COLUMN title into multiple lines. ON and OFF separate titles into ON or OFF. when the title is separated into OFF, SQL * PLUS prints the title separator like any character.

LINESIZE {80 (default) | n} sets the total number of characters that SQL * PLUS displays in a row. It also controls the center-aligned text and right-aligned text in TTITLE and BTITLE. LINESIZE can be defined as 1 to the maximum value, and the maximum value depends on the operating system.

LONG {80 (default) | n} is the maximum width setting for displaying and copying LONG values. For ORACLE7, the maximum value of n is 2 GB; for version 6, the maximum value is 32767.

LONGCHUNKSIZE {80 (default) | n} is the incremental size of the LONG value retrieved by SQL * PLUS. Due to memory restrictions, you can perform incremental search. This variable is only applied to ORACLE7.

MAXDATA n sets SQL * PLUS to handle the maximum number of characters in a wide row. Its default value and maximum value are variable in different operating systems.

NEWPAGE {1 (default) | n} sets the number of blank lines to be printed Between the header and the top title of each page. if the value is 0, a line break is sent between pages and the screen is cleared on many terminals.

The NULL text setting indicates null text. If NULL does not contain text, spaces are displayed (lack of time is saved). You can use the NULL clause in the COLUMN command to control the setting of NULL variables for this COLUMN.

The default format of the displayed value is NUMFORMAT.

NUMWIDTH {10 (default) | n} sets the default width for the displayed value.

PAGESIZE {14 (default) | n} specifies the number of rows from the top title to the end of the page. print the report on a 11 inch-long paper with a value of 54 and a value of 1 inch (the value of NEWPAGE is 6 ).

PAUSE {OFF (default) | ON | text} controls terminal scrolling when the report is displayed. during each pause, you must press the RETURN key. ON Causes SQL * PLUS to be suspended at the beginning of each report output page. the specified text is displayed when SQL * PLUS is paused. to type multiple words, you must enclose the text in single quotes.

RECSEP {WRAPPED (default) | EACH | OFF}
RECSEPCHAR {| C} specifies the condition for displaying or printing the record branch character. A record branch character is a single line consisting of the characters specified by RECSEPCHAR. The space is the default character of RECSEPCHAR.
RECSEP tells SQL * PLUS where to separate records. for example, set the RECSEP to WRAPPED, and print the record line break after each Winding Line. if you set RECSEP to EACH, SQL * PLUS prints a line break after EACH line. if you set the RECSEP to OFF, SQL * PLUS does not print the line break.

SCAN {OFF | ON (default)} controls the scanning of existing replacement variables and values. OFF prohibits the processing of replacement variables and values. ON allows normal processing.

SERVEROUTPUT {OFF | ON} SIZE n determines whether the output is displayed in the SQL * PLUS stored procedure. when OFF, the output is displayed. SIZE: specifies the number of buffer output bytes. The default value is 2000. n cannot be less than 2000 or greater than 1 million.

SHOWMODE {OFF (default) | ON} controls whether SQL * PLUS lists its new old or new settings when executing the SET command.

SPACE {1 (default) | n} sets the number of spaces between output columns. The maximum value is 10.

SQLCASE {MIXED (default) | LOWER | UPPER} converts the case sensitivity of SQL commands and PL/SQL blocks before execution. SQL * PLUS converts all the text in the command, including the direct amount and identifier with quotation marks. SQLCASE does not change the SQL Buffer itself.

SQLCONTINUE {>; (default value) | text} When an SQL * PLUS command is continued on an additional line, SQL * PLUS is prompted Based on the character sequence set.

SQLNUMBER {OFF | ON (default)} is the prompt for setting the second and subsequent rows of SQL commands and PL/SQL blocks. if it is ON, the row number is prompted; if it is OFF, the prompt is set to SQLPROMPT value.

SQLPERFIX {# (default) | C} sets the SQL * PLUS prefix character. when you type an SQL command or PL/SQL block, you can type an SQL * PLUS command on a separate line. The prefix of SQL * PLUS is used as the prefix. SQL * PLUS directly executes this command without affecting SQL commands or PL/SQL blocks. the prefix must be a non-alphanumeric character.

SQLPROMPT {SQL>; (default) | text} sets the SQL * PLUS command prompt.

SQLTERMINATOR {; (default) | C | OFF | ON (default)} sets the characters used to end and execute SQL commands. OFF means that SQL * PLUS does not recognize the command Terminator and ends the SQL command by entering a blank line. ON Reset the terminator to the default semicolon (.

Suffix {SQL (default) | text} sets the suffix of the default file. SQL * Plus is used in the command to reference the command file. suffix does not control the extension of the output (Spool) file.

Tab {off | on (default)} determines how SQL * Plus formats the blank space in terminal output. when it is off, spaces are used in the output to format the blank space; when it is on, TAB characters are used. the default value of a tab depends on the system. You can use the show tab command to view the default value.

Termout {off | on (default)} controls the display of output generated by the file execution command. off indicates that the command file is output offline without output on the screen. the output is displayed on. termout off does not affect interactive command output.

Time {off (default) | on} controls the display of the current date. On, the current time is displayed before each command prompt; the display of the prohibited time when off.

Timing {off (default) | on} controls the display of time statistics. the time statistics of each running SQL command or PL/SQL block are displayed. When off, the time statistics of every command is disabled.

Trimout {off | on (default)} determines whether SQL * Plus can contain spaces at the end of each display row. the space at the end of each line is removed when on, especially when SQL * Plus is accessed from a slow communication device, the performance can be improved. When off, SQL * Plus is allowed to display the space at the end. trimout on does not affect off-line output. when tab on is set, SQL * Plus ignores trimout on.

UNDERLINE {-(default) | C | OFF | ON (default)} sets the character. ON or OFF used in the UNDERLINE column title in the SQL * PLUS report to enable or disable the UNDERLINE.

VERIFY {OFF | ON (default)} controls whether the text of the command is listed before and after SQL * PLUS is replaced with the value. The text is displayed when SQL * PLUS is used; the column list is prohibited when SQL * PLUS is OFF.

WRAP {OFF | ON (default)} controls whether SQL * PLUS truncates the display of data items. when OFF, the data item is truncated. When ON, the data item is allowed to be wound into the next row. use the WRAPPED and TRUNCATED clauses in the COLUMN command to control the setting of the WRAP for the specified COLUMN.