iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
SQL*Plus environment commands make Oracle command-line sessions predictable: use environment variables to locate clients, network configuration, and scripts; use SQL*Plus SET commands to control prompts and report output; and use profile scripts to apply repeatable defaults. These are client-side settings, distinct from SQL statements and database configuration.
SQL*Plus commands are different from SQL and PL/SQL
SQL*Plus is Oracle’s interactive and batch command-line tool for running SQL and PL/SQL, executing scripts, formatting query output, and issuing some operating-system commands. Oracle describes it as “an interactive and batch query tool that is installed with every Oracle Database installation” in its SQL*Plus Quick Start. Availability and packaging can depend on platform; Oracle also offers SQL*Plus Instant Client for supported platforms.
A SQL statement such as SELECT is sent to the database. A SQL*Plus command such as SET, SHOW, or STORE configures or controls the client session. SQL*Plus commands are not SQL, and their availability or behavior may vary by client release.
Set up executable, connection, and script lookup
These variables serve different purposes. Their exact availability, path separators, and behavior depend on the client version and operating system; consult Oracle’s platform-specific documentation before changing a shared setup. Oracle’s SQL*Plus configuration guide documents the environment-variable roles below.
#1 Best Overall
| Variable | What it affects | Practical note |
|---|---|---|
PATH |
Locating executables; on Windows, DLLs as well | An Oracle home’s bin directory is commonly included. If the wrong executable starts, check which Oracle client appears first in the path. |
ORACLE_SID |
Optionally identifies a database instance | It is not a substitute for network naming in every connection setup. |
TNS_ADMIN |
Locates network configuration such as tnsnames.ora |
When it is not set, Oracle documents a default under the Oracle home’s network administration directory. |
SQLPATH |
Script discovery through an environment variable or, on Windows, a registry entry | Search behavior and separators vary by platform and release. |
ORACLE_PATH |
Script locations on applicable UNIX/Linux configurations | Current Database 26 profile documentation uses it when searching for login.sql. |
TWO_TASK |
Default connection identifier on UNIX | Oracle’s example sets TWO_TASK=MYDB; then sqlplus hr is equivalent to connecting as hr@MYDB. |
LOCAL |
Default connection identifier on Windows | It serves a corresponding default-connection purpose on that platform. |
NLS_LANG |
Client globalization behavior | Do not change casually: character-set and locale implications matter. |
Do not assume script lookup and login-profile lookup are the same
Ordinary scripts invoked with @ and automatically discovered login.sql profiles do not necessarily follow identical search rules. Oracle Database 19 documentation describes SQL*Plus searching the current directory and configured script paths for scripts. By contrast, current Database 26 documentation says SQL*Plus no longer searches the current directory for login.sql; it searches ORACLE_PATH directories and their subdirectories in order. Check the guide for the installed release rather than relying on one path rule for both cases.
Use SET to make the session easier to read
SET changes SQL*Plus client behavior. A prompt that exposes the current account or connection identifier helps prevent confusion when moving between databases. Oracle documents substitutions including _USER, _CONNECT_IDENTIFIER, _DATE, and _PRIVILEGE in its SET System Variable Summary.
SET SQLPROMPT "_CONNECT_IDENTIFIER > "
SET SQLPROMPT "_USER > "
SET SQLPROMPT "_DATE _USER _PRIVILEGE> "For example, a user-specific prompt can show both account and connection:
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Scan for outdated or missing drivers - takes under a minute3Repair Windows errors before they cause bigger problemsSET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER> "Other settings affect tabular reports. Oracle’s profile example uses SET PAGESIZE 24, SET LINESIZE 78, and SET NUMFORMAT $99,999. Those are examples, not universal recommendations: choose page and line widths to suit the output destination and your queries. Use SHOW to inspect SQL*Plus system-variable settings. Use STORE SET file_name to save them for later restoration, as documented in the Database 26 configuration guide.
Persist defaults with glogin.sql and login.sql
SQL*Plus has two commonly used profile scripts. The site profile, glogin.sql, is for DBA-managed defaults; Oracle documents the UNIX location as $ORACLE_HOME/sqlplus/admin/glogin.sql, with the corresponding Oracle home directory on Windows. The user profile, login.sql, holds personal session settings. When both run, the site profile runs before the user profile.
Here is an illustrative starting point for a user profile, not a universal policy:
-- login.sql: user-specific SQL*Plus defaults
SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER> "
SET PAGESIZE 24
SET LINESIZE 100Current Database 26 documentation says implicit profile processing depends on ORA_PLUS_AUTOEXEC. Depending on that setting, profiles may run after a successful connection or before the prompt when SQL*Plus is started with /NOLOG. Profiles do not run when switching PDBs with ALTER SESSION SET CONTAINER. When automatic execution is restricted, Oracle blocks certain commands during implicit profile processing; explicitly running a script is a separate case. Some privileged connections can also report errors if commands such as SET SERVEROUTPUT or SET APPINFO are placed in profiles. Verify the installed release’s rules and local security policy before relying on automatic execution or putting commands in a shared profile.
Choose between SQL*Plus and SQLcl
SQLcl is a related Oracle command-line client, not simply another name for SQL*Plus. Oracle calls it “a Java-based command-line interface for Oracle Database” in its SQLcl 25.2 guide.
| Decision point | SQL*Plus | SQLcl |
|---|---|---|
| Installation | Installed with Oracle Database; Instant Client is available on supported platforms. | Separately downloadable and Java-based. |
| Existing SQL*Plus scripts | Native environment for SQL*Plus scripts. | Supports existing SQL*Plus scripts, but Oracle documents unsupported SQL*Plus commands and features. Test scripts that use less common commands. |
| Interactive workflow | Established interactive and batch command-line workflow. | Documents inline editing, statement completion, and command recall. |
| Runtime considerations | Depends on the installed Oracle client and platform. | Requirements are release-specific. SQLcl 25.4.2 release notes specify Java 17 or 21; when using an ORACLE_HOME or Instant Client with that release, the client must be 23ai. |
The SQLcl requirements above are explicitly for release 25.4.2, not a guarantee for later versions. Oracle’s download page reported SQLcl 26.2.1, released August 10, 2026, when consulted; because downloads and requirements can change, check the current SQLcl download page and the selected release notes before installation. Oracle’s older SQLcl 20.4 guide illustrates that SQL*Plus compatibility is not complete.
Quick Recap
A practical setup sequence
- Confirm the client: check that the intended SQL*Plus executable is found through
PATH, and confirm the Oracle home or Instant Client installation matches your environment. - Confirm connection naming: verify
TNS_ADMINpoints to the expected network configuration directory, or check the platform-specific default connection variable (TWO_TASKon UNIX orLOCALon Windows) if you use one. - Check script paths: distinguish locations for scripts invoked with
@from the profile lookup rules forlogin.sql. ReviewSQLPATH,ORACLE_PATH, and your SQL*Plus release documentation. - Set visible and readable defaults: choose an informative
SQLPROMPTand report settings such asPAGESIZEandLINESIZEfor your workflow. - Persist and validate: put appropriate personal settings in
login.sql, or ask the DBA to manage site defaults inglogin.sql. Test when profiles run and confirm the prompt identifies the expected session.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
