Release 8.1.5
A66736-01
 Library |
 Product |
 Contents |
 Index |
| SQL*Plus User's Guide and Reference Release 8.1.5 A66736-01 |
|
Command Reference, 40 of 52
Sets a system variable to alter the SQL*Plus environment for your current session, such as
SET system_variable value
where system_variable value represents a system variable followed by a value, as shown below:
APPI[NFO]{ON|OFF|text} ARRAY[SIZE] {15|n} AUTO[COMMIT] {OFF|ON|IMM[EDIATE]|n} AUTOP[RINT] {OFF|ON} AUTORECOVERY {ON|OFF] AUTOT[RACE] {OFF|ON|TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]] BLO[CKTERMINATOR] {.|c} CMDS[EP] {;|c|OFF|ON} COLSEP {_|text} COM[PATIBILITY] {V7|V8|NATIVE} CON[CAT] {.|c|OFF|ON} COPYC[OMMIT] {0|n} COPYTYPECHECK {OFF|ON} DEF[INE] {'&'|c|OFF|ON} DESCRIBE [DEPTH {1|n|ALL}][LINENUM {ON|OFF}][INDENT {ON|OFF}] ECHO {OFF|ON} EDITF[ILE] file_name[.ext] EMB[EDDED] {OFF|ON} ESC[APE] {\|c|OFF|ON} FEED[BACK] {6|n|OFF|ON} FLAGGER {OFF|ENTRY|INTERMED[IATE]|FULL} FLU[SH] {OFF|ON} HEA[DING] {OFF|ON} HEADS[EP] {||c|OFF|ON} INSTANCE [instance_path|LOCAL] LIN[ESIZE] {80|n} LOBOF[FSET] {n|1} LOGSOURCE [pathname] LONG {80|n} LONGC[HUNKSIZE] {80|n} NEWP[AGE] {1|n|NONE} NULL text NUMF[ORMAT] format NUM[WIDTH] {10|n} PAGES[IZE] {24|n} PAU[SE] {OFF|ON|text} RECSEP {WR[APPED]|EA[CH]|OFF} RECSEPCHAR {_|c} SERVEROUT[PUT] {OFF|ON} [SIZE n] [FOR[MAT] {WRA[PPED]| WOR[D_WRAPPED]|TRU[NCATED]}] SHIFT[INOUT] {VIS[IBLE]|INV[ISIBLE]} SHOW[MODE] {OFF|ON} SQLBL[ANKLINES] {ON|OFF} SQLC[ASE] {MIX[ED]|LO[WER]|UP[PER]} SQLCO[NTINUE] {> |text} SQLN[UMBER] {OFF|ON} SQLPRE[FIX] {#|c} SQLP[ROMPT] {SQL>|text} SQLT[ERMINATOR] {;|c|OFF|ON} SUF[FIX] {SQL|text} TAB {OFF|ON} TERM[OUT] {OFF|ON} TI[ME] {OFF|ON} TIMI[NG] {OFF|ON} TRIM[OUT] {OFF|ON} TRIMS[POOL] {ON|OFF} UND[ERLINE] {-|c|ON|OFF} VER[IFY] {OFF|ON} WRA[P] {OFF|ON}
Refer to the following list for a description of each term, clause, or system variable:
APPI[NFO]{ON|OFF|text}
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. 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 Oracle8i Tuning manual.
ARRAY[SIZE] {15|n}
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]{OFF|ON|IMM[EDIATE]|n}
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
AUTOP[RINT] {OFF|ON}
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]
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.
OFF, the default option, requires that you enter the filenames manually or accept the suggested default filename given.
AUTOT[RACE] {OFF|ON|TRACE[ONLY]} [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.
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.
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 Oracle8i Tuning 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.
See "Tracing Statements" in Chapter 3 for more information on AUTOTRACE.
BLO[CKTERMINATOR] {.|c}
CMDS[EP] {;|c|OFF|ON}
COLSEP { |text}
Sets the text to be printed between SELECTed columns. 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.
In multi-line rows, the column separator does not print between columns that begin on different lines. The column separator does not appear on blank lines produced by BREAK ... SKIP n and does not overwrite the record separator. See SET RECSEP in this chapter for more information.
COM[PATIBILITY]{V7|V8|NATIVE}
Specifies the version of Oracle to which you are currently connected. Set COMPATIBILITY to V7 for Oracle7, or V8 for Oracle8 and Oracle8i. Set COMPATIBILITY to NATIVE if you wish the database to determine the setting (for example, if connected to Oracle8 or Oracle8i, compatibility would default to V8). COMPATIBILITY must be correctly set for the version of Oracle to which you are connected; otherwise, you will be unable to run any SQL commands. Note that you can set COMPATIBILITY to V7 when connected to Oracle8i. This enables you to run Oracle7 SQL against Oracle8i.
CON[CAT] {.|c|OFF|ON}
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}
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 {OFF|ON}
DEF[INE] {&|c|OFF|ON}
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. For more information on the SCAN variable, see the SET SCAN command in Appendix F.
DESCRIBE [DEPTH {1|n|ALL}][LINENUM {ON|OFF}][INDENT {ON|OFF}]
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 the DESCRIBE command earlier in this chapter.
ECHO {OFF|ON}
EDITF[ILE] file_name[.ext]
Sets the default filename for the EDIT command. For more information about the EDIT command, see EDIT in this chapter.
You can include a path and/or file extension. For information on changing the default extension, see the SUFFIX variable of this command. The default filename and maximum filename length are operating system specific.
EMB[EDDED] {OFF|ON}
ESC[APE] {\|c|OFF|ON}
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|OFF|ON}
FLAGGER {OFF|ENTRY |INTERMED[IATE]|FULL}
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.
FLU[SH] {OFF|ON}
Controls when output is sent to the user's display device. OFF allows the host operating system to buffer output. ON disables buffering.
Use OFF only when you run a command file non-interactively (that is, when you do not need to see output and/or prompts until the command file finishes running). The use of FLUSH OFF may improve performance by reducing the amount of program I/O.
HEA[DING] {OFF|ON}
HEADS[EP] {||c|OFF|ON}
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]
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 Net8 is running. You can use any valid Net8 connect string as the specified instance path. See your operating system-specific Oracle documentation for a complete description of how your operating system specifies Net8 connect strings. The maximum length of the instance path is 64 characters.
LIN[ESIZE] {80|n}
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}
LOGSOURCE [pathname]
LONG {80|n}
LONGC[HUNKSIZE] {80|n}
NEWP[AGE] {1|n|NONE}
Sets the number of blank lines to be printed from the top of each page to the top title. A value of zero places a formfeed at the beginning of each page (including the first page) and clears the screen on most terminals. If you set NEWPAGE to NONE, SQL*Plus does not print a blank line or formfeed between the report pages.
NULL text
NUMF[ORMAT] format
NUM[WIDTH] {10|n}
PAGES[IZE] {24|n}
PAU[SE] {OFF|ON|text}
Allows you to control scrolling of your terminal when running reports. ON causes SQL*Plus to pause at the beginning of each page of report output. You must press [Return] after each pause. The text you enter specifies the text to be displayed each time SQL*Plus pauses. If you enter multiple words, you must enclose text in single quotes.
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.
RECSEP {WR[APPED]|EA[CH]|OFF}
RECSEPCHAR { |c}
Display or print record separators. A record separator consists of a single line of the RECSEPCHAR (record separating character) repeated LINESIZE times.
RECSEPCHAR defines the record separating character. A single space is the default.
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] {OFF|ON} [SIZE n] [FOR[MAT]
{WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]}]
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 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 Oracle8i Supplied Packages Reference.
SHIFT[INOUT] {VIS[IBLE]|INV[ISIBLE]}
Allows 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.
SHOW[MODE] {OFF|ON}
SQLBL[ANKLINES] {ON|OFF}
Controls whether SQL*Plus allows blank lines within a SQL command. ON interprets blank lines and new lines as part of a SQL command. OFF, the default value, does not allow blank lines or new lines in a SQL command. SQL*Plus returns to the default behavior when a SQLTERMINTATOR or BLOCKTERMINATOR is encountered.
SQLC[ASE] {MIX[ED]|LO[WER]|UP[PER]}
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, as follows:
SQLCASE does not change the SQL buffer itself.
SQLCO[NTINUE] {> |text}
SQLN[UMBER] {OFF|ON}
SQLPRE[FIX] {#|c}
Sets the SQL*Plus prefix character. While you are entering a SQL command or PL/SQL block, you can enter a SQL*Plus command on a separate line, prefixed by the SQL*Plus prefix character. SQL*Plus will execute the command immediately without affecting the SQL command or PL/SQL block that you are entering. The prefix character must be a non-alphanumeric character.
SQLP[ROMPT] {SQL>|text}
SQLT[ERMINATOR] {;|c|OFF|ON}
SUF[FIX] {SQL|text}
TAB {OFF|ON}
TERM[OUT] {OFF|ON}
TI[ME] {OFF|ON}
TIMI[NG] {OFF|ON}
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.
TRIM[OUT] {OFF|ON}
Determines whether SQL*Plus allows trailing blanks at the end of each displayed line. ON removes blanks at the end of each line, improving performance especially when you access SQL*Plus from a slow communications device. OFF allows SQL*Plus to display trailing blanks. TRIMOUT ON does not affect spooled output.
TRIMS[POOL] {ON|OFF}
UND[ERLINE] {-|c|ON|OFF}
VER[IFY] {OFF|ON}
WRA[P] {OFF|ON}
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 allow you to establish 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 Oracle8i 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, enter
SQL> SHOW APPINFO SQL> appinfo is ON and set to "SQL*Plus"
To change the default text, enter
SQL> SET APPI 'This is SQL*Plus' SQL> SHOW APPINFO SQL> appinfo is ON and set to "This is SQL*Plus"
To make sure that registration has taken place, enter
SQL> VARIABLE MOD VARCHAR2(50) SQL> VARIABLE ACT VARCHAR2(40) SQL> EXECUTE DBMS_APPLICATION_INFO.READ_MODULE(:MOD, :ACT); SQL> PRINT MOD MOD --------------------------------------------------- This is SQL*Plus
To set the recovery mode to AUTOMATIC, enter
SQL> SET AUTORECOVERY ON SQL> RECOVER DATABASE
To specify a TTITLE and format a column on the same line, enter
SQL> SET CMDSEP + SQL> TTITLE LEFT 'SALARIES' + COLUMN SAL FORMAT $9,999 SQL> SELECT ENAME, SAL FROM EMP 2 WHERE JOB = 'CLERK';
The following output results:
SALARIES ENAME SAL ---------- ------- SMITH $800 ADAMS $1,100 JAMES $950 MILLER $1,300
To set the column separator to "|" enter
SQL> SET COLSEP '|' SQL> SELECT ENAME, JOB, DEPTNO 2 FROM EMP 3 WHERE DEPTNO = 20;
The following output results:
ENAME |JOB | DEPTNO ------------------------------- SMITH |CLERK | 20 JONES |MANAGER | 20 SCOTT |ANALYST | 20 ADAMS |CLERK | 20 FORD |ANALYST | 20
To run a command file, SALARY.SQL, created with Oracle7, enter
SQL> SET COMPATIBILITY V7 SQL> START SALARY
After running the file, reset compatibility to V8 to run command files created with Oracle8:
SQL> SET COMPATIBILITY V8
Alternatively, you can add the command SET COMPATIBILITY V7 to the beginning of the command file, and reset COMPATIBILITY to V8 at the end of the file.
To describe the object emp_object to a depth of two levels, and indent the output while also displaying line numbers, first describe the object as follows:
SQL> desc emp_object
The following output results:
Name Null Type ----------------------------------------- -------- ---------------------------- EMPLOYEE RECUR_PERSON NAME VARCHAR2(20) ADDR RECUR_ADDRESS ADDR1 RECUR_ADDRESS1 DOB DATE GENDER VARCHAR2(10) DEPT RECUR_DEPARTMENT DEPTNO NUMBER DEPT_NAME VARCHAR2(20) LOCATION VARCHAR2(20) START_DATE DATE POSITION VARCHAR2(1) SAL RECUR_SALARY ANNUAL_SAL NUMBER(10,2) EMP_TYPE VARCHAR2(1) COMM NUMBER(10,2) PENALTY_RATE NUMBER(5,2)
To format emp_object so that the output displays with indentation and line numbers, use the SET DESCRIBE command as follows:
SQL> SET DESCRIBE DEPTH 2 LINENUM ON INDENT ON
To display the above settings, enter
SQL> DESCRIBE emp_object
The following output results:
Name Null Type ------------------------------ -------- ---------------------------- 1 EMPLOYEE RECUR_PERSON 2 1 NAME VARCHAR2(20) 3 1 ADDR RECUR_ADDRESS 4 1 ADDR1 RECUR_ADDRESS1 5 1 DOB DATE 6 1 GENDER VARCHAR2(10) 7 DEPT RECUR_DEPARTMENT 8 7 DEPTNO NUMBER 9 7 DEPT_NAME VARCHAR2(20) 10 7 LOCATION VARCHAR2(20) 11 START_DATE DATE 12 POSITION VARCHAR2(1) 13 SAL RECUR_SALARY 14 13 ANNUAL_SAL NUMBER(10,2) 15 13 EMP_TYPE VARCHAR2(1) 16 13 COMM NUMBER(10,2) 17 13 PENALTY_RATE NUMBER(5,2)
If you define the escape character as an exclamation point (!), then
SQL> SET ESCAPE ! SQL> ACCEPT v1 PROMPT 'Enter !&1:'
displays this prompt:
Enter &1:
To suppress the display of column headings in a report, enter
SQL> SET HEADING OFF
If you then run a SQL SELECT command,
SQL> SELECT ENAME, SAL FROM EMP 2 WHERE JOB = 'CLERK';
the following output results:
ADAMS 1100 JAMES 950 MILLER 1300
To set the default instance to "PROD1" enter
SQL> SET INSTANCE PROD1
To set the instance back to the default or local, enter
SQL> SET INSTANCE local
To set the starting position from which a CLOB column's data is retrieved to the 22nd position, enter
SQL> 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/oracle81/dbs/arch" enter
SQL> SET LOGSOURCE "/usr/oracle81/dbs/arch" SQL> RECOVER DATABASE
To set the maximum width for displaying and copying LONG values to 500, enter
SQL> SET LONG 500
The LONG data will wrap on your screen; SQL*Plus will not truncate until the 501st character.
To set the size of the increments in which SQL*Plus retrieves LONG values to 100 characters, enter
SQL> 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.
To enable the display of DBMS_OUTPUT.PUT_LINE, enter
SQL> SET SERVEROUTPUT ON
The following example shows what happens when you execute an anonymous procedure with SET SERVEROUTPUT ON:
SQL> BEGIN 2 DBMS_OUTPUT.PUT_LINE('Task is complete'); 3 END; 4 / Task is complete. PL/SQL procedure successfully completed.
The following example shows what happens when you create a trigger with SET SERVEROUTPUT ON:
SQL> CREATE TRIGGER SERVER_TRIG BEFORE INSERT OR UPDATE - > OR DELETE 2 ON SERVER_TAB 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE('Task is complete.'); 5 END; 6 / Trigger created. SQL> INSERT INTO SERVER_TAB VALUES ('TEXT'); Task is complete. 1 row created.
To set the output to WORD_WRAPPED, enter
SQL> SET SERVEROUTPUT ON FORMAT WORD_WRAPPED SQL> SET LINESIZE 20 SQL> BEGIN 2 DBMS_OUTPUT.PUT_LINE('If there is nothing left to do'); 3 DBMS_OUTPUT.PUT_LINE('shall we continue with plan B?'); 4 end; 5 / If there is nothing left to do shall we continue with plan B?
To set the output to TRUNCATED, enter
SQL> SET SERVEROUTPUT ON FORMAT TRUNCATED SQL> SET LINESIZE 20 SQL> BEGIN 2 DBMS_OUTPUT.PUT_LINE('If there is nothing left to do'); 3 DBMS_OUTPUT.PUT_LINE('shall we continue with plan B?'); 4 END; 5 / If there is nothing shall we continue wi
To enable the display of shift characters, enter
SQL> SET SHIFTINOUT VISIBLE SQL> SELECT ENAME, JOB FROM EMP;
The following output results:
ENAME JOB ---------- ---------- :JJOO: :AABBCC: :AA:abc :DDEE:e
where ":" = shift character
uppercase = multibyte character
lowercase = singlebyte character
|
Note: This example illustrates that the columns are aligned correctly. The data used in this example is an illustration only and does not represent real data. |
To preserve blank lines in a SQL statement, enter
SQL> SET SQLBLANKLINES ON SQL> SELECT * 2 3 FROM 4 5 DUAL 6 7 ;
The following output results:
D - X
To set the SQL*Plus command continuation prompt to an exclamation point followed by a space, enter
SQL> SET SQLCONTINUE '! '
SQL*Plus will prompt for continuation as follows:
SQL> TTITLE 'YEARLY INCOME' - ! RIGHT SQL.PNO SKIP 2 - ! CENTER 'PC DIVISION' SQL>
To set the default command-file extension to UFI, enter
SQL> SET SUFFIX UFI
If you then enter
SQL> GET EXAMPLE
SQL*Plus will look for a file named EXAMPLE with an extension of UFI instead of EXAMPLE with an extension of SQL.
|
 Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|