Use SET COLSEP to change the text that prints between columns of data.

SET COLSEP column_separator

where:

The default column separator is a single space. The following example shows how you can change it to a comma:

SQL> SET COLSEP ","
SQL> SELECT 'One' one,
  2         'Two' two
  3    FROM dual;

ONE,TWO
---,---
One,Two

Like a space, the comma in this example is a single character. You aren’t limited to one character, though. You could just as easily change the column separator to a string of several characters.

The SET EMBEDDED command controls the printing of embedded reports. The default setting is OFF, which causes the results of each new query to print on a new page, and also causes page numbering to start over each time a SELECT statement is executed.

SET EMB[EDDED] {ON|OFF}

An embedded report is one that prints as if it were the continuation of a previous report. The following example shows the results of executing two SELECT statements with the default setting. A page title has been created in order to show the effect on page numbering:

SQL> SET EMBEDDED OFF
SQL> SET PAGESIZE 24
SQL> SET NEWPAGE 1
SQL> TTITLE LEFT "Example of SET EMBEDDED, Page " SQL.PNO
SQL> SELECT * FROM dual;

Example of SET EMBEDDED, Page         1
D
-
X

SQL> SELECT * FROM dual;

Example of SET EMBEDDED, Page         1
D
-
X

Notice that the second SELECT statement generated a page break — you can see that the page title printed again. You can also see, by looking at the titles, that page numbering for each query began with page 1. Now take a look at the same example, but with SET EMBEDDED ON:

SQL> SET EMBEDDED OFF
SQL> SET PAGESIZE 24
SQL> SET NEWPAGE 1
SQL> TTITLE LEFT "Example of SET EMBEDDED, Page " SQL.PNO
SQL> SELECT * FROM dual;

Example of SET EMBEDDED, Page         1
D
-
X

SQL> SET EMBEDDED ON
SQL> SELECT * FROM dual;
D
-
X

This time, the second SELECT statement did not generate a page break. The second report began printing on the same page on which the first report ended.

If you have defined a page footer (BTITLE) for your report, then a page break will occur at the end of the report, regardless of the EMBEDDED setting. There are a couple of reasons for this behavior. One is that SQL*Plus processes commands one line at a time. SQL*Plus doesn’t know about the second SELECT statement until after it is completely finished printing the results of the first one. If a page footer is defined, SQL*Plus must print it, because it has no way of knowing whether or not another SELECT statement is coming. In order for a page footer to print, SQL*Plus must advance printing to the bottom of the page. After the footer has printed, any new data must begin on a new page. The embedded setting, however, still controls whether or not the page number is reset.

Use the SET HEADSEP command when you want to change the character used when defining a two-line column heading:

SET HEADS[EP] heading_separator

where:

Normally, this character is a vertical bar, and marks the place in a column’s heading where you want a line break to occur. The following example shows a two-line heading being defined:

SQL> COLUMN dummy FORMAT A10 HEADING 'Line 1|Line 2'
SQL> SELECT * FROM dual;

Line 1
Line 2
----------
X

As you can see, the vertical bar in the column’s heading text was replaced by a line break when the column heading was printed. If you need to use the vertical bar as part of a column heading, use SET HEADSEP to choose some other character to act as the line break marker.

Use SET HEADING to control whether or not column headings print when you SELECT or PRINT data. The default value for this setting is ON, which allows column headings to print.

SET HEA[DING] [ON|OFF]

where:

This setting is on by default, so you normally get a column heading whenever you select or print data using either the SELECT command or the PRINT command. For example:

SQL> SELECT * FROM dual;

D
-
X

Change the value to OFF, and your column headings go away. The following example shows this:

SQL> SET HEADING OFF
SQL> SELECT * FROM dual;

X

Use SET LINESIZE to control the number of characters SQL*Plus prints on one physical line. The default setting is 80. The maximum width is system-dependent. Under Windows 95, it is 32,767.

SET LIN[ESIZE] line_width

where:

The linesize setting is also referenced by SQL*Plus when you define any headers or footers (see TTITLE, BTITLE, REPHEADER, REPFOOTER) that are centered or right-justified.

MAXDATA is an obsolete setting that should no longer be used. SQL*Plus supports it in order to be backward-compatible. MAXDATA controls the maximum row length SQL*Plus can handle.

SET MAXD[ATA] max_row_width

where:

The default setting under Windows 95 is zero, but may vary from one operating system to the next. Under some operating systems, such as Windows 95, this setting seems to have no effect whatsoever on SQL*Plus’s behavior.

Controls the manner in which the transition from one page to the next is marked. You can have SQL*Plus print a formfeed character at the start of each new page, skip a specific number of lines between pages, or do nothing at all when advancing from one page to another:

SET NEWP[AGE] {lines_to_print|NONE}

where:

SET NULL allows you to change the text SQL*Plus prints in a column when the value for that column is null:

SET NULL null_text

where:

The default null text setting is an empty string, which causes null values to print as blanks. The following example shows this, and also shows how the null text may be changed:

SQL> SELECT employee_termination_date 
  2    FROM employee 
  3   WHERE employee_id=101;

EMPLOYEE_
---------

SQL> SET NULL "*NULL*"
SQL> SELECT employee_termination_date 
  2    FROM employee 
  3   WHERE employee_id=101;

EMPLOYEE_
---------
*NULL*

If you use the COLUMN command to format a column, the NULL clause of that command will override this setting, but only for that one column.

SET NUMFORMAT allows you to specify the default formatting of numeric values returned from a SELECT statement. Any number format usable with the COLUMN command may also be used with SET NUMFORMAT.

SET NUMF[ORMAT] format_spec

where:

The following example shows the effect of changing the NUMFORMAT setting:

SQL> SELECT 123456.7 FROM dual;

 123456.7
---------
 123456.7

SQL> SET NUMFORMAT $999,999.99
SQL> SELECT 123456.7 FROM dual;

    123456.7
------------
 $123,456.70

The NUMFORMAT setting controls the default display format for numeric values. You can use the COLUMN command to specify display formats on a column-by-column basis, and those take precedence over the NUMFORMAT setting.

SET NUMWIDTH controls the default width used when displaying numeric values:

SET NUM[WIDTH] width

where:

The default NUMWIDTH setting is 10. NUMWIDTH is used only when no other settings apply. The following example shows the effect of setting NUMWIDTH to 5:

SQL> SET NUMWIDTH 5
SQL> SELECT 123 FROM dual;

 123
----
 123

A numeric format specified by a COLUMN command or by a SET NUMFORMAT command will override NUMWIDTH. The following example shows this:

SQL> SET NUMWIDTH 5
SQL> SET NUMFORMAT 999,999.99
SQL> SELECT 123 FROM dual;

        123
-----------
     123.00

SQL> SHOW NUMWIDTH
numwidth 5

As you can see, NUMWIDTH is still 5, but that value is ignored because the NUMFORMAT setting takes precedence. A long column title can also cause NUMWIDTH to be ignored, as the following example demonstrates:

SQL> SET NUMWIDTH 5
SQL> SET NUMFORMAT ""
SQL> COLUMN a HEADING "This is a long column title"
SQL> SELECT 123 a FROM dual;

This is a long column title
---------------------------
                        123

As you can see, the column title takes precedence over NUMWIDTH when it comes to determining the width of the column.

Use SET PAGESIZE to tell SQL*Plus the number of printed lines that will fit on one page of output. You can also use this setting to completely turn off all pagination functions.

SET PAGES[IZE] lines_on_page

where:

The PAGESIZE must be set in conjunction with NEWPAGE. The sum of PAGESIZE and NEWPAGE should equal the number of lines that will physically fit on one page. SQL*Plus will print headers, detail, and footers until PAGESIZE lines have been printed. Then it will print NEWPAGE lines to advance to the next page, where the process starts over again. Your page titles will drift up or down with each new page if these settings don’t match the page’s physical size. The exception to this is when you use SET NEWPAGE 0.

If you use SET NEWPAGE 0 to cause a formfeed to print at the beginning of each page, you should set PAGESIZE to at least one less than the physical number of lines on a page. Failure to do so may result in alternating blank pages in your printed report.

You can completely turn off all pagination by issuing a SET PAGESIZE 0 command. This will eliminate page titles, page footers, column titles, and any blank lines or formfeeds from the NEWPAGE setting.

SET RECSEP is used to tell SQL*Plus whether or not to print a record separator between each record displayed as the result of a query. The default setting is not to print anything, except when a long record wraps to a second line. In that case, a blank line is printed as a record separator.

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

where:

The default setting is WRAPPED, which is probably a safe choice, because it causes a separator to print only in cases where a break between records may not be obvious — in other words, where each line does not necessarily start a new record. Here is an example of a record separator being printed:

SQL> COLUMN view_name FORMAT A15
SQL> SELECT view_name FROM all_views;
ALL_ALL_TABLES
ALL_ARGUMENTS
ALL_CATALOG
ALL_CLUSTERS
ALL_CLUSTER_HAS
H_EXPRESSIONS

ALL_COLL_TYPES
...

You can see that the record separator, by default a blank line, was printed after the ALL_CLUSTER_HASH_EXPRESSIONS view was listed. This blank line removes any ambiguity about whether H_EXPRESSIONS and ALL_CLUSTER_HAS are two separate views or if a long view name has wrapped to a second line. Setting RECSEP to OFF yields the following results:

SQL> SET RECSEP OFF
SQL> SELECT VIEW_NAME from all_views;
ALL_ALL_TABLES
ALL_ARGUMENTS
ALL_CATALOG
ALL_CLUSTERS
ALL_CLUSTER_HAS
H_EXPRESSIONS
ALL_COLL_TYPES

Do you see six views listed or seven? How would you know?

In addition to controlling whether or not the record separator prints, you can control the character used as well. See the SET RECSEPCHAR command.

Use SET RECSEPCHAR to change the record separator to something other than a line of space characters:

SET RECSEPCHAR separator_char

where:

The default record separator is a blank line. The following example shows how you can change it to be a line of asterisks instead:

SQL> SET RECSEPCHAR "*"
SQL> SET RECSEP EACH
SQL> SELECT view_name FROM all_views;
ALL_ALL_TABLES
*****************************************************************************
ALL_ARGUMENTS
*****************************************************************************
ALL_CATALOG
*****************************************************************************

This example uses SET RECSEP EACH to cause a separator to print after each record, so that you will have something to look at. The separator character was set to an asterisk, causing you to get a full line of asterisks between each record. The length of the separator line will match the LINESIZE setting.

The SHIFTINOUT setting controls whether or not shift characters are displayed as part of the output. It is usable only with IBM 3270 terminals and their equivalent, and only when SQL*Plus is displaying data in a shift-sensitive character set.

SET SHIFT[INOUT] {VIS[IBLE]|INV[ISIBLE]}

where:

The default setting is INVISIBLE.

SET SPACE is an obsolete command similar to SET COLSEP. It allows you to specify the number of spaces SQL*Plus prints between columns of output.

SET SPACE num_of_spaces

where:

The following example shows how SET SPACE works by changing the spacing between columns from one to five spaces:

SQL> SELECT 'A' a, 'B' b FROM dual;

A B
- -
A B

SQL> SET SPACE 5
SQL> SELECT 'A' a, 'B' b FROM dual;

A     B
-     -
A     B

Issuing SET SPACE 5 has the exact same effect as if you were to issue SET COLSEP " “. In fact, the two settings are kept in sync with one another. The SET SPACE command will change the COLSEP setting to match.

The TAB setting controls whether or not SQL*Plus uses tab characters when generating whitespace in terminal output. This setting is a throwback to the days when terminal connections ran at very slow data rates, such as 1200 bits per second.

SET TAB {OFF|ON}

where:

The default setting is ON, which allows SQL*Plus to use tab characters in the output.

The TRIMOUT setting controls whether or not SQL*Plus displays any trailing spaces that may occur at the end of a line. The default setting is ON, which causes SQL*Plus to display only up to the last non-blank character on a line.

SET TRIM[OUT] {ON|OFF}

where:

The default setting of ON usually works well when displaying data on a terminal, so there’s rarely a reason to turn this setting off. TRIMOUT has no effect on spooled output. If you are spooling output to a file, the TRIMSPOOL setting controls whether or not trailing spaces are spooled as well.

The TRIMSPOOL setting controls whether or not SQL*Plus writes trailing spaces when spooling data to a file. The default setting is OFF, which causes SQL*Plus to write each line to the spool file in its entirety, trailing spaces and all.

SET TRIMS[POOL] {ON|OFF}

where:

If you are spooling data to a file in order to load it into another program, you will want to leave TRIMSPOOL ON. Otherwise, the program reading the spool file might return errors because the records are shorter than expected.

SET TRUNCATE is an obsolete command that gives you the same choice as SET WRAP. You can choose whether or not to truncate lines that are longer than the LINESIZE setting.

SET TRU[NCATE] {OFF|ON}

where:

TRUNCATE and WRAP both affect the same internal setting. Turning one on results in the other being turned off, and vice versa. For example:

SQL> SHOW WRAP
wrap : lines will be wrapped
SQL> SET TRUNCATE ON
SQL> SHOW WRAP
wrap : lines will be truncated

When you select data that is longer than the LINESIZE and the WRAP setting is off, some versions of SQL*Plus will print a warning message telling you that your rows may be truncated.

With the SET UNDERLINE command, you can control the character used to underline column headings. You can also control whether or not column headings are underlined at all.

SET UND[ERLINE] {underline_char | {ON|OFF}}

where:

The following example shows the underline character being changed to an asterisk:

SQL> SET UNDERLINE *
SQL> SELECT USER FROM dual;

USER
******************************
JEFF

You can also use SET UNDERLINE to turn underlining off completely, for example:

SQL> SET UNDERLINE OFF
SQL> SELECT USER FROM dual;

USER
JEFF

One reason you might turn underlining off would be if you wanted some columns to print without any heading at all. First you would turn UNDERLINE off. Then you would define column headings for all columns with the COLUMN command. Those headings you did want to print would need to include the underlines as part of the heading definition. Other headings would be defined as empty strings.

The WRAP setting controls how SQL*Plus prints lines that contain more characters than the current LINESIZE setting allows. With WRAP ON, long lines are wrapped around and continued on as many physical lines as necessary. With WRAP OFF, lines are truncated to match the LINESIZE setting.

SET WRA[P] {ON|OFF}

where:

The following example shows the results of issuing the same SELECT statement, once with WRAP turned on, and once with it turned off:

SQL> SET WRAP ON
SQL> SELECT * FROM ALL_VIEWS WHERE VIEW_NAME = 'ALL_VIEWS';

OWNER                          VIEW_NAME                      TEXT_LENGTH
------------------------------ ------------------------------ -----------
TEXT
-----------------------------------------------------------------------------
TYPE_TEXT_LENGTH
----------------
TYPE_TEXT
-----------------------------------------------------------------------------
OID_TEXT_LENGTH
---------------
OID_TEXT
-----------------------------------------------------------------------------
VIEW_TYPE_OWNER                VIEW_TYPE
------------------------------ ------------------------------
SYS                            ALL_VIEWS                              821
select u.name, o.name, v.textlength, v.text, t.typetextlength, t.typetext,



SQL> SET WRAP OFF
SQL> /
rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated


OWNER                          VIEW_NAME                     TEXT_LENGTH TEXT
------------------------------ ----------------------------- ----------- ----
SYS                            ALL_VIEWS                           821 select

As you can see, SQL*Plus displays several warning messages telling you that the rows you are about to see displayed may have been truncated.

The AUTOPRINT setting controls whether or not SQL*Plus automatically prints the contents of any bind variables referenced by a PL/SQL block after it executes.

SET AUTOP[RINT] {OFF|ON}

where:

SET DOCUMENT controls whether or not SQL*Plus prints text created with the DOCUMENT command.

SET DOC[UMENT] {ON|OFF}

where:

This setting only affects the DOCUMENT command when it is issued from a command file. Say that you had a file with the following lines:

DOCUMENT
This is documentation.
#

The following example shows the results of executing this file with the DOCUMENT setting on, and then with it off.

SQL> SET DOCUMENT ON
SQL> @c:adoc
DOC>This is documentation.
DOC>#
SQL> SET DOCUMENT OFF
SQL> @c:adoc

Use SET ECHO to tell SQL*Plus whether or not you want the contents of command files to be echoed to the screen as they are executed.

SET ECHO {OFF|ON}

where:

SET ECHO is one of the few debugging tools SQL*Plus has, if indeed you could call it a debugging tool. It’s often very helpful to turn command echoing on while you are developing and testing a new script file. The following example shows the same script file being executed, once with ECHO on, and once with it off:

SQL> @c:aecho_test

D
-
X

SQL> SET ECHO ON
SQL> @c:aecho_test
SQL> SELECT * FROM dual;

D
-
X

For the second execution of the script, ECHO had been turned on, so the SELECT statement was displayed on the screen when SQL*Plus executed it.

The FEEDBACK setting controls whether or not SQL*Plus displays the number of records returned by a SELECT statement, deleted by a DELETE statement, updated by an UPDATE statement, or inserted by an INSERT statement. You can set a threshold below which you do not get any feedback regardless of whether the setting is on or not.

SET FEED[BACK] {OFF|ON|row_threshold}

where:

The following example shows a feedback message from a SELECT statement:

SQL> SET FEEDBACK 1
SQL> SELECT * FROM dual;

D
-
X

1 row selected.

In this example, feedback was set to 1 prior to executing the SELECT statement. That caused a feedback message to be displayed, even though the statement returned only one row.

You can use the SET PAUSE command to have SQL*Plus pause after each page of output when displaying rows returned by a query:

SET PAU[SE] {ON|OFF|pause_message}

where:

The following example shows how the pause feature works:

SQL> SET PAGESIZE 10
SQL> SET PAUSE ON
SQL> SET PAUSE "Press ENTER to continue..."
SQL> SELECT view_name FROM all_views;
Press ENTER to continue...

VIEW_NAME
------------------------------
ALL_ALL_TABLES
ALL_ARGUMENTS
ALL_CATALOG
ALL_CLUSTERS
ALL_CLUSTER_HASH_EXPRESSIONS
ALL_COLL_TYPES
ALL_COL_COMMENTS
Press ENTER to continue...

The PAGESIZE setting controls the number of lines printed on a page. In this example, PAGESIZE was set to 10, so SQL*Plus paused after every 10 lines of output.

The SERVEROUTPUT setting controls whether or not SQL*Plus prints the output generated by the DBMS_OUTPUT package from PL/SQL procedures:

SET SERVEROUT[PUT] {OFF|ON} 
                   [SIZE buffer_size] 
                   [FOR[MAT] {WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]}]

where:

By default, SQL*Plus does not display output from PL/SQL. The following example shows this:

SQL> BEGIN
  2    DBMS_OUTPUT.PUT_LINE('Hello World'),
  3  END;
  4  /

PL/SQL procedure successfully completed.

Now, the same block is executed again, after issuing a SET SERVEROUTPUT ON command:

SQL> SET SERVEROUTPUT ON
SQL> BEGIN
  2    DBMS_OUTPUT.PUT_LINE('Hello World'),
  3  END;
  4  /
Hello World

PL/SQL procedure successfully completed.

Older versions of SQL*Plus do not support the SIZE and FORMAT clauses of this command.

The SHOWMODE setting controls the feedback you get when you use the SET command to change a setting:

SET SHOW[MODE] {ON|OFF|BOTH}

where:

The following example shows the results of turning SHOWMODE on:

SQL> SET SHOWMODE ON
new: showmode BOTH
SQL> SET LINESIZE 132
old: linesize 80
new: linesize 132
SQL> SET PAGESIZE 60
old: pagesize 10
new: pagesize 60

You can see that with SHOWMODE on, the old and new values of each setting are displayed when they are changed.

You can use the SET SQLPROMPT command to change the SQL*Plus command prompt:

SET SQLP[ROMPT] prompt_text

where

The following example shows the prompt being changed from “SQL>” to “SQL*Plus>”:

SQL> SET SQLPROMPT "SQL*Plus> "
SQL*Plus>

Some people like to set their prompt to the name of the database to which they are connected. One way to do this is to place the following commands in your login.sql file:

SET TERMOUT OFF
COLUMN dbname NEW_VALUE prompt_dbname
SELECT SUBSTR(global_name,1,INSTR(global_name,'.')-1) dbname 
       FROM global_name;
SET SQLPROMPT "&&prompt_dbname> "
SET TERMOUT ON

These commands will set your prompt to the global database name. The global name comes from the global_name view, and may or may not be the same as the connect string you used to connect to the database. By placing these commands in your login.sql file, you ensure that the prompt will be set automatically, based on the database you are connecting to, whenever you start SQL*Plus.

The login.sql file was discussed earlier in this chapter, in the section titled Section 11.2.

Controls whether or not SQL*Plus displays output generated by SQL statements, PL/SQL blocks, and SQL*Plus commands. This setting only applies when SQL*Plus is executing a script file. SQL*Plus always displays output from commands entered interactively.

SET TERM[OUT] {OFF|ON}

where:

Terminal output is often turned off while a command file is running to keep the user’s screen from becoming cluttered with query output and feedback messages.

The SET TIME command controls whether or not SQL*Plus displays the current time with each command prompt:

SET TI[ME] {OFF|ON}

where:

The following example shows the effect of issuing a SET TIME ON command:

SQL> SET TIME ON
22:44:41 SQL>

The VERIFY setting controls whether or not SQL*Plus displays before and after images of each line that contains a substitution variable:

SET VER[IFY] {OFF|ON}

where:

Verification is done only on lines that are part of a SQL statement or a PL/SQL block. When substitution occurs in a SQL*Plus command, before and after images are never displayed. The following example shows what verification looks like:

SQL> DEFINE dummy_char = 'X'
SQL> SELECT * FROM dual
  2   WHERE dummy = '&&dummy_char';
old   2:  where dummy = '&&dummy_char'
new   2:  where dummy = 'X'

D
-
X

Notice that line 2, which contained the reference to the &&dummy_char substitution variable, was displayed both before and after the reference to the variable was replaced by its value. If you don’t like this display, you can turn it off with SET VERIFY OFF.

The BLOCKTERMINATOR setting controls the character used to terminate a PL/SQL block that is being entered into the buffer for editing:

SET BLO[CKTERMINATOR] block_term_char

where:

When you enter a PL/SQL block into the buffer, you need a way to tell SQL*Plus when the block has ended. By default, the period can be used for this purpose, but you can use the SET BLOCKTERMINATOR command to change that. The following example shows how this is done by changing the block terminator to a pound sign character:

SQL> SET BLOCKTERMINATOR #
SQL> BEGIN
  2    DBMS_OUTPUT.PUT_LINE('PL/SQL is powerful.'),
  3  END;
  4  #
SQL>

Terminating the block this way leaves it in the buffer for you to edit. Do not confuse this with the use of the slash command, which both terminates and executes a block.

The SET BUFFER command allows you to switch to another buffer for editing purposes:

SET BUF[FER] {buffer_name|SQL}

where:

Changing the buffer has limited use, because you can only execute a statement from the SQL buffer. The GET, SAVE, and EDIT commands work, as do all the editing commands. The following example shows a second buffer being used to edit a statement that exists in a text file without disturbing the statement currently in the SQL buffer:

SQL> SHOW BUFFER
buffer SQL
SQL> SELECT * FROM employee
  2  
SQL> L
  1* SELECT * FROM employee
SQL> SET BUFFER project
SQL> L
No lines in PROJECT buffer.
SQL> GET c:aproject.sql
  1* SELECT * FROM project
SQL> I
  2  WHERE project_budget > 1000000
  3  
SQL> SAVE c:aproject.sql REPLACE
Wrote file c:aproject.sql
SQL> SET BUFFER SQL
SQL> L
  1* SELECT * FROM employee
SQL>

As you can see, using a second buffer made it possible to edit the SQL statement in the project.sql file without disturbing the statement currently in the SQL buffer. Of course, you could do the same thing more easily with the EDIT command, so SET BUFFER isn’t used much anymore.

The SET CMDSEP command controls whether or not you can enter multiple commands on one line. The commands in question must both be SQL*Plus commands. SET CMDSEP is also used to change the character used to separate these commands:

SET CMDS[EP] {OFF|ON|separator_char

where:

The following example turns this feature on, sets the separator character to an exclamation point, and shows how two commands may be placed on one line:

SQL> SET CMDSEP ON
SQL> SET CMDSEP "!"
SQL> SHOW CMDSEP! SHOW BUFFER!
cmdsep "!" (hex 21)
buffer SQL
SQL>

The SQLBLANKLINES setting is a new feature in version 8.1 of SQL*Plus, and allows SQL statements to contain embedded blank lines:

SET SQLBLANKLINES {OFF|ON}

where:

Pressing ENTER on a blank line while typing a SQL statement into SQL*Plus normally signals the end of the statement. The statement is then placed into the buffer, and you then have the option of making further edits or of executing the statement. Turning SQLBLANKLINES on allows you to put a blank line in the middle of your statement, as the following example demonstrates:

SQL> SET SQLBLANKLINES ON
SQL> SELECT
  2  *
  3  
  4  FROM EMPLOYEE
  5  
SQL>

This feature was added to SQL*Plus to allow it to execute existing Server Manager scripts, such as CATPROC.SQL, without having to go through and modify all the SQL statements in those scripts. Unlike SQL*Plus, the Server Manager utility allows blank lines in a SQL statement.

The SQLCASE setting controls whether or not SQL*Plus automatically uppercases or lowercases SQL statements and PL/SQL blocks as they are transmitted to the server for execution:

SET SQLC[ASE] {MIXED|UPPER|LOWER}

where:

Be careful when changing this setting. Any case conversions SQL*Plus does will affect not only your SQL statement keywords, but also any quoted text literals as well. This is seldom desirable behavior. The following example demonstrates this.

SQL> SET SQLCASE UPPER
SQL> SELECT * FROM dual WHERE dummy='x';

D
-
X

You can see that the SELECT statement succeeded, even though the lowercase “x” in the WHERE clause does not match the uppercase “X” in the dummy column.

Case conversion occurs when the statement is transmitted to the database server. The contents of the buffer will always reflect what you actually typed.

The SQLCONTINUE setting controls the prompt used when you continue a statement to a second line, using the SQL*Plus continuation character:

SET SQLCO[NTINUE] continuation_prompt

where:

The following example shows the effect of changing this setting:

SQL> SET SQLCONTINUE "Continue > "
SQL> DESCRIBE -
Continue >

Notice that the SQL*Plus continuation character, a dash, was used following the DESCRIBE command. The continuation prompt is used only when you use the continuation character to continue a command to a new line. It is not used when you enter a multiline SQL statement.

The SQLNUMBER setting controls whether or not SQL*Plus uses the line number as a prompt when you enter a multiline SQL statement:

SET SQLN[UMBER] {OFF|ON}

where:

The following example shows the difference between the ON and OFF settings:

SQL> SET SQLNUMBER ON
SQL> SELECT
  2  * 
  3  FROM dual
  4  
SQL> SET SQLNUMBER OFF
SQL> SELECT
SQL> *
SQL> FROM dual
SQL>

I can’t imagine any reason to ever turn this setting off. Look at the first and second statements in the example. It’s immediately obvious that the three lines of the first query all belong together as one statement. This is not so obvious with the second statement — you have to think about it a bit. The visual cue provided by the line numbers is missing, making you take more time to figure out what you are really looking at.

The SQLPREFIX setting controls the SQL*Plus prefix character. The prefix character allows you to execute a SQL*Plus command while in the middle of entering an SQL statement (or PL/SQL block).

SET SQLPRE[FIX] prefix_char

where:

The following example shows how the prefix character is used by using it to execute a DESCRIBE command while entering a SELECT statement:

SQL> SELECT 
  2  #DESCRIBE EMPLOYEE
 Name                            Null?    Type
 ------------------------------- -------- ----
 EMPLOYEE_ID                     NOT NULL NUMBER
 EMPLOYEE_NAME                            VARCHAR2(40
 EMPLOYEE_HIRE_DATE                       DATE
 EMPLOYEE_TERMINATION_DATE                DATE
 EMPLOYEE_BILLING_RATE                    NUMBER

  2  employee_id, employee_name
  3  FROM employee
  4

This ability to execute a SQL*Plus command (and it must be a SQL*Plus command) while entering a SQL statement can come in handy when you need to quickly refresh your memory regarding the column names in the table.

The SQLTERMINATOR setting controls whether or not SQL*Plus allows you to use a semicolon to terminate and execute a SQL statement. This setting also controls the specific character used for this purpose.

SET SQLT[ERMINATOR] {OFF|ON|term_char}

where:

The following example changes the terminator character to a percent sign and uses it to terminate and execute a SELECT statement:

SQL> SET SQLTERMINATOR "%"
SQL> SELECT employee_name FROM employee%

EMPLOYEE_NAME
----------------------------------------
Bohdan Khmelnytsky
Pavlo Chubynsky
Ivan Mazepa
Taras Shevchenko
...

If the feature had been turned off in the above example, the percent sign would have been placed in the buffer as part of the SELECT statement.

The SUFFIX setting controls the default extension used for command files:

SET SUF[FIX] extension

where:

This setting is used by commands such as START, @, SAVE, and others that refer to SQL files. It does not apply to files created with the SPOOL command.

The APPINFO setting controls whether or not SQL*Plus automatically registers command files using the DBMS_APPLICATION_INFO package:

SET APPI[NFO] {OFF|ON|app_text}

where:

The DBMS_APPLICATION_INFO package is defined in the Oracle Tuning manual. Among other things, this package controls the contents of the module field in both the V$SESSION and V$SQLAREA views.

Whenever you connect to a database, SQL*Plus registers itself as the active application by making a call to the DBMS_APPLICATION_INFO.SET_MODULE procedure. This sets the module name for your session to “SQL*Plus”. This is reflected in the v$session view, as the following example demonstrates:

SQL> SELECT module FROM v$session WHERE username=USER;

MODULE
------------------------------------------------
SQL*Plus

SQL*Plus has the ability to update the module name whenever you execute a command file. The module name can be set to either the command file name or to some arbitrary text that you specify. The default setting for APPINFO causes SQL*Plus to register the name of each command file you execute. So, if you execute a command file from one SQL*Plus session and query the v$session view from a second SQL*Plus session, you will get results like the following:

SQL> SELECT module FROM v$session WHERE username='JEFF';

MODULE
------------------------------------------------
01@ c:aproject_hours_dollars.SQL

The module column now tells you the name of the command file being executed by the user named JEFF. SQL*Plus actually crams in a bit more information than just the filename. You can break down the format of the module field as follows:

where:

You may find that you don’t care about command file names, but that you just want to know when users are using SQL*Plus. You can accomplish that by setting APPINFO to OFF. In that case, SQL*Plus will still register itself, but will not subsequently change the module name. It will always be “SQL*Plus”. For this to apply to all users, you would need to place the setting in each user’s global or site profile.

An additional option is to supply a fixed text string that SQL*Plus can use instead of a filename. This string is passed as the module name whenever a command file is executed. The result is that while you will know that a command file is being executed, you won’t know which one.

The ARRAYSIZE setting controls the number of rows SQL*Plus fetches from the database at one time.

SET ARRAY[SIZE] array_size

where:

Increasing the arraysize allows SQL*Plus to return more rows in one fetch, thus lessening the required number of network round trips between it and the database server. The tradeoff is that larger arraysize settings require more memory. Using the default value of 15, SQL*Plus would require 10 fetches to return 150 rows from a query. By increasing the arraysize to 50, you reduce the number of fetches to three.

The autocommit setting controls whether or not SQL*Plus automatically commits changes you make to the database. It also controls how often those changes are committed.

SET AUTO[COMMIT] {OFF|ON|IMMEDIATE|statement_count}

where:

When you set autocommit to occur after a specified number of successful SQL statements, be aware that manually executing a COMMIT, a ROLLBACK, or another SET AUTOCOMMIT command will cause the counter to be reset back to zero. Take a look at the following example:

SET AUTOCOMMIT 5
DELETE FROM project_hours WHERE employee_id = 101 AND project_id = 1001;
DELETE FROM project_hours WHERE employee_id = 102 AND project_id = 1001;
COMMIT;
DELETE FROM project_hours WHERE employee_id = 103 AND project_id = 1001;
DELETE FROM project_hours WHERE employee_id = 104 AND project_id = 1001;
DELETE FROM project_hours WHERE employee_id = 105 AND project_id = 1001;
DELETE FROM project_hours WHERE employee_id = 106 AND project_id = 1001;
DELETE FROM project_hours WHERE employee_id = 107 AND project_id = 1001;

The COMMIT statement in the fourth line will cause the counter to be reset. Counting will start over again, and five more SQL statements must be executed successfully before an automatic commit occurs.

The CLOSECURSOR setting controls whether or not SQL*Plus closes the cursor used to execute an SQL statement after the statement has executed:

SET CLOSECUR[SOR] {OFF|ON}

where:

While you normally think of a cursor only in the context of returning data from a SELECT statement, Oracle also uses cursors to execute other SQL statements, such as DELETE, INSERT, UPDATE, and so forth. The same cursor can be used to execute many SQL statements, so SQL*Plus leaves it open all the time by default.

Use SET COMPATIBILITY to tell SQL*Plus the version of Oracle to which you are connected:

SET COM[PATIBILITY] {V7|V8|NATIVE}

where:

This setting controls the way SQL statements are transmitted to the server and the way the results are brought back. It’s usually best just to leave this at the default setting, which causes SQL*Plus to automatically choose the correct method based on the database to which you are connected.

The copycommit setting controls how often SQL*Plus commits during execution of a COPY command:

SET COPYC[OMMIT] batch_count

where:

Normally, when you execute a COPY command, SQL*Plus copies all the rows from the source table to the destination table, then commits those changes. This can make for a rather large transaction if you are copying a large number of records, and your rollback segments may not be big enough to accommodate it. You can use SET COPYCOMMIT to have SQL*Plus periodically commit the changes, thus reducing the transaction size.

The copycommit setting works in conjunction with the arraysize setting. The arraysize setting controls the number of rows in a batch. The copycommit setting controls how many batches are copied before committing. The number of rows copied before each commit is equal to ARRAYSIZE * COPYCOMMIT. Take a look at this example:

SET ARRAYSIZE 15
SET COPYCOMMIT 10
COPY TO jonathan/chatham@jonathan
CREATE employee_copy
USING SELECT * FROM employee;

Because the arraysize is 15 and the copycommit setting is 10, the COPY statement shown here will commit changes after every 150 rows (15 * 10).

Use SET FLAGGER to have your SQL statements checked for conformance to ANSI/ISO SQL92 syntax. There are three compliance levels to choose from: entry, intermediate, and full.

SET FLAGGER {OFF|ENTRY|INTERMED[IATE]|FULL}

where:

Using the SET FLAGGER command has the same effect as executing the ALTER DATABASE SET FLAGGER command. However, SET FLAGGER is a SQL*Plus command, so you can execute it even before connecting to the database. Once you’ve turned this feature on, any attempt to execute a nonconforming command will result in an error message like the following:

ERROR:
ORA-00097: Use of Oracle SQL feature not in SQL92 Entry Level

This feature is useful if you are writing software for the federal government and are required to deliver an implementation that uses no non-standard, vendor-specific features.

The flush setting indicates whether or not the host operating system is allowed to buffer output:

SET FLU[SH] {OFF|ON}

where:

If you are running a command file, turning flush off might keep you from seeing any output until SQL*Plus is finished executing that file.

The SET CONCAT command allows you to change the character used to terminate a substitution variable reference. You can also use the command to turn the feature off so that SQL*Plus doesn’t recognize any character as the terminator.

SET CON[CAT] {OFF|ON|concat_char}

where:

This setting is important only when you immediately follow a substitution variable name with characters that SQL*Plus might interpret as part of the name. Consider the following example:

DEFINE table="PROJECT"
SELECT &&table._name FROM &&table;

The period (or concatenation character) in the SELECT statement is used to terminate the reference to &&table. Without the period, SQL*Plus would recognize &&table_name as the substitution variable.

The SET DEFINE command allows you to change the prefix character used to mark substitution variables. You can also use SET DEFINE to turn variable substitution off.

SET DEF[INE] {OFF|ON|prefix_char}

where:

When you start SQL*Plus, variable substitution will be on by default, and the default prefix character is an ampersand. If you are running a script that uses ampersands in text strings, you may want to change the prefix character to something else. If your script does not use substitution variables, you may find it easiest to just turn the feature off completely.

Use SET ESCAPE to specify the character used to escape the substitution variable prefix:

SET ESC[APE] {OFF|ON|escape_char}

where:

You use the escape character when you want to place an ampersand in a command and you don’t want that ampersand interpreted as a substitution variable prefix character. The following example shows a case where this can be a problem:

SQL> SELECT 'O''Reilly & Associates' FROM dual;
Enter value for associates:

The ampersand in front of the word “Associates” causes SQL*Plus to interpret it as substitution variable name. To work around this behavior, you can turn the escape feature on and precede the ampersand with a backslash. Here is an example:

SQL> SET ESCAPE ON
SQL> SELECT 'O''Reilly & Associates' FROM dual;

'O''REILLY&ASSOCIATES
---------------------
O'Reilly & Associates

You can also use the SET ESCAPE command to change the escape character to something other than a backslash.

SET SCAN is an obsolete command that allows you to choose whether or not SQL*Plus scans for substitution variables:

SET SCAN {OFF|ON}

The SET SCAN command duplicates functionality provided by the SET DEFINE command.

The LOBOFFSET setting represents an index into a LONG column. When SQL*Plus displays a LONG, it begins with the character pointed to by LOBOFFSET.

SET LOBOF[FSET] offset

where:

Using the LOBOFFSET setting in conjunction with the LONG setting allows you to print any arbitrary substring from a LONG column.

The LONG setting controls the number of characters displayed by SQL*Plus from any LONG columns returned by a query:

SET LONG long_length

where:

The LONGCHUNKSIZE is a performance-related setting. It controls the number of characters retrieved at one time from a LONG column.

SET LONGC[HUNKSIZE] size

where:

With the default setting of 80 characters, SQL*Plus will need 10 round trips to the database to retrieve an 800-character LONG value. These network round trips take time, so you will tend to get better performance by increasing this setting. If you can afford the memory, make LONGCHUNKSIZE equal to the LONG setting. That way, the entire LONG value will be retrieved in one fetch.

The AUTOTRACE setting is used to control whether or not SQL*Plus displays the execution plan and statistics for each SQL statement as it is executed:

SET AUTOT[RACE] {OFF|ON|TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

where:

See Chapter 8 for comprehensive examples showing how to use the autotrace feature of SQL*Plus. Chapter 8 also shows you how to interpret the execution plan output and describes the operations that may be used in that plan.

The TIMING setting controls whether or not SQL*Plus displays the elapsed time for each SQL statement or PL/SQL block you execute:

SET TIMI[NG] {OFF|ON}

where:

See Chapter 8, for examples of this command.

The AUTORECOVERY option causes the RECOVER command to run without user intervention, as long as the archived log files are in the destination pointed to by the LOG_ARCHIVE_DEST parameter and the names conform to the LOG_ARCHIVE_FORMAT parameter.

SET AUTORECOVERY {OFF|ON}

where:

Chapter 10, talks about recovery in more detail.

The LOGSOURCE setting specifies the location of the archive log files, and is referenced during recovery:

SET LOGSOURCE logpath

where:

The COPYTYPECHECK setting controls whether or not SQL*Plus checks the datatypes when you use the COPY command to move data between two databases:

SET COPYTYPECHECK {OFF|ON}

where:

This option was created specifically for use when copying data to a DB2 database.

The SET EDITFILE command lets you change the name of the work file that is created when you use the EDIT command to edit the SQL statement in the buffer:

SET EDITF[ILE] edit_filename

where:

If you do not include an extension as part of the filename, the current value of the SUFFIX setting will be used as the extension.

The SET INSTANCE command allows you to specify a default database to connect to when you use the CONNECT command without specifying a service name. You cannot issue this command while connected to a database; you must disconnect first.

SET INSTANCE [service_name|LOCAL]

where: