SQL*Plus Release Notes

Release 9.2

April 2002

Part No. A96545-01

This document summarizes requirements, differences between SQL*Plus and its documented functionality, new features in this release and support information. It contains the following topics:

• Certification

• General Issues and Workarounds

• New Features in SQL*Plus

• New Features in Previous Releases

• Bugs Fixed

• Support

• Desupport Notices

• Documentation Errata

1 Certification

SQL*Plus (including iSQL*Plus) is certified against the operating systems set out in the operating-system specific Oracle documentation.

SQL*Plus (including iSQL*Plus) is certified against Oracle9i Release 2 (9.2) and all supported versions of the Oracle Server.

2 General Issues and Workarounds

This section describes general issues and their workarounds for SQL*Plus.

2.1 SQL*Plus Commands not in iSQL*Plus

Appendix D of the SQL*Plus User's Guide and Reference lists SQL*Plus commands which have no context in iSQL*Plus, or have not been implemented for other reasons. Attempting to use an unsupported command raises an iSQL*Plus error message.

2.2 Windows iSQL*Plus Server Port in Use

After Oracle9i installation on Windows, if you are unable to connect to your iSQL*Plus Server, check that your Oracle HTTP Server is running, and that you are using the correct URL to connect to your iSQL*Plus Server. If you are still unable to connect, it may be because the port that the iSQL*Plus Server is attempting to use is already in use by another application. There is no consistent message to indicate that the port is already in use. A message, if any, depends on the application using the port.

To determine the port number that the iSQL*Plus Server is attempting to use, do the following:

1. Open the iSQL*Plus configuration file located at

   %ORACLE_HOME%\sqlplus\admin\isqlplus.conf

2. Find the following block in the iSQL*Plus configuration file.

   ## Setup the iSQL*Plus FastCGI application.
   #<IfModule mod_fastcgi.c>FastCgiServer c:\oracle\bin\isqlplus -port 8228 -initial-env iSQLPlusNumberOfThreads=20 -initial-env iSQLPlusTimeOutInterval=30 -initial-env iSQLPlusLogLevel=warn -idle-timeout 3600
   </IfModule>

   The number specified by the -port parameter, in this case shown as 8228, is the port number that the iSQL*Plus Server is attempting to use.

To view currently used ports and determine if the iSQL*Plus Server is trying to use a port that is already in use, run the following command in a Windows command prompt:

netstat -an

If there is another application using the same port, you need to change the port used by the iSQL*Plus Server to a number that is not in use. By convention, it is recommended that you use a port number above 2000, and that you do not use 80 or 8080 as they are usually used by web services. A port number can be any unique integer number.

To change the port number used by the iSQL*Plus Server, do the following:

1. Stop the Oracle HTTP Server.

2. Open the iSQL*Plus configuration file located at

   %ORACLE_HOME%\sqlplus\admin\isqlplus.conf

3. Find the following block in the iSQL*Plus configuration file. Note that the line beginning FASTCGI is a continuous single line.

   ## Setup the iSQL*Plus FastCGI application.
   #<IfModule mod_fastcgi.c>
   FastCgiServer c:\oracle\bin\isqlplus -port 8228 -initial-env iSQLPlusNumberOfThreads=20 -initial-env iSQLPlusTimeOutInterval=30 -initial-env iSQLPlusLogLevel=warn -idle-timeout 3600
   </IfModule>

4. Replace the port number specified by the -port parameter, in this case shown as 8228, with a unique port number that you want the iSQL*Plus Server to use.

5. Save the iSQL*Plus configuration file.

6. Restart the Oracle HTTP Server.

2.3 Connecting to Oracle7 from SQL*Plus 9.2

Connections to an Oracle7 database from SQL*Plus 9.2 are not supported.

2.4 Cannot WORD_WRAP NCLOB Columns

NCLOB columns cannot be formatted with WORD_WRAPPING. When the "COLUMN" command is used to format NCLOB columns with the "WORD_WRAP" option, the column data will WRAP instead of WORD_WRAP.

2.5 Non-Blocking Database Calls

Non-blocking database calls to the Oracle9i Server are not supported on Windows. Therefore there is no cancel dialog while statements are executing in the SQL*Plus for Windows graphical user interface (GUI).

2.6 Querying of LONG Columns

Querying of LONG columns requires enough local memory to store the amount of data, as specified by the value of SET LONG command, irrespective of the value of the SET LONGCHUNKSIZE command. This restriction does not apply when querying LOBs.

2.7 Privileged Connection Errors

Some privileged connections to the Oracle9i Server may generate errors if SET APPINFO commands have been put in the global initialization file (glogin.sql) or a local initialization file (login.sql).

2.8 Loading a URL from iSQL*Plus Work screen

Loading a script from a URL in the Work screen of iSQL*Plus may not work in some point releases of Netscape Navigator 4.x. You may get an error "SP2-0871: No script to load" due to a problem with Navigator. A work around is to load the file into another browser window and cut and paste the script into the Work screen. If you just want to execute the script without editing it, enter "@http://..." in the Input area of the Work screen.

3 New Features in SQL*Plus

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.

3.1 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.

3.2 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 for Windows graphical user interface.

3.3 _CONNECT_IDENTIFIER DEFINE Variable

There is a new DEFINE variable, _CONNECT_IDENTIFIER. Where available, it contains the connection identifier as supplied by the user to make a connection. This allows the connection information to be accessed like any other DEFINE variable.

3.4 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.

3.5 @URL Support

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

3.6 iSQL*Plus

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

3.6.1 UNIX Support

The iSQL*Plus Server is supported on UNIX platforms.

3.6.2 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.

3.6.3 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.

3.6.4 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 as the same user or as any other valid user.

3.6.5 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.

3.6.6 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.

3.6.7 iSQL*Plus Server Configuration Parameters

In addition to the existing iSQLPlusTimeOutInterval parameter, there are six new iSQL*Plus Server parameters. You can change the values of the iSQL*Plus Server parameters in the isqlplus.conf configuration file which is located in the $ORACLE_HOME/sqlplus/admin/ directory.

iSQLPlusLogLevel

Enables iSQL*Plus to maintain log files and determines to what level log files are maintained. The default log file location is $ORACLE_HOME/sqlplus/log/isqlplus/log.xml.

iSQL*Plus must be able to write to the $ORACLE_HOME/sqlplus/log directory. This directory has owner write permissions. If iSQL*Plus is running as the nobody user and therefore cannot write to the log file, you must manually give iSQL*Plus write permissions on the log file. It is not recommended to grant world write permission to the file except for temporary testing or tracing purposes.

iSQLPlusNumberOfThreads

Sets the maximum number of simultaneous HTTP requests that can be handled by the iSQL*Plus Server.

iSQLPlusHashTableSize

Sets the maximum number of concurrent iSQL*Plus sessions that can be handled by the iSQL*Plus Server. The default value is derived from the value of the iSQLPlusNumberOfThreads parameter.

iSQLPlusConnectIdList

Replaces the Connection Identifier text field on the Login screen with a drop-down list of databases that users can access in iSQL*Plus. This allows greater security for iSQL*Plus Servers in hosted environments.

iSQLPlusAllowUserEntMap

Controls whether iSQL*Plus permits users to change the SET MARKUP HTML ENTMAP and COLUMN ENTMAP settings.

In HTML, entity mapping replaces characters of special significance with printable representations of those characters. By default, entity mapping is enabled, and iSQLPlusAllowUserEntMap is set to none, preventing users from changing the entity mapping settings, and preventing the use of user defined HTML in iSQL*Plus output.

-idle-timeout

Sets the time the Oracle HTTP Server waits for results from iSQL*Plus.

3.6.8 iSQL*Plus Online Help Language Support

The iSQL*Plus online help is available in the following languages:

Brazilian Portuguese
French
German
Italian
Japanese
Korean
Simplified Chinese
Spanish

3.7 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:

• Run or load local scripts accessible from your workstation

• Select the server to use from a list of configured iSQL*Plus Servers

• Choose the double click file association for .SQL files

• Choose the language to use from a list of configured languages

3.7.1 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:

Brazilian Portuguese
English
French/Canadian French
German
Italian
Japanese
Korean
Simplified Chinese
Spanish/LA Spanish
Traditional Chinese

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

4 New Features in Previous Releases

This section lists new features introduced to SQL*Plus in previous releases.

4.1 New Features in SQL*Plus 9.0.1 Production

• iSQL*Plus

• SET APPINFO

• Command line switches

• SET SQLPLUSCOMPATIBILITY

• Read script from Uniform Resource Identifier

4.2 New Features in SQL*Plus 8.1.7 Production

• SQLPLUS -MARKUP and SET MARKUP

• COLUMN ENTMAP {ON|OFF}

4.3 New Features in SQL*Plus 8.1.6 Production

• SQLPLUS -MARKUP

• SET MARKUP

• SHOW MARKUP

• SQLPLUS -RESTRICT

• Help System

4.4 New Features in SQL*Plus 8.1.5 Production

• SET SQLBLANKLINES

• SHOW SQLBLANKLINES

• SQL*Plus Messages

4.5 New Features in SQL*Plus 8.1.4 Beta

• SP2- prefix for SQL*Plus Messages

4.6 New Features in SQL*Plus 8.1.3 Beta

• ARCHIVE LOG

• DESCRIBE

• RECOVER

• SET AUTORECOVERY

• SET DESCRIBE

• SET INSTANCE

• SET LOGSOURCE

• SHOW AUTORECOVERY

• SHOW DESCRIBE

• SHOW INSTANCE

• SHOW LOGSOURCE

• SHOW PARAMETERS

• SHOW SGA

• SHUTDOWN

• STARTUP

4.7 New Features in SQL*Plus 8.0.5 Production

• CONNECT supports mode of connection

4.8 New Features in SQL*Plus 8.0.3 Production

• CREATE TYPE

• DESCRIBE TABLE, VIEW, PROCEDURE, FUNCTION, SYNONYM, PACKAGE, OBJECT TYPE

• PASSWORD

• CONNECT

• SET MAXDATA and CLOSECURSOR commands have been obsoleted

• SET COMPATIBILITY command modified

• SET CONSTRAINT command is supported

• SET NEWPAGE command has the new keyword NONE

• SET LOBOFFSET

• VARIABLE supports NCHAR, NVARCHAR2, NCLOB, CLOB

• SHOW ERRORS

• ATTRIBUTE

• EXIT

• Version variable _SQLPLUS_RELEASE