Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to understand the SQL*Plus Set parameters, which may not be well understood by many people. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

Using SQL*Plus to maintain system variables (also known as set variables) can establish a special environment for SQL*Plus interaction, such as setting the display width of number data, setting the number of rows per page, setting the width of columns, and so on. You can change these system variables with the set command, or you can list them with the show command. The following is the full usage, and the syntax for using the set command is as follows:

Set system variable value

Where the system variable and its optional values are as follows:

Arraysize

{20 (default) | n}

Sets the number of rows in a batch, which is the number of rows sqlplus fetches from the database at a time. Valid values are 1 to 5000. Large values improve the effectiveness of queries and subqueries, fetching many rows, but also require more memory. When it exceeds 1000, it has little effect.

Autocommit

{off (default) | on | immediate}

Controls the submission of oracle changes to the database. When setting on, commit changes to the database after oracle executes each sql command or pl/sql block; when setting off, automatic commit is suppressed and changes need to be committed manually. For example, using the commit command of sql, the immediate function is the same as on.

Blockterminator

{. (default) | c}

Used to end the pl/sql block. The run command or / command must be issued to execute the block.

Cmdsep

{| c | off (default) | on}

Used to separate multiple sql/plus commands entered on one line. On or off controls whether multiple commands can be entered on a single line. When on, the command delimiter is automatically set to a semicolon (where c represents the character you set).

Compatibility

{v5 | V6 | V7 | native (default)}

Specifies the version of oracle that is currently connected. If the current oracle version is 5, set compatibility to v5, v6 for version 6, and v7 for version 7. If you want the database to determine this setting, set it to native.

Concat

{. (default) | c | off | on (default)}

Sets the character that ends a replacement variable reference. After aborting the replacement variable reference character, you can follow all the characters as part of the experience, otherwise sqlplus will be interpreted as part of the replacement variable name. When the concat switch is on, sqlplus resets the value of concat to point (.).

Copycommit

{0 (default) | n}

Controls the number of batches of changes to the database submitted by the copy command. Each copy in n batches will be committed to the target database. Valid values are 0 to 5000. You can set the size of a batch with the variable arraysize. If copycommit is set to 0, only one commit is performed at the end of the copy operation.

Crt

Crt

Change the default crt file used by the sqlplus runform command. If setting crt contains nothing, then crt contains only'. If you want to use new.crt during a system call to a form (the default crt is old.crt). Form can be called 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 the next time you run the runform command (in the same sqlplus interaction), you don't need to specify it.

Define

{& | c | off | on (default)}

Sets the characters used when replacing variables. On or off controls whether sqlplus scans for commands that replace variables and replaces them with their values. The setting of the on or off of define controls the setting of the scan variable.

Echo

{off | on}

Controls whether the start command lists each command in the command file. When on, list commands; when off, stop listing.

Embedded

{off (default) | on}

Controls where each report begins on one page. When off, force each report to start at the top of the new page; when on, run a report anywhere on the page.

Escape

{\ (default) | c | off (default) | on}

Defines a character as an escape character. When off, the escape character does not work; when on, the escape character works.

Feedback

{6 (default) | n | off | on}

Displays the number of records returned by the query. On and off are displayed on or off. Setting feedback to on is equivalent to setting n to 1; setting feedback to 0 is equivalent to setting it to off.

Flush

{off | on (default)}

Controls the display device that the output is sent to the user. For off, run the operating system for buffer output; for on, buffering is not allowed. Using off only when running command files non-interactively can improve performance by reducing the total number of program iUnix.

Heading

{off | on (default)}

Controls the printing of column headings in the report. Print column headings in the report when on; disable printing column headings when off.

Headsep

{| (default) | c | off | on (default)}

Defines the title delimiter. You can use the header delimiter in the column command to divide the column header into multiple rows. On and off separate the title into on or off. When the title is separated by off, sqlplus prints the title delimiter like any character.

Linesize

{80 (default) | n}

Sets the total number of characters displayed by sqlplus on one line, which also controls centered and right-aligned text in ttitle and btitle. Linesize can be defined from 1 to maximum, which depends on the operating system.

Long

{80 (default) | n}

The setting for the maximum width of the display and copy of long type values. The maximum value is 2g bytes for oracle7,n and 32767 for version 6.

Longchunksize

{80 (default) | n}

Retrieves the incremental size of the long type value for sqlplus. Due to memory limitations, it can be retrieved incrementally, and this variable applies only to oracle7.

Maxdata

N

Sets the maximum number of line width characters that can be processed by sqlplus, whose default and maximum values are variable in different operating systems.

Newpage

{1 (default) | n}

Sets the number of blank lines to print between the header and the top title of each page. If 0, send a sign change between pages and clear the screen on many terminals.

Null

Text

Sets text that represents a null value (null). If null has no text, spaces are displayed (default). Use the null clause in the column command to control the setting of the null variable for this column.

Numformat

Format

Sets the default format for displaying numeric values, which is a numeric format.

Numwidth

{10 (default) | n}

Sets the default width for the display value.

Pagesize

{14 (default) | n}

Sets the number of rows from the top title to the end of the page. Print the report on 11-inch paper with a value of 54, leaving an inch above and below (the newpage value is 6).

Pause

{off (default) | on | text}

When the report is displayed, the control terminal scrolls. You must press the return key at each pause. On will cause sqlplus to pause at the beginning of each report output page. The text specified is the text that is displayed each time sqlplus is paused. If you type more than one word, you must enclose the text in single quotation marks.

Recsep

{wrapped (default) | each | off}

Recsep tells sqlplus where to separate records. For example, set recsep to wrapped, print record line breaks after each winding line; if recsep is set to each,sqlplus, print a record line break after each line; if recsep is set to off,sqlplus, do not print line breaks.

Recsepchar

{| c}

Specifies the conditions under which record line breaks are displayed or printed. A record line break is a single line of characters specified by recsepchar. The space is the default character for recsepchar.

Scan

{off | on (default)}

Controls the scanning of existing replacement variables and values. Off forbids the handling of replacement variables and values; on allows normal processing.

Serveroutput

{off | on} size

Controls whether stored procedures in sqlplus display output. Disable when off, and display output when on. Size sets the number of bytes of buffered output, with a default value of 2000. N cannot be less than 2000 or greater than 1 million.

Showmode

{off (default) | on}

A setting that controls whether sqlplus lists its new and old values old or new when executing the set command.

Space

{1 (default) | n}

Sets the number of spaces between output columns, with a maximum of 10.

Sqlcase

{mixed (default) | lower | upper}

Convert the case of the sql command and the pl/sql block before execution. Sqlplus converts all the text in the command, including quoted literals and markers. Sqlcase does not change the sql buffer itself.

Sqlcontinue

{> (default) | text}

When a sqlplus command is continued on an additional line, sqlplus prompts with the character sequence of the setting.

Sqlnumber

{off | on (default)}

Set prompts for the second and subsequent lines of the sql command and pl/sql block. When on, prompt the line number; when off, prompt to the value of sqlprompt.

Sqlperfix

{# (default) | c}

Sets the sqlplus prefix character. When typing a sql command or pl/sql block, you can type a sqlplus command on a separate line, prefixed by the prefix character of sqlplus. Sqlplus executes the command directly without affecting the sql command or the pl/sql block. Prefix characters must be non-alphanumeric characters.

Sqlprompt

{sql > (default) | text}

Set the command prompt for sqlplus.

Sqlterminator

{(default) | c | off | on (default)}

Sets the characters used to end and execute sql commands. Off means that sqlplus does not recognize the command Terminator and ends the sql command by typing a blank line; the on reset Terminator is the default semicolon.

Suffix

{sql (default) | text}

Sets the suffix of the default file. Sqlplus is used in commands to reference command files. Suffix does not control the extension of the output (spool) file.

Tab

{off | on (default)}

Determines how sqlplus formats white space in the terminal output. When off, use spaces to format white space in the output; when on, use tab characters. The default value of tab depends on the system, and can be viewed with the show tab command.

Termout

{off | on (default)}

Controls the display of the output generated by the file executing the command. Off disables display so that output is spooled from a command file and cannot be seen on the screen; output is displayed on on. Termout off does not affect the output of commands interactively.

Time

{off (default) | on}

Controls the display of the current date. When on, the current time is displayed before each command prompt; the display of time is disabled when off.

Timing

{off (default) | on}

Controls the display of time statistics. When on, the time statistics of each running sql command or pl/sql block are displayed; when off, the time statistics of each command are disabled.

Trimout

{off | on (default)}

Determines whether sqlplus allows spaces at the end of each display line. On removes the trailing space of each line, especially when accessing sqlplus from slow communication devices; off allows sqlplus to display trailing spaces. Trimout on does not affect spooling output. When setting tab on, sqlplus ignores trimout on.

Underline

{- (default) | c | off | on (default)}

Sets the characters used in underlined column headings in sqlplus reports. On or off sets the underscore on or off.

Verify

{off | on (default)}

Controls whether the text of the command is listed before and after sqlplus replaces it with a value. Text is displayed on on; listing is prohibited on off.

Wrap

{off | on (default)}

Controls whether sqlplus truncates the display of data items. Truncate the data item when off; allow the data item to be wound to the next row when on. Use the wrapped and truncated clauses in the column command to control the setting of the wrap for the specified column.

After reading the above, do you have any further understanding of how to understand the SQL*Plus Set parameters? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.