What's New in SQL*Plus?

This section describes new features of SQL*Plus Release 9.2 and provides pointers to additional information. New features information from previous releases is also retained to help those users migrating to the current release.

The following sections describe the new features in SQL*Plus:

• New Features in SQL*Plus 9.2
• New Features in iSQL*Plus 9.2
• Upgrading iSQL*Plus Server from 9.0.1 to 9.2
• New Features in SQL*Plus 9.0.1

New Features in SQL*Plus 9.2

SQL*Plus Release 9.2.0 is a superset of SQL*Plus 9.0.1. This section describes new features introduced in this release of SQL*Plus.

XMLType Support

There is additional support for XMLType in SQL*Plus. The DESCRIBE command now provides details about XMLType columns and usage in functions and procedures. The maximum size of a retrieved XMLType column is determined by SET LONG, and SET LONGCHUNKSIZE determines the size of the increments or chunks SQL*Plus uses to retrieve XMLType data.

SQLPLUS -L Argument

There is a new command-line argument for the SQLPLUS command, SQLPLUS -L which specifies not to reprompt for username or password if the initial connection does not succeed. This can be useful in operating-system scripts that must either succeed or fail. It is also useful for situations where you do not want to be reprompted for connection details if the Server is not running. This option is not available with the SQL*Plus Windows graphical user interface.

_CONNECT_IDENTIFIER DEFINE Variable

There is a new DEFINE variable _CONNECT_IDENTIFIER, which contains the SID as supplied by the user to make a connection where it is available. This allows the connection information to be accessed like any other DEFINE variable. For example, it 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 change your SQL*Plus prompt to display your username and SID, enter:

SET SQLPROMPT '&_CONNECT_IDENTIFIER > '

You can use the DEFINE command to view the _CONNECT_IDENTIFIER definition 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 _CONNECT_IDENTIFIER

You can use UNDEFINE to remove the _CONNECT_IDENTIFIER definition and make it unavailable.

Cause/Action Text for SP2 Error Messages

There is new Cause and Action documentation for SP2- error messages. The UNIX oerr script now recognizes SP2- error prefixes to display the Cause and Action text.

@URL Support

The @URL syntax is now supported on all platforms. Use '@URL' to run a script on any platform, not just Windows.

New Features in iSQL*Plus 9.2

There are changes to the functionality of the iSQL*Plus user interface, and to its look and feel.

UNIX Support

The iSQL*Plus Server is supported on UNIX platforms.

Set System Variables

There is a new iSQL*Plus System Variables screen. This screen provides a graphical interface to the system variables applicable to iSQL*Plus. It allows you to view current settings, and to change one or more settings from a single screen. The default system variable settings are used each time you start a new iSQL*Plus session. You can also use the SET command in the Work screen if you prefer.

History Screen

There is a new iSQL*Plus History screen. This screen allows you to reload one or more scripts that you have previously executed in the same session.

A History entry is created each time you execute a script. The number of entries listed is definable through the Preferences screen. Each iSQL*Plus session has its own history list which is cleared when that session ends.

New Session

There is a new icon called New Session in the iSQL*Plus user interface. The New Session icon starts a new iSQL*Plus session in a separate web browser window. You can log in to the new session as the same user or as any other valid user.

Cancel Script

There is a new Cancel button on the iSQL*Plus Work screen. The Cancel button interrupts the script currently running in your iSQL*Plus session.

iSQL*Plus DBA Login

The iSQL*Plus DBA Login screen contains a Privilege dropdown list to select either SYSDBA or SYSOPER privileged login to perform system administration tasks. This field is not available through the standard iSQL*Plus Login screen. Use the iSQL*Plus DBA URL to access the DBA Login screen.

Once logged in with DBA privileges, the iSQL*Plus screens have the iSQL*Plus DBA logo to clearly distinguish this administrative session from standard iSQL*Plus sessions.

iSQL*Plus Extension for Windows

The iSQL*Plus Extension for Windows provides a context menu to load or execute local SQL scripts in iSQL*Plus. The iSQL*Plus Extension for Windows works with .SQL files and is activated by right clicking a .SQL file in Windows Explorer.

After you have configured an iSQL*Plus Server, you can use the context menu to:

Language Support in iSQL*Plus Extension for Windows

On Windows XP, Windows 2000 and Windows NT, the iSQL*Plus Extension for Windows supports the following languages:

On Windows 98, the iSQL*Plus Extension for Windows is only available in English.

Upgrading iSQL*Plus Server from 9.0.1 to 9.2

There are some changes between iSQL*Plus in Release 9.0.1 and 9.2.

iSQL*Plus URL

The default Oracle HTTP Server installation now specifies a port number. If the port number was not recorded during installation, check the Port and Listen directives in httpd.conf. On Windows, httpd.conf is located in %ORACLE_HOME%\Apache\Apache\conf. On UNIX, httpd.conf is located in ORACLE_HOME/Apache/Apache/bin/conf on UNIX.

The URL for iSQL*Plus Release 9.2 is

http://machine_name.doman:port/isqlplus

You must ensure that any HTML links to iSQL*Plus are updated to the new URL.

iSQL*Plus Configuration File

The format of the iSQL*Plus configuration file, isqlplus.conf, has changed. Any user customizations can be manually merged into the default iSQL*Plus Release 9.2 configuration file. On Windows, isqlplus.conf is located in %ORACLE_HOME%\sqlplus\admin. On UNIX, isqlplus.conf is located in $ORACLE_HOME/sqlplus/admin.

iSQL*Plus Release 9.0.1 Parameters

There were two parameters that could be configured in iSQL*Plus Release 9.0.1.

• iSQLPlusTimeOut This parameter has been removed. Expiry is now always enabled.
• iSQLPlusTimeOutInterval The new default value is 30 minutes. This directive is now a parameter to the FastCgiServer directive.

iSQL*Plus Release 9.2 Parameters

There are several new parameters for sizing and tuning iSQL*Plus Release 9.2. You can edit the isqlplus.conf configuration file directly to change settings for iSQL*Plus Server parameter settings:

• iSQLPlusNumberOfThreads
• iSQLPlusLogLevel
• iSQLPlusTimeOutInterval
• iSQLPlusHashTableSize
• iSQLPlusConnectIdList
• -idle-timeout

For information about these parameters, see "Configuring the Middle Tier: iSQL*Plus Server".

Security

HTTP Server authentication usernames for DBA connections need to be added to the iSQL*Plus 9.2 password file iplusdba.pw using the htpasswd utility.

If you had HTTP Authentication for user connections enabled in 9.0.1, verify the new isqlplus.conf format is followed and add users to the specified password file.

You may wish to allow access only to a specified list of database servers by enabling iSQLPlusConnectIdList

For more information, see Chapter 10, "SQL*Plus Security".

Style Sheet

The style sheet iplus.css has changed. It is recommended that you use the default 9.2 style sheet as the basis for iSQL*Plus Release 9.2 customization.

New Features in SQL*Plus 9.0.1

SQLPLUS Command-line Switches

The version and usage command-line switches have been standardized as:

-V[ERSION]
-H[HELP]

Invalid options give the usage message corresponding to -HELP. The "-" and "-?" options have been obsoleted.

iSQL*Plus

iSQL*Plus is a browser-based interface to SQL*Plus. iSQL*Plus is only available on the Microsoft Windows operating system in this release.

START, @, @@

New commands were added to read and execute a SQL script from a Uniform Resource Locator (URL). The ability to read and execute a script from a URL is available in the @, @@ and START commands. The syntax is:

@{url|file_name[.ext]} [arg...]
@@file_name[.ext]
STA[RT] {url|file_name[.ext]} [arg...]

"url" specifies a script to run on the specified web server. SQL*Plus supports HTTP, FTP and gopher protocols. Pass variable values to the script in the usual way, for example:

http://machine_name.domain:port/script.sql value1 value2

Note: This feature is only available on Microsoft Windows.

SET APPINFO

The default for SET APPINFO is now OFF. This allows some privileged DBA operations to be executed when the database is not fully configured. Add SET APPINFO ON to glogin.sql to get the previous behavior.

SET SQLPLUSCOMPATIBILITY

There is a new SET command, SET SQLPLUSCOMPAT[IBILITY] {x.y[.z]}. It sets the behavior of the VARIABLE command to that of the version specified by SQLPLUSCOMPATIBILITY.

In later releases of SQL*Plus, SQLPLUSCOMPATIBILITY may support features other than VARIABLE. The SQLPLUSCOMPATIBILITY command can be used to help keep the expected behavior of a release.

HR Sample Schema

The SQL*Plus documentation uses a new set of sample schemas for all examples. See the Oracle9i Sample Schemas guide for details on access, implementation and use of the sample schemas.