10g SQLPlus Enhancements

10g SQL*Plus Enhancements

The Oracle 10g Database offers several new features for SQL*Plus. The two main new features introduced with 10g are the new DEFINE variables and the enhanced SPOOL command.

DEFINE Enhancements

Starting with Oracle 10g, the DEFINE command has three new predefined variables:

• _USER. This Contains the user name that is supplied by the user to make the current connection. The result here is the same as the output from the SHOW USER command.

• _DATE. This contains the current date or a user-defined fixed string. By default, _DATE displays the current date and is dynamic. _DATE uses the date format as shown with NLS_DATE_FORMAT.

• _PRIVILEGE. This contains the privilege level of the current connection. The value for _PRIVILEGE will be AS SYSDBA, AS SYSOPER, or blank to indicate a normal connection.

You can access and redefine the new variables just as with the original DEFINE variables, as well as use them for TTITLE SQL*Plus reporting, & substitution variables, and with SQL*Plus prompts using the SET SQLROMPT command.

To display the current DEFINE values, you can issue the DEFINE command at the SQL prompt as follows:

[View full width]
 SQL> DEFINE DEFINE _DATE           = "28-MAY-05" (CHAR) DEFINE _CONNECT_IDENTIFIER = "TS10G" (CHAR) DEFINE _USER           = "STROUPE" (CHAR) DEFINE _PRIVILEGE      = "" (CHAR) DEFINE _SQLPLUS_RELEASE = "1001000300" (CHAR) DEFINE _EDITOR         = "Notepad" (CHAR) DEFINE _O_VERSION      = "Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 -  Production With the Partitioning, OLAP and Data Mining options" (CHAR) DEFINE _O_RELEASE      = "1001000300" (CHAR) 

SET SQLPROMPT is not supported with Oracle 10g iSQL*Plus.

As noted, you can now use any of the DEFINE variables with SQL prompt variable substitution. To reset the SQL*Plus prompt with any DEFINE variable setting, you will need to use the SET SQLPROMPT command or add the entries in the glogin.sql or local login.sql profile files. Following are examples showing how one can use the DEFINE variable values for this purpose:

Starting with Oracle 10g, the site profile files glogin.sql and login.sql are executed after each successful database connection from a SQL*Plus or CONNECT command.

• To set the SQL*Plus command prompt to show the current user, use the following:

   SQL> SET SQLPROMPT "_USER > " STROUPE > 

  
  

• To set the SQL*Plus command prompt to display the current user, the current date, and the user's privilege level, use the following:

   SQL> SET SQLPROMPT "_USER _DATE _PRIVILEGE> " STROUPE 28-MAY-05 > STROUPE 28-MAY-05 > conn / as sysdba Connected. SYS 28-MAY-05 AS SYSDBA> 

  
  

• You can also use user-defined variables with the SQL*Plus prompt. To set the SQL*Plus prompt to display a user-defined variable, use the following:

   SQL>DEFINE INSTANCE=ZEUS SQL>SET SQLPROMPT"INSTANCE> " ZEUS> 

  
  

• You can also use custom text with the SQL*Plus prompt. Because text in nested quotes is not parsed, to have a SQL*Plus prompt of your user name, followed by @ and then your connection identifier, you can use the following:

   SQL>SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER > " SYS@TS10G > 

  
  

SPOOL Enhancements

Prior to Oracle 10g, when you invoked the SQL*Plus SPOOL command, the spool file was either created or replaced depending on the file name you used. If the file did not exist, then a new file was created for you. If the file did exist, then SPOOL would automatically overwrite the existing file and replace it with the new file. This limitation was very troublesome for anyone who inadvertently overwrote files in the working directory.

Oracle 10g has removed this limitation by allowing users to either append or replace existing files when using the SPOOL command. Replacing a file is still the default behavior, however. To append an existing file you must use the new APPEND key word. Here is a small example showing how this new feature can be used:

 SQL> spool asu.txt SQL> select name from v$database; NAME --------- TS10G SQL> spool off SQL> spool asu.txt append SQL> select sys_context ('USERENV', 'SESSION_USER') from dual; SYS_CONTEXT('USERENV','SESSION_USER') ------------------------------------- STROUPE SQL> spool off SQL> ! cat asu.txt SQL> select name from v$database; NAME --------- TS10G SQL> spool off SQL> select sys_context ('USERENV', 'SESSION_USER') from dual; SYS_CONTEXT('USERENV','SESSION_USER') ------------------------------------- STROUPE SQL> spool off