The Site and User Profiles

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.

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.

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.

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

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.