Recommended Free Tools
For a predictable Oracle command-line session, configure the SQL*Plus client separately from the SQL statements you run: use environment variables for executable, connection, and script lookup; use SET commands for the prompt and report formatting; and use profile scripts for defaults. The exact behavior depends on the SQL*Plus version, operating system, and security configuration.
SQL statements and SQL*Plus commands do different jobs
SQL*Plus is Oracle’s interactive and batch command-line tool for running SQL and PL/SQL, scripts, and operating-system commands, as well as formatting query output and performing database administration. Oracle describes it as “an interactive and batch query tool that is installed with every Oracle Database installation.” SQL*Plus commands configure or control the client; they are not SQL statements sent to the database. For example, SET SQLPROMPT changes the SQL*Plus prompt, while a SELECT statement queries the database.
| # | Preview | Product | Price | |
|---|---|---|---|---|
| 1 |
|
Oracle SQL and Pl/Sql | $50.50 | Buy on Amazon |
| 2 |
|
Oracle PL / SQL For Dummies | $15.95 | Buy on Amazon |
| 3 |
|
Mastering Oracle SQL, 2nd Edition | $20.80 | Buy on Amazon |
| 4 |
|
Oracle PL/SQL by Example (The Oracle Press Database and Data Science) | $48.81 | Buy on Amazon |
| 5 |
|
Oracle PL/SQL Programming: Covers Versions Through Oracle Database 12c | $63.84 | Buy on Amazon |
As an Amazon Associate I earn from qualifying purchases.
That distinction matters when setting up an environment: client settings such as the prompt and report layout belong in SQL*Plus commands, while connection details and script lookup can also depend on operating-system environment variables and Oracle client configuration.
Set the environment for executables, connections, and scripts
These variables have separate roles. Their availability, search behavior, and path separators vary by platform and client version; consult the Oracle documentation for the installed release and operating system before relying on a particular search order. Oracle’s SQL*Plus configuration reference documents the variables below for Database 19.
#1 Best Overall
| Variable | Purpose | Practical note |
|---|---|---|
PATH |
Locates executables and, on Windows, DLLs. | Oracle installations commonly include the Oracle home bin directory. |
ORACLE_SID |
Optionally identifies the database instance. | It is an instance identifier, not a script search path. |
TNS_ADMIN |
Points to the directory containing network configuration such as tnsnames.ora. |
Without an override, Oracle documents a default beneath the Oracle home network administration directory. |
SQLPATH |
Identifies locations for SQL script lookup. | Oracle documents platform-specific behavior; on Windows it may be an environment variable or registry entry. |
ORACLE_PATH |
Provides script locations on applicable UNIX/Linux configurations. | Its profile-file behavior in Database 26 is described separately below. |
TWO_TASK |
On UNIX, can supply a default connection identifier. | Oracle’s example sets TWO_TASK=MYDB; then sqlplus hr is equivalent to connecting as hr@MYDB. |
LOCAL |
On Windows, can supply a default connection identifier. | It serves the corresponding default-connection purpose to TWO_TASK. |
NLS_LANG |
Affects globalization behavior. | Do not change it casually; client and database character sets and locale implications matter. |
Do not assume that script lookup and automatic profile discovery use the same rules. Oracle Database 19 documentation describes SQL*Plus script lookup in the current directory and configured paths. The Database 26 configuration guide says SQL*Plus no longer searches the current directory for login.sql and describes searching ORACLE_PATH. Ordinary scripts invoked with @ and the automatically discovered login profile are distinct cases.
Make the active connection visible in the prompt
When switching databases or accounts, a prompt that shows connection context can help prevent commands being run in the wrong session. Oracle documents SET SQLPROMPT substitutions including _USER, _CONNECT_IDENTIFIER, _DATE, and _PRIVILEGE. For example:
Rank #2
SET SQLPROMPT "_CONNECT_IDENTIFIER > "
SET SQLPROMPT "_USER > "
SET SQLPROMPT "_DATE _USER _PRIVILEGE> "A combined prompt can show the user and connection identifier at once:
SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER> "These are SQL*Plus commands, so enter them at the SQL*Plus prompt or place them in a profile script. See Oracle’s SET system variable summary for the documented syntax and substitutions.
Rank #3
Shape query output with SET
SQL*Plus settings can make reports easier to read, but values depend on the query and terminal width. Oracle’s profile example uses PAGESIZE 24, LINESIZE 78, and NUMFORMAT $99,999; those are examples rather than universal defaults. A user profile might instead start with:
SET PAGESIZE 24
SET LINESIZE 100Use SHOW to inspect SQL*Plus system-variable settings, and STORE SET file_name to save settings for later restoration. The latter is documented in Oracle Database 26’s SQL*Plus configuration guide.
Persist defaults with SQL*Plus profile scripts
SQL*Plus supports a site profile, glogin.sql, and a user profile, login.sql. The site profile is intended for DBA-managed defaults; the user profile holds individual preferences. Oracle documents the UNIX site-profile path as $ORACLE_HOME/sqlplus/admin/glogin.sql, with the corresponding Oracle home directory used on Windows.
Windows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallOutdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchIn Database 26 documentation, login.sql is searched for in directories specified by ORACLE_PATH and their subdirectories, in order; the current directory is no longer searched for that file. Do not infer this profile search behavior from rules for ordinary @ scripts.
Example user profile
This illustrative login.sql combines a connection-aware prompt with basic report settings:
-- login.sql: user-specific SQL*Plus defaults
SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER> "
SET PAGESIZE 24
SET LINESIZE 100Automatic profile processing is version- and configuration-sensitive. Oracle Database 26 says it depends on ORA_PLUS_AUTOEXEC; scripts may run after a successful connection or before the prompt when SQL*Plus is started with /NOLOG, depending on that setting. When both profiles run, the site profile precedes the user profile. Profiles do not run when switching PDBs with ALTER SESSION SET CONTAINER.
Oracle also warns that some commands in profiles can cause errors for privileged connections, including SET SERVEROUTPUT or SET APPINFO. During implicit profile processing, restricted automatic execution blocks certain commands; explicitly invoking scripts is a separate case. Check the configuration guide for the installed release and follow local DBA policy rather than assuming every command is safe in every profile.
Free tools Windows power users keep installed
One-click scans. No signup required.
Choose SQL*Plus or SQLcl for the task
SQL*Plus is available with Oracle Database installations and as a standalone Instant Client command-line option on supported platforms. SQLcl is Oracle’s Java-based command-line interface for interactive or batch SQL and PL/SQL. Oracle says SQLcl supports existing SQL*Plus scripts, but compatibility is not complete; its 20.4 guide, for example, documents unsupported SQL*Plus commands and features.
| Decision point | SQL*Plus | SQLcl |
|---|---|---|
| Installation context | Installed with Oracle Database; Instant Client is also available on supported platforms. | Separately downloadable and Java-based. |
| Existing scripts | Native SQL*Plus command environment. | Supports existing SQL*Plus scripts, but some commands and features are unsupported. |
| Interactive workflow | SQL*Plus commands and session settings. | Documents inline editing, statement completion, and command recall. |
| Runtime requirements | Depends on the installed Oracle client and platform. | SQLcl 25.4.2 release notes require Java 17 or 21; they also state that when using an ORACLE_HOME or Instant Client with SQLcl, the client version must be 23ai. |
Runtime and download details change by release. Oracle’s SQLcl 25.4.2 release notes state the requirements above; the SQLcl download page reported version 26.2.1, released August 10, 2026. Check the selected release’s requirements before installing. For features and version-specific guidance, consult Oracle’s SQLcl 25.2 guide.
Quick Recap
Oracle references
- Oracle Database 26: Configuring SQL*Plus
- Oracle Database 26: SET System Variable Summary
- Oracle Database 19: Configuring SQL*Plus
- Oracle Database 26: Introduction to SQL*Plus
- Oracle Database 21: SQL*Plus Quick Start
- Oracle Database 21c: SQL*Plus User’s Guide and Reference (PDF)
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.
