Lesson 1

SQL*Plus is the primary command-line interface to an Oracle database, and it has been for as long as most working DBAs can remember. This module introduces SQL*Plus and shows you how to use some of its more advanced features to write scripts and generate well-organized reports. It builds directly on the connectivity work from Module 2: once you can connect to your COIN database using the tools covered there, SQL*Plus is the tool you'll actually be typing into once that connection is open.

When you have completed this module, you should be comfortable with the following tasks:

1. Generating SQL*Plus reports with page headers, page footers, formatted columns, and column titles
2. Executing scripts
3. Spooling report and script output to a file
4. Using the SQL*Plus built-in, line-editing commands
5. Using substitution variables and prompting a user for input
6. Using SQL*Plus and SQL to generate SQL scripts based on information in the data dictionary

Mastering SQL*Plus is worth the effort, partly because of what you can do with it, but also because it is so widely available. Think of it as the standard language of database administration. Not every Oracle site has Enterprise Manager installed, and not every cloud environment gives you a graphical console. If you move around a lot doing work for different clients, a working knowledge of SQL*Plus and SQL lets you get things done regardless of what other tools a given client happens to have. It's also, in a very literal sense, the lowest common denominator: if a machine has Oracle client software on it at all, SQL*Plus works.

SQL*Plus is an interactive and batch query tool installed with every Oracle Database installation. In Oracle AI Database 26ai, it is a command-line interface, standard across every supported operating system. When SQL*Plus starts, it displays the version and copyright information before presenting its prompt:

Oracle AI Database 26ai Enterprise Edition Release 23.26.1.0.0 - Production

SQL>

That SQL> prompt tells you SQL*Plus is ready to accept commands.

SQL*Plus has its own commands and environment, and it provides access to the Oracle Database. It lets you enter and execute SQL, PL/SQL, SQL*Plus, and operating system commands to:

1. Format, perform calculations on, store, and print query results
2. Examine table and object definitions
3. Develop and run batch scripts
4. Perform database administration

You can use SQL*Plus to generate reports interactively or as batch processes, and send the output to a text file, the screen, or an HTML file for browsing.

One detail worth knowing early, since it can otherwise be confusing the first time you notice it: SQL*Plus automatically looks for two startup scripts every time it launches. The Site Profile, named glogin.sql and typically found at $ORACLE_HOME/sqlplus/admin/glogin.sql, contains site-wide settings an administrator has configured for every user on that installation. The User Profile, named login.sql, contains settings specific to an individual user, and takes effect immediately after the Site Profile runs.

This is exactly why the same SQL*Plus commands can produce differently formatted output on two different machines: someone customized one machine's glogin.sql or a user's login.sql, and the other machine never had that customization applied. If you ever see unexpected formatting, unexpected column widths, or unexpected environment settings the moment SQL*Plus starts, checking both profile scripts is a reasonable first step. Whether these scripts run automatically at all is controlled by a setting called ORA_PLUS_AUTOEXEC; if it's set to disable, neither script executes automatically.

Alongside the standard SQL*Plus installation, Oracle also supplies the SQL*Plus Instant Client: a stand-alone command-line interface for platforms that support the OCI Instant Client. It connects to any available Oracle database over the network without requiring its own local Oracle Database installation, which makes it a convenient option when you need SQL*Plus on a machine that isn't otherwise running Oracle software.

You can get SQL*Plus Instant Client onto a machine in one of two ways: downloading the installation packages directly from the Oracle Technology Network, or copying the relevant files from an existing Oracle Database 21c Client Administrator installation, if one is already available to you. Whichever path you choose, the SQL*Plus and OCI packages both need to come from the same Oracle Database version, or you'll run into mismatches.

Instant Client comes in two variants worth knowing about:

• Basic Instant Client supports any NLS_LANG setting the Oracle Database itself supports, including every available character set and language.
• Lightweight Instant Client is significantly smaller, but trades away some flexibility: error messages appear in English only, and only specific character sets are supported.

For a quick troubleshooting laptop or a lightweight jump box where disk space matters more than full internationalization support, the Lightweight variant is often the more practical choice. For anything client-facing, or anywhere you need full character set support, Basic is the safer default. See the SQL*Plus User's Guide and Reference for the full installation and configuration details for either variant.

It's worth knowing, as you get started, that SQL*Plus isn't the only command-line option Oracle provides. SQLcl is Oracle's modern, actively developed command-line client, built to work alongside SQL*Plus rather than replace it outright. Where SQL*Plus emphasizes stability and universal availability, SQLcl adds conveniences like command history, tab completion, and built-in support for several modern output formats. Everything you learn about SQL and SQL*Plus commands in this module carries over directly to SQLcl, since both tools accept the same underlying SQL and PL/SQL.

This module focuses on SQL*Plus specifically, for two reasons. First, it remains the more universally available tool across Oracle installations; every environment that has Oracle client software has SQL*Plus, which isn't always true of SQLcl. Second, SQLcl's added conveniences are genuinely easier to appreciate once you already understand the fundamentals SQL*Plus teaches. Once you're comfortable with the material in this module, moving to SQLcl for day-to-day work is a natural next step, not a separate skill to learn from scratch.

The SQL*Plus, SQL, and PL/SQL command languages are powerful enough to serve users with real database experience, yet straightforward enough for someone just learning to work with Oracle. For example, to rename a column labeled LAST_NAME with the heading "Family Name," you'd enter:

COLUMN LAST_NAME HEADING 'Family Name'

Similarly, to list column definitions for the EMPLOYEES table, you'd enter:

DESCRIBE EMPLOYEES

And to run a saved script rather than typing commands interactively every time, you'd enter:

@my_report.sql

None of these commands require deep expertise to use correctly, which is exactly the point: SQL*Plus rewards you for learning it gradually, one command at a time, rather than demanding you master everything before you can be productive with it.

Each objective listed earlier in this lesson gets its own dedicated treatment later in this module, and it's worth previewing what each one actually involves before you get there:

• Report formatting covers page headers, page footers, and column formatting in real depth: controlling column widths, applying number formats, and giving columns readable titles instead of raw table column names.
• Script execution covers running saved .sql files, both interactively and as part of automated batch processes, so you're not retyping the same queries every time you need them.
• Spooling covers capturing everything SQL*Plus displays, whether from a script or an interactive session, into a permanent file you can review, archive, or hand off to someone else.
• Line-editing commands cover SQL*Plus's built-in ability to modify a command you've already entered without retyping the whole thing, useful the moment you make a small typo in an otherwise long statement.
• Substitution variables cover writing scripts that prompt the person running them for input, rather than hardcoding specific values, which is what turns a one-off query into a genuinely reusable tool.
• Data-dictionary-driven script generation covers something DBAs use constantly in practice: writing a SQL*Plus script that queries the data dictionary and generates other SQL statements as its output, letting you automate work across dozens or hundreds of objects at once instead of writing each statement by hand.

Taken together, these six skills are what separate someone who can run a query from someone who can build genuinely useful, repeatable administrative tooling with nothing but SQL*Plus and a text editor.