SQL*Plus User's Guide and Reference
Release 9.0.1
Part Number A88827-02
Go To Documentation Library
Home		 Go To Product List
Book List		 Go To Table Of Contents
Contents		 Go To Index
Index
Master Index
Feedback

Go to previous page Go to beginning of chapter Go to next page

Command Reference, 40 of 52

SET

Syntax

SET system_variable value

where system_variable and value represent one of the following clauses.

Sets a system variable to alter the SQL*Plus environment settings for your current session, for example:

Terms

Refer to the following list for a description of each term, clause, or system variable:

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] [TABLE 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}

RECSEPCHAR {_|c}

RECSEP {WR[APPED]|EA[CH]|OFF}

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}

SQLPLUSCOMPAT[IBILITY] {x.y[.z]}

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}

Usage

SQL*Plus maintains system variables (also called SET command variables) to enable you to setup a particular environment for a SQL*Plus session. You can change these system variables with the SET command and list them with the SHOW command.

SET ROLE and SET TRANSACTION are SQL commands (see the Oracle9i SQL Reference for more information). When not followed by the keywords TRANSACTION or ROLE, SET is assumed to be a SQL*Plus command.

Examples

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

APPINFO

To display the setting of APPINFO, as it is SET OFF by default, enter 

Keyboard icon
SET APPINFO ON
SHOW APPINFO

Screen icon
APPINFO is ON and set to "SQL*Plus"

To change the default text, enter 

Keyboard icon
SET APPINFO 'This is SQL*Plus'

To make sure that registration has taken place, enter 

Keyboard icon
VARIABLE MOD VARCHAR2(50)
VARIABLE ACT VARCHAR2(40)
EXECUTE DBMS_APPLICATION_INFO.READ_MODULE(:MOD, :ACT);
Screen icon
PL/SQL procedure successfully completed.

Keyboard icon
PRINT MOD
Screen icon
MOD
---------------------------------------------------
This is SQL*Plus

To change APPINFO back to its default setting, enter 

Keyboard icon
SET APPI OFF
AUTORECOVERY

To set the recovery mode to AUTOMATIC, enter 

Keyboard icon
SET AUTORECOVERY ON
RECOVER DATABASE
CMDSEP

To specify a title with TTITLE and format a column with COLUMN, both on the same line, enter 

Keyboard icon
SET CMDSEP +
TTITLE LEFT 'SALARIES' + COLUMN SALARY FORMAT $99,999
SELECT LAST_NAME, SALARY FROM EMP_DETAILS_VIEW
WHERE JOB_ID = 'SH_CLERK';

Screen icon
SALARIES
LAST_NAME                   SALARY
------------------------- --------
Taylor                      $3,200
Fleaur                      $3,100
Sullivan                    $2,500
Geoni                       $2,800
Sarchand                    $4,200
Bull                        $4,100
Dellinger                   $3,400
Cabrio                      $3,000
Chung                       $3,800
Dilly                       $3,600
Gates                       $2,900
Perkins                     $2,500
Bell                        $4,000
Everett                     $3,900
McCain                      $3,200
Jones                       $2,800

SALARIES
LAST_NAME                   SALARY
------------------------- --------
Walsh                       $3,100
Feeney                      $3,000
OConnell                    $2,600
Grant                       $2,600

20 rows selected.
COLSEP

To set the column separator to "|" enter 

Keyboard icon
SET COLSEP '|'
SELECT LAST_NAME, JOB_ID, DEPARTMENT_ID
FROM EMP_DETAILS_VIEW
WHERE DEPARTMENT_ID = 20;

Screen icon
LAST_NAME                |JOB_ID    |DEPARTMENT_ID
-------------------------|----------|-------------
Hartstein                |MK_MAN    |           20
Fay                      |MK_REP    |           20
COMPATIBILITY

To run a command file, SALARY.SQL, created with Oracle7 SQL syntax, enter 

Keyboard icon
SET COMPATIBILITY V7
START SALARY

After running the file, reset compatibility to NATIVE to run command files created for Oracle9i: 

Keyboard icon
SET COMPATIBILITY NATIVE

Alternatively, you can add the command SET COMPATIBILITY V7 to the beginning of the command file, and reset COMPATIBILITY to NATIVE at the end of the file.

DESCRIBE

To describe the view EMP_DETAILS_VIEW to a depth of two levels, and indent the output while also displaying line numbers, first describe the view as follows: 

Keyboard icon
DESCRIBE EMP_DETAILS_VIEW

Screen icon
  Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                               NOT NULL NUMBER(6)
 JOB_ID                                    NOT NULL VARCHAR2(10)
 MANAGER_ID                                         NUMBER(6)
 DEPARTMENT_ID                                      NUMBER(4)
 LOCATION_ID                                        NUMBER(4)
 COUNTRY_ID                                         CHAR(2)
 FIRST_NAME                                         VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(25)
 SALARY                                             NUMBER(8,2)
 COMMISSION_PCT                                     NUMBER(2,2)
 DEPARTMENT_NAME                           NOT NULL VARCHAR2(30)
 JOB_TITLE                                 NOT NULL VARCHAR2(35)
 CITY                                      NOT NULL VARCHAR2(30)
 STATE_PROVINCE                                     VARCHAR2(25)
 COUNTRY_NAME                                       VARCHAR2(40)
 REGION_NAME                                        VARCHAR2(25)

To format EMP_DETAILS_VIEW so that the output displays with indentation and line numbers, use the SET DESCRIBE command as follows: 

Keyboard icon
SET DESCRIBE DEPTH 2 LINENUM ON INDENT ON

To display the above settings, enter 

Keyboard icon
DESCRIBE EMP_DETAILS_VIEW

Screen icon
           Name                Null?    Type
           ------------------- -------- --------------------
    1      EMPLOYEE_ID         NOT NULL NUMBER(6)
    2      JOB_ID              NOT NULL VARCHAR2(10)
    3      MANAGER_ID                   NUMBER(6)
    4      DEPARTMENT_ID                NUMBER(4)
    5      LOCATION_ID                  NUMBER(4)
    6      COUNTRY_ID                   CHAR(2)
    7      FIRST_NAME                   VARCHAR2(20)
    8      LAST_NAME           NOT NULL VARCHAR2(25)
    9      SALARY                       NUMBER(8,2)
   10      COMMISSION_PCT               NUMBER(2,2)
   11      DEPARTMENT_NAME     NOT NULL VARCHAR2(30)
   12      JOB_TITLE           NOT NULL VARCHAR2(35)
   13      CITY                NOT NULL VARCHAR2(30)
   14      STATE_PROVINCE               VARCHAR2(25)
   15      COUNTRY_NAME                 VARCHAR2(40)
   16      REGION_NAME                  VARCHAR2(25)
ESCAPE

If you define the escape character as an exclamation point (!), then 

Keyboard icon
SET ESCAPE !
ACCEPT v1 PROMPT 'Enter !&1:'

displays this prompt: 

Screen icon
Enter &1:

To set the escape character back to the default value of \ (backslash), enter 

Keyboard icon
SET ESCAPE ON
HEADING

To suppress the display of column headings in a report, enter 

Keyboard icon
SET HEADING OFF

If you then run a SQL SELECT command 

Keyboard icon
SELECT LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE JOB_ID = 'AC_MGR';

the following output results: 

Screen icon
Higgins                   12000

To turn the display of column headings back on, enter 

Keyboard icon
SET HEADING ON
INSTANCE

To set the default instance to "PROD1" enter 

Keyboard icon
DISCONNECT
SET INSTANCE PROD1

To set the instance back to the default of local, enter 

Keyboard icon
SET INSTANCE local

You must disconnect from any connected instances to change the instance.

LOBOFFSET

To set the starting position from which a CLOB column's data is retrieved to the 22nd position, enter 

Keyboard icon
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/oracle90/dbs/arch" enter 

Keyboard icon
SET LOGSOURCE "/usr/oracle90/dbs/arch"
RECOVER DATABASE
LONG

To set the maximum number of characters to fetch for displaying and copying LONG values, to 500, enter 

Keyboard icon
SET LONG 500

The LONG data will wrap on your screen; SQL*Plus will not truncate until the 501st character. The default for LONG is 80 characters.

LONGCHUNKSIZE

To set the size of the increments in which SQL*Plus retrieves LONG values to 100 characters, enter 

Keyboard icon
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.

MARKUP

The following is a script which uses the SET MARKUP HTML command to enable HTML marked up text to be spooled to a specified file:

Note:

The SET MARKUP example command is laid out for readability using line continuation characters "-" and spacing. Command options are concatenated in normal entry. 

Use the input command to enter the commands necessary to set up the HTML options and the query you want for your report. 

Keyboard icon
INPUT
SET MARKUP HTML ON SPOOL ON HEAD '<TITLE>Employee List</title> -
STYLE TYPE="TEXT/CSS"><!--BODY {background: ffffc6} --></STYLE>'
SET ECHO OFF
SPOOL employee.htm
SELECT FIRST_NAME, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
SPOOL OFF
SET MARKUP HTML OFF
SET ECHO ON
.

As this script contains SQL*Plus commands, do not attempt to run it with / (slash) from the buffer because it will fail. Save the script and use START to execute it: 

Keyboard icon
SAVE employee.sql
START employee.sql

As well as writing the html spool file, employee.htm, the output is also displayed on screen because SET TERMOUT defaults to ON. You can view the spool file, employee.htm, in your web browser. It should appear something like the following:


Text description of 8-markup.gif follows.
Text description of the illustration 8-markup.gif
SERVEROUTPUT

To enable the display of text within a PL/SQL block using DBMS_OUTPUT.PUT_LINE, enter 

Keyboard icon
SET SERVEROUTPUT ON

The following example shows what happens when you execute an anonymous procedure with SET SERVEROUTPUT ON: 

Keyboard icon
BEGIN
  DBMS_OUTPUT.PUT_LINE('Task is complete');
END;
/
Screen icon
Task is complete.
PL/SQL procedure successfully completed.

The following example shows what happens when you create a trigger with SET SERVEROUTPUT ON: 

Keyboard icon
CREATE TRIGGER SERVER_TRIG BEFORE INSERT OR UPDATE -
OR DELETE
ON SERVER_TAB
BEGIN
DBMS_OUTPUT.PUT_LINE('Task is complete.');
END;
/
Screen icon
Trigger created.

Keyboard icon
INSERT INTO SERVER_TAB VALUES ('TEXT');

Screen icon
Task is complete.
1 row created.

To set the output to WORD_WRAPPED, enter 

Keyboard icon
SET SERVEROUTPUT ON FORMAT WORD_WRAPPED
SET LINESIZE 20
BEGIN
  DBMS_OUTPUT.PUT_LINE('If there is nothing left to do');
  DBMS_OUTPUT.PUT_LINE('shall we continue with plan B?');
END;
/
Screen icon
If there is nothing
left to do
shall we continue
with plan B?

To set the output to TRUNCATED, enter 

Keyboard icon
SET SERVEROUTPUT ON FORMAT TRUNCATED
SET LINESIZE 20
BEGIN
  DBMS_OUTPUT.PUT_LINE('If there is nothing left to do');
  DBMS_OUTPUT.PUT_LINE('shall we continue with plan B?');
END;
/
Screen icon
If there is nothing
shall we continue wi
SHIFTINOUT

To enable the display of shift characters on a terminal that supports them, enter 

Keyboard icon
SET SHIFTINOUT VISIBLE
SELECT LAST_NAME, JOB_ID FROM EMP_DETAILS_VIEW
WHERE SALARY > 12000;

Screen icon
LAST_NAME      JOB_ID
---------- ----------
:JJOO:     :AABBCC:
:AA:abc    :DDEE:e

where ":" = visible shift character
uppercase represents multibyte characters

lowercase represents singlebyte characters

SQLBLANKLINES

To allow blank lines in a SQL statement, enter 

Keyboard icon
SET SQLBLANKLINES ON
REM Using the SQLTERMINATOR (default is ";") 
REM Could have used the BLOCKTERMINATOR (default is ".")
SELECT *

FROM

DUAL

;

The following output results: 

Screen icon
D
-
X
SQLCONTINUE

To set the SQL*Plus command continuation prompt to an exclamation point followed by a space, enter 

Keyboard icon
SET SQLCONTINUE '! '

SQL*Plus will prompt for continuation as follows: 

TTITLE 'MONTHLY INCOME' -
! RIGHT SQL.PNO SKIP 2 -
! CENTER 'PC DIVISION'

The default continuation prompt is "> ".

SQLPROMPT

You need the Select Any Table privilege to successfully run the following example scripts.

To set the SQL*Plus command prompt to show your SID, enter 

Keyboard icon
SET TERMOUT OFF
COLUMN X NEW_VALUE Y
SELECT RTRIM(INSTANCE, CHR(0)) X FROM V$THREAD;
SQLPROMPT '&Y SQL>'
SET TERMOUT ON

To set the SQL*Plus command prompt to show the current user, enter 

Keyboard icon
SET TERMOUT OFF
COLUMN D22 NEW_VALUE VAR
SELECT USERNAME D22 FROM USER_USERS;
SQLPROMPT '&&VAR>'
SET TERMOUT ON

These settings are not dynamic. You need to reset them whenever you change instances, such as when you use the connect command to log on to another instance.

SUFFIX

To change the default command-file extension from the default, .SQL to .UFI, enter 

Keyboard icon
SET SUFFIX UFI

If you then enter 

Keyboard icon
GET EXAMPLE

SQL*Plus will look for a file named EXAMPLE.UFI instead of EXAMPLE.SQL.

Go to previous page Go to beginning of chapter Go to next page
Oracle
Copyright © 1996-2001, Oracle Corporation.
All Rights Reserved.
Go To Documentation Library
Home		 Go To Product List
Book List		 Go To Table Of Contents
Contents		 Go To Index
Index
Master Index
Feedback