Monday, May 14, 2012

SQLPLUS SET System Variable Summary

I need to lookup these variables very often for my sqlplus scripts

System Variable Description
Sets automatic registering of scripts through the DBMS_APPLICATION_INFO package.
Sets the number of rows, called a batch, that SQL*Plus will fetch from the database at one time.
Controls when Oracle Database commits pending changes to the database.
Sets the automatic printing of bind variables.
ON sets the RECOVER command to automatically apply the default filenames of archived redo log files needed during recovery.
Displays a report on the execution of successful SQL DML statements (SELECT, INSERT, UPDATE or DELETE).
Sets the non-alphanumeric character used to end PL/SQL blocks to c.
Sets the non-alphanumeric character used to separate multiple SQL*Plus commands entered on one line to c.
In iSQL*Plus, SET COLSEP determines the column separator character to be printed between column output that is rendered inside <PRE> tags. Sets the text to be printed between selected columns.
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.
Controls the number of batches after which the COPY command commits changes to the database.
Sets the suppression of the comparison of datatypes while inserting or appending to tables with the COPY command.
Sets the character used to prefix variables to c.
Sets the depth of the level to which you can recursively describe an object.
Controls whether the START command lists each command in a script as the command is executed.
Sets the default filename for the EDIT command.
Controls where on a page each report begins.
Defines the character you enter as the escape character.
Displays the number of records returned by a query when a query selects at least n records.
Checks to make sure that SQL statements conform to the ANSI/ISO SQL92 standard.
Controls when output is sent to the user's display device.
Controls printing of column headings in reports.
Defines the character you enter as the heading separator character.
Changes the default instance for your session to the specified instance path.
Sets the total number of characters that SQL*Plus displays on one line before beginning a new line.
Sets the starting position from which CLOB and NCLOB data is retrieved and displayed.
Specifies the location from which archive logs are retrieved during recovery.
Sets maximum width (in bytes) for displaying LONG, CLOB, NCLOB and XMLType values; and for copying LONG values.
Sets the size (in bytes) of the increments in which SQL*Plus retrieves a LONG, CLOB, NCLOB or XMLType value.
Outputs HTML marked up text, which is the output used by iSQL*Plus.
Sets the number of blank lines to be printed from the top of each page to the top title.
Sets the text that represents a null value in the result of a SQL SELECT command.
Sets the default format for displaying numbers.
Sets the default width for displaying numbers.
Sets the number of lines in each page.
Enables you to control scrolling of your terminal when running reports.
RECSEP tells SQL*Plus where to make the record separation.
Display or print record separators.
Controls whether to display the output (that is, DBMS_OUTPUT PUT_LINE) of stored procedures or PL/SQL blocks in SQL*Plus.
Enables correct alignment for terminals that display shift characters.
Controls whether SQL*Plus lists the old and new settings of a SQL*Plus system variable when you change the setting with SET.
Controls whether SQL*Plus puts blank lines within a SQL command or script.
Converts the case of SQL commands and PL/SQL blocks just prior to execution.
Sets the character sequence SQL*Plus displays as a prompt after you continue a SQL*Plus command on an additional line using a hyphen (–).
Sets the prompt for the second and subsequent lines of a SQL command or PL/SQL block.
Sets the behavior or output format of VARIABLE to that of the release or version specified by x.y[.z].
Sets the SQL*Plus prefix character.
Sets the SQL*Plus command prompt.
Sets the character used to end and execute SQL commands to c.
Sets the default file that SQL*Plus uses in commands that refer to scripts.
Determines how SQL*Plus formats white space in terminal output.
Controls the display of output generated by commands executed from a script.
Controls the display of the current time.
Controls the display of timing statistics.
Determines whether SQL*Plus puts trailing blanks at the end of each displayed line.
Determines whether SQL*Plus puts trailing blanks at the end of each spooled line.
Sets the character used to underline column headings in SQL*Plus reports to c.
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.
Controls whether SQL*Plus truncates the display of a SELECTed row if it is too long for the current line width.
Defines the base URI to use. This is useful to change the prefix of the file to access when writing generic XQuery expressions.
Controls the ordering of results from an XQuery.
Sets the preservation mode for notes created or returned.
Specifies an XQuery context item which can be either a node or a value.

No comments:

Post a Comment