SQL PLUS

SQL*Plus: A Comprehensive Guide to Commands and Configuration

SQL*Plus is the classic command-line interface for Oracle Database. It allows developers to execute SQL statements, run PL/SQL blocks, and format query results (Oracle, 2025). The following guide details the essential commands available in SQL*Plus, based on the official Oracle documentation.

---

1. Running Scripts

The @ and @@ commands are used to execute SQL scripts (Oracle, 2026).

• @pathname: Runs an SQL Script. For example, @MyScript.sql parameter1 parameter2. Inside the script, refer to parameters as &1, &2, etc.
• @@pathname: Runs a sub-script from the same directory as the main script.

2. Substitution Variables

Substitution variables are text variables unique to SQL*Plus that can be used to hold values for generic scripts (Oracle, 2025).

• &variable: A substitution variable.
• &&variable: A substitution variable valid for the entire session.

3. Buffer and Editing Commands

• /: Execute (or re-execute) commands in the SQL*Plus buffer without listing them (Oracle, 2026).
• APPEND text: Add text to the end of the current line in the buffer.
• CHANGE /oldval/newval: Change text on the current line.
• CLEAR: Clear the SQL*Plus screen and buffer.
• DEL: Delete the current line in the SQL buffer.
• EDIT: Load the SQL*Plus buffer into an editor.
• INPUT: Add line(s) to the buffer.
• LIST: Display buffer lines.
• RUN: Execute commands in the buffer, listing them before running.
• SAVE file: Save the contents of the buffer to a command file.

4. Formatting and Reporting

• BREAK: Specify where and how formatting will change.
• BTITLE: Place and format a title at the bottom of each page.
• COLUMN: Change the display width of a column.
• COMPUTE: Calculate and display totals.
• TTITLE: Define a page title.

5. Session and Database Control

• CONNECT username/password@SID: Connect to a database as a specified user (Oracle, 2026).
• DISCONNECT: Logoff (but do not exit SQL*Plus).
• EXIT [n]: Commit, logoff, and exit.
• SHUTDOWN [ABORT|IMMEDIATE|NORMAL]: Shut down the database.
• STARTUP [NoMOUNT|MOUNT|OPEN]: Start the database.
• SQLPLUS: Start SQL*Plus and connect to a database.

6. Variables and Bind Variables

• ACCEPT variable [NUMBER|CHAR|DATE]: Prompt the user for input.
• DEFINE varName = String: Define a user variable.
• UNDEFINE varName: Delete a substitution variable.
• VARIABLE variable [NUMBER|CHAR|REFCURSOR]: Define a bind variable that can be used in both SQL*Plus and PL/SQL (Oracle, 2026).
• PRINT variable: List the value of a bind variable or REF Cursor.
• SHOW: List the value of a system variable.

7. Miscellaneous Commands

• DESC[RIBE] object: Describe a table, view, or procedure (Oracle, 2026).
• EXECUTE statement: Run a single PL/SQL statement.
• HELP topic: Display help for a SQL*Plus command.
• HOST command: Execute an operating system command.
• PAUSE message: Wait for the user to press RETURN.
• PROMPT message: Echo a message to the screen.
• REMARK comment: Add a comment in a script.
• SPOOL file: Store query results in a file.
• TIMING START|SHOW|STOP: Record timing data.
• WHENEVER SQLERROR: Exit if an SQL or PL/SQL error occurs.

8. Customizing the SQL*Plus Prompt

To display the currently connected UserName and SID instead of just SQL>, you can use the following command (Oracle, 2025):

SET sqlprompt '&_user:&_connect_identifier > '

Add this line to the file $ORACLE_SID/sqlplus/admin/glogin.sql. This tip requires Oracle 10g or greater.

---

References

Oracle. (2025). SQL*Plus user's guide and reference (19c). Oracle Help Center. https://docs.oracle.com/en/database/oracle/oracle-database/19/sqpug/index.html

Oracle. (2026). SQL language quick reference: SQL*Plus commands (26). Oracle Help Center. https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlqr/SQL-Plus-Commands.html