8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | 24ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

SQL*Plus Enhancements in Oracle Database 10g

Whitespace Support in Windows Path and File Names

Support for whitespaces in file names has been added to the START, @, @@, RUN, SPOOL, SAVE and EDIT commands. Names containing whitespaces must be quoted for them to be recognised correctly.

SPOOL "My Report.txt"
@"My Report.sql"

Glogin, Login and Predefined Variables

The user profile files, glogin.sql and login.sql are now run after each successful connection in addition to SQL*Plus startup. This is particularly useful when the login.sql file is used to set the SQLPROMPT to the current connection details.

Three new predefined variables have been added to SQL*Plus.

_DATE - Contains the current date or a user defined fixed string.
_PRIVILEGE - Contains privilege level such as AS SYSDBA, AS SYSOPER or blank.
_USER - Contains the current username (like SHOW USER).

An example of their use is shown below.

SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER _PRIVILEGE _DATE> "

The values of the variables can be viewed using the DEFINE command with no parameters.

APPEND, CREATE and REPLACE extensions to SPOOL and SAVE

The following extentions have been added to the SPOOL and SAVE commands.

REPLACE - (Default) This option replaces an existing file or creates it if it is not already present.
CREATE - This option creates a new file or produces an error if the file already exists.
APPEND - This option appends to an existing file, or creates a new file if it's not already present.

The following example shows their usage.

scott@db10g> spool d:\temp\test1.txt
scott@db10g> spool off
scott@db10g> spool d:\temp\test1.txt replace
scott@db10g> spool off
scott@db10g> spool d:\temp\test2.txt create
scott@db10g> spool off
scott@db10g> spool d:\temp\test2.txt create
SP2-0771: File "d:\temp\test2.txt" already exists.
Use another name or "SPOOL filename[.ext] REPLACE"
scott@db10g> spool d:\temp\test2.txt append
scott@db10g> spool off
scott@db10g> spool d:\temp\test3.txt append
scott@db10g> spool off

scott@db10g> save d:\temp\test4.sql
Created file d:\temp\test4.sql
scott@db10g> save d:\temp\test4.sql replace
Wrote file d:\temp\test4.sql
scott@db10g> save d:\temp\test4.sql create
SP2-0540: File "d:\temp\test4.sql" already exists.
Use "SAVE filename[.ext] REPLACE".
scott@db10g> save d:\temp\test5.sql create
Created file d:\temp\test5.sql
scott@db10g> save d:\temp\test5.sql append
Appended file to d:\temp\test5.sql

SHOW RECYCLEBIN

The SHOW RECYCLEBIN [original_table_name] option has been added to display all the contents of the recycle bin, or just those for a specified table.

scott@db10g> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
BONUS            BIN$F5d+By1uRvieQy5o0TVxJA==$0 TABLE        2004-03-23:11:03:38
DEPT             BIN$Ie1ifZzHTV6bDhFraYImTA==$0 TABLE        2004-03-23:11:03:38
EMP              BIN$Vu5i5jelR5yPGTP2M99vgQ==$0 TABLE        2004-03-23:11:03:38
SALGRADE         BIN$L/27VyBRRP+ZGWnZylVbZg==$0 TABLE        2004-03-23:11:03:38
TEST1            BIN$0lObShnuS0+6VS1cvLny0A==$0 TABLE        2004-03-24:15:38:42

scott@db10g> show recyclebin test1
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST1            BIN$0lObShnuS0+6VS1cvLny0A==$0 TABLE        2004-03-24:15:38:42

This allows users to inspect the contents of the recycle bin before a PURGE or FLASHBACK operation.

Miscellaneous Enhancements

The DESCRIBE command now validates invalid objects before describing them. This means that the "ORA-24372: invalid object for describe" error message will only be displayed if the object can't be validated.

The SET SERVEROUPUT ON setting now displays DBMS_OUTPUT data produced from functions nested in SQL statements properly.

A new command line argument (-c) has been added to allow the SQLPLUSCOMPATIBILITY option to be specified such that "sqlplus -c 9.2" equates to "SET SQLPLUSCOMPATIBILITY 9.2".

When connecting to SQL*Plus from the command line as a privileged user it is no longer necessary to use quotes, meaning both of the following are valid.

sqlplus "/ AS SYSDBA"
sqlplus / AS SYSDBA

The SET PAGESIZE 14 and SET SQLPLUSCOMPATIBILITY 8.1.7 settings have been removed from the glogin.sql file. The default value for PAGESIZE is "24" and the default value for SQLPLUSCOMPATIBILITY is "10.1".

There are new error messages for the following COPY command errors (See COPY Command Messages):

Missing usernames
Missing FROM and TO clauses
FROM and TO clauses that are too long
Password input errors

For more information see:

New Features in SQL*Plus and iSQL*Plus 10.1

Hope this helps. Regards Tim...

Back to the Top.