New Features in Oracle 10g for SQL*Plus and iSQL*Plus - Page 2
June 3, 2004
A few more runtime variables have been introduced to help you identify the instance you are connected to and as which user you have connected. All I can say to Oracle is thanks for this one. As we all switch from one database instance to the next it is very easy to get lost--especially when you are connected to production and test instance and have multiple windows up on your workspace. I know it is a bit difficult, but if Oracle could just change the background color on my windows, I would be totally happy. For now, this is as good as it gets. Keep in mind that these variables are available for use in any manner that you would normally use variables. The following new variables are as follows:
_DATE : the current date or a pre-defined string.
SET SQLPROMPT Example
Also, be aware the syntax has changed a bit on these. Here is an example of how it used to be in pre-10g and current 10g for the CONNECT_IDENTIFIER.
SQLPROMPT Switching Between Instances (pre-Oracle10g)
SQLPROMPT Switching Between Instances (new-Oracle10g)
As a side note, if you are using the glogin.sql script and have the pre-10g CONNECT_IDENTIFIER since the glogin.sql script is now executed after every successful connection as previously discussed, you do not need to worry about the proper prompt being displayed.
SQLPROMPT Switching Between Instances with pre-Oracle10g & _CONNECT_IDENTIFIER in globin.sql
SPOOL Command Enhancement
The SPOOL command has now been altered to handle the ability to append to the end of a previously spooled file. There is really only one new option for this command since the CREATE and REPLACE behave just as with no option and the behavior we are all use to. The new options to the SPOOL command are as follows:
CREATE : Create a new spool file
Display the spooled file.
SPOOL APPEND to the previously created spooled file
Display the appended spooled file