Hubbry Logo
SQL PlusSQL PlusMain
Open search
SQL Plus
Community hub
SQL Plus
logo
7 pages, 0 posts
0 subscribers
Be the first to start a discussion here.
Be the first to start a discussion here.
SQL Plus
SQL Plus
SQL Plus
View on Wikipedia
from Wikipedia
Command line

SQL Plus is the most basic Oracle Database utility, with a basic command-line interface, commonly used by users, administrators, and programmers.

Command types

[edit]

SQL Plus understands five categories of text:[1]

  1. SQL statements
  2. PL/SQL blocks
  3. SQL Plus internal commands, for example:
    • environment control commands such as SET
    • environment monitoring commands such as SHOW
  4. Comments
  5. External commands prefixed by the ! char

Scripts can include all of these components.

An Oracle programmer in the appropriately configured software environment can launch SQL Plus, for example, by entering: 

$ sqlplus scott/tiger

where the Oracle user scott has the password tiger. SQL Plus then presents a prompt with the default form of: 

SQL>

Interactive use can then start by entering a SQL statement (terminated by a semicolon), a PL/SQL block, or another command. For example: 

SQL> select 'Hello world' as example from dual;

EXAMPLE
--------------------------------
Hello world

History

[edit]

The first version of SQL Plus was called UFI ("User Friendly Interface"). UFI appeared in Oracle database releases up to Version 4.

After Oracle programmers had added new features to UFI, its name became Advanced UFI. The name "Advanced UFI" changed to "SQL Plus" with the release of the version 5 of Oracle.[2]

Usage

[edit]

Graphical interfaces from Oracle or third parties have diminished the proportion of Oracle database end-users who depend on the SQL Plus environment. Oracle shops typically continue to use SQL Plus scripts for batch updating or simple reports.[citation needed]

Oracle Corporation's wrappers/gui-fications/replacements for SQL Plus include:

Oracle 11g

[edit]

Starting from Oracle database 11g, iSqlplus (web based) and sqlplus GUI no longer ship with Oracle database software.[7] The command-line SQL Plus interface continues in use, mostly[citation needed] for non-interactive scripting or for administrative purposes. The Server Manager Command Line — a replacement of SQL*DBA — is obsolete and SQL Plus 8i and later allows the user to issue statements like STARTUP and SHUTDOWN when connected as SYSDBA. Server Manager 7.1 introduced the command CONNECT / AS SYSDBA to replace CONNECT INTERNAL.[8] SQL Plus 8i and later allows the use of CONNECT / AS SYSDBA

Compatibility

[edit]

Other vendors have made their software somewhat compatible with SQL Plus script commands or offer a SQL Plus mode of operation. Relevant products include TOAD from Quest Software.[9]

Integration

[edit]

Variables

[edit]

SQL Plus-internal variables, accessible within an SQL Plus session, include:

  • user variables, displayable with the DEFINE command and referenceable with one or two cases of a prefixed character (default prefixes: '&' and '&&'). Oracle Corporation calls these variables "substitution variables". Programmers can use them anywhere in a SQL or PL/SQL statement or in SQL Plus commands. They can be populated by a literal using DEFINE or from the database using the column command.
    • predefined variables, prefixed with an underscore ('_')[10]
    • substitution variables, useful for interacting with user-input[11]
  • bind variables, prefixed by a colon (':'), which can interact with the PL/SQL environment. Displayable with the VARIABLE and PRINT commands[12]

Error trapping

[edit]

The WHENEVER command specifies an action to perform in the event of the system detecting an SQL error[13] or an operating-system error[14] while running a subsequent SQL Plus command.

Installation and configuration

[edit]

SQL*Plus needs to be installed and configured on the machine where you intend to use it. This usually involves setting up Oracle client software and configuring network connections to Oracle databases.[15]

Supplementary software

[edit]
  • SQL Assistant SQL Assistant add-on for SQL Plus Windows version extends SQL Plus with SQL automatic word completion, in-line Oracle SQL Reference, data export/import, code unit testing, data browsing, and code development functions.
  • rlwrap rlwrap does GNU Readline like command completion for SQL Plus on UNIX and Linux.

See also

[edit]

References

[edit]
[edit]
Revisions and contributorsEdit on WikipediaRead on Wikipedia

SQL Plus

View on Grokipedia
from Grokipedia
SQLPlus is a command-line tool developed by that serves as an interactive and batch query processing interface for , allowing users to execute SQL statements, blocks, and SQLPlus-specific commands to query, manage, and administer relational databases. Installed by default with every edition, it provides direct access to database functionality without requiring a , making it essential for database administrators, developers, and power users. Key capabilities of SQLPlus include formatting, calculating, storing, and printing the results of SQL queries; examining definitions of tables, views, and other database objects; developing and executing batch scripts for automated tasks; and generating reports in text, screen, or formats. It supports a two-tier architecture where the SQLPlus client communicates with the server via Net services, enabling efficient interaction over networks. Additionally, SQL*Plus can invoke operating system commands, facilitating integration with external scripts and tools for comprehensive database operations. Originating from the early development of in the late , SQL*Plus—originally authored by Bruce Scott—has evolved as a foundational utility, accompanying Oracle releases since Version 2 in 1979 and renaming to its current form with Oracle Version 5. Over decades, it has maintained compatibility across all supported versions, with enhancements like improved scripting and reporting features in modern releases such as 21c, 23ai, and AI Database 26ai (as of November 2025). This longevity underscores its role as a reliable, lightweight alternative to graphical tools like SQL Developer, particularly in command-line environments, , and automated deployment scenarios.

History

Origins and Development

SQL*Plus originated in the late as part of Corporation's early development efforts, initially under the name User Friendly Interface (UFI). Developed primarily by Bruce Scott, one of 's founding employees and co-architect of its first versions, UFI served as a basic command-line tool for interacting with the database. It was introduced alongside Version 2, the company's first commercially available management system, released in 1979. The primary motivations for creating UFI, later evolving into SQL*Plus, were to provide database administrators and developers with a straightforward interface for executing SQL queries and retrieving results without relying on graphical user interfaces, which were not yet prevalent. This tool emphasized portability across diverse early computing platforms, including mainframes and minicomputers, and drew inspiration from IBM's System R User Friendly Interface, one of the earliest relational database prototypes. Early contributors like Kirk Bradley highlighted the need for a simple mechanism to input SQL statements and format output, addressing the limitations of direct database access methods at the time. UFI remained the standard interface through Oracle Version 4, released in 1984, supporting core functions like query execution and basic reporting on platforms such as PDP-11 systems. By 1985, with the release of Version 5 in , the tool was renamed SQL*Plus and enhanced to better align with the growing adoption of SQL as a . This version introduced improved command-line interaction tailored for mainframes and minicomputers, marking its first widespread public availability. In the mid-1980s, SQLPlus began evolving from a rudimentary query tool to include scripting capabilities, allowing users to automate sequences of SQL and control commands in batch files. This development supported 's expansion into client-server architectures with Versions 5.0 and 5.1, facilitating more efficient database administration on emerging networked systems. These early advancements laid the foundation for SQLPlus's role as a durable, text-based utility in environments.

Major Releases and Evolution

The name SQLPlus was introduced with version 5 in 1985, enabling basic scripting and interactive query processing as an evolution of the earlier UFI tool. By version 6 in 1991, SQLPlus gained support for blocks, allowing users to execute procedural code alongside standard SQL commands. In 8i (1998), enhancements to support were added, including improved handling of national character sets (NCHAR and NVARCHAR) for multilingual data processing and display. Oracle Database 10g (2004) integrated XML capabilities into SQLPlus, with the SET MARKUP command enabling XML-formatted output for queries and support for XMLType data manipulation. The release of Oracle 12c in 2013 introduced multitenant architecture, and SQLPlus was updated to connect to container databases (CDBs) and pluggable databases (PDBs) using the ALTER SESSION SET CONTAINER command, facilitating management in consolidated environments. Oracle 19c (2019), designated as a long-term support release, optimized SQL*Plus for cloud deployments with features like Easy Connect Plus syntax for simplified connections and desupport of legacy elements such as the PRODUCT_USER_PROFILE table. In Oracle 21c (2021), SQLPlus received updates including an enhanced command for easier access to previous commands and the SET JSONPRINT option for formatted JSON output readability. The iSQLPlus web interface, introduced earlier for browser-based access, was deprecated starting with 11g in 2009 and fully desupported by 2010. Oracle Database 23ai (2024) maintained SQL*Plus compatibility while adding commands like for parameter handling, SET ERRORDETAILS for diagnostic output, PING for connection testing, OERR for error lookups, and support for data types in queries. As of November 2025, SQL*Plus remains bundled with 23ai and the newly announced 26ai release, receiving minor patches focused on security hardening and cloud integration, such as improved compatibility with Oracle Cloud Infrastructure services, amid a broader industry shift toward automated database tools.

Overview

Purpose and Functionality

SQL*Plus serves as both an interactive and non-interactive for executing SQL statements, blocks, and Oracle-specific commands directly against databases. As a core component of the installation, it enables users to interact with the (RDBMS) in a text-based environment, supporting both ad-hoc queries and automated . Its primary functionalities encompass executing queries for and manipulation, running code blocks for procedural logic, formatting and printing report outputs with customizable column headings and page layouts, and performing essential database administration tasks such as starting up or shutting down database instances. SQLPlus also allows users to connect to remote or local databases, examine object definitions like tables and views, and store or retrieve command buffers for repeated use. These capabilities make it a versatile tool for database developers, administrators, and scripters. In recent versions such as 23ai and 26ai, SQLPlus has been enhanced to support new SQL features like the and improved error handling with commands such as OERR and SET ERRORDETAILS. In contrast to comprehensive graphical Integrated Development Environments (IDEs) like , SQLPlus is designed as a , platform-independent utility that operates without a , rendering it ideal for headless server environments, command-line scripting, and resource-constrained systems. The SQLPlus Instant Client variant further enhances this portability by providing a standalone without requiring a full local database installation. Effective use of SQL*Plus requires installation of the client software or the Instant Client package, which includes the necessary binaries and libraries. Additionally, database connectivity must be configured, typically through (TNS) entries in a or via direct Easy Connect strings specifying the host, port, and service name.

Core Components

SQLPlus maintains a SQL buffer as its primary internal storage mechanism for user-entered commands. This buffer temporarily holds the most recently executed SQL statement or block, excluding SQLPlus-specific commands, and persists until overwritten by new input. The buffer enables editing without retyping, supporting operations such as viewing contents with the command, replacing text in the current line via CHANGE (e.g., CHANGE /old_text/new_text), appending lines with , inserting new lines with INPUT, or deleting lines with . For more extensive modifications, the EDIT command loads the buffer into an external editor, allowing users to refine commands before re-execution with RUN or the forward slash (/). Input to SQLPlus occurs through multiple channels to facilitate both interactive and automated workflows. In interactive mode, users enter commands directly at the SQL> prompt, with each line processed upon pressing Return, and multi-line blocks terminated by a semicolon or forward slash. For batch operations, input can be drawn from script files using the @ or START command (e.g., @myscript.sql), which loads and executes the file's contents sequentially into the buffer. Additionally, SQLPlus supports piped input from standard input (stdin), enabling automation by redirecting commands from external processes, such as echo "SELECT * FROM table;" | sqlplus username/password. Output processing in SQL*Plus emphasizes structured presentation and control for reporting. By default, query results feature column headings derived from table or expression names, with widths automatically set based on types—such as 10 characters for NUMBER columns via the NUMWIDTH setting or the database-defined width for other types—and overflow handled by truncation or hash symbols. Users can suppress elements like headings (SET HEADING OFF) or feedback messages (SET FEEDBACK OFF) to streamline output, while the COLUMN command allows precise formatting (e.g., COLUMN [salary](/page/Salary) FORMAT $99,999). For persistence, the SPOOL command directs output to a file (e.g., SPOOL results.txt), capturing formatted results until SPOOL OFF, which is essential for logging in non-interactive sessions. Editor integration in SQLPlus is handled externally to leverage operating system tools, avoiding any native graphical interface. The EDIT command invokes the user's defined —typically vi on systems or on Windows, configurable via DEFINE _EDITOR = editor_name—to modify the SQL buffer or a specified file, returning changes upon exit. This approach ensures compatibility across platforms but relies on command-line or basic OS editors, with no embedded GUI for buffer editing. These components collectively underpin SQLPlus's role in scripting environments by enabling seamless input processing and output capture.

Commands and Syntax

SQL and PL/SQL Execution

SQLPlus facilitates the direct execution of standard SQL (DML) statements, including SELECT, INSERT, UPDATE, and DELETE, by allowing users to enter them interactively at the command prompt. These statements are processed by the connected server, with SQLPlus handling input, transmission, and output formatting. To execute a SQL statement, it is terminated with a (;) or by entering a forward slash (/) on a subsequent line, which prompts SQLPlus to run the contents of its SQL buffer. For query results from SELECT statements, SQLPlus automatically formats output with column headings enabled by default via the SET HEADING ON system variable, which can be toggled to control header display. For example, the following SQL statement retrieves employee data and displays it with formatted columns:

SQL> SELECT employee_id, last_name FROM employees WHERE department_id = 10;

SQL> SELECT employee_id, last_name FROM employees WHERE department_id = 10;

This executes immediately upon entering the slash (/) or , returning results in a tabular format with headers like "EMPLOYEE_ID" and "LAST_NAME". SQLPlus supports the execution of code, including anonymous blocks, stored procedures, and functions, by accepting procedural language constructs directly in the input buffer. Anonymous blocks are delimited with BEGIN and END keywords, terminated by a period (.) or forward slash (/), and executed upon encountering the terminator. Stored procedures and functions can be invoked using the EXECUTE command for single statements or by direct reference in a block. If compilation errors occur during execution, SQLPlus reports them via error messages, and the SHOW ERRORS command can be used to display detailed compilation issues. An example of executing an anonymous block to output a is:

SQL> SET SERVEROUTPUT ON SQL> BEGIN 2 DBMS_OUTPUT.PUT_LINE('Hello, SQL*Plus!'); 3 END; 4 / Hello, SQL*Plus!

SQL> SET SERVEROUTPUT ON SQL> BEGIN 2 DBMS_OUTPUT.PUT_LINE('Hello, SQL*Plus!'); 3 END; 4 / Hello, SQL*Plus!

Stored procedures are executed as follows:

SQL> EXECUTE procedure_name('argument');

SQL> EXECUTE procedure_name('argument');

This approach ensures immediate processing while providing feedback on runtime or compilation errors. Unlike native SQL execution in a database client without SQLPlus features, SQLPlus introduces enhancements for dynamic query building through substitution variables and the DEFINE command. Substitution variables, prefixed with an (&) for one-time use or (&&) for session persistence, allow runtime replacement of placeholders with user-provided values, enabling parameterized queries without recompilation. The DEFINE command pre-sets these variables explicitly, such as DEFINE deptno = 10, which can then be referenced in SQL or as &deptno. This facilitates interactive and scripted dynamic execution not available in pure SQL environments. For instance:

SQL> SELECT * FROM employees WHERE department_id = &deptno; Enter value for deptno: 20

SQL> SELECT * FROM employees WHERE department_id = &deptno; Enter value for deptno: 20

SQL*Plus prompts for the value and substitutes it seamlessly, distinguishing its capabilities from static SQL execution.

SQL*Plus-Specific Commands

SQLPlus provides a set of commands distinct from standard SQL and that enable users to manage sessions, format output, handle files, and interact with the host operating system, facilitating efficient database administration and scripting. These commands operate within the SQLPlus environment to control its behavior without directly affecting the or data.

Session Control

Session control commands in SQLPlus allow users to establish, maintain, and terminate connections to the Oracle database. The CONNECT command logs a user into a specified database instance, optionally with elevated privileges such as SYSDBA for administrative access. Its syntax is CONNECT [username[/password][@connect_identifier] [AS {SYSASM|SYSBACKUP|SYSDBA|SYSDG|SYSOPER|SYSKM}]], as in the example CONNECT SYS/password@orcl AS SYSDBA, which connects as SYS with password to the orcl database as a system database administrator. The DISCONNECT command severs the current database connection while keeping the SQLPlus session active, using the simple syntax DISCONNECT; this is useful for switching databases without exiting the tool. For complete termination, the EXIT command logs out of SQL*Plus entirely, with syntax EXIT [SUCCESS|FAILURE|WARNING|n] [COMMIT|ROLLBACK], such as EXIT SUCCESS COMMIT to end the session successfully and commit pending changes.

Formatting

Formatting commands customize the display of query results and session output to improve readability and fit specific reporting needs. The SET command configures various SQL*Plus environment variables, particularly for output control; for instance, SET PAGESIZE 50 limits each page of output to 50 lines, while SET LINESIZE 100 sets the maximum width of each line to 100 characters, preventing wrapping in wide reports. Complementing this, the COLUMN command formats individual columns in query results, specifying options like width or justification; an example is COLUMN ename FORMAT A10, which displays the ename column as a left-justified string of up to 10 characters. These commands are applied before executing SQL statements to ensure consistent presentation without altering the underlying data.

File Operations

File operations in SQLPlus support loading, saving, and redirecting content for scripting and logging purposes. The GET command retrieves the contents of a script file into the SQL buffer for editing or immediate execution, using syntax GET filename [.ext] [LIST|NOLIST], as in GET script.sql to load and optionally list the file's commands. Conversely, SAVE stores the current buffer contents to a file, with syntax SAVE filename [.ext] [CREATE|REPLACE|APPEND], such as SAVE myscript.sql to create or overwrite a file with the buffered SQL. The SPOOL command redirects output from SQLPlus sessions to a file, enabling capture of results for review or printing; its syntax is SPOOL [filename[.ext] [CREATE|REPLACE|APPEND]|OFF|OUT], exemplified by SPOOL output.txt to start spooling query results to a text file, followed by SPOOL OFF to stop.

Host and Shell Integration

To bridge SQLPlus with the underlying operating , the HOST command executes external programs or shell commands directly from within a session. Its syntax is HOST [command], allowing integration like HOST ls -l on systems to list files in the current directory or HOST dir on Windows for similar functionality. This command temporarily suspends SQLPlus, runs the host operation, and returns control upon completion, supporting tasks such as file management or queries without leaving the tool.

Usage

Starting and Basic Operations

SQLPlus is invoked from the command line using the sqlplus command, which can include credentials and connection details directly. For example, to connect as a specific user to a remote database, the syntax is sqlplus username/[password](/page/Password)@connect_identifier, such as sqlplus hr/mypassword@SALES1. administrative access is achieved with sqlplus / as sysdba, allowing privileged operations without specifying a username or on the same host. If credentials are omitted, SQLPlus prompts for the username and separately. The login process requires a valid username and password, with the connect identifier specifying the target database instance. Supported identifiers include TNS names resolved via the tnsnames.ora file, such as @SALES1, or Easy Connect strings in the format host:port/service_name, like @"sales-server:1521/sales.us.acme.com". For connections using Oracle Wallet or other secure stores, the syntax CONNECT /@database_alias can be used after initial login. To start SQL*Plus without connecting, the command SQLPLUS /NOLOG opens the tool in a disconnected state. Once connected, SQLPlus displays the SQL> prompt, where users enter SQL statements or SQLPlus commands interactively. SQL statements must be terminated with a semicolon (;) to execute, for instance, SELECT * FROM employees;. Commands are processed line by line, and multi-line statements continue until the semicolon is encountered. To explore available commands, users can invoke HELP INDEX or HELP TOPICS at the SQL> prompt, which lists all supported topics. For details on a specific command, HELP command_name provides syntax and usage, such as HELP ACCEPT. Schema information for database objects is retrieved using the DESCRIBE command, abbreviated as DESC, followed by the object name, e.g., DESCRIBE employees, which outputs column names, data types, and nullability. Basic navigation includes re-executing the most recent command or buffer contents with a forward slash (/) on a new line, which is useful for repeating queries without retyping. This operation runs the last SQL statement or SQL*Plus command entered.

Scripting and Batch Processing

SQLPlus supports scripting to automate repetitive database tasks by allowing users to create files containing sequences of SQL, , and SQLPlus commands. These scripts are typically saved with a .sql extension using any , and SQL statements within them must end with a (;), while PL/SQL blocks conclude with a forward slash (/). To execute a script from within a SQL*Plus session, the START command or its shorthand @ is used, followed by the filename, such as START myscript.sql, which runs the commands in the specified file sequentially. This enables modular workflows where multiple scripts can reference each other for complex operations. For batch processing in non-interactive environments, SQL*Plus operates in silent mode using the -s flag on the command line, suppressing prompts and banners, as in sqlplus -s username/password@database @script.sql, ideal for automated runs without user intervention. This mode facilitates headless execution on servers, integrating with operating system schedulers. Parameter passing enhances script flexibility; positional parameters like &1 substitute values provided at runtime, for example, SELECT * FROM employees WHERE dept_id = &1;, while the ACCEPT command prompts for input if not supplied, such as ACCEPT dept_num PROMPT 'Enter department ID:'. These mechanisms allow scripts to adapt dynamically without modification. A common example is a script that spools query results to a CSV file:

SPOOL employee_backup.csv SET PAGESIZE 0 SET LINESIZE 1000 SET FEEDBACK OFF SET HEADING ON SELECT employee_id || ',' || first_name || ',' || last_name || ',' || [salary](/page/Salary) FROM employees; SPOOL OFF EXIT

SPOOL employee_backup.csv SET PAGESIZE 0 SET LINESIZE 1000 SET FEEDBACK OFF SET HEADING ON SELECT employee_id || ',' || first_name || ',' || last_name || ',' || [salary](/page/Salary) FROM employees; SPOOL OFF EXIT

This script outputs comma-separated data suitable for export, and when invoked as sqlplus -s hr/hr@orcl @backup.sql, it generates the file silently. Such scripts can be scheduled for regular execution using operating system tools like Unix or to perform tasks such as daily s. Error handling within scripts is managed via the WHENEVER SQLERROR command to control behavior on failures, such as exiting or continuing.

Configuration and Integration

Installation and Setup

SQLPlus is bundled with Instant Client, which provides a minimal installation option, as well as full and Client installations supporting platforms including Windows, , and macOS. The Instant Client packages include the SQLPlus along with necessary libraries for basic functionality, while full installations offer additional tools and administrative features. Prerequisites for SQL*Plus deployment include Oracle Net Services for database connectivity, which is incorporated in the Instant Client Basic or Basic Light packages. Environment variables such as PATH must be configured to include the Instant Client directory, and on systems, LD_LIBRARY_PATH may be required to locate shared libraries; ORACLE_HOME is typically unnecessary for Instant Client but is set in full installations. Setup involves downloading the relevant packages from the website, such as the Basic and SQLPlus ZIP files for (version 23.26) as of November 2025. Extract the files to a designated directory (e.g., C:\instantclient on Windows or /opt//instantclient on Linux), then update the PATH environment variable to point to this directory. For Linux, RPM packages can be installed via yum from repositories. On macOS, DMG or ZIP files are available for x86-64 and architectures, with similar extraction and PATH configuration. To verify the installation, execute sqlplus -v in the command prompt or terminal, which displays the SQLPlus version information. Initial configuration requires editing files such as sqlnet.ora for network settings, located by default in the network/admin subdirectory of the installation directory or specified via the . This file configures parameters like naming methods and protocols for Net Services. Additionally, glogin.sql serves as a global startup script, typically placed in $ORACLE_HOME/sqlplus/admin/ for full installations or a user-defined location, to set site-wide defaults executed upon connection.

Variables and Customization

SQLPlus supports substitution variables to enable dynamic content in SQL statements and commands, allowing users to parameterize scripts and prompts for input at runtime. These variables are referenced using an (&) prefix for single-use substitution or double ampersand (&&) for persistent definition within the session. When SQLPlus encounters an undefined substitution variable, it prompts the user for a value, which is then substituted before executing the command; this feature is particularly useful for tailoring queries without hardcoding values. Substitution variables can be used anywhere in SQL and SQL*Plus commands except as the first word in a statement. User-defined substitution variables are created and managed through the DEFINE and UNDEFINE commands. The DEFINE command assigns a value to a variable, storing it as a character string up to 240 bytes, which can then be referenced in subsequent commands; for example, DEFINE dept = 10 creates a variable named "dept" with the value "10", allowing its use in queries like SELECT * FROM employees WHERE department_id = &dept. Variables defined with && are retained for the entire session unless explicitly undefined, while single & prompts anew each time. The UNDEFINE command removes a variable from the session, such as UNDEFINE dept, preventing accidental reuse and conserving session resources; SQL*Plus supports up to 2048 such variables simultaneously. System-defined substitution variables provide predefined session information without user intervention, accessible across SQL*Plus sessions and modifiable via commands. Examples include _CONNECT_IDENTIFIER, which holds the current database connection identifier, and others like SQL.USER for the current username or DATE for the system date; these are automatically available and can be referenced directly, such as in prompts or titles, to incorporate dynamic metadata into output. There are nine such predefined variables, all modifiable, ensuring consistent access to essential context like user details and timestamps. Customization in SQLPlus extends to startup scripts that automate session configuration for individual users or sites. The user profile script, typically named login.sql, executes automatically after a successful database connection, applying per-user settings such as formatting options or environment variables. For instance, it can include SET TIMING ON to display execution times for commands, or SET PAGESIZE 24 and SET LINESIZE 78 to control report layout; SQLPlus searches for login.sql in directories specified by the SQLPATH environment variable. Similarly, the site-wide glogin.sql, located in the SQL*Plus administration directory (e.g., $ORACLE_HOME/sqlplus/admin/ on Unix), runs before login.sql to enforce global defaults like NLS date formats via ALTER SESSION SET nls_date_format = 'DD-MON-YYYY HH24:MI:SS'. These scripts enhance usability by preconfiguring sessions without manual intervention each time. For advanced scenarios, the COLUMN command's NEW_VALUE clause captures query output into substitution variables, facilitating iterative scripting by storing column values for reuse. This is not a bind variable mechanism but a post-fetch assignment, where the variable receives the value from the specified column in the result set—typically the last row or per break group. An example illustrates this:

COLUMN ename NEW_VALUE empvar NOPRINT SELECT ename FROM emp WHERE deptno = 10;

COLUMN ename NEW_VALUE empvar NOPRINT SELECT ename FROM emp WHERE deptno = 10;

Here, after execution, &empvar holds the last employee's name from the query, which can then be used in subsequent statements like PRINT &empvar; the NOPRINT option suppresses column display in output. This feature supports dynamic report generation, such as populating titles with query-derived data when combined with TTITLE or BREAK commands, but requires the column to be ordered appropriately for accurate capture.

Error Handling and Logging

In SQLPlus, error trapping is primarily managed through the WHENEVER SQLERROR command, which specifies actions to take when a SQL statement or PL/SQL block encounters an Oracle error, such as an ORA- error code. This command does not respond to SQLPlus-specific errors but only to database-level SQL or PL/SQL failures, allowing scripts to conditionally terminate or continue execution. The syntax is WHENEVER SQLERROR {EXIT [SUCCESS | FAILURE | WARNING | n | variable | :BindVariable] [COMMIT | ROLLBACK] | CONTINUE [COMMIT | ROLLBACK | NONE]}, where EXIT halts the session with a specified return code (defaulting to the SQL error code if unspecified), and CONTINUE proceeds to the next command without interruption. For instance, WHENEVER SQLERROR EXIT FAILURE ROLLBACK instructs SQLPlus to exit with a failure status and undo any uncommitted changes upon error, enhancing script reliability in batch environments. Without this command, SQLPlus defaults to continuing execution after displaying the error message. Starting with Oracle AI Database 26ai, SQL*Plus displays a help URL alongside many error messages, providing direct access to detailed descriptions and resolution guidance. Logging mechanisms in SQL*Plus capture session output and errors for auditing or debugging. The SPOOL command directs output—including query results, error messages, and command echoes—to a file, facilitating persistent records of interactions. Its syntax is SPO[OL] [file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]] | OFF | OUT], where CREATE generates a new file, REPLACE overwrites an existing one (default), and APPEND adds to the end of a current file; OFF ceases logging without deleting the file, while OUT also sends it to the default printer on supported platforms. For PL/SQL compilation issues, the SHOW ERRORS command displays detailed error information from the most recent CREATE or ALTER operation on objects like procedures, packages, or triggers. Invoked as SHOW ERRORS [object_type [schema.]name], it lists line/column positions and error descriptions, such as PLS-00302: component 'NON_EXISTENT' must be declared, aiding rapid identification of syntax or semantic problems. Integration with the DBMS_OUTPUT package allows SQLPlus to display procedural output messages generated by code. To enable this, the SET SERVEROUTPUT command must be used, with syntax SET SERVEROUTPUT {ON | OFF | size_clause}, where ON activates retrieval of buffered messages via DBMS_OUTPUT.GET_LINES after each SQL or execution, and size_clause (e.g., SIZE 1000000) sets the buffer limit to prevent truncation. OFF disables this feature, suppressing output for cleaner sessions. This is essential for viewing debug statements from PUT_LINE calls within procedures, as SQLPlus does not fetch them by default. Best practices for error handling in SQL*Plus emphasize combining tool-specific controls with 's built-in functions for robust, custom responses. Within exception handlers, SQLCODE returns the numeric (e.g., -1 for cursor-related issues) and SQLERRM provides the associated message string (up to 512 bytes), enabling precise logging or recovery actions. These functions are only meaningful inside handlers and must be assigned to variables before use in SQL statements to avoid errors. For transaction integrity, a common approach is to implement in an OTHERS handler upon failure, as in the following example:

BEGIN -- Transaction logic here UPDATE employees SET salary = salary * 1.1; COMMIT; EXCEPTION WHEN OTHERS THEN DECLARE err_code NUMBER := SQLCODE; err_msg VARCHAR2(512) := SQLERRM; BEGIN ROLLBACK; -- Undo changes on error DBMS_OUTPUT.PUT_LINE('Error ' || err_code || ': ' || err_msg); END; END; /

BEGIN -- Transaction logic here UPDATE employees SET salary = salary * 1.1; COMMIT; EXCEPTION WHEN OTHERS THEN DECLARE err_code NUMBER := SQLCODE; err_msg VARCHAR2(512) := SQLERRM; BEGIN ROLLBACK; -- Undo changes on error DBMS_OUTPUT.PUT_LINE('Error ' || err_code || ': ' || err_msg); END; END; /

This ensures data consistency by reverting changes and logging details for review, while specific handlers (e.g., for NO_DATA_FOUND) can override for targeted continuations. Prioritizing named exceptions over broad OTHERS clauses improves performance and maintainability.

Compatibility and Limitations

Supported Oracle Versions

SQLPlus maintains broad compatibility with Oracle Database releases, providing full support from Oracle 9i (9.0.1) through the latest long-term support version, Oracle AI Database 26ai (via Release Update in October 2025), with SQLPlus Release 26ai available as of November 2025. The official client/server interoperability matrix outlines supported combinations, allowing SQL*Plus as part of the Oracle Client to connect to database servers typically within two prior major releases while ensuring certification for current and recent versions. Backward compatibility enables older SQL*Plus versions, such as those bundled with 11g, to establish connections to newer s, including 12c and beyond; however, these legacy clients lack support for database-specific enhancements like native data handling introduced in 12c Release 1. This means users may encounter limitations in formatting output or utilizing server-side features without upgrading the client. Forward compatibility requires newer SQL*Plus versions to access advanced capabilities in environments like Oracle Autonomous Database, where features such as wallet-based secure connections and optimized cloud networking demand clients from Oracle Database 19c or later for seamless integration. Notable deprecations include iSQLPlus, a web-based interface, which was removed starting with Oracle Database 11g Release 1, shifting focus to command-line SQLPlus and graphical alternatives. The 19c Long Term Support release introduced no significant changes to SQLPlus core functionality, preserving stability for extended support periods. In contrast, Oracle AI Database 26ai includes enhancements to SQLPlus, such as new commands for argument handling (ARGUMENT) and connectivity testing (PING), while providing database support for AI-driven features like vector data types and query optimizations for AI vector search, accessible via SQLPlus. As of October 2025, Oracle Database 23ai was updated to Oracle AI Database 26ai via Release Update 23.26.0, introducing SQLPlus enhancements including the CONFIG command for generating configuration files and improved error details.
Oracle Database VersionSQL*Plus Version SupportKey Compatibility Notes
9i (9.2)9.2.xBasic connectivity; limited to legacy SQL features.
11g (11.2)11.2.xFull support; connects to up to 19c with feature gaps.
12c (12.2)12.2.xJSON support added; backward to 11g, forward to 26ai with limits.
19c LTS19.x.xStable; no major SQL*Plus changes, certified through 2027 Premier Support.
26ai (26.0)26aiNew SQL*Plus commands (e.g., ARGUMENT, PING); supports database AI features like vector types via SQL; full with prior clients where possible (as of November 2025).

Platform and Environment Support

SQL*Plus, as a tool bundled with client installations including Instant Client, is supported on multiple operating systems as of 2025. It runs natively on 64-bit Windows systems, providing full compatibility for database administration and querying tasks. distributions are extensively supported, encompassing architectures on and other compatible variants, as well as ARM64 for modern server environments. Unix-based platforms including Solaris on and , AIX on Power Systems, and are also certified, ensuring enterprise-grade reliability in legacy and setups. For macOS, support is available through Instant Client packages for both and (ARM64) processors, enabling development and testing on Apple hardware. Environment requirements for SQLPlus remain lightweight, requiring no Java runtime unlike the deprecated iSQLPlus web interface, which relied on Java for browser-based access. It operates as a native executable, depending primarily on the Oracle client libraries and standard system utilities for connectivity to Oracle Database instances. Containerized deployments are fully supported, with SQL*Plus included in official Oracle Database Docker images for Oracle Linux, allowing seamless integration into microservices architectures and CI/CD pipelines without additional configuration. Key limitations include the absence of native support for mobile operating systems such as or Android, restricting its use to desktop and server environments. As a text-based tool, SQL*Plus is not designed for graphical user interfaces or high-volume reporting scenarios that demand visual formatting, making it less suitable for applications requiring charts, dashboards, or interactive visualizations. Portability across platforms is achieved through consistent command syntax and semantics, despite binaries being compiled for specific architectures, which facilitates script reuse in heterogeneous environments. support has been integral since Oracle 9i, enabling handling of international character sets via NLS parameters like AL32UTF8 for global data processing.

Alternatives and Extensions

Graphical User Interfaces

Oracle SQL Developer, introduced in early 2006, serves as a free (GUI) successor to SQLPlus, providing an for management. It includes a dedicated for interactive SQL query execution, enabling users to browse database objects, edit code, and run scripts in a visual context. A key feature is its support for importing and executing SQLPlus scripts, allowing seamless transition from command-line workflows to a graphical format while preserving compatibility with existing scripts. Complementing SQL Developer, Oracle SQLcl, released in 2016, represents a command-line evolution of SQLPlus with enhanced features for modern database interactions. Built as a Java-based interface, it incorporates auto-completion for SQL statements and commands, inline , and support for scripting via embedded engines like , facilitating advanced automation beyond traditional SQLPlus capabilities. While retaining the efficiency of a CLI for and scripting, SQLcl bridges to GUI tools by integrating with SQL Developer's framework. Third-party tools like Toad for Oracle and extend SQLPlus functionality through visual environments with emulation features. Toad for Oracle, developed by , offers an option to execute SQL via SQLPlus directly within its GUI, supporting command emulation for formatting, pagination, and output handling in a graphical editor with . Similarly, provides integration for running SQLPlus scripts by configuring Oracle connections to leverage the native SQLPlus executable, enabling users to combine visual query building with command-line precision in a multi-database tool. Transitioning to these GUIs from SQLPlus yields benefits such as , integrated debugging for , and visual result grids, which streamline ad-hoc querying and code development absent in the text-based SQLPlus interface. However, the command-line nature of SQL*Plus and its evolutions like SQLcl remains essential for automated scripting and server-side operations where graphical overhead is impractical.

Modern Tools and Successors

As Oracle's long-standing command-line interface for executing SQL and PL/SQL, SQLPlus has been supplemented by modern tools that address its limitations in usability, integration, and feature depth. The primary successor in the command-line domain is Oracle SQLcl, introduced in 2016 as a free, enhanced alternative that builds directly on SQLPlus's foundation while incorporating scripting capabilities from Oracle SQL Developer. SQLcl supports all SQL*Plus commands for backward compatibility but adds modern features such as command history, auto-completion, syntax highlighting, and integration with tools like Liquibase for database change management. It also includes an embedded JavaScript engine for advanced scripting and supports connections to Oracle Cloud and on-premises databases, making it suitable for DevOps workflows and automation. As of 2025, SQLcl version 25.2 introduces the Model Context Protocol (MCP) server for integrating with AI-driven applications. For graphical user interfaces, serves as a comprehensive modern tool that extends beyond SQLPlus's text-based interaction, offering a free (IDE) for management. Released in and continually updated, SQL Developer provides visual tools for browsing database objects, editing code with debugging support, generating reports, and performing , all while maintaining compatibility with SQLPlus scripts through its . It supports over 5 million users across Windows, macOS, and , and includes extensions for migration from third-party databases to , as well as browser-based Database Actions for web access without installation. Third-party modern tools have also emerged as versatile alternatives, often providing cross-database support to reduce dependency on Oracle-specific clients. DBeaver, an open-source, multi-platform SQL client, offers a user-friendly GUI for Oracle connections with features like ER diagrams, query formatting, and data export, making it popular for development teams handling multiple database types. Similarly, dbForge Studio for Oracle provides advanced querying, schema comparison, and tools tailored for Oracle, with a focus on productivity enhancements over SQL*Plus's basic output. These tools prioritize intuitive interfaces and extensibility, reflecting a shift toward integrated environments for contemporary database administration.

References

  1. https://docs.oracle.com/en/database/oracle/oracle-database/26/sqpug/SQL-Plus-quick-start.html
  2. https://www.oreilly.com/library/view/oracle-sql-plus-the/1565925785/ch01s02.html
  3. https://docs.oracle.com/en/database/oracle/oracle-database/26/sqpug/
  4. https://www.informit.com/articles/article.aspx?p=1390284&seqNum=3
  5. https://www.oreilly.com/library/view/oracle-sql-plus-the/0596007469/ch01s02.html
  6. https://www.orafaq.com/wiki/SQL%2APlus_FAQ
  7. https://www.orafaq.com/wiki/Oracle_5
  8. https://forums.oracle.com/ords/apexds/post/a-history-of-oracle-5117
  9. https://docs.oracle.com/cd/E18283_01/server.112/e16604/ch_eleven.htm
  10. https://docs.oracle.com/cd/B19306_01/server.102/b14357.pdf
  11. https://docs.oracle.com/database/122/ADMIN/overview-of-managing-a-multitenant-environment.htm
  12. https://docs.oracle.com/en/database/oracle/oracle-database/19/sqprn/index.html
  13. https://docs.oracle.com/en/database/oracle/oracle-database/21/sqprn/index.html
  14. https://oracle-base.com/articles/23/sqlplus-new-features-23
  15. https://mikedietrichde.com/2025/10/14/oracle-ai-database-26ai-replaces-oracle-database-23ai/
  16. https://docs.oracle.com/en/database/oracle/oracle-database/23/sqpug/index.html
  17. https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlqr/SQL-Plus-Commands.html
  18. https://docs.oracle.com/en/database/oracle/oracle-database/26/sqpug/changes-this-release-sqpug.html
  19. https://docs.oracle.com/cd/B14117_01/server.101/b12170/qstart.htm
  20. https://docs.oracle.com/en/database/oracle/oracle-database/21/sqpug/SQL-Plus-basics.html
  21. https://docs.oracle.com/en/database/oracle/oracle-database/21/sqpug/using-scripts-in-SQL-Plus.html
  22. https://docs.oracle.com/en/database/oracle/oracle-database/19/sqpug/using-scripts-in-SQL-Plus.html
  23. https://docs.oracle.com/en/database/oracle/oracle-database/21/sqpug/formatting-SQL-Plus-reports.html
  24. https://docs.oracle.com/cd/B10500_01/server.920/a90842/ch5.htm
  25. https://docs.oracle.com/en/database/oracle/oracle-database/23/sqpug/substitution-variables.html
  26. https://docs.oracle.com/en/database/oracle/oracle-database/26/sqpug/starting-SQL-Plus.html
  27. https://docs.oracle.com/en/database/oracle/oracle-database/26/sqpug/using-scripts-in-SQL-Plus.html
  28. https://docs.oracle.com/en/database/oracle/oracle-database/26/sqpug/START.html
  29. https://docs.oracle.com/en/database/oracle/oracle-database/26/sqpug/ACCEPT.html
  30. https://docs.oracle.com/en/database/oracle/oracle-database/26/sqpug/WHENEVER-SQLERROR.html
  31. https://www.oracle.com/database/technologies/instant-client.html
  32. https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html
  33. https://docs.oracle.com/en/database/oracle/oracle-database/23/ntcli/installing-oracle-instant-client.html
  34. https://www.oracle.com/database/technologies/instant-client/downloads.html
  35. https://www.oracle.com/database/technologies/instant-client/winx64-64-downloads.html
  36. https://yum.oracle.com/repo/OracleLinux/OL9/oracle/instantclient23/x86_64/index.html
  37. https://docs.oracle.com/en/database/oracle/oracle-database/21/sqpug/using-substitution-variables-sqlplus.html
  38. https://docs.oracle.com/en/database/oracle/oracle-database/26/sqpug/configuring-SQL-Plus.html
  39. https://docs.oracle.com/en/database/oracle/oracle-database/19/sqpug/COLUMN.html
  40. https://docs.oracle.com/en/database/oracle/oracle-database/21/sqpug/WHENEVER-SQLERROR.html
  41. https://docs.oracle.com/en/database/oracle/oracle-database/21/sqpug/SPOOL.html
  42. https://docs.oracle.com/en/database/oracle/oracle-database/21/sqpug/SHOW.html
  43. https://docs.oracle.com/en/database/oracle/oracle-database/21/sqpug/SET.html
  44. https://docs.oracle.com/en/database/oracle/oracle-database/21/lnpls/plsql-error-handling.html
  45. https://docs.oracle.com/en/database/oracle/oracle-database/26/sqpug/sqlplus-users-guide-and-reference.pdf
  46. https://support.oracle.com/knowledge/Oracle%20Database%20Products/207303_1.html
  47. https://docs.oracle.com/cd/B13789_01/server.101/b10749/ch6unico.htm
  48. https://www.oracle.com/database/technologies/appdev/sqldev/history.html
  49. https://docs.oracle.com/en/database/oracle/sql-developer/23.1/index.html
  50. https://oracledeli.wordpress.com/2011/09/23/sql-developer_execute_via_sqlplus/
  51. https://www.thatjeffsmith.com/archive/2018/10/sell-me-on-oracle-sqlcl-in-50-seconds/
  52. https://docs.oracle.com/en/database/oracle/sql-developer-command-line/23.1/sqcug/index.html
  53. https://www.thatjeffsmith.com/archive/2022/04/running-javascript-in-oracle-sqlcl-22-1/
  54. https://support.quest.com/toad-for-oracle/17.1
  55. https://dbeaver.com/docs/dbeaver/SQL-Plus-Script-Execution/
  56. https://www.oracle.com/database/sqldeveloper/technologies/sqlcl/
  57. https://connor-mcdonald.com/2025/07/14/first-dabblings-with-sqlcl-mcp-server/
  58. https://www.oracle.com/database/sqldeveloper/
  59. https://www.devart.com/dbforge/oracle/studio/best-oracle-database-gui-tools.html
  60. https://uibakery.io/blog/oracle-gui-tools
Add your contribution
Related Hubs
User Avatar
No comments yet.