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:





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:



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



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>



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 - 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 - 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 :





in which:


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


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


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:




export NLS_LANG


sqlplus gennick/secret

SQL*Plus: Release - 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 - 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) .


Introduction to SQL*Plus

Command-Line SQL*Plus

Browser-Based SQL*Plus

A Lightning SQL Tutorial

Generating Reports with SQL*Plus

Creating HTML Reports

Advanced Reports

Writing SQL*Plus Scripts

Extracting and Loading Data

Exploring Your Database

Advanced Scripting

Tuning and Timing

The Product User Profile

Customizing Your SQL*Plus Environment

Appendix A. SQL*Plus Command Reference

Appendix B. SQL*Plus Format Elements

Oracle SQL Plus The Definitive Guide, 2nd Edition
Oracle SQL*Plus: The Definitive Guide (Definitive Guides)
ISBN: 0596007469
EAN: 2147483647
Year: N/A
Pages: 151

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net