There are a number of environment variables that can affect how SQL*Plus operates. One of the most commonly used of these is the SQLPATH variable. SQLPATH functions much like a command search path, except that it applies to SQL scripts. It contains a list of directories to search when looking for a script. The way you set SQLPATH and other variables depends on the specific operating system you are using. The next few sections demonstrate how SQLPATH is set under Windows 3.1, Windows 95, Windows 98, Windows NT, and Unix. Following these examples is a complete list of all the environment variables that apply to SQL*Plus.
Under Unix, SQL*Plus settings are stored in environment variables. You usually set these variables with the Unix export command. The situation is pretty much the same for Windows 3.1 and DOS, except that you use the DOS SET command. In later versions of Windows, such as Windows 95, these settings are stored in the registry.
To set an environment variable from Unix, you generally use either the export or setenv command, depending on which shell you are using. The following example, using the Korn shell, shows how to set the SQLPATH variable to point to your home directory:
$ export SQLPATH=$HOMEHere is the same example using the C Shell’s setenv command:
$ setenv SQLPATH $HOMENote that with export you use an equals sign between the variable and the value, while with setenv you do not. You can use the export (or setenv) command by itself to get a listing of all the variables you have set, including SQLPATH.
$ export
...
SQLPATH=/home/oracle
...All other environment variables are set using the same method.
Under Windows 95, 98, and NT, the SQL*Plus environment variable settings are stored in the registry. You can edit these registry settings using the REGEDIT program. Go to the Start menu, select Run, and type REGEDIT into the Run dialog box. Figure 11.1 shows how this will look.
Click the OK button to run REGEDIT. A window with two panes will open up (see Figure 11.2). The pane on the left contains a list of registry keys that you navigate through in a hierarchical manner very similar to that used in the Windows Explorer. You want to navigate to the following key:
My ComputerHKEY_LOCAL_MACHINESOFTWAREORACLE
Be sure to actually click on the key named ORACLE, so that you can see the entries associated with it. The resulting screen will look like that shown in Figure 11.2.
The right-hand side of the screen now shows a large number of registry entries used by the Oracle database server, SQL*Plus, and other Oracle products. Down near the bottom of this list, you should see an entry named SQLPATH. You can edit this entry by double-clicking on it. Be sure to double-click the word SQLPATH itself, and not the string representing its current value. You should see the dialog shown in Figure 11.3.
When you have the entry edited the way you want it, click the OK button. The new value will be saved, and the Edit String dialog will close. In some cases, you may need to create an entirely new registry entry. Do this with the Edit → New → String Value menu item. Once you’ve created the new entry and given it the appropriate name, you can edit its value as described earlier. When you are done making changes, use the Registry → Exit menu option to exit REGEDIT.
Warning
Be extremely careful when using REGEDIT. Consider using REG-EDIT’s File → Export Registry File menu item to make a backup first. Without a backup, there is no recourse if you make a mistake, and deleting the wrong key or entry could cause you some serious grief. If you’ve never used REGEDIT before, ask someone who has to help you.
If you open an MS-DOS window, you may use the SET command to change an environment variable. When you do this, that setting only takes effect when you start SQL*Plus from the same MS-DOS window. Thus, it is possible to have two or more MS-DOS windows open, each with a different SQLPATH setting.
In a Windows 3.1 environment, the SQL*Plus environment variables are
set using the DOS SET command. Often this is done from within the
autoexec.bat file, so that all the settings are
in place when the system starts. The following example shows how you
would define the SQLPATH variable from a DOS prompt:
C:>SET SQLPATH=C:JONATHANSQLYou can use the SET command by itself to get a list of variables you have set; for example:
C:>SET
...
SQLPATH=C:JONATHANSQL
...This section describes the environment variables that affect SQL*Plus’s operation. Not all settings are available in all environments, but most are. PLUS_DFLT is an example of a setting that’s relevant only under Windows. The description for each setting will note whether or not it is operating-system-specific.
The LOCAL setting applies only to Windows environments, and specifies the default connect string to use when you start SQL*Plus. For example, if you start SQL*Plus under Windows, and enter only a username and password as shown in Figure 11.4, one of two things will happen. If you have Oracle8 Personal Edition installed, you will be connected to the local database on your PC. Otherwise, you will receive the following error:
ORA-03121: no interface driver connected - function not performed
The reason you get an error is that when you leave off the connect string, SQL*Plus has no idea where to go. The only exception to this is that Oracle has added some extra code to the version of SQL*Plus included with the Personal Edition, so it will default to the Personal Edition database if there is one.
By setting the LOCAL variable, you can make any Oracle database the default. Setting it to PRODUCTION, for example, makes SQL*Plus interpret Figure 11.4 as if the user had really typed in PRODUCTION for the connect string. In other words, SQL*Plus would reinterpret the inputs shown in Figure 11.4 as those shown in Figure 11.5.
By default, the Oracle installer will not create a LOCAL entry in the registry. You have to create it. Once you do, SQL*Plus, even the version that ships with Oracle Personal Edition, will respect it.
This parameter applies to all versions of SQL*Plus. It controls the language used for messages, the character set used, the sort order to be 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
where:
- 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 impacts sorting and the way characters are converted between upper- and lowercase.
The default value of the NLS_LANG setting for a Windows client installation is:
AMERICAN_AMERICA.WE8ISO8859P1
This setting is used by other Oracle products, and is not one to toy with lightly. 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 Oracle8 S erver Reference manual.
The PRO80 variable specifies the location
of the SQLUS.MSB message file. This message file
is used by a number of different Oracle products, including some
versions of SQL*Plus. The “80” in the setting name refers
to the specific Oracle version being used, so when Oracle 8.1 is
shipped, the name of this setting will change to PRO81. The default
value for this setting, under Windows 95, is:
C:ORAWIN95PRO80
This setting applies to all operating systems.
The PLUS80 variable points to the directory
that contains the SQL*Plus message files, the
glogin.sql file, and the scripts to create the
SQL*Plus help. The default value for this setting, under Windows NT,
is:
C:ORANTPLUS80
The “80” corresponds to the SQL*Plus version. SQL*Plus 2.3, which shipped with Oracle 7.3, uses the PLUS23 variable. Under some operating systems, such as HP-UX, Oracle does not use this setting.
This variable points to the directory containing the message files that correspond to the particular language you’re using. The default setting, under Windows NT, is:
C:ORANTRDBMS80
The message files of interest to SQL*Plus are:
LCDUS.MSB, ORAUS.MSB, and
SOSDUS.MSB. These are the names of the American
English versions of the message files; if you are using a different
language, the file names may be different.
As with PRO80 and PLUS80, the “80” in the setting name refers to the specific version of Oracle you are using.
Of all the variables, SQLPATH is probably the most useful. The SQLPATH variable contains a search path that is followed by SQL*Plus when it’s looking for a script file. It consists of one or more directory names, separated by semicolons. There is no default value under Unix, but under Windows, the default will be something like this:
C:ORAWIN95DBS
The ...DBS directory is where the default
login.sql script file is installed under
Windows. You can add your own directories to the search path; just be
sure to separate each directory name from the next with a semicolon,
as shown here:
c:jonathansql_scripts;C:ORAWIN95DBS
When you invoke a SQL script without providing the full path, SQL*Plus will first look in the current directory; then it will search each directory listed in SQLPATH. The directories are searched in the order in which they are listed.
The SQLPATH setting applies to SQL*Plus on all operating systems.
This is a Windows-specific setting that controls which version of
SQL*Plus is executed when you invoke the
sqlplus.exe executable. A Windows system will
typically have at least two different SQL*Plus executables installed.
One is a GUI version and the other is a DOS version. For SQL*Plus
8.0, the executables are named according to the following convention:
PLUS80 the DOS executable PLUS80W the GUI executable
In addition, it is possible to have two or more versions of SQL*Plus installed under Windows, and each version will still have two executables. Thus, a PC with SQL*Plus 3.3 and SQL*Plus 8.0 would have the following four executable files:
PLUS80 8.0 DOS PLUS80W 8.0 GUI PLUS33 7.3 DOS PLUS33W 7.3 GUI
This makes for a bit of a problem if you are writing batch files that
make use of SQL*Plus. You would need to hardcode the executable name
in each batch file, then edit those files whenever you upgraded to a
new version. To ease your pain, Oracle supplies a generic executable
named sqlplus.exe. This generic executable reads
the registry, gets the value of the PLUS_DFLT variable, and invokes
the executable named by that value.
The default value of PLUS_DFLT will be that of the DOS executable,
for example, PLUS80, in order to facilitate the
writing of batch files. When you set this variable, do not include
the exe extension as part of the filename. For
example, PLUS80 is correct,
PLUS80.EXE is
not.