Obsolete SQL*Plus Commands

C.1 SQL*Plus Obsolete Command Alternatives

Obsolete commands are available in current releases of SQL*Plus. In future releases, they may only be available by setting the SQLPLUSCOMPATIBILITY variable. You should modify scripts using obsolete commands to use the alternative commands.

Obsolete Command	Alternative Command	Description of Alternative Command
BTITLE (old form)	BTITLE	Places and formats a title at the bottom of each report page or lists the current BTITLE definition.
COLUMN DEFAULT	COLUMN CLEAR	Resets column display attributes to default values.
DOCUMENT	REMARK	Places a comment which SQL*Plus does not interpret as a command.
NEWPAGE	SET NEWP[AGE] {1 \| n \| NONE}	Sets the number of blank lines to be printed from the top of each page to the top title.
SET BUFFER	EDIT	Enables the editing of the SQLPlus command buffer, or the contents of a saved file. Use the SQLPlus SAVE, GET, @ and START commands to create and use external files.
SET COMPATIBILITY	none	Obsolete
SET CLOSECURSOR	none	Obsolete
SET DOCUMENT	none	Obsolete
SET MAXDATA	none	Obsolete
SET SCAN	SET DEF[INE] {& \| c \| ON \| OFF}	Sets the character used to prefix substitution variables.
SET SPACE	SET COLSEP { \| text}	Sets the text to be printed between SELECTed columns.
SET TRUNCATE	SET WRA[P] {ON \| OFF}	Controls whether SQL*Plus truncates a SELECTed row if it is too long for the current line width.
SHOW LABEL	none	Obsolete
TTITLE (old form)	TTITLE	Places and formats a title at the top of each report page or lists the current TTITLE definition.

C.2 BTI[TLE] text (obsolete old form)

Displays a title at the bottom of each report page.

The old form of BTITLE offers formatting features more limited than those of the new form, but provides compatibility with UFI (a predecessor of SQL*Plus). The old form defines the bottom title as an empty line followed by a line with centered text. See TTI[TLE] text (obsolete old form) for more details.

C.3 COL[UMN] {column|expr} DEF[AULT] (obsolete)

Resets the display attributes for a given column to default values.

Has the same effect as COLUMN CLEAR.

C.4 DOC[UMENT] (obsolete)

Begins a block of documentation in a script.

For information on the current method of inserting comments in a script, see the section About Placing Comments in Scripts and the REMARK command.

After you type DOCUMENT and enter [Return], SQL*Plus displays the prompt DOC> in place of SQL> until you end the documentation. The "pound" character (#) on a line by itself ends the documentation.

If you have set DOCUMENT to OFF, SQL*Plus suppresses the display of the block of documentation created by the DOCUMENT command. For more information, see SET DOC[UMENT] {ON|OFF} (obsolete).

C.5 NEWPAGE [1|n] (obsolete)

Advances spooled output n lines beyond the beginning of the next page.

See SET NEWP[AGE] {1 | n | NONE} for information on the current method for advancing spooled output.

C.6 SET BUF[FER] {buffer|SQL} (obsolete)

Makes the specified buffer the current buffer.

Initially, the SQL buffer is the current buffer. SQL*Plus does not require the use of multiple buffers; the SQL buffer alone should meet your needs.

If the buffer name you enter does not exist, SET BUFFER defines (creates and names) the buffer. SQL*Plus deletes the buffer and its contents when you exit SQL*Plus.

Running a query automatically makes the SQL buffer the current buffer. To copy text from one buffer to another, use the GET and SAVE commands. To clear text from the current buffer, use CLEAR BUFFER. To clear text from the SQL buffer while using a different buffer, use CLEAR SQL.

C.7 SET COM[PATIBILITY]{V7 | V8 | NATIVE} (obsolete)

Specifies the version of the SQL language parsing syntax to use.

Set COMPATIBILITY to V7 for Oracle7, or to V8 for Oracle8 or later. COMPATIBILITY always defaults to NATIVE. Set COMPATIBILITY for the version of Oracle Database SQL syntax you want to use on the connected database, otherwise.

The default compatibility setting, NATIVE, is the most relevant setting for modern Oracle databases.

For information about SQL*Plus version compatibility settings, see SET SQLPLUSCOMPAT[IBILITY] {x.y[.z]}.

Example

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

SET COMPATIBILITY V7
START SALARY

After running the file, reset compatibility to NATIVE to run scripts created for Oracle Database 10g:

SET COMPATIBILITY NATIVE

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

C.8 SET CLOSECUR[SOR] {ON|OFF} (obsolete)

Sets the cursor usage behavior.

On or OFF sets whether or not the cursor will close and reopen after each SQL statement. This feature may be useful in some circumstances to release resources in the database server.

C.9 SET DOC[UMENT] {ON|OFF} (obsolete)

Displays or suppresses blocks of documentation created by the DOCUMENT command.

SET DOCUMENT ON causes blocks of documentation to be echoed to the screen. Set DOCUMENT OFF suppresses the display of blocks of documentation.

See DOC[UMENT] (obsolete) for information on the DOCUMENT command.

C.10 SET MAXD[ATA] n (obsolete)

Sets the maximum total row width that SQL*Plus can process.

In SQL*Plus, the maximum row width is now unlimited. Any values you set using SET MAXDATA are ignored by SQL*Plus.

C.11 SET SCAN {ON|OFF} (obsolete)

Controls scanning for the presence of substitution variables and parameters. OFF suppresses processing of substitution variables and parameters; ON enables normal processing.

ON functions in the same manner as SET DEFINE ON.

C.12 SET SPACE {1|n} (obsolete)

Sets the number of spaces between columns in output. The maximum value of n is 10.

The SET SPACE 0 and SET COLSEP " commands have the same effect. This command is obsoleted by SET COLSEP, but you can still use it for backward compatibility. You may prefer to use COLSEP because the SHOW command recognizes COLSEP and does not recognize SPACE.

C.13 SET TRU[NCATE] {ON|OFF} (obsolete)

Controls whether SQL*Plus truncates or wraps a data item that is too long for the current line width.

ON functions in the same manner as SET WRAP OFF, and vice versa. You may prefer to use WRAP because the SHOW command recognizes WRAP and does not recognize TRUNCATE.

C.14 TTI[TLE] text (obsolete old form)

Displays a title at the top of each report page.

The old form of TTITLE offers formatting features more limited than those of the new form, but provides compatibility with UFI (a predecessor of SQL*Plus). The old form defines the top title as a line with the date left-aligned and the page number right-aligned, followed by a line with centered text and then a blank line.

The text you enter defines the title TTITLE displays.

SQL*Plus centers text based on the size of a line as determined by SET LINESIZE. A separator character (|) begins a new line; two line separator characters in a row (||) insert a blank line. You can change the line separator character with SET HEADSEP.

You can control the formatting of page numbers in the old forms of TTITLE and BTITLE by defining a variable named "_page". The default value of _page is the formatting string "page &P4". To alter the format, you can DEFINE _page with a new formatting string as follows:

SET ESCAPE / SQL> DEFINE _page = 'Page /&P2'

This formatting string will print the word "page" with an initial capital letter and format the page number to a width of two. You can substitute any text for "page" and any number for the width. You must set escape so that SQL*Plus does not interpret the ampersand (&) as a substitution variable. See SET ERRORL[OGGING] {ON | OFF} [TABLE [schema.]tablename] [TRUNCATE] [IDENTIFIER identifier] for more information on setting the escape character.

SQL*Plus interprets TTITLE in the old form if a valid new-form clause does not immediately follow the command name.

If you want to use CENTER with TTITLE and put more than one word on a line, you should use the new form of TTITLE. For more information see the TTITLE command.

Example

To use the old form of TTITLE to set a top title with a left-aligned date and right-aligned page number on one line followed by SALES DEPARTMENT on the next line and PERSONNEL REPORT on a third line, enter

TTITLE 'SALES DEPARTMENT|PERSONNEL REPORT'

C.15 About the PRODUCT_USER_PROFILE Table

Note:

Starting in Oracle Database 18c, the SQL*Plus table PRODUCT_USER_PROFILE (PUP) table is deprecated.

The only use for the PRODUCT_USER_PROFILE (PUP) table is to provide a mechanism to control product-level security for SQL*Plus. Starting with Oracle Database 18c, this mechanism is no longer relevant. This SQL*Plus product-level security feature will be unavailable in Oracle Database 19c.

Oracle recommends that you protect data by using Oracle Database settings, so that you ensure consistent security across all client applications.

SQL*Plus uses the PRODUCT_USER_PROFILE (PUP) table, a table in the SYSTEM account, to provide product-level security that supplements the user-level security provided by the SQL GRANT and REVOKE commands and user roles.

DBAs can use the PUP table to disable certain SQL and SQL*Plus commands in the SQL*Plus environment on a per-user basis. SQL*Plus—not Oracle Database—enforces this security. DBAs can even restrict access to the GRANT, REVOKE, and SET ROLE commands to control users' ability to change their database privileges.

SQL*Plus reads restrictions from the PUP table when a user logs in to SQL*Plus and maintains those restrictions for the duration of the session. Changes to the PUP table will only take effect the next time the affected users log in to SQL*Plus.

When SYSTEM, SYS, or a user authenticating with SYSBACKUP, SYSDBA, SYSDG, SYSKM or SYSOPER privileges connects or logs in, SQL*Plus does not read the PUP table. Therefore, no restrictions apply to these users.

The PUP table applies only to the local database. If accessing objects on a remote database through a database link, the PUP table for the remote database does not apply. The remote database cannot extract the username and password from the database link in order to determine that user's profile and privileges.

C.15.1 About Creating the PUP Table

You can create the PUP table by running the script named PUPBLD with the extension SQL as SYSTEM. The exact format of the file extension and the location of the file are system dependent. See your DBA for more information.

Note:

If the table is created incorrectly, all users other than privileged users will see a warning when connecting to Oracle Database that the PUP table information is not loaded.

C.15.2 PUP Table Structure

The PUP table has the following columns:

PRODUCT                 NOT NULL VARCHAR2 (30)
USERID                  VARCHAR2(30)
ATTRIBUTE               VARCHAR2(240)
SCOPE                   VARCHAR2(240)
NUMERIC_VALUE           NUMBER(15,2)
CHAR_VALUE              VARCHAR2(240)
DATE_VALUE              DATE
LONG_VALUE              LONG

C.15.3 Description and Use of PUP Columns

The following list describes each column in the PUP table:

PUP Column	Description
PRODUCT	Must contain the product name (in this case "SQL*Plus"). You cannot enter wildcards or NULL in this column.
USERID	Must contain the username (uppercase) of the user for whom you wish to disable the command. To disable the command for more than one user, use SQL wild cards (%) or make multiple entries. Thus, all of the following entries are valid: HR CLASS1 CLASS% (all users whose names start with CLASS) % (all users)
ATTRIBUTE	Must contain the name (in uppercase) of the SQL, SQLPlus, or PL/SQL command to disable (for example, RUN). If you are disabling a role, it must contain the character string "ROLES". You cannot enter a wildcard. See PUP Table Administration for a list of SQL and SQLPlus commands you can disable. See About Creating and Controlling Roles for information on how to disable a role.
SCOPE	Not used, it is recommended that you enter NULL. Other products may store specific file restrictions or other data in this column.
NUMERIC_VALUE	Not used, it is recommended that you enter NULL. Other products may store numeric values in this column.
CHAR_VALUE	Must contain the character string "DISABLED" to disable a SQL, SQL*Plus, or PL/SQL command. If you are disabling a role, it must contain the name of the role you wish to disable. You cannot use a wildcard. See About Disabling Commands with SQLPLUS -RESTRICT for information on disabling a role.
DATE_VALUE	Not used, it is recommended that you enter NULL. Other products may store DATE values in this column.
LONG_VALUE	Not used, it is recommended that you enter NULL. Other products may store LONG values in this column.

C.15.4 PUP Table Administration

The DBA username SYSTEM owns and has all privileges on the PUP table. Other Oracle Database usernames should have only SELECT access to this table, which enables a view of restrictions for that username and those restrictions assigned to PUBLIC. The script PUPBLD.SQL, when run, grants SELECT access on the PUP table to PUBLIC.