SQL*Plus User's Guide and Reference
Release 8.1.5

A66736-01

Library

Product

Contents

Index

Prev Up Next

Command Reference, 40 of 52


SET

Purpose

Sets a system variable to alter the SQL*Plus environment for your current session, such as

Syntax

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}

Terms and Clauses

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.


      Note:

      To use this feature, you must have access to the DBMS_APPLICATION_INFO package. Run DBMSUTIL.SQL (this name may vary depending on your operating system) as SYS to create the DBMS_APPLICATION_INFO package. DBMSUTIL.SQL is part of the Oracle8 database server product. 


      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

    • n INSERT, UPDATE or DELETE commands or PL/SQL blocks

    • a commit

    • a rollback

    • a SET AUTOCOMMIT command


      Note:

      For this feature, a PL/SQL block is considered one transaction, regardless of the actual number of SQL commands contained within it. 


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}

      Sets the non-alphanumeric character used to end PL/SQL blocks to c. To execute the block, you must issue a RUN or / (slash) command.

CMDS[EP] {;|c|OFF|ON}

      Sets the non-alphanumeric character used to separate multiple SQL*Plus commands entered on one line to c. ON or OFF controls whether you can enter multiple commands on a line; ON automatically sets the command separator character to a semicolon (;).

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}

      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.

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}

      Controls whether the START command lists each command in a command file as the command is executed. ON lists the commands; OFF suppresses the listing.

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}

      Controls where on a page each report begins. OFF forces each report to start at the top of a new page. ON allows 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.

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}

      Displays the number of records returned by a query when a query 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.

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}

      Controls printing of column headings in reports. ON prints column headings in reports; OFF suppresses column headings.

      The SET HEADING OFF command will not affect the column width displayed, and only suppresses the printing of the column header itself.

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}

      Sets the starting position from which CLOB and NCLOB data is retrieved and displayed.

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. Using the SET LOGSOURCE command without a pathname restores the default location.

LONG {80|n}

      Sets maximum width (in bytes) for displaying LONG, CLOB and NCLOB values; and for copying LONG values. The maximum value of n is 2 gigabytes.

LONGC[HUNKSIZE] {80|n}

      Sets the size (in bytes) of the increments in which SQL*Plus retrieves a LONG, CLOB or NCLOB value.

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

      Sets the text that represents a null value 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.

NUMF[ORMAT] format

      Sets the default format for displaying numbers. Enter a number format for format. For number format descriptions, see the FORMAT clause of the COLUMN command in this chapter.

NUM[WIDTH] {10|n}

      Sets the default width for displaying numbers. For number format descriptions, see the FORMAT clause of the COLUMN command in this chapter.

PAGES[IZE] {24|n}

      Sets the number of lines in each page. You can set PAGESIZE to zero to suppress all headings, page breaks, titles, the initial blank line, and other formatting information.

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}

      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.

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:

    • uppercase if SQLCASE equals UPPER

    • lowercase if SQLCASE equals LOWER

    • unchanged if SQLCASE equals MIXED

      SQLCASE does not change the SQL buffer itself.

    SQLCO[NTINUE] {> |text}

        Sets the character sequence SQL*Plus displays as a prompt after you continue a SQL*Plus command on an additional line using a hyphen (-).

    SQLN[UMBER] {OFF|ON}

        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.

    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}

        Sets the SQL*Plus command prompt.

    SQLT[ERMINATOR] {;|c|OFF|ON}

        Sets the character used to end and execute SQL commands to c. OFF means that SQL*Plus recognizes no command terminator; you terminate a SQL command by entering an empty line. ON resets the terminator to the default semicolon (;).

    SUF[FIX] {SQL|text}

        Sets the default file extension that SQL*Plus uses in commands that refer to command files. SUFFIX does not control extensions for spool files.

    TAB {OFF|ON}

        Determines how SQL*Plus formats white space in terminal output. OFF uses spaces to format white space in the output. ON uses the TAB character. TAB settings are every eight characters. The default value for TAB is system dependent.

    TERM[OUT] {OFF|ON}

        Controls the display of output generated by commands executed from a command file. OFF suppresses the display so that you can spool output from a command file without seeing the output on the screen. ON displays the output. TERMOUT OFF does not affect output from commands you enter interactively.

    TI[ME] {OFF|ON}

        Controls the display of the current time. ON displays the current time before each command prompt. OFF suppresses the time display.

    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}

        Determines whether SQL*Plus allows trailing blanks at the end of each spooled line. ON removes blanks at the end of each line. OFF allows SQL*Plus to include trailing blanks. TRIMSPOOL ON does not affect terminal output.

    UND[ERLINE] {-|c|ON|OFF}

        Sets the character used to underline column headings in SQL*Plus reports to c. Note, c cannot be an alphanumeric character or a white space. ON or OFF turns underlining on or off. ON changes the value of c back to the default "-".

    VER[IFY] {OFF|ON}

        Controls whether SQL*Plus lists the text of a SQL statement or PL/SQL command before and after SQL*Plus replaces substitution variables with values. ON lists the text; OFF suppresses the listing.

    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.

    Usage Notes

    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.

    Examples

    The following examples show sample uses of selected SET command variables.

    APPINFO

    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
    
    AUTORECOVERY

    To set the recovery mode to AUTOMATIC, enter

    SQL> SET AUTORECOVERY ON
    SQL> RECOVER DATABASE
    
    CMDSEP

    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
    
    COLSEP

    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
    
    COMPATIBILITY

    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.

    DESCRIBE

    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)
    
    ESCAPE

    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:
    
    HEADING

    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
    
    
    INSTANCE

    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
    
    LOBOFFSET

    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.

    LOGSOURCE

    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
    
    LONG

    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.

    LONGCHUNKSIZE

    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.

    SERVEROUTPUT

    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
    
    SHIFTINOUT

    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.  


    SQLBLANKLINES

    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
    
    
    SQLCONTINUE

    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>
    
    SUFFIX

    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.


Prev Up Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index