This chapter explains how to configure your SQL*Plus command-line environment. It has the following topics:
These environment variables specify the location or path of files used by SQL*Plus. For other environment variables that influence the behavior of SQL*Plus, see the Oracle Database Administrator's Reference.
Table 2-1 Parameters or Environment Variables influencing SQL*Plus
| Parameter or Variable | Description |
|---|---|
|
Environment variable to specify the path used to search for libraries on UNIX and Linux. The environment variable may have a different name on some operating systems, such as DYLD_LIBRARY_PATH on Apple Mac OS, LIBPATH on IBM/AIX-5L, and SHLIB_PATH on HP-UX. Not applicable to Windows operating systems. Example $ORACLE_HOME/lib |
|
|
Windows environment variable to specify a connection string. Performs the same function as TWO_TASK on UNIX. |
|
|
Environment variable to specify globalization behavior. Example american_america.utf8 |
|
|
Environment variable to specify where SQL*Plus is installed. It is also used by SQL*Plus to specify where message files are located. Examples: d:\oracle\10g /u01/app/oracle/product/v10g |
|
|
Environment variable to specify the database edition to use. If you specify the edition with the CONNECT or SQLPLUS command option, edition=value, it is used instead of ORA_EDITION. If no edition is specified in either the CONNECT or SQLPLUS command option, or in ORA_EDITION, SQL*Plus connects to the default edition. |
|
|
Environment variable to specify the locations of the NLS data and the user boot file in SQL*Plus 10.2. The default location is $ORACLE_HOME/nls/data. In a system with both Oracle9i and 10g, or a system under version upgrade, you should set ORA_NLS10 for Oracle 10g and set ORA_NLS33 for 9i. The default NLS location in 9i was $ORACLE_HOME/common/nls/admin/data. |
|
|
Environment variable to specify the location of SQL scripts. If SQL*Plus cannot find the file in ORACLE_PATH, or if ORACLE_PATH is not set, it searches for the file in the current working directory. Not applicable to Windows |
|
|
Environment variable to specify the database instance, optional |
|
|
Environment variable to specify the path to search for executables, and DLLs in Windows. Typically includes ORACLE_HOME/bin |
|
|
Environment variable or Windows registry entry to specify the location of SQL scripts. SQL*Plus searches for SQL scripts, including login.sql, in the current directory and then in the directories specified by SQLPATH, and in the subdirectories of SQLPATH directories. SQLPATH is a colon separated list of directories. There is no default value set in UNIX installations. In Windows, SQLPATH is defined in a registry entry during installation. For more information about the SQLPATH registry entry, see SQLPATH Registry Entry. |
|
|
Environment variable to specify the location of the tnsnames.ora file. If not specified, $ORACLE_HOME/network/admin is used Example h:\network /var/opt/oracle |
|
|
UNIX environment variable to specify a connection string. Connections that do not specify a database will connect to the database specified in TWO_TASK. Example TWO_TASK=MYDB export TWO_TASK sqlplus hr is the same as: sqlplus hr@MYDB |
The SQLPATH registry entry specifies the location of SQL scripts. SQL*Plus searches for SQL scripts in the current directory and then in the directories specified by the SQLPATH registry entry, and in the subdirectories of SQLPATH directories.
The HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0 registry subkey (or the HOMEn directory for the associated ORACLE_HOME) contains the SQLPATH registry entry. SQLPATH is created with a default value of ORACLE_HOME\DBS. You can specify any directories on any drive as valid values for SQLPATH.
When setting the SQLPATH registry entry, you can concatenate directories with a semicolon (;). For example:
c:\oracle\ora11\database;c:\oracle\ora11\dbs
See the Registry Editor's help system for instructions on how to edit the SQLPATH registry entry.
You can set up your SQL*Plus environment to use the same settings with each session.
There are two operating system files to do this:
The Site Profile file, glogin.sql, for site wide settings.
Additionally, the User Profile, login.sql, sets user specific settings.
The exact names of these files is system dependent.
Some privileged connections may generate errors if SET SERVEROUTPUT or SET APPINFO commands are put in the Site Profile or User Profile.
The following tables show the profile scripts, and some commands and settings that affect the Command-line user interface.
Table 2-2 Profile Scripts affecting SQL*Plus User Interface Settings
| This script ... | is run in the Command-line... |
|---|---|
|
Site Profile (glogin.sql) Can contain any content that can be included in a SQL*Plus script, such as system variable settings or other global settings the DBA wants to implement. |
After successful Oracle Database connection from a SQLPLUS or CONNECT command. Where /NOLOG is specified. |
|
User Profile (login.sql) Can contain any content that can be included in a SQL*Plus script, but the settings are only applicable to the user's sessions. |
Immediately after the Site Profile. |
Table 2-3 Commands in Profile scripts affecting SQL*Plus User Interface Settings
| In a profile script, this command ... | affects the Command-line by ... |
|---|---|
|
SET SQLPLUSCOMPAT[IBILITY] {x.y[.z]} Also see the SQL*Plus Compatibility Matrix. |
Setting the SQL*Plus compatibility mode to obtain the behavior the DBA wants for this site. |
|
SQLPLUS command COMPATIBILITY Option |
As for SET SQLPLUSCOMPATIBILITY but set with the SQLPLUS command COMPATIBILITY option. |
|
SQLPLUS command RESTRICT Option |
Starting SQL*Plus with the RESTRICT option set to 3 prevents the User Profile script from being read. |
A Site Profile script is created during installation. It is used by the database administrator to configure site-wide behavior for SQL*Plus Command-line connections. The Site Profile script installed during installation is an empty script.
The Site Profile script is generally named glogin.sql. SQL*Plus executes this script whenever a user starts a SQL*Plus session and successfully establishes the Oracle Database connection.
The Site Profile enables the DBA to set up site wide SQL*Plus environment defaults for all users of a particular SQL*Plus installation
Users cannot directly access the Site Profile.
The Site Profile script is $ORACLE_HOME/sqlplus/admin/glogin.sql in UNIX, and ORACLE_HOME\sqlplus\admin\glogin.sql in Windows. If a Site Profile already exists at this location, it is overwritten when you install SQL*Plus. If SQL*Plus is removed, the Site Profile script is deleted.
For SQL*Plus command-line connections, SQL*Plus also supports a User Profile script. The User Profile is executed after the Site Profile and is intended to allow users to specifically customize their session. The User Profile script is generally named login.sql. SQL*Plus searches for the User Profile in your current directory, and then the directories you specify with the SQLPATH environment variable. SQL*Plus searches this colon-separated list of directories and their subdirectories in the order they are listed.
You can add any SQL commands, PL/SQL blocks, or SQL*Plus commands to your user profile. When you start SQL*Plus, it automatically searches for your user profile and runs the commands it contains.
You can modify your LOGIN file just as you would any other script. The following sample User Profile script shows some modifications that you could include:
-- login.sql -- SQL*Plus user login startup file. -- -- This script is automatically run after glogin.sql -- -- To change the SQL*Plus prompt to display the current user, -- connection identifier and current time. -- First set the database date format to show the time. ALTER SESSION SET nls_date_format = 'HH:MI:SS'; -- SET the SQLPROMPT to include the _USER, _CONNECT_IDENTIFIER -- and _DATE variables. SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER _DATE> " -- To set the number of lines to display in a report page to 24. SET PAGESIZE 24 -- To set the number of characters to display on each report line to 78. SET LINESIZE 78 -- To set the number format used in a report to $99,999. SET NUMFORMAT $99,999
See Also:
SET command for more information on these and other SET command variables you may wish to set in your SQL*Plus LOGIN file.
Using Predefined Variables for more information about predefined variables.
From the Command-line you can store the current SQL*Plus system variables in a script with the STORE command. If you alter any variables, this script can be run to restore the original values. This is useful if you want to reset system variables after running a report that alters them. You could also include the script in your User Profile script so that these system variables are set each time you start SQL*Plus.
To store the current setting of all system variables, enter
STORE SET file_name
Enter a file name and file extension, or enter only the file name to use the default extension .SQL. You can use the SET SUF[FIX] {SQL | text} command to change the default file extension.
To restore the stored system variables, enter
START file_name
If the file has the default extension (as specified by the SET SUF[FIX] {SQL | text} command), you do not need to add the period and extension to the file name.
You can also use the @ (at sign) or the @@ (double at sign) commands to run the script.
Example 2-1 Storing and Restoring SQL*Plus System Variables
To store the current values of the SQL*Plus system variables in a new script "plusenv.sql":
STORE SET plusenv
Created file plusenv |
Now the value of any system variable can be changed:
SHOW PAGESIZE
PAGESIZE 24 |
SET PAGESIZE 60 SHOW PAGESIZE
PAGESIZE 60 |
The original values of system variables can then be restored from the script:
START plusenv SHOW PAGESIZE
PAGESIZE 24 |
Command-line help is usually installed during Oracle Database installation. If not, the database administrator can create the SQL*Plus command-line help tables and populate them with SQL*Plus help data by running a supplied SQL script from SQL*Plus.
The database administrator can also remove the SQL*Plus command-line help tables by running a SQL script from SQL*Plus.
Before you can install or remove SQL*Plus help, ensure that:
SQL*Plus is installed.
The ORACLE_HOME environment variable is set.
The SQL*Plus help script files exist:
Run the provided SQL script, HLPBLD.SQL, to load command-line help.
Log in to SQL*Plus as the SYSTEM user with:
SQLPLUS SYSTEM
You are prompted to enter the password you have defined for the SYSTEM user.
In UNIX run the SQL script, HLPBLD.SQL, from SQL*Plus with:
@$ORACLE_HOME/SQLPLUS/ADMIN/HELP/HLPBLD.SQL HELPUS.SQL
In Windows run the SQL script, HLPBLD.SQL, from SQL*Plus with:
@ORACLE_HOME\SQLPLUS\ADMIN\HELP\HLPBLD.SQL HELPUS.SQL
The HLPBLD.SQL script creates and loads the help tables.
Run the provided SQL script, HELPDROP.SQL, to remove the command-line help.
Log in to SQL*Plus as the SYSTEM user with:
SQLPLUS SYSTEM
You are prompted to enter the password you have defined for the SYSTEM user.
In UNIX run the SQL script, HELPDROP.SQL, from SQL*Plus with:
@$ORACLE_HOME/SQLPLUS/ADMIN/HELP/HELPDROP.SQL
In Windows run the SQL script, HELPDROP.SQL, from SQL*Plus with:
@ORACLE_HOME\SQLPLUS\ADMIN\HELP\HELPDROP.SQL
The HELPDROP.SQL script drops the help tables, and then disconnects.
If you plan to connect to a database other than the default, whether on the same computer or another computer, you need to ensure that Oracle Net is installed, and the database listener is configured and running. Oracle Net services are used by SQL*Plus.
Oracle Net services and the database listener are installed by default during Oracle Database installation. For further information about installing and configuring Oracle Net, see the Oracle Database documentation at http://www.oracle.com/technology/documentation.