iSQL*Plus User's Guide and Reference Release 9.0.1 Part Number A88826-01 |
|
Command Reference, 28 of 38
SET system_variable value
where system_variable and value represent one of the following clauses.
APPI[NFO]{ON|OFF|text}
ARRAY[SIZE] {15|n}
AUTO[COMMIT]{ON|OFF|IMM[EDIATE]|n}
AUTOP[RINT] {ON|OFF}
AUTORECOVERY [ON|OFF]
AUTOT[RACE] {ON|OFF|TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
BLO[CKTERMINATOR] {.|c}
CMDS[EP] {;|c|ON|OFF}
COM[PATIBILITY]{V7|V8|NATIVE}
CON[CAT] {.|c|ON|OFF}
COPYC[OMMIT] {0|n}
COPYTYPECHECK {ON|OFF}
DEF[INE] {&|c|ON|OFF}
DESCRIBE [DEPTH {1|n|ALL}][LINENUM {ON|OFF}][INDENT {ON|OFF}]
ECHO {ON|OFF}
EMB[EDDED] {ON|OFF}
ESC[APE] {\|c|ON|OFF}
FEED[BACK] {6|n|ON|OFF}
FLAGGER {OFF|ENTRY |INTERMED[IATE]|FULL}
HEA[DING] {ON|OFF}
HEADS[EP] {||c|ON|OFF}
INSTANCE [instance_path|LOCAL]
LIN[ESIZE] {80|n}
LOBOF[FSET] {n|1}
LOGSOURCE [pathname]
LONG {80|n}
LONGC[HUNKSIZE] {80|n}
MARK[UP] HTML [HEAD text] [BODY text] [TABLE text] [ENTMAP {ON|OFF}] [PRE[FORMAT] {ON|OFF}]
NULL text
NUMF[ORMAT] format
NUM[WIDTH] {10|n}
PAGES[IZE] {24|n}
RECSEP {WR[APPED]|EA[CH]|OFF}
RECSEPCHAR {_|c}
SERVEROUT[PUT] {ON|OFF} [SIZE n] [FOR[MAT] {WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]}]
SQLC[ASE] {MIX[ED]|LO[WER]|UP[PER]}
SQLPLUSCOMPAT[IBILITY] {x.y[.z]}
SQLT[ERMINATOR] {;|c|ON|OFF}
TIMI[NG] {ON|OFF}
UND[ERLINE] {-|c|ON|OFF}
VER[IFY] {ON|OFF}
WRA[P] {ON|OFF}
Sets a system variable to alter the SQL*Plus environment settings for your current session, for example:
Refer to the following list for a description of each term, clause, or system variable:
APPI[NFO]{ON|OFF|text} | Usage | Examples | Top
Sets automatic registering of command files through the DBMS_APPLICATION_INFO package. This enables the performance and resource usage of each command file to be monitored by your DBA. The registered name appears in the MODULE column of the V$SESSION and V$SQLAREA virtual tables. You can also read the registered name using the DBMS_APPLICATION_INFO.READ_MODULE procedure.
ON registers command files invoked by the @, @@ or START commands. OFF disables registering of command files. Instead, the current value of text is registered. Text specifies the text to register when no command file is being run or when APPINFO is OFF, which is the default. The default for text is "SQL*Plus". If you enter multiple words for text, you must enclose them in quotes. The maximum length for text is limited by the DBMS_APPLICATION_INFO package.
The registered name has the format nn@xfilename where: nn is the depth level of command file; x is '<' when the command file name is truncated, otherwise, it is blank; and filename is the command file name, possibly truncated to the length allowed by the DBMS_APPLICATION_INFO package interface.
For more information on the DBMS_APPLICATION_INFO package, see the Oracle9i Performance Guide and Reference manual.
ARRAY[SIZE] {15|n} | Usage | Examples | Top
Sets the number of rows--called a batch--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.
AUTO[COMMIT]{ON|OFF|IMM[EDIATE]|n} | Usage | Examples | Top
Controls when Oracle commits pending changes to the database. ON commits pending changes to the database after Oracle executes each successful INSERT, UPDATE, or DELETE command 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 the ON option. n commits pending changes to the database after Oracle 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.
AUTOP[RINT] {ON|OFF} | Usage | Examples | Top
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). For more information about displaying bind variables, see the PRINT command in this chapter.
AUTORECOVERY [ON|OFF] | Usage | Examples | Top
ON sets the RECOVER command to automatically apply the default filenames of archived redo log files needed during recovery. No interaction is needed when AUTORECOVERY is set to ON, provided the necessary files are in the expected locations with the expected names. The filenames used when AUTORECOVERY is ON are derived from the values of the initialization parameters LOG_ARCHIVE_DEST and LOG_ARCHIVE_FORMAT.
As iSQL*Plus cannot handle interactive commands, you must set AUTORECOVERY ON to use the RECOVER command. Attempting to RECOVER a database with AUTORECOVERY OFF raises the error:
SP2-0872 SET AUTORECOVERY ON must be used in iSQL*Plus
AUTOT[RACE] {ON|OFF|TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]] | Usage | Examples | Top
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.
OFF does not display a trace report. ON displays a trace report. TRACEONLY displays a trace report, but does not print query data, if any. EXPLAIN shows the query execution path by performing an EXPLAIN PLAN. STATISTICS displays SQL statement statistics. Information about EXPLAIN PLAN is documented in the Oracle9i SQL Reference manual.
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.
Information about Execution Plans and the statistics is documented in the Oracle9i Performance Guide and Reference manual.
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.
AUTOTRACE is not available when FIPS flagging is enabled.
BLO[CKTERMINATOR] {.|c} | Usage | Examples | Top
CMDS[EP] {;|c|ON|OFF} | Usage | Examples | Top
COM[PATIBILITY]{V7|V8|NATIVE} | Usage | Examples | Top
Specifies the version of Oracle to which you are currently connected. Set COMPATIBILITY to V7 for Oracle7, or to V8 for Oracle8 or later. Set COMPATIBILITY to NATIVE if you wish the database to determine the setting (If connected to Oracle9i, compatibility defaults to NATIVE). COMPATIBILITY should be correctly set for the version of Oracle to which you are connected; otherwise, you may be unable to run any SQL commands.
CON[CAT] {.|c|ON|OFF} | Usage | Examples | Top
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. SQL*Plus resets the value of CONCAT to a period when you switch CONCAT on.
COPYC[OMMIT] {0|n} | Usage | Examples | Top
Controls the number of batches 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.
COPYTYPECHECK {ON|OFF} | Usage | Examples | Top
DEF[INE] {&|c|ON|OFF} | Usage | Examples | Top
Sets the character used to prefix substitution variables to c. 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.
DESCRIBE [DEPTH {1|n|ALL}][LINENUM {ON|OFF}][INDENT {ON|OFF}] | Usage | Examples | Top
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.
For more information about describing objects, see DESCRIBE earlier in this chapter.
ECHO {ON|OFF} | Usage | Examples | Top
EMB[EDDED] {ON|OFF} | Usage | Examples | Top
ESC[APE] {\|c|ON|OFF} | Usage | Examples | Top
Defines the character you enter 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.
FEED[BACK] {6|n|ON|OFF} | Usage | Examples | Top
FLAGGER {OFF|ENTRY |INTERMED[IATE]|FULL} | Usage | Examples | Top
Checks to make sure that SQL statements conform to the ANSI/ISO SQL92 standard. If any non-standard constructs are found, the Oracle 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.
HEA[DING] {ON|OFF} | Usage | Examples | Top
HEADS[EP] {||c|ON|OFF} | Usage | Examples | Top
Defines the character you enter as the heading separator character. 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 "|".
INSTANCE [instance_path|LOCAL] | Usage | Examples | Top
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. See your operating system-specific Oracle documentation for a description of how to set the initial default instance.
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 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.
LIN[ESIZE] {80|n} | Usage | Examples | Top
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. You can define LINESIZE as a value from 1 to a maximum that is system dependent. Refer to the Oracle installation and user's manual(s) provided for your operating system.
LOBOF[FSET] {n|1} | Usage | Examples | Top
LOGSOURCE [pathname] | Usage | Examples | Top
LONG {80|n} | Usage | Examples | Top
LONGC[HUNKSIZE] {80|n} | Usage | Examples | Top
MARK[UP] HTML [HEAD text] [BODY text] [TABLE text] [ENTMAP {ON|OFF}]
[PRE[FORMAT] {ON|OFF}] | Usage | Examples | Top
Outputs HTML marked up text, which is the output used by iSQL*Plus. Beware of using options which generate invalid HTML output in iSQL*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.
Use the SHOW MARKUP command to view the status of MARKUP options.
NULL text | Usage | Examples | Top
NUMF[ORMAT] format | Usage | Examples | Top
NUM[WIDTH] {10|n} | Usage | Examples | Top
PAGES[IZE] {24|n} | Usage | Examples | Top
RECSEPCHAR {_|c} | Usage | Examples | Top
RECSEP {WR[APPED]|EA[CH]|OFF} | Usage | Examples | Top
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.
SERVEROUT[PUT] {ON|OFF} [SIZE n] [FOR[MAT] {WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]}] | Usage | Examples | Top
Controls whether to display the output (that is, DBMS_OUTPUT.PUT_LINE) of stored procedures or PL/SQL blocks in SQL*Plus. 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 Oracle8i or Oracle9i database server. The default for n is 2000. n cannot be less than 2000 or greater than 1,000,000.
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.
For each FORMAT, every server output line begins on a new output line.
For more information on DBMS_OUTPUT.PUT_LINE, see your Oracle9i Application Developer's Guide - Fundamentals.
SQLC[ASE] {MIX[ED]|LO[WER]|UP[PER]} | Usage | Examples | Top
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.
SQLPLUSCOMPAT[IBILITY] {x.y[.z]} | Usage | Examples | Top
Sets the behavior or output format of VARIABLE to that of the release or version specified by x.y[.z]. Where x is the version number, y is the release number, and z is the update number. For example, 8.1, 8.1.7 or 9.0.0. In later releases, SQLPLUSCOMPATIBILITY may affect features other than VARIABLE.
Setting the value of SQLPLUSCOMPATIBILITY to a version less than 9.0.0 will result in VARIABLE definition of NCHAR or NVARCHAR2 datatypes to revert to Oracle8i behavior whereby the size of the variable is in bytes or characters depending on the chosen national character set.
The default glogin.sql file contains SET SQLPLUSCOMPAT 8.1.7. It is recommended that you add SET SQLPLUSCOMPAT 9.0.0 to your scripts to maximize their compatibility with future versions of SQL*Plus.
SQLT[ERMINATOR] {;|c|ON|OFF} | Usage | Examples | Top
Sets the character used to end and execute SQL commands to c. It cannot be an alphanumeric character or a whitespace. OFF means that SQL*Plus recognizes no command terminator; you terminate a SQL command by entering an empty line. If SQLBLANKLINES is set ON, you must use the BLOCKTERMINATOR to terminate a SQL command. ON resets the terminator to the default semicolon (;).
TIMI[NG] {ON|OFF} | Usage | Examples | Top
Controls the display of timing statistics. ON displays timing statistics on each SQL command or PL/SQL block run. OFF suppresses timing of each command. For information about the data SET TIMING ON displays, see the Oracle installation and user's manual(s) provided for your operating system. Refer to the TIMING command for information on timing multiple commands.
UND[ERLINE] {-|c|ON|OFF} | Usage | Examples | Top
VER[IFY] {ON|OFF} | Usage | Examples | Top
WRA[P] {ON|OFF} | Usage | Examples | Top
Controls whether SQL*Plus truncates the display of a SELECTed row if it is too long for the current line width. OFF truncates the SELECTed row; ON allows the SELECTed row to wrap to the next line.
Use the WRAPPED and TRUNCATED clauses of the COLUMN command to override the setting of WRAP for specific columns.
SQL*Plus maintains system variables (also called SET command variables) to enable you to setup a particular environment for a SQL*Plus session. You can change these system variables with the SET command and list them with the SHOW command.
SET ROLE and SET TRANSACTION are SQL commands (see the Oracle9i SQL Reference for more information). When not followed by the keywords TRANSACTION or ROLE, SET is assumed to be a SQL*Plus command.
The following examples show sample uses of selected SET command variables.
To display the setting of APPINFO, as it is SET OFF by default, enter
SET APPINFO ON SHOW APPINFO APPINFO is ON and set to "SQL*Plus"
To change the default text, enter
SET APPINFO 'This is SQL*Plus'
To make sure that registration has taken place, enter
VARIABLE MOD VARCHAR2(50) VARIABLE ACT VARCHAR2(40) EXECUTE DBMS_APPLICATION_INFO.READ_MODULE(:MOD, :ACT); PL/SQL procedure successfully completed. PRINT MOD MOD --------------------------------------------------- This is SQL*Plus
To change APPINFO back to its default setting, enter
SET APPI OFF
To set the recovery mode to AUTOMATIC, enter
SET AUTORECOVERY ON RECOVER DATABASE
To specify a title with TTITLE and format a column with COLUMN, both on the same line, enter
SET CMDSEP + TTITLE LEFT 'SALARIES' + COLUMN SALARY FORMAT $99,999 SELECT LAST_NAME, SALARY FROM EMP_DETAILS_VIEW WHERE JOB_ID = 'SH_CLERK'; SALARIES LAST_NAME SALARY ------------------------- -------- Taylor $3,200 Fleaur $3,100 Sullivan $2,500 Geoni $2,800 Sarchand $4,200 Bull $4,100 Dellinger $3,400 Cabrio $3,000 Chung $3,800 Dilly $3,600 Gates $2,900 Perkins $2,500 Bell $4,000 Everett $3,900 McCain $3,200 Jones $2,800 SALARIES LAST_NAME SALARY ------------------------- -------- Walsh $3,100 Feeney $3,000 OConnell $2,600 Grant $2,600 20 rows selected.
To run a command file, SALARY.SQL, created with Oracle7 SQL syntax, enter
SET COMPATIBILITY V7 START SALARY
After running the file, reset compatibility to NATIVE to run command files created for Oracle9i:
SET COMPATIBILITY NATIVE
Alternatively, you can add the command SET COMPATIBILITY V7 to the beginning of the command file, and reset COMPATIBILITY to NATIVE at the end of the file.
To describe the view EMP_DETAILS_VIEW to a depth of two levels, and indent the output while also displaying line numbers, first describe the view as follows:
DESCRIBE EMP_DETAILS_VIEW Name Null? Type ----------------------------------------- -------- ---------------------------- EMPLOYEE_ID NOT NULL NUMBER(6) JOB_ID NOT NULL VARCHAR2(10) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4) LOCATION_ID NUMBER(4) COUNTRY_ID CHAR(2) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) DEPARTMENT_NAME NOT NULL VARCHAR2(30) JOB_TITLE NOT NULL VARCHAR2(35) CITY NOT NULL VARCHAR2(30) STATE_PROVINCE VARCHAR2(25) COUNTRY_NAME VARCHAR2(40) REGION_NAME VARCHAR2(25)
To format EMP_DETAILS_VIEW so that the output displays with indentation and line numbers, use the SET DESCRIBE command as follows:
SET DESCRIBE DEPTH 2 LINENUM ON INDENT ON
To display the above settings, enter
DESCRIBE EMP_DETAILS_VIEW Name Null? Type ------------------- -------- -------------------- 1 EMPLOYEE_ID NOT NULL NUMBER(6) 2 JOB_ID NOT NULL VARCHAR2(10) 3 MANAGER_ID NUMBER(6) 4 DEPARTMENT_ID NUMBER(4) 5 LOCATION_ID NUMBER(4) 6 COUNTRY_ID CHAR(2) 7 FIRST_NAME VARCHAR2(20) 8 LAST_NAME NOT NULL VARCHAR2(25) 9 SALARY NUMBER(8,2) 10 COMMISSION_PCT NUMBER(2,2) 11 DEPARTMENT_NAME NOT NULL VARCHAR2(30) 12 JOB_TITLE NOT NULL VARCHAR2(35) 13 CITY NOT NULL VARCHAR2(30) 14 STATE_PROVINCE VARCHAR2(25) 15 COUNTRY_NAME VARCHAR2(40) 16 REGION_NAME VARCHAR2(25)
If you define the escape character as an exclamation point (!), then
SET ESCAPE ! ACCEPT v1 PROMPT 'Enter !&1:'
displays this prompt:
Enter &1:
To set the escape character back to the default value of \ (backslash), enter
SET ESCAPE ON
To suppress the display of column headings in a report, enter
SET HEADING OFF
If you then run a SQL SELECT command
SELECT LAST_NAME, SALARY FROM EMP_DETAILS_VIEW WHERE JOB_ID = 'AC_MGR';
the following output results:
Higgins 12000
To turn the display of column headings back on, enter
SET HEADING ON
To set the default instance to "PROD1" enter
DISCONNECT SET INSTANCE PROD1
To set the instance back to the default of local, enter
SET INSTANCE local
You must disconnect from any connected instances to change the instance.
To set the starting position from which a CLOB column's data is retrieved to the 22nd position, enter
SET LOBOFFSET 22
The CLOB data will wrap on your screen; SQL*Plus will not truncate until the 23rd character.
To set the default location of log files for recovery to the directory "/usr/oracle90/dbs/arch" enter
SET LOGSOURCE "/usr/oracle90/dbs/arch" RECOVER DATABASE
To set the maximum number of characters to fetch for displaying and copying LONG values, to 500, enter
SET LONG 500
The LONG data will wrap on your screen; SQL*Plus will not truncate until the 501st character. The default for LONG is 80 characters.
To set the size of the increments in which SQL*Plus retrieves LONG values to 100 characters, enter
SET LONGCHUNKSIZE 100
The LONG data will be retrieved in increments of 100 characters until the entire value is retrieved or the value of SET LONG is reached, whichever is the smaller.
To enable the display of text within a PL/SQL block using DBMS_OUTPUT.PUT_LINE, enter
SET SERVEROUTPUT ON
The following example shows what happens when you execute an anonymous procedure with SET SERVEROUTPUT ON:
BEGIN DBMS_OUTPUT.PUT_LINE('Task is complete'); END; / Task is complete. PL/SQL procedure successfully completed.
The following example shows what happens when you create a trigger with SET SERVEROUTPUT ON:
CREATE TRIGGER SERVER_TRIG BEFORE INSERT OR UPDATE - OR DELETE ON SERVER_TAB BEGIN DBMS_OUTPUT.PUT_LINE('Task is complete.'); END; / Trigger created. INSERT INTO SERVER_TAB VALUES ('TEXT'); Task is complete. 1 row created.
To set the output to WORD_WRAPPED, enter
SET SERVEROUTPUT ON FORMAT WORD_WRAPPED SET LINESIZE 20 BEGIN DBMS_OUTPUT.PUT_LINE('If there is nothing left to do'); DBMS_OUTPUT.PUT_LINE('shall we continue with plan B?'); END; / If there is nothing left to do shall we continue with plan B?
To set the output to TRUNCATED, enter
SET SERVEROUTPUT ON FORMAT TRUNCATED SET LINESIZE 20 BEGIN DBMS_OUTPUT.PUT_LINE('If there is nothing left to do'); DBMS_OUTPUT.PUT_LINE('shall we continue with plan B?'); END; / If there is nothing shall we continue wi
To set the SQL*Plus command continuation prompt to an exclamation point followed by a space, enter
SET SQLCONTINUE '! '
SQL*Plus will prompt for continuation as follows:
TTITLE 'MONTHLY INCOME' - ! RIGHT SQL.PNO SKIP 2 - ! CENTER 'PC DIVISION'
The default continuation prompt is "> ".
You need the Select Any Table privilege to successfully run the following example scripts.
To set the SQL*Plus command prompt to show your SID, enter
SET TERMOUT OFF COLUMN X NEW_VALUE Y SELECT RTRIM(INSTANCE, CHR(0)) X FROM V$THREAD; SQLPROMPT '&Y SQL>' SET TERMOUT ON
To set the SQL*Plus command prompt to show the current user, enter
SET TERMOUT OFF COLUMN D22 NEW_VALUE VAR SELECT USERNAME D22 FROM USER_USERS; SQLPROMPT '&&VAR>' SET TERMOUT ON
These settings are not dynamic. You need to reset them whenever you change instances, such as when you use the connect command to log on to another instance.
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|