Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

14.2 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.

An Oracle8 install on a Windows system would place glogin.sql in a directory such as C:\ORAWIN95\PLUS80. For an Oracle 7.3 install on Windows, you would find glogin.sql in C:\ORAWIN95\PLUS33, 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:\ORAWIN95\DBS (Windows 95), C:\ORANT\DBS (Windows NT), or C:\ORAWIN\DBS (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.

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.