The Site and User Profiles | Customizing Your SQL*Plus Environment

Two script files are executed every time SQL*Plus is started. These scripts define the site profile and the user profile and are named, respectively, glogin.sql and login.sql . Beginning with Oracle Database 10 g , these scripts are executed each time you create a new database connection via the CONNECT command.

The site profile is made up of the commands contained in glogin.sql , which is the global login file . For all recent releases of Oracle, you'll find glogin.sql in the $ORACLE_HOME/ sqlplus/admin directory.

An Oracle8 install on a Windows system would place glogin.sql in a directory such as C:ORAWIN95PLUS80 . For an Oracle 7.3 install on Windows, you would find glogin.sql in C:ORAWIN95PLUS33 , or in a similarly named directory. In those releases, the registry entries PLUS80 and PLUS33 under the Oracle registry tree would point to the respective glogin.sql directories.

The user profile is similar to the site profile, except that it is intended to be user-specific . The script name is login.sql , and it is executed immediately after glogin.sql . SQL*Plus searches for the login.sql file in the current directory first, and then searches the directories listed in the SQLPATH environment variable. In a Unix installation and in Windows installations of recent releases, no default login.sql file or default SQLPATH variable will exist.

Windows installs of Oracle8 and Oracle 7.3 typically included default, login.sql files in directories named after the specific version of Windows that you were running: C:ORAWIN95DBS (Windows 95), C:ORANTDBS (Windows NT), or C:ORAWINDBS (Windows 3.1, 3.11).

You can add to the login.sql file, entering in whatever commands make your life easier. Make certain that your SQLPATH environment variable points to the directory containing your login.sql ; otherwise , SQL*Plus won't find it when you are working in another directory.

For a full description of all the many SET commands that you can use to customize your SQL*Plus environment, see Appendix A.

14.2.1 Customizing the SQL*Plus Prompt

It's common to customize the SQL*Plus prompt to provide an indication of the database to which you are connected. This helps when you have multiple SQL*Plus windows open to different databases. Under such circumstances, embedding the database name into your prompt might save you from dropping a table in production when you mean to drop it in test. Example 14-1 shows a login.sql script to set your SQL*Plus prompt to a combination of username, net service name, and database name.

Example 14-1. User profile (login.sql) to embed username, net service name, and database name into the SQL*Plus prompt

SET TERMOUT OFF



--Specify that new values for the database_name column 

--go into a substitution variable called databasae_name

COLUMN database_name NEW_VALUE database_name



--Use SYS_CONTEXT to retrieve the database name. Alias the

--column as database_name to correspond to previous COLUMN 

--command

SELECT SYS_CONTEXT('USERENV','DB_NAME') database_name

FROM dual;



--Set the prompt. Use predefined variables to access login

--user name and net service name

SET SQLPROMPT "&_user@&_connect_identifier(&database_name) >"



SET TERMOUT ON

The prompt set by the login.sql script from Example 14-1 will take the following form:



username


@


net_service_name


(


database_name


) >

For example:

oracle@gennick02:~/sqlplus/ExampleScripts>

sqlplus gennick/secret@prod

SQL*Plus: Release 10.1.0.2.0 - Production on Mon Aug 2 20:58:05 2004



Copyright (c) 1982, 2004, Oracle. All rights reserved.





Connected to:

Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production

With the Partitioning, OLAP and Data Mining options



GENNICK@prod(db01) >

The SET TERMOUT OFF and SET TERMOUT ON commands bracketing the login.sql script prevent showing the output from the script, and especially from the SELECT against dual , thus preserving a clean-looking login.

Table 14-1 lists predefined substitution variables that you may use when customizing your prompt. The variables listed are automatically initialized by SQL*Plus with the values described in the table.

Table 14-1. Predefined substitution variables

Variable	Description
_CONNECT_IDENTIFIER	Net service name used to make the connection. New in Oracle Database 9 i , Release 2.
_DATE	Current date. New in Oracle Database 10 g .
_EDITOR	Command used to invoke an external text editor in response to the EDIT command.
_O_VERSION	Text message describing the version of the Oracle database software corresponding to the copy of SQLPlus that you are running. This is the same message that SQLPlus displays upon login. For example: "Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production."
_O_RELEASE	Release number, corresponding to _O_VERSION, but in the form of a "number" (a string of digits). For example: "1001000200."
_PRIVILEGE	Whether you have connected AS SYSDBA or AS SYSOPER. Otherwise, this variable will contain an empty string. New in Oracle Database 10 g .
_SQLPLUS_RELEASE	Release of SQL*Plus that you are running, in the same form as _O_VERSION. For example: "1001000200."
_USER	Your login user name. New in Oracle Database 10 g .

14.2.2 Choosing an Editor

Another common customization of the SQL*Plus environment is to designate the editor to be invoked in response to the EDIT command. On Linux and Unix systems, the default editor is often a command-line editor named ed . Changing your editor setting is as simple as changing the substitution variable named _EDITOR:

GENNICK@db01(db01) >define _editor = "vi"

You can make this definition in your login.sql file, so you don't need to make it repeatedly each time you run SQL*Plus. Whatever value you place in _EDITOR, that is the command that SQL*Plus uses to invoke an external editor in response to an EDIT command. SQL*Plus will pass the name of the file to be edited as the first command-line argument to that command.

Introduction to SQL*Plus

Command-Line SQL*Plus