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

14.3 Environment Variables That Affect SQL*Plus

A number of environment variable settings
affect the behavior of SQL*Plus. The following sections describe some
commonly used environment variables. For detailed descriptions of all
the environment variables applicable to your version of SQL*Plus,
consult your manual.

14.3.1 Specifying a Search Path for Scripts

Use the SQLPATH environment variable to
designate one or more directories containing
.sql files you wish to invoke from the SQL*Plus
command prompt. Here are two from Linux:

oracle@gennick02:~> SQLPATH=$HOME/sqlplus/ExampleScripts:$HOME/sqlplus
/ExampleData
oracle@gennick02:~> export SQLPATH These commands designate a search path consisting of two directories,
which are separated by colons. When you execute a script using this
SQLPATH setting, SQL*Plus will search the following directories in
order:

Your current working directory , which is the
directory you were in when you started SQL*Plus $HOME/sqlplus/ExampleScripts $HOME/sqlplus/ExampleData On Windows systems, you set environment variables from the
Advanced tab of the System Control
Panel , after clicking the Environment
Variables button, as illustrated in Figure 14-1.

A Windows install of Oracle Database 10 g
includes a default SQLPATH specified in a registry entry such as the
following:

My Computer\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1 The default path specified in the registry points to the
dbs directory in your Oracle Home directory:

C:\oracle\product\10.1.0\Db_1\dbs When you specify a path via the SQLPATH environment variable, the
environment variable overrides the path specified in the registry.

14.3.2 Designating a Default Net Service Name

If you frequently connect to a remote database using a net service
name, you can make SQL*Plus use that service name by default by
setting the LOCAL environment variable. The
following example is taken from Windows and shows how environment
variables can be specified at the Windows command prompt:

C:\Documents and Settings\JonathanGennick>SET LOCAL=prod From here on, whenever you connect with a username and password:

sqlplus gennick/secret it will be as if you had typed:

sqlplus gennick/secret@prod You can see this behavior at work in the following example. Notice
the occurrence of the net service name prod in the
prompt created by the login.sql script:

C:\Documents and Settings\JonathanGennick>sqlplus gennick/secret
SQL*Plus: Release 10.1.0.2.0 - Production on Mon Aug 2 21:20:28 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) >

On Unix and Linux systems, use the environment variable TWO_TASK
rather than LOCAL.

14.3.3 Controlling Language and Character Set

Use the NLS_LANG
environment variable to specify globalization
options. This parameter controls the language
used for messages, the character set used, the sort order used, the
manner in which dates are displayed, and other language-specific
settings. The format for this setting is as follows:

language_territory.character_set in which:

language
Specifies the language to be used. This controls the language used
for messages and the names of days and months among other things.

territory
Specifies the territory. This controls the currency indicator, the
decimal character, and the way dates are formatted.

character_set
Specifies the character
set to be used. This affects sorting and the way characters are
converted between uppercase and lowercase.

The following example requests the French language and
France's territory settings. Character set is
omitted, so the current, operating system default will be assumed:

oracle@gennick02:~> NLS_LANG=french_france
oracle@gennick02:~> export NLS_LANG
oracle@gennick02:~> sqlplus gennick/secret
SQL*Plus: Release 10.1.0.2.0 - Production on Lun. Août 2 21:41:02 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@db01(db01) >

Look at the first line that SQL*Plus displays after login.
You'll see that the date is displayed as
"Lun. Août 2". The
remaining messages are in English, likely because the French language
files aren't installed on my PC.

The NLS_LANG setting is used by other
Oracle products and is not one to toy with lightly. It affects
SQL*Plus and SQL*Loader, Export,
Import, and any other utility used to pass data between server and
client. If you aren't sure what you are doing,
it's best to leave this alone. For detailed
information on Oracle's language support, see the
Oracle Database Globalization Support Guide (Oracle
Corporation) .