Release 8.1.6
A75664-01
Library |
Product |
Contents |
Index |
| SQL*Plus
User's Guide and Reference
Release 8.1.6 A75664-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 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} COLSEP {_|text} 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} EDITF[ILE] file_name[.ext] EMB[EDDED] {ON|OFF} ESC[APE] {\|c|ON|OFF} FEED[BACK] {6|n|ON|OFF} FLAGGER {OFF|ENTRY |INTERMED[IATE]|FULL} FLU[SH] {ON|OFF} 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 [ON|OFF] [HEAD text] [BODY text] [ENTMAP {ON|OFF}] [SPOOL {ON|OFF}] [PRE[FORMAT] {ON|OFF}] NEWP[AGE] {1|n|NONE} NULL text NUMF[ORMAT] format NUM[WIDTH] {10|n} PAGES[IZE] {24|n} PAU[SE] {ON|OFF|text} RECSEP {WR[APPED]|EA[CH]|OFF} RECSEPCHAR {_|c} SERVEROUT[PUT] {ON|OFF} [SIZE n] [FOR[MAT] {WRA[PPED]|WOR[D_ WRAPPED]|TRU[NCATED]}] SHIFT[INOUT] {VIS[IBLE]|INV[ISIBLE]} SHOW[MODE] {ON|OFF} SQLBL[ANKLINES] {ON|OFF} SQLC[ASE] {MIX[ED]|LO[WER]|UP[PER]} SQLCO[NTINUE] {> |text} SQLN[UMBER] {ON|OFF} SQLPRE[FIX] {#|c} SQLP[ROMPT] {SQL>|text} SQLT[ERMINATOR] {;|c|ON|OFF} SUF[FIX] {SQL|text} TAB {ON|OFF} TERM[OUT] {ON|OFF} TI[ME] {ON|OFF} TIMI[NG] {ON|OFF} TRIM[OUT] {ON|OFF} TRIMS[POOL] {ON|OFF} UND[ERLINE] {-|c|ON|OFF} VER[IFY] {ON|OFF} WRA[P] {ON|OFF}
Refer to the following list for a description of each term, clause, or system variable:
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.
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.
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
|
Note: For this feature, a PL/SQL block is considered one transaction, regardless of the actual number of SQL commands contained within it. |
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.
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.
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 Oracle8i 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 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.
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.
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: You can set COMPATIBILITY to V7 when connected to Oracle8i. This enables you to run Oracle7 SQL against Oracle8i. |
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.
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.
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.
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.
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.
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.
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.
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.
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 "|".
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.
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.
Outputs HTML marked up text. SET MARKUP has the same options
and behavior as SQLPLUS -MARKUP and supports HTML 3.2.
The mandatory argument HTML specifies that the type of output
generated by MARKUP is HTML. The optional HTML arguments, ON and OFF, specify
whether or not to generate HTML output. SET MARKUP HTML ON generates HTML
output using the specified MARKUP options, or options set by previous SET
MARKUP HTML commands.
You can turn HTML output ON and OFF as required during a session. The default is OFF.
|
Note: SQL*Plus 8.1.6 implements the HTML <PRE> tag. More HTML tags may be implemented in future releases. |
You enable HTML output with the SPOOL ON option, and you
initiate the writing of the spool file with the SPOOL command. You can
specify <HEAD> tag contents and <BODY> attributes using the HEAD
and BODY options.
The HEAD text option allows you to specify content
for the <HEAD> tag. By default, text is '<TITLE>SQL*Plus Report</TITLE>'.
If you enter more than one word in text, it must be enclosed in
quotes. text must be valid HTML for the <HEAD> tag. SQL*Plus
does not verify this free text entry. It is your responsibility to ensure
that the text you enter is valid HTML.
The BODY text option allows you to specify attributes
for the <BODY> tag. By default, there are no attributes. If you enter
more than one word in text, it must be enclosed in quotes. text
must be valid HTML for the <HEAD> tag. SQL*Plus does not verify this
free text entry. It is your responsibility to ensure that the text you
enter is valid HTML.
SPOOL ON or OFF specifies whether or not SQL*Plus writes
HTML tags to the start and end of each file created by the SQL*Plus SPOOL
command.
You can turn SPOOL ON and OFF as required during a session. The default is OFF.
SQL*Plus writes several HTML tags to the spool file with default content, when you issue the SQL*Plus SPOOL command:
<HTML> <HEAD> <TITLE>SQL*Plus Report</TITLE> <META name="generator" content="SQL*Plus 8.1.6"> </HEAD> <BODY> <PRE>
When you issue the SQL*Plus SPOOL OFF command, SQL*Plus appends the following end tags and then closes the file:
</PRE></BODY></HTML>
ENTMAP ON or OFF specifies whether or not SQL*Plus displays
the special characters "<", ">" and "&" as the HTML entities <,
> and & respectively.
You can turn ENTMAP ON and OFF as required during a session. The default is ON. For example, with ENTMAP OFF, SQL*Plus screen output would be:
SQL> SELECT ENAME, EMPNO 2 FROM EMP 3 WHERE SAL <= 2000;
With ENTMAP ON, SQL*Plus screen output would be:
SQL> SELECT ENAME, EMPNO 2 FROM EMP 3 WHERE SAL <= 2000;
|
Note: ENTMAP only has affect when either the HTML option is set ON, or the SPOOL option is set ON. |
PREFORMAT ON or OFF specifies whether or not SQL*Plus writes
output inside the HTML <PRE> tag.
You can turn PREFORMAT ON and OFF as required during a session. The default is OFF.
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.
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.
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.
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 Application Developer's Guide.
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.
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.
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.
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.
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 (;).
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.
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.
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 Oracle8i:
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> DESCRIBE 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 number of characters to fetch 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,
whichever is the smaller.
To output HTML marked up text to a spool file, enter
SQL> SET MARKUP HTML ON HEAD '<TITLE>HTML output from SQL*Plus</TITLE>' BODY '<H1>Employee Report</H1> <P>This is an employee report generated with <BR><BLOCKQUOTE>select empno, ename, job <BR>from emp;</BLOCKQUOTE><BR> <H2>Monthly Employee Report</H2> <BLOCKQUOTE>' SPOOL ON PREFORMAT ON" SCOTT/TIGER SQL> SET MARKUP HTML ENTMAP OFF SQL> SPOOL employee.html SQL> SELECT EMPNO, ENAME, JOB 2 FROM EMP; EMPNO ENAME JOB --------------- ---------- --------- 7839 KING PRESIDENT 7698 BLAKE MANAGER 7782 CLARK MANAGER 7566 JONES MANAGER 4 rows selected. SQL> SPOOL OFF
The previous SQL*Plus session generates the following HTML output in the spool file employee.html:
<HTML> <HEAD> <TITLE>HTML output from SQL*Plus</TITLE> <META Name="generator" content="SQL*Plus 8.1.6"> </HEAD> <BODY <H1>Employee Report</H1><P>This is an employee report generated with<BR><BLOCKQUOTE>select empno, ename, job<BR>from emp; </BLOCKQUOTE><BR><H2>Monthly Employee Report</H2><BLOCKQUOTE> <PRE> EMPNO ENAME JOB ---------- ---------- --------- 7839 KING PRESIDENT 7698 BLAKE MANAGER 7782 CLARK MANAGER 7566 JONES MANAGER 4 rows selected. </PRE></BODY></HTML> SQL> SPOOL OFF
To enable the display of text within a PL/SQL block using 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
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> REM Using the SQLTERMINATOR (default is ";") SQL> REM Could have used the BLOCKTERMINATOR (default is ".") 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 change the default command-file extension from the default, .SQL 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. |
|