Skip Headers

SQL*Plus® User's Guide and Reference
Release 10.1

Part Number B12170-01
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to previous page
Go to next page
View PDF

What's New in SQL*Plus?

This section describes new features of SQL*Plus Release 10.1 and provides pointers to additional information.

New Features in SQL*Plus and iSQL*Plus 10.1

SQL*Plus Release 10.1 is a superset of SQL*Plus 9.2. This section describes new features introduced in SQL*Plus Release 10.1.

Change in DESCRIBE Behavior

Previously, DESCRIBE on an invalidated object failed with error "ORA-24372: invalid object for describe" and continued to fail even if the object had since been validated. DESCRIBE now automatically validates the object and continues if the validation is successful.

Glogin and Login Calls

Previously, the SQL*Plus site and user profile files, glogin and login, were run when SQL*Plus was started with a username and password, or with /NOLOG. The profile files, glogin and login are now also run after successful CONNECT commands.

Whitespace Support in File and Path Names in Windows

In Windows, whitespace can be included in file names and paths, in particular, START, @, @@, and RUN commands, and SPOOL, SAVE and EDIT commands. To reference files or paths containing spaces, enclose the name or path in quotes. For example:

SPOOL "Monthly Report.sql"
SAVE "c:\program files\ora10\scripts\Monthly Report.sql"   


Changes to the way output from nested PL/SQL functions is displayed may change the appearance of output with SET SERVEROUTPUT ON. SET SERVEROUTPUT ON now correctly shows output (DBMS_OUTPUT.PUT_LINE) from a PL/SQL function nested inside a SQL statement. Previously, output from a nested PL/SQL function did not display until a subsequent PL/SQL function was executed.

See Also:



A new option RECYCLEBIN original_name has been added to the SHOW command. It enables users to view tables that are available for purging or reverting using the new PURGE and FLASHBACK BEFORE DROP commands.

See Also:


SET PROMPT Support for Substitution Variables

You can now use substitution variables in the SQL*Plus command-line prompt to display, for example, the database and server you are connected to, or other information available through a substitution variable you choose. This is similar to the substitution variable usage in TTITLE.

Predefined Variables: _DATE, _PRIVILEGE, _USER

There are three new predefined variables:

These variables can be accessed like any other substitution variable. For example, they could be used in TTITLE, in '&' substitution variables, or as your SQL*Plus command-line prompt by using the SET SQLPROMPT command. For example, to make your prompt always show your username (_USER), the @ symbol, and then your connection identifier (_CONNECT_IDENTIFIER) during your session, enter:


You can view the predefined variable definitions in the same way as you view other DEFINE definitions, using the DEFINE command with no arguments, or with the specific argument you wish to display, for example:




You can use UNDEFINE to remove variable definitions.


The SPOOL command has been enhanced. You can now append to, or replace an existing file, where previously you could only use SPOOL to create (and replace) a file. Replace is the default. The new SPOOL command syntax is as follows:

SPOOL { file_name[.ext] [CRE[ATE]|REP[LACE]|APP[END]]| OFF | OUT }

See Also:


Windows Specific Information

The SQL*Plus Getting Started for Windows guide has been discontinued. Windows specific information is now included in this guide.

COPY Command Messages and Prompts

There are new error messages for the following COPY command errors:


The default value of PAGESIZE has been changed from 24 to 14.

SQL*Plus Site Profile and User Profile Changes

SET PAGESIZE 14 and SET SQLPLUSCOMPATIBILITY 8.1.7 have been removed from the Site Profile (glogin.sql). As the new default for pagesize has been changed from 24 to 14, the default value of 14 effectively remains unchanged. The default for SQLPLUSCOMPATIBILITY is 10.1.


There is a new command-line argument for the SQLPLUS command, SQLPLUS -C x.y.z which specifies the value of the SQLPLUSCOMPATIBILITY system variable, for example:

sqlplus -c 9.2

This has the same effect as using


It is not to be confused with the SET COMPATIBILITY command which sets the SQL language version.