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:

1. Your current working directory , which is the directory you were in when you started SQL*Plus
2. $HOME/sqlplus/ExampleScripts
3. $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.

Figure 14-1. Setting SQLPATH on a Windows system

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

My ComputerHKEY_LOCAL_MACHINESOFTWAREORACLEKEY_OraDb10g_home1

The default path specified in the registry points to the dbs directory in your Oracle Home directory:

C:oracleproduct.1.0Db_1dbs

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 SettingsJonathanGennick> 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 SettingsJonathanGennick> 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. Aot 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) .