oracleskill

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 would be:

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 further information see:

• New Features in SQL*Plus and iSQL*Plus 10.1

Hope this helps. Regards Tim...