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 SET SERVEROUTPUT ON

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.

SHOW RECYCLEBIN

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.

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:

• _DATE contains the current date or a user defined fixed string.

• _PRIVILEGE contains the privilege level of the current connect. This will be either AS SYSDBA, AS SYSOPER or blank to indicate a normal connection.

• _USER contains the username as supplied by the user to make the current connection. This is the same as the output from the SHOW USER command.

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:

SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER > "

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:

DEFINE

or

DEFINE _PRIVILEGE

You can use UNDEFINE to remove variable definitions.

APPEND, CREATE and REPLACE extensions to SPOOL

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 }

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:

• Missing usernames

• Missing FROM and TO clauses

• FROM and TO clauses that are too long

• Password input errors

  
  

  See Also: Appendix B, " SQL*Plus COPY Command". Chapter 14, "SQL*Plus Error Messages".

  
  

PAGESIZE Default

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.

SQLPLUS -C[OMPATIBILITY] Argument

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

SET SQLPLUSCOMPATIBILITY 9.2

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