Using the SQL*Plus Environment

SQL*Plus is the foundational command-line interface for interacting with Oracle Database. It ships with every Oracle installation and has been used by developers, DBAs, and analysts for decades. While Oracle now provides modern GUI tools and web-based management interfaces, SQL*Plus remains essential for scripting, automation, remote administration, and fast execution of SQL and PL/SQL commands. This lesson explains what SQL*Plus is, how it operates, and when it should be your tool of choice.

What SQL*Plus Provides

SQL*Plus is a versatile programming environment for SQL and PL/SQL. You can use it to:

1. Run commands interactively in a terminal or command window.
2. Execute predefined scripts in batch mode.
3. Create and modify database users, tables, indexes, and other schema objects.
4. Control environment settings for generating readable, formatted output.
5. Test and debug SQL and PL/SQL code by running it repeatedly and receiving immediate feedback.

Many administrators rely on SQL*Plus to generate custom scripts for granting privileges, building schema objects, automating maintenance tasks, or creating periodically refreshed web pages. SQL*Plus supports complete automation via shell scripts and scheduled jobs, making it ideal for environments where repeatability and reliability matter.

Using SET ERRORLOGGING

SQL*Plus includes the SET ERRORLOGGING command, which records SQL, PL/SQL, and SQL*Plus errors into a log table. When enabled, SQL*Plus writes errors automatically to the SPERRORLOG table, creating it if necessary.

Example:

-- enable error logging
SQL> set errorlogging on

-- bad query
SQL> select * from dud;
ORA-00942: table or view does not exist

Inspecting the structure of the logging table:

SQL> desc sperrorlog

Name         Null?     Type
------------ --------- ----------------------------
USERNAME               VARCHAR2(256)
TIMESTAMP              TIMESTAMP(6)
SCRIPT                 VARCHAR2(1024)
IDENTIFIER             VARCHAR2(256)
MESSAGE                CLOB
STATEMENT              CLOB

Querying the logged errors:

SQL> select username, timestamp, statement, message 
       from sperrorlog;

USERNAME  TIMESTAMP                        STATEMENT
--------- -------------------------------- -------------------------
MESSAGE
-------------------------------------------
SYS       11-AUG-07 09.54.47.000000 PM     select * from dud
ORA-00942: table or view does not exist

Interfaces: Command Line, Windows GUI, and SQLPlus Worksheet

SQL*Plus is available in two main interfaces:

• Command-Line Interface — The traditional terminal-based environment.
• Windows GUI Interface — A simple graphical wrapper around SQL*Plus commands.

Oracle Enterprise Manager introduced the SQLPlus Worksheet, which runs inside a web browser and provides a more modern interface. Although it resembles SQL*Plus, it lacks the full set of SQL*Plus environment commands and is not a complete replacement for SQL*Plus reporting features.

SQL*Plus in the Modern Oracle Toolset

Oracle continues expanding its GUI- and web-based administration tools, gradually phasing out older command-line components like SVRMGR. However, SQL*Plus remains relevant for automation, script execution, and environments where GUI tools are impractical. Enterprise Manager’s SQLPlus Worksheet supports code execution but relies heavily on automated SQL generation and lacks SQL*Plus’s rich reporting features.

Most modern report-writing and analysis tasks are now handled by tools such as Oracle Developer, Oracle APEX, BI Publisher, or third-party reporting engines. SQL*Plus remains most valuable for rapid scripting, administrative tasks, and simple report generation.

Enterprise Manager Cloud Control 12c

Enterprise Manager Cloud Control provides a unified platform for managing Oracle environments, both on-premises and in the cloud. It includes:

1. Complete cloud lifecycle management.
2. Deep integration with Oracle technologies for stack-level intelligence.
3. Business-driven application monitoring and performance diagnostics.

Although SQL*Plus is not replaced by Cloud Control, many routine tasks traditionally executed manually in SQL*Plus can now be automated or orchestrated using Cloud Control’s GUI and automation tools.

Who Should Use SQL*Plus

SQL*Plus is useful for beginners and experienced users alike. It provides simple syntax, immediate feedback, and predictable behavior. For example, renaming a column heading is as simple as:

COLUMN LAST_NAME HEADING 'Family Name'

To view the columns of a table:

DESCRIBE EMPLOYEES

SQL*Plus Command-Line Architecture

SQL*Plus follows a two-tier architecture:

1. Client — The command-line interface where you type commands.
2. Database — Oracle Database, accessed through Oracle Net services.

The client and database may be on the same machine or separated across the network. SQL*Plus communicates with Oracle Database using Oracle Net packets, ensuring reliable and efficient command processing.