Skip Headers
SQL*Plus Release Notes
Release 10.1
Part Number B12172-01 |
|
SQL*Plus
Release Notes
Release 10.1
December 2003
Part No. B12172-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:
1 Certification
SQL*Plus (including iSQL*Plus) is certified against the operating systems set out in the operating-system specific Oracle Database documentation.
SQL*Plus (including iSQL*Plus) is certified against Oracle Database 10g Release 1 (10.1) and all supported versions of the Oracle Server.
2 Migrating from iSQL*Plus 9.2 to iSQL*Plus 10.1
The standardization of iSQL*Plus to use Oracle Containers for Java (OC4J) has altered some iSQL*Plus configuration and interfaces. This section is a summary of the changes. The SQL*Plus User's Guide and Reference has full details of iSQL*Plus 10.1 configuration and use.
-
The method of prompting for substitution variables has changed. In iSQL*Plus 9.2, a page prompting for values of all substitution variables in the script was displayed, then the script was executed with the supplied values.In iSQL*Plus 10.1, the script begins executing, and a prompt for a value for each undefined substitution variable is displayed as it is encountered in the script. This makes iSQL*Plus substitution variable prompting similar to command-line SQL*Plus.
If required, an HTML form calling an iSQL*Plus dynamic report can be used to prompt for multiple values in one screen prior to running a script.
-
The ACCEPT command is now supported and prompts for a value.
-
iSQL*Plus now displays script output on multiple pages by default. You can set output to be displayed on a single page, or on multiple pages using Preferences > Interface Configuration > Output Page Setup.
When iSQL*Plus is generating a dynamic report, or is set to save to an HTML file, users are not prompted for any script input, and output is set to a single page. This is the same behavior as iSQL*Plus 9.2.
-
iSQL*Plus output can no longer be set to be displayed in a separate web browser window.
-
Session handling is now performed by OC4J. The session handling in OC4J brings iSQL*Plus sessions in line with current web usage. With some browsers, it may no longer be possible for a single machine user to have multiple browser windows open to the same iSQL*Plus Server. You may be able to open more than one window, however, they might behave as a single iSQL*Plus user session and values changed in one window will affect the other. Different machine users can still have simultaneous sessions to an iSQL*Plus Server.
Each browser handles sessions differently. For example, starting a second Internet Explorer browser via the Windows Start menu allows a user to have multiple concurrent sessions to an iSQL*Plus server. Opening a new window using the Internet Explorer File > New > Window option does not.
-
There is no New session button.
3 SQL*Plus Instant Client
SQL*Plus Instant Client is optionally available for SQL*Plus command-line interfaces that support the OCI Instant Client. It offers all the functionality of SQL*Plus command-line, without the need for a traditional ORACLE_HOME installation. SQL*Plus Instant Client is easier to install, and uses significantly less disk space compared to a traditional ORACLE_HOME installation.
See the Oracle Call Interface Programmer's Guide for more information on the OCI Instant Client.
3.1 SQL*Plus Instant Client Installation
This Table shows the only Oracle client-side files required to deploy the SQL*Plus Instant Client.
|
Platform |
Description |
| UNIX |
Windows |
| sqlplus |
sqlplus.exe |
SQL*Plus executable |
| libsqlplus.so |
not applicable |
SQL*Plus library |
| glogin.sql |
glogin.sql |
SQL*Plus site profile |
| libclnstsh.so.10.1 |
oci.dll |
Client code library |
| libociei.so |
oraociei10.dll |
OCI Instant Client data shared library |
| libnnz10.so |
orannzsbb10.dll |
Security library |
SQL*Plus Instant Client 10.1 can be installed in two ways:
-
-
By choosing the Administrator install option from the Oracle Database 10g Client CD. After the installation completes, you must create a new directory and copy the SQL*Plus and OCI Instant Client files to it. All libraries must be copied from the same ORACLE_HOME.
On UNIX copy these files:
$ORACLE_HOME/instantclient/libociei.so
$ORACLE_HOME/lib/libclntsh.so.10.1
$ORACLE_HOME/lib/libnnz10.so
$ORACLE_HOME/lib/libsqlplus.so
$ORACLE_HOME/bin/sqlplus
$ORACLE_HOME/sqlplus/admin/glogin.sql
On Windows copy these files:
%ORACLE_HOME%\instantclient\oraociei10.dll
%ORACLE_HOME%\bin\oci.dll
%ORACLE_HOME%\bin\orannzsbb10.dll
%ORACLE_HOME%\bin\sqlplus.exe
%ORACLE_HOME%\sqlplus\admin\glogin.sql
After the required files are in one directory, for example /home/myic on UNIX, or c:\myic on Windows, perform these steps:
-
If your operating system requires a shared library path such as LD_LIBARAY_PATH, LIBPATH or SHLIB_PATH, add the name of the directory containing the Instant Client files, and remove any other Oracle directories.
For example on Solaris:
LD_LIBRARY_PATH=/home/myic:${LD_LIBRARY_PATH}
export LD_LIBRARY_PATH
-
Add the directory to the PATH environment variable. This is mandatory on Windows. If it is not set on UNIX, then an absolute or relative path must be used to start the SQL*Plus executable. Remove any other Oracle directories from PATH.
For example on UNIX:
PATH=/home/myic:${PATH}
export PATH
For example in a Windows console window:
set PATH=c:\myic;%PATH%
-
Set SQLPATH to the directory. This lets SQL*Plus find glogin.sql.
For example on UNIX:
SQLPATH=/home/myic:${SQLPATH}
export SQLPATH
For example in a Windows console window:
set SQLPATH=c:\myic;%SQLPATH%
SQL*Plus is now ready to run. Note that no ORACLE_HOME or ORACLE_SID environment variables need to be set.
3.2 Connecting to a Database with SQL*Plus Instant Client
SQL*Plus Instant Client is always 'remote' from any database server. To connect to a database you must specify it using an Oracle Net connection identifier.
Example using an Easy Connection Identifier:
sqlplus hr/your_password@//mymachine.mydomain:port/MYDB
Example using a Net Service Name:
sqlplus hr/your_password@MYDB
Net Service Names can be stored in a number of places, including in Oracle Names. See the Net Services Reference Guide for more information.
If you want to use Net Service Names configured in a local Oracle Net tnsnames.ora file, then set the environment variable TNS_ADMIN to the directory containing the tnsnames.ora file. For example, on UNIX, if your tnsnames.ora file is in /home/user1 and it defines the Net Service Name MYDB2:
TNS_ADMIN=/home/user1
export TNS_ADMIN
sqlplus hr/your_password@MYDB2
If TNS_ADMIN is not set, then an operating system dependent set of directories is examined to find tnsnames.ora. This search path includes looking for network/admin/tnsnames.ora in the directory specified by the ORACLE_HOME environment variable, for example, $ORACLE_HOME/network/admin/tnsnames.ora. This is the only reason to set the ORACLE_HOME environment variable for SQL*Plus Instant Client. If ORACLE_HOME is set when running Instant Client applications, it must be set to a directory that exists.
This example assumes the ORACLE_HOME environment variable is set, and the $ORACLE_HOME/network/admin/tnsnames.ora or %ORACLE_HOME%\network\admin\tnsnames.ora file defines the Net Service Name MYDB3:
sqlplus hr/your_password@MYDB3
The TWO_TASK (on UNIX) or LOCAL (on Windows) environment variable can be set to a connection identifier. This removes the need to explicitly enter the connection identifier whenever a connection is made in SQL*Plus or SQL*Plus Instant Client. This UNIX example connects to the database known as MYDB4:
TNS_ADMIN=/home/user1
export TNS_ADMIN
TWO_TASK=MYDB4
export TWO_TASK
sqlplus hr/your_password
3.3 Connecting using AS SYSDBA or AS SYSOPER with SQL*Plus Instant Client
To connect AS SYSDBA or AS SYSOPER to perform DBA tasks, you need to set up an Oracle password file on the database server using the database's orapwd utility. Once this is configured, your SQL*Plus Instant Client connection string might look something like:
sqlplus sys/your_password@MYDB AS SYSDBA
See the Oracle Database Administrator's Guide for information on Oracle password files.
3.4 Uninstalling SQL*Plus Instant Client
To remove the complete SQL*Plus Instant Client, delete the directory containing the SQL*Plus executable, Oracle libraries and glogin.sql. If you created a tnsnames.ora file, remove it too. Reset any environment variables such as LD_LIBRARY_PATH, PATH or TNS_ADMIN.
The SQL*Plus tool can be removed separately from the OCI Instant Client. The remaining libraries will allow custom written OCI programs or third party database utilities to connect to a database.
To delete only the SQL*Plus tool from the OCI Instant Client, remove the following SQL*Plus specific files which are not needed for the OCI Instant Client:
|
Platform |
Description |
| UNIX |
Windows |
| sqlplus |
sqlplus.exe |
SQL*Plus executable |
| libsqlplus.so |
not applicable |
SQL*Plus library |
| glogin.sql |
glogin.sql |
SQL*Plus site profile |
:
4 General Issues and Workarounds
This section describes general issues and their workarounds for SQL*Plus.
4.1 Connecting to Oracle7 from SQL*Plus 10.1
Connections to an Oracle7 database from SQL*Plus 10.1 are not supported. You cannot connect to an Oracle7 database from SQL*Plus 10.1.
4.2 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.
4.3 Non-Blocking Database Calls
Non-blocking database calls to the Oracle8 Server are not supported by the Windows graphical user interface and therefore there is no cancel dialog while statements are executing. This does not affect multi-tasking operating systems.
4.4 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.
4.5 Privileged Connection Errors
Some privileged connections to the Server may generate errors if the SET SERVEROUTPUT or SET APPINFO commands have been put in the global initialization file (glogin.sql) or local file (login.sql).
4.6 Cannot Use Some Web Browsers' Back Button in iSQL*Plus
The Back button of some web browsers cannot be used to view the previous page of iSQL*Plus output.
4.7 PAUSE Command Requests Input in iSQL*Plus
The PAUSE command should print any PAUSE [text], and display the Next Page button. Instead, it displays the Input Required screen, and the [text] as the prompt before the input field.
4.8 iSQL*Plus Script Output Location
The SQL*Plus User's Guide and Reference states that iSQL*Plus output
can be displayed:
-
- Saved to an HTML file
- In a new browser window
- In the same browser window
In this release, the output can only be displayed below the input area, and saved to an HTML file. A future version of iSQL*Plus may include the extra functionality of displaying input in a new browser window, or in the same browser window.
4.9 Command Restrictions in iSQL*Plus
There are some command restrictions in this release of iSQL*Plus:
- Use the identical EXIT command in place of the unavailable QUIT command.
- Use Preferences > Database Administration > Automatic Recovery ON to use the RECOVER command. Otherwise, a RECOVER command gives the message:
SP2-0872 Message 872 not found; product=SQLPlus; facility=SP2.
4.10 Restart iSQL*Plus After Editing web.xml
iSQL*Plus should be restarted after making any changes to the web.xml configuration file.
4.11 Restart iSQL*Plus After Changes to JAZN Authentication File
iSQL*Plus should be restarted after making any changes to the JAZN authentication file, jazn-data.xml. The JAZN authentication file is changed when you use the JAZN admin tool to set up users for the iSQL*Plus DBA URL.
4.12 Starting JAZN
The SQL*Plus User's Guide and Reference documents that JAZN should be
started with the following command:
$JAVA_HOME/bin/java
-Djava.security.properties=$ORACLE_HOME/sqlplus/admin/iplus/provider
-jar $ORACLE_HOME/oc4j/j2ee/home/jazn.jar
-user "iSQL*Plus DBA/admin"
-password admin_password
-shell
Although the above command will start up JAZN, for future compatibility,
you should use the following command to start up JAZN instead:
$JAVA_HOME/bin/java
-Djava.security.properties=$ORACLE_HOME/oc4j/j2ee/home/config/jazn.security.props
-jar $ORACLE_HOME/oc4j/j2ee/home/jazn.jar
-user "iSQL*Plus DBA/admin"
-password admin_password
-shell
4.13 iSQL*Plus Does Not Display the Password Expiry Grace Period
If a user's password is about to expire, iSQL*Plus does not display the error:
ORA-28002: the password will expire within grace_period days
The user may not realise their password requires changing until the account is locked due to the grace period time expiring.
5 New Features in SQL*Plus
SQL*Plus Release 10.1 is a superset of SQL*Plus Release 9.2.
This section describes new features introduced in this release of SQL*Plus. Some features may be affected by the SQLPLUSCOMPATIBILITY setting. See the SET SQLPLUSCOMPATIBILITY Matrix in chapter 13, "SQL*Plus Command Reference" in the SQL*Plus User's Guide and Reference.
5.1 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. They are now also run after successful CONNECT commands.
5.2 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. SQLPLUSCOMPATIBILITY will default to 10.1.
5.3 SET SQLPROMPT
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. This is particularly useful for the display of runtime information such as the current connection or current user at runtime.
5.4 Predefined Substitution Variables: _DATE, _PRIVILEGE, and _USER
There are three new predefined substitution variables:
- _DATE contains the current date, or a user defined fixed string.
- _PRIVILEGE contains the privilege level of the current connection. 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 and viewed like any other substitution variable.
5.5 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.
5.6 SHOW RECYCLEBIN
A new SHOW RECYCLEBIN [original_name] command has been added. It enables you to view objects that are available for purging or reverting using the PURGE and FLASHBACK BEFORE DROP commands.
5.7 APPEND, CREATE, and REPLACE Extensions to SPOOL Command
The SPOOL command has been enhanced. You can now append to, or replace an existing file, where previously you could only create (and replace) files. The default is to replace the file. The new syntax is:
SPOOL {file_name[.ext] [CRE[ATE]|REP[LACE]|APP[END]]|OFF|OUT}
5.8 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.
5.9 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"
5.10 Improved Messages and Prompts
Some SQL*Plus messages have been improved. For example, there are new error messages for the COPY command errors:
- Missing usernames
- Missing FROM and TO clauses
- FROM and TO clauses that are too long
- Password input errors
5.11 SET PAGESIZE Default
The default value of SET PAGESIZE has been changed from 24 to 14 in SQL*Plus command line. iSQL*Plus has a default value of 24.
5.12 Windows Specific Information
The SQL*Plus Getting Started for Windows guide has been discontinued. Windows specific information is now included in the SQL*Plus User's Guide and Reference.
6 New Features in Previous Releases
This section lists new features introduced to SQL*Plus in previous releases.
6.1 New Features in SQL*Plus 9.2 Production
- XMLType Support
- SQLPLUS -L Argument
- _CONNECT_IDENTIFIER DEFINE Variable
- Cause/Action Text for SP2 Error Messages
- @URL Support
- iSQL*Plus
- UNIX Support
- Set System Variables
- History Screen
- New Session
- Cancel Script
- iSQL*Plus DBA Login
- iSQL*Plus Extension for Windows
6.2 New Features in SQL*Plus 9.0.1 Production
- iSQL*Plus
- SET APPINFO
- Command line switches
- SET SQLPLUSCOMPATIBILITY
- Read script from Uniform Resource Identifier
7 Bugs Fixed
The following section lists bugs fixed in SQL*Plus. Numbers in parentheses following the problem description refer to bug numbers in the Oracle Bug Database.
7.1 Bugs Fixed in SQL*Plus Release 10.1
- URL argument to START command behaves as expected (3157884)
- ORACLE_PATH now behaves as expected (3131163)
- Null bytes in data output are now represented as blanks (858569, 2755590)
- COPY password prompts have been made clearer (2354991)
- DESCRIBE nested objects with depth greater than two and SET MARKUP HTML ON now displays correctly (2586198)
- DESCRIBE collection of FLOAT now shows datatype correctly (2634521)
- HTML output is now left aligned (2682887)
- An appropriate message is now displayed when an incomplete multibyte data message is returned (2690096)
- A nested @ command now expands filenames correctly (2719895)
- Multiple temporary LOBs returned by a SELECT are now freed after being displayed (2754938)
- An appropriate error message is now returned for an invalid character set (2760780)
- A HOST command on UNIX no longer causes SQL*Plus to terminate (2796956)
- Printing invalid clob bind variable now gives appropriate error (2853507)
- SQL*Plus no longer exits when using CONNECT from a session where WHENEVER OSERROR is SET (2858130)
- The ";" list command now works correctly in a multibyte character set (2876261)
- DBMS_OUTPUT.PUT_LINE in a stored function in a query now displays (572481)
- COPY command is now able to copy ROWID datatypes (1521681, 233778)
- DESCRIBE XMLTYPE object now returns correct information (2323971, 2323971)
- DESCRIBE XMLTYPE now displays correct information (2211086)
- DESCRIBE object type with "NOT FINAL TYPE" now displays correctly (1891289)
- DESCRIBE subtype now gives correct instantiable status of overriding method (1558988)
- AUTOTRACE now returns correct statistics information when logging in AS SYSDBA (2097904)
- Execute a script without a suffix now works correctly (2117791)
- Execute a script using @ without a file extension inside a nested script with @URI now works correctly (2224166)
- A message is now displayed when the @ command is used without a file name (2601028)
- A script is no longer truncated when using the GET command to open a file that is longer than the maximum input line length (for example, 2500 on UNIX) (2601039)
- An SP2-0778 error message is now displayed when a script or parameters are too long (2601047)
- The length of a username in the PASSWORD command has been increased to 30 bytes (2607611)
- Query data containing "\r\n" now prints correctly (2121622, 2546865)
- Query Unicode surrogate pairs stored as CLOB/NCLOB now returns correct data (2157907)
- The correct runtime errors are reported when executing PL/SQL (2282273)
- SELECT object type now works correctly for user privileges (2287599)
- An empty HTML table is no longer generated when no rows are returned (2335193)
- MARKUP HTML now displays the correct message in iSQL*Plus (1773141)
- Column headings will no longer be printed when SET MARKUP HTML is ON and SET PAGESIZE is 0 (2366126)
- Multi-line headings are now displayed in the correct order when SET MARKUP HTML is ON and SET MARKUP HTML PREFORMAT is OFF (2494108)
- STARTUP command with invalid options now gives the correct error (2387804)
- Arguments in quotes are now handled correctly (2471872)
- TIMING command now returns the correct values on Microsoft Windows (2487103)
- An appropriate error message is now displayed when incomplete multibyte data in UTF8 character set is encountered (2676447)
- Internal code changes (817734, 1542021, 1734290, 2172268, 2209001, 2282469, 2311319, 2316034, 2318470, 2337687, 2348155, 2350477, 2468376, 2468418, 2489217, 2501390, 2520700, 2537111, 2567594, 2570153, 2581911, 2584965, 2602666, 2602825, 2605403, 2625949, 2638831, 2644718, 2661677, 2677220, 2706158, 2736722, 2747005, 2789571, 2920908, 2973160, 2973176, 2973251, 3036946)
8 Support
For SQL*Plus and iSQL*Plus support, please contact your local Oracle Support Services Center. There is also an iSQL*Plus Discussion Forum on the Oracle Technology Network located at http://otn.oracle.com/ which may be helpful.
9 Desupport Notices
This section gives advance notice of the desupport of SQL*Plus commands and interfaces. It is not official notice of desupport dates, nor is it intended to replace the Oracle obsolescence process. This information provides you with advance warning that Oracle Corporation intends to desupport these features/interfaces in upcoming releases.
9.1 COPY Command
COPY supports the datatypes listed for the COPY command in the SQL*Plus User's Guide and Reference, but no new datatypes will be supported. The COPY command may be made obsolete in a future release of SQL*Plus.
9.2 Windows Graphical User Interface
The SQL*Plus for Windows graphical user interface (GUI) will be desupported in a future release of SQL*Plus. The browser-based iSQL*Plus user interface will replace the SQL*Plus for Windows GUI. The SQL*Plus for Windows command line (DOS) interface will continue to be supported.
9.3 iSQL*Plus Extension for Windows
The iSQL*Plus Extension for Windows is not available in this release of SQL*Plus. No future versions of this extension will be available.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
Copyright © 1996, 2003 Oracle Corporation.
All Rights Reserved.
