8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
SQL*Plus Enhancements in Oracle Database 10g
- Whitespace Support in Windows Path and File Names
- Glogin, Login and Predefined Variables
- APPEND, CREATE and REPLACE extensions to SPOOL and SAVE
- SHOW RECYCLEBIN
- Miscellaneous Enhancements
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:
Hope this helps. Regards Tim...