Set define off oracle для чего
SET System Variable Summary
To change the default text, enter
To make sure that registration has taken place, enter
To change APPINFO back to its default setting, enter
SET ARRAY[SIZE]
Sets the number of rows that SQL*Plus will fetch from the database at one time.
Valid values are 1 to 5000. A large value increases the efficiency of queries and subqueries that fetch many rows, but requires more memory. Values over approximately 100 provide little added performance. ARRAYSIZE has no effect on the results of SQL*Plus operations other than increasing efficiency.
SET AUTO[COMMIT]
ON commits pending changes to the database after Oracle Database executes each successful INSERT, UPDATE, or DELETE, or PL/SQL block. OFF suppresses automatic committing so that you must commit changes manually (for example, with the SQL command COMMIT). IMMEDIATE functions in the same manner as ON. n commits pending changes to the database after Oracle Database executes n successful SQL INSERT, UPDATE, or DELETE commands, or PL/SQL blocks. n cannot be less than zero or greater than 2,000,000,000. The statement counter is reset to zero after successful completion of n INSERT, UPDATE or DELETE commands or PL/SQL blocks, a commit, a rollback, or a SET AUTOCOMMIT command.
SET AUTOCOMMIT does not alter the commit behavior when SQL*Plus exits. Any uncommitted data is committed by default.
For this feature, a PL/SQL block is considered one transaction, regardless of the actual number of SQL commands contained within it.
SET AUTOP[RINT]
Sets the automatic printing of bind variables.
ON or OFF controls whether SQL*Plus automatically displays bind variables (referenced in a successful PL/SQL block or used in an EXECUTE command).
See PRINT for more information about displaying bind variables.
SET AUTORECOVERY [ON | OFF ]
ON sets the RECOVER command to automatically apply the default filenames of archived redo log files needed during recovery.
No interaction is needed, provided the necessary files are in the expected locations with the expected names. The filenames used are derived from the values of the initialization parameters LOG_ARCHIVE_DEST and LOG_ARCHIVE_FORMAT.
OFF, the default option, requires that you enter the filenames manually or accept the suggested default filename given. See RECOVER for more information about database recovery.
You must set AUTORECOVERY to ON to use the RECOVER command in i SQL*Plus.
To set the recovery mode to AUTOMATIC, enter
SET AUTOT[RACE] [EXP[LAIN]] [STAT[ISTICS]]
Displays a report on the execution of successful SQL DML statements (SELECT, INSERT, UPDATE or DELETE).
The report can include execution statistics and the query execution path.
Using ON or TRACEONLY with no explicit options defaults to EXPLAIN STATISTICS.
The TRACEONLY option may be useful to suppress the query data of large queries. If STATISTICS is specified, SQL*Plus still fetches the query data from the server, however, the data is not displayed.
The AUTOTRACE report is printed after the statement has successfully completed.
When SQL*Plus produces a STATISTICS report, a second connection to the database is automatically created. This connection is closed when the STATISTICS option is set to OFF, or you log out of SQL*Plus.
The formatting of your AUTOTRACE report may vary depending on the version of the server to which you are connected and the configuration of the server. The additional information and tabular output of AUTOTRACE PLAN is supported when connecting to Oracle Database 10 g (Release 10.1) or later. When you connect to an earlier database, the older form or AUTOTRACE reporting is used.
AUTOTRACE is not available when FIPS flagging is enabled.
AUTOTRACE PLAN does not use glogin.sql column definitions to display output.
See Tracing Statements for more information on AUTOTRACE.
SET BLO[CKTERMINATOR]
It cannot be an alphanumeric character or a whitespace. To execute the block, you must issue a RUN or / (slash) command.
OFF means that SQL*Plus recognizes no PL/SQL block terminator. ON changes the value of c back to the default period (.), not the most recently used character.
SET CMDS[EP]
ON or OFF controls whether you can enter multiple commands on a line. ON automatically sets the command separator character to a semicolon (;).
To specify a title with TTITLE and format a column with COLUMN, both on the same line, enter
SET COLSEP
S ets the column separator character printed between columns in output.
If the COLSEP variable contains blanks or punctuation characters, you must enclose it with single quotes. The default value for text is a single space.
The Column Separator (SET COLSEP) is only used in i SQL*Plus when Preformatted Output is ON (SET MARKUP HTML PREFORMAT).
To set the column separator to «|» enter
SET CON[CAT]
Sets the character used to terminate a substitution variable reference when SQL*Plus would otherwise interpret the next character as a part of the variable name.
SQL*Plus resets the value of CONCAT to a period when you switch CONCAT on.
SET COPYC[OMMIT]
Controls the number of rows after which the COPY command commits changes to the database.
COPY commits rows to the destination database each time it copies n row batches. Valid values are zero to 5000. You can set the size of a batch with the ARRAYSIZE variable. If you set COPYCOMMIT to zero, COPY performs a commit only at the end of a copy operation.
SET COPYTYPECHECK
Sets the suppression of the comparison of datatypes while inserting or appending to tables with the COPY command.
This is to facilitate copying to DB2, which requires that a CHAR be copied to a DB2 DATE.
SET DEF[INE]
ON or OFF controls whether SQL*Plus will scan commands for substitution variables and replace them with their values. ON changes the value of c back to the default ‘&’, not the most recently used character. The setting of DEFINE to OFF overrides the setting of the SCAN variable.
See SET SCAN
SET DESCRIBE [DEPTH < 1 | n | ALL>] [LINENUM ] [INDENT ]
Sets the depth of the level to which you can recursively describe an object.
The valid range of the DEPTH clause is from 1 to 50. If you SET DESCRIBE DEPTH ALL, then the depth will be set to 50, which is the maximum level allowed. You can also display the line number and indentation of the attribute or column name when an object contains multiple object types. Use the SET LINESIZE command to control the width of the data displayed.
See DESCRIBE for more information about describing objects.
To create an object type ADDRESS, enter
To create the table EMPLOYEE that contains a nested object, EMPADDR, of type ADDRESS, enter
To describe the table EMPLOYEE to a depth of two levels, and to indent the output and display line numbers, enter:
SET ECHO
Controls whether or not to echo commands in a script that is executed with @, @@ or START. ON displays the commands on screen. OFF suppresses the display. ECHO does not affect the display of commands you enter interactively or redirect to SQL*Plus from the operating system.
SET EDITF[ILE] file_name [. ext ]
SET EDITFILE is not supported in i SQL*Plus
Sets the default filename for the EDIT command. See EDIT for more information about the EDIT command. The default filename for the EDIT command is afiedt.buf which is the SQL buffer. The buffer has no command history list and does not record SQL*Plus commands.
SET EMB[EDDED]
Controls where on a page each report begins.
OFF forces each report to start at the top of a new page. ON enables a report to begin anywhere on a page. Set EMBEDDED to ON when you want a report to begin printing immediately following the end of the previously run report.
SET ESC[APE]
Defines the character used as the escape character.
OFF undefines the escape character. ON enables the escape character. ON changes the value of c back to the default «\».
You can use the escape character before the substitution character (set through SET DEFINE) to indicate that SQL*Plus should treat the substitution character as an ordinary character rather than as a request for variable substitution.
If you define the escape character as an exclamation point (!), then
displays this prompt:
To set the escape character back to the default value of \ (backslash), enter
SET FEED[BACK]
Displays the number of records returned by a script when a script selects at least n records.
ON or OFF turns this display on or off. Turning feedback ON sets n to 1. Setting feedback to zero is equivalent to turning it OFF.
SET FEEDBACK OFF also turns off the statement confirmation messages such as ‘Table created’ and ‘PL/SQL procedure successfully completed’ that are displayed after successful SQL or PL/SQL statements.
SET FLAGGER
Checks to make sure that SQL statements conform to the ANSI/ISO SQL92 standard.
If any non-standard constructs are found, the Oracle Database Server flags them as errors and displays the violating syntax. This is the equivalent of the SQL language ALTER SESSION SET FLAGGER command.
You may execute SET FLAGGER even if you are not connected to a database. FIPS flagging will remain in effect across SQL*Plus sessions until a SET FLAGGER OFF (or ALTER SESSION SET FLAGGER = OFF) command is successful or you exit SQL*Plus.
When FIPS flagging is enabled, SQL*Plus displays a warning for the CONNECT, DISCONNECT, and ALTER SESSION SET FLAGGER commands, even if they are successful.
SET FLU[SH]
SET FLUSH is not supported in i SQL*Plus
Controls when output is sent to the user’s display device. OFF enables the operating system to buffer output. ON disables buffering. FLUSH only affects display output, it does not affect spooled output.
Use OFF only when you run a script non-interactively (that is, when you do not need to see output and/or prompts until the script finishes running). The use of FLUSH OFF may improve performance by reducing the amount of program I/O.
SET HEA[DING]
Controls printing of column headings in reports.
ON prints column headings in reports; OFF suppresses column headings.
The SET HEADING OFF command does not affect the column width displayed, it only suppresses the printing of the column header itself.
To suppress the display of column headings in a report, enter
If you then run a SQL SELECT command
the following output results:
To turn the display of column headings back on, enter
SET HEADS[EP]
Defines the character used as a line break in column headings.
The heading separator character cannot be alphanumeric or white space. You can use the heading separator character in the COLUMN command and in the old forms of BTITLE and TTITLE to divide a column heading or title onto more than one line. ON or OFF turns heading separation on or off. When heading separation is OFF, SQL*Plus prints a heading separator character like any other character. ON changes the value of c back to the default «|».
The Heading Separator character (SET HEADSEP) is only supported in i SQL*Plus when the Preformatted Output preference is ON (SET MARKUP HTML PREFORMAT).
SET INSTANCE [ instance_path | LOCAL ]
Changes the default instance for your session to the specified instance path.
Using the SET INSTANCE command does not connect to a database. The default instance is used for commands when no instance is specified. Any commands preceding the first use of SET INSTANCE communicate with the default instance.
To reset the instance to the default value for your operating system, you can either enter SET INSTANCE with no instance_path or SET INSTANCE LOCAL.
Note, you can only change the instance when you are not currently connected to any instance. That is, you must first make sure that you have disconnected from the current instance, then set or change the instance, and reconnect to an instance in order for the new setting to be enabled.
This command may only be issued when Oracle Net is running. You can use any valid Oracle Net connect identifier as the specified instance path. See your operating system-specific Oracle Database documentation for a complete description of how your operating system specifies Oracle Net connect identifiers. The maximum length of the instance path is 64 characters.
To set the default instance to «PROD1» enter
To set the instance back to the default of local, enter
You must disconnect from any connected instances to change the instance.
SET LIN[ESIZE] < 80 | n >SET LIN[ESIZE] < 150 | n >in i SQL*Plus
Sets the total number of characters that SQL*Plus displays on one line before beginning a new line.
It also controls the position of centered and right-aligned text in TTITLE, BTITLE, REPHEADER and REPFOOTER. Changing the linesize setting can affect text wrapping in output from the DESCRIBE command. DESCRIBE output columns are typically allocated a proportion of the linesize. Decreasing or increasing the linesize may give unexpected text wrapping in your display. You can define LINESIZE as a value from 1 to a maximum that is system dependent.
SET LOBOF[FSET]
Sets the starting position from which CLOB and NCLOB data is retrieved and displayed.
To set the starting position from which a CLOB column’s data is retrieved to the 22nd position, enter
The CLOB data will wrap on your screen; SQL*Plus will not truncate until the 23rd character.
SET LOGSOURCE [ pathname ]
Specifies the location from which archive logs are retrieved during recovery.
The default value is set by the LOG_ARCHIVE_DEST initialization parameter in the Oracle Database initialization file, init.ora. Using the SET LOGSOURCE command without a pathname restores the default location.
To set the default location of log files for recovery to the directory «/usr/oracle10/dbs/arch» enter
SET LONG
Querying LONG columns requires enough local memory to store the amount of data specified by SET LONG, irrespective of the value of the SET LONGCHUNKSIZE command. This requirement does not apply when querying LOBs.
It is recommended that you do not create tables with LONG columns. LONG columns are supported only for backward compatibility. Use LOB columns (CLOB, NCLOB) instead. LOB columns have fewer restrictions than LONG columns and are still being enhanced.
The maximum value of n is 2,000,000,000 bytes.
To set the maximum number of bytes to fetch for displaying and copying LONG values, to 500, enter
The LONG data will wrap on your screen; SQL*Plus will not truncate until the 501st byte. The default for LONG is 80 bytes.
SET LONGC[HUNKSIZE]
To set the size of the increments in which SQL*Plus retrieves LONG values to 100 bytes, enter
The LONG data will be retrieved in increments of 100 bytes until the entire value is retrieved or the value of SET LONG is reached, whichever is the smaller.
SET MARK[UP] HTML [ON | OFF ] [HEAD text ] [BODY text ] [TABLE text ] [ENTMAP < ON | OFF>] [SPOOL ] [PRE[FORMAT] ]
Outputs HTML marked up text, which is the output used by i SQL*Plus.
Beware of using options which generate invalid HTML output in i SQL*Plus as it may corrupt the browser screen. The HEAD and BODY options may be useful for dynamic reports and for reports saved to local files.
To be effective, SET MARKUP commands that change values in dynamic report output must occur before statements that produce query output. The first statement that produces query output triggers the output of information affected by SET MARKUP such as HEAD and TABLE settings. Subsequent SET MARKUP commands have no effect on the information already sent to the report.
Use the SHOW MARKUP command to view the status of MARKUP options.
The following is a script which uses the SET MARKUP HTML command to enable HTML marked up text to be spooled to a specified file:
The SET MARKUP example command is laid out for readability using line continuation characters «–» and spacing. Command options are concatenated in normal entry.
Use your favorite text editor to enter the commands necessary to set up the HTML options and the query you want for your report.
As this script contains SQL*Plus commands, do not attempt to run it with / (slash) from the buffer because it will fail. Save the script in your text editor and use START to execute it:
As well as writing the HTML spool file, employee.htm, the output is also displayed on screen because SET TERMOUT defaults to ON. You can view the spool file, employee.htm, in your web browser. It should appear something like the following:
Description of the illustration markup.gif
SET NEWP[AGE]SET NULL text
Sets the text displayed whenever a null value occurs in the result of a SQL SELECT command.
Use the NULL clause of the COLUMN command to override the setting of the NULL variable for a given column. The default output for a null is blank («»).
SET NUMF[ORMAT] format
SET NUM[WIDTH]
Sets the default width for displaying numbers. See the FORMAT clause of the COLUMN command for number format descriptions.
COLUMN FORMAT settings take precedence over SET NUMFORMAT settings, which take precedence over SET NUMWIDTH settings.
SET PAGES[IZE]
Sets the number of rows on each page of output in i SQL*Plus, and the number of lines on each page of output in command-line and Windows GUI. You can set PAGESIZE to zero to suppress all headings, page breaks, titles, the initial blank line, and other formatting information.
In i SQL*Plus, sets the number of rows displayed on each page. Error and informational messages are not counted in the page size, so pages may not always be exactly the same length. The default pagesize for i SQL*Plus is 24.
SET PAU[SE]
You can embed terminal-dependent escape sequences in the PAUSE command. These sequences allow you to create inverse video messages or other effects on terminals that support such characteristics.
SET RECSEP
RECSEP tells SQL*Plus where to make the record separation.
For example, if you set RECSEP to WRAPPED, SQL*Plus prints a record separator only after wrapped lines. If you set RECSEP to EACH, SQL*Plus prints a record separator following every row. If you set RECSEP to OFF, SQL*Plus does not print a record separator.
The Display Record Separator preference (SET RECSEP) is only supported in i SQL*Plus when Preformatted Output is On (SET MARKUP HTML PREFORMAT).
SET RECSEPCHAR
Defines the character to display or print to separate records.
A record separator consists of a single line of the RECSEPCHAR (record separating character) repeated LINESIZE times. The default is a single space.
SET SERVEROUT[PUT] [SIZE < n | UNL[IMITED] >] [FOR[MAT] ]
Controls whether to display output (that is, DBMS_OUTPUT.PUT_LINE) of stored procedures or PL/SQL blocks in SQL*Plus. The DBMS_OUTPUT line length limit is 32767 bytes.
OFF suppresses the output of DBMS_OUTPUT.PUT_LINE. ON displays the output.
SIZE sets the number of bytes of the output that can be buffered within the Oracle Database server. The default is UNLIMITED. n cannot be less than 2000 or greater than 1,000,000. In i SQL*Plus you can enter the word UNLIMITED in the Size field of the Script Formatting Preferences screen.
Resources are not pre-allocated when SERVEROUTPUT is set. As there is no performance penalty, use UNLIMITED unless you want to conserve physical memory.
Every server output line begins on a new output line.
When WRAPPED is enabled SQL*Plus wraps the server output within the line size specified by SET LINESIZE, beginning new lines when required.
When WORD_WRAPPED is enabled, each line of server output is wrapped within the line size specified by SET LINESIZE. Lines are broken on word boundaries. SQL*Plus left justifies each line, skipping all leading whitespace.
When TRUNCATED is enabled, each line of server output is truncated to the line size specified by SET LINESIZE.
To enable text display in a PL/SQL block using DBMS_OUTPUT.PUT_LINE, enter
The following example shows what happens when you execute an anonymous procedure with SET SERVEROUTPUT ON:
The following example shows what happens when you create a trigger with SET SERVEROUTPUT ON:
To set the output to WORD_WRAPPED, enter
To set the output to TRUNCATED, enter
SET SHIFT[INOUT]
SET SHIFTINOUT is not supported in i SQL*Plus
Enables correct alignment for terminals that display shift characters. The SET SHIFTINOUT command is useful for terminals which display shift characters together with data (for example, IBM 3270 terminals). You can only use this command with shift sensitive character sets (for example, JA16DBCS).
Use VISIBLE for terminals that display shift characters as a visible character (for example, a space or a colon). INVISIBLE is the opposite and does not display any shift characters.
To enable the display of shift characters on a terminal that supports them, enter
where «:» = visible shift character uppercase represents multibyte characters
lowercase represents singlebyte characters
SET SHOW[MODE]
SET SHOWMODE is not supported in i SQL*Plus
Controls whether SQL*Plus lists the old and new settings of a SQL*Plus system variable when you change the setting with SET. ON lists the settings; OFF suppresses the listing. SHOWMODE ON has the same behavior as the obsolete SHOWMODE BOTH.
SET SQLBL[ANKLINES]
SET SQLBLANKLINES is not supported in i SQL*Plus
Controls whether SQL*Plus puts blank lines within a SQL command or script. ON interprets blank lines and new lines as part of a SQL command or script. OFF, the default value, does not allow blank lines or new lines in a SQL command or script or script.
Enter the BLOCKTERMINATOR to stop SQL command entry without running the SQL command. Enter the SQLTERMINATOR character to stop SQL command entry and run the SQL statement.
To allow blank lines in a SQL statement, enter
The following output results:
SET SQLC[ASE]
Converts the case of SQL commands and PL/SQL blocks just prior to execution.
SQL*Plus converts all text within the command, including quoted literals and identifiers, to uppercase if SQLCASE equals UPPER, to lowercase if SQLCASE equals LOWER, and makes no changes if SQLCASE equals MIXED.
SQLCASE does not change the SQL buffer itself.
SET SQLCO[NTINUE] < >| text >
SET SQLCONTINUE is not supported in i SQL*Plus
Sets the character sequence SQL*Plus displays as a prompt after you continue a SQL*Plus command on an additional line using a hyphen (–).
To set the SQL*Plus command continuation prompt to an exclamation point followed by a space, enter
SQL*Plus will prompt for continuation as follows:
The default continuation prompt is «> «.
SET SQLN[UMBER]
SET SQLNUMBER is not supported in i SQL*Plus
Sets the prompt for the second and subsequent lines of a SQL command or PL/SQL block. ON sets the prompt to be the line number. OFF sets the prompt to the value of SQLPROMPT.
SET SQLPLUSCOMPAT[IBILITY]
The default setting for SQLPLUSCOMPATIBILITY is the value of the SQL*Plus client.
It is recommended that you add SET SQLPLUSCOMPATIBILITY 10.2.0 to your scripts to maximize their compatibility with future versions of SQL*Plus.
SQL*Plus Compatibility Matrix
The SQL*Plus Compatibility Matrix tabulates behavior affected by each SQL*Plus compatibility setting. SQL*Plus compatibility modes can be set in three ways:
You can include a SET SQLPLUSCOMPATIBILITY command in your site or user profile. On installation, there is no SET SQLPLUSCOMPATIBILITY setting in glogin.sql. Therefore the default compatibility is 10.2.
The following table shows the release of SQL*Plus which introduced the behavior change, and hence the minimum value of SQLPLUSCOMPATIBILITY to obtain that behavior. For example, to obtain the earlier behavior of the VARIABLE command, you must either use a version of SQL*Plus earlier than 9.0.1, or you must use a SQLPLUSCOMPATIBILITY value of less than 9.0.1. The lowest value that can be set for SQLPLUSCOMPATIBILITY is 7.3.4
Table 12-4 Compatibility Matrix