SQL* Plus CLI  «Prev  Next»

Lesson 15 Line Editing
Objective Use SQL*Plus line-editing commands.

SQL*Plus Line Editing Commands

SQL*Plus provides a set of line-editing commands for correcting SQL statements without retyping them entirely. These commands are crude by the standards of a modern code editor, and they remain fully current in Oracle AI Database 26ai regardless: there are only a handful of them, and they can save real time once you're comfortable with them.

The SQL Buffer

Whenever you enter and execute a SQL statement, SQL*Plus holds that statement in an area of memory known as the SQL buffer[1]. As long as the statement remains there, you can list it, change it, execute it, and change it again. Every line-editing command in this lesson operates on whatever is currently sitting in that buffer.

LIST

Use the LIST command, abbreviated to L, to list lines in the buffer 
L-LIST

Syntax
    L
    L line_num
    L start end
Example 
SQL> L
  1  SELECT table_name
  2  FROM dba_tables
  3  WHERE owner='SYSTEM'
  4* ORDER BY table_name
SQL> L 2 3
  2  FROM dba_tables
  3* WHERE owner='SYSTEM'
Use the LIST command, abbreviated to L, to list lines in the buffer. By itself, L lists the entire buffer. You can supply a single line number to list just that line, or a range to list several lines at once. A bare semicolon (;) also lists the entire buffer, as a shortcut for L with no arguments.
Notice the asterisk after line 4 in the example above. That marks the current line, the one that later commands like CHANGE and APPEND act on by default. Listing a line, or a range ending on a particular line, makes that line the new current line.

CHANGE

The CHANGE command, abbreviated to C, changes text on the current line 
C-CHANGE

Syntax
    C /old_text/new_text/
    C /old_text/
Example 
SQL> 3
  3* WHERE owner='SYSTEM'
SQL> C /SYSTEM/SYSG/
  3* WHERE owner='SYSG'
SQL> C /G/
  3* WHERE owner='SYS'
CHANGE, abbreviated to C, replaces the first occurrence of specified text on the current line. Typing just the line number, as shown above, makes that line current before editing it. To delete text instead of replacing it, run CHANGE with only the old text and no replacement.
A few details worth knowing that aren't obvious from the basic pattern above: CHANGE searches for old text without regard to case, so C /aq/aw/ matches "aq," "AQ," "aQ," or "Aq" equally. It also supports a wildcard-style marker using three dots. Prefixing old with "..." matches everything up through old's first occurrence; suffixing it matches old and everything after it on the line; and embedding "..." in the middle of old matches everything between two specified fragments. This makes CHANGE considerably more capable than a simple exact-text swap once you need to edit a longer or more complex line precisely.

DEL

The DEL command deletes one line or a range of lines and cannot be abbreviated 
DEL

Syntax
    DEL line_num
    DEL start end
Example 
SQL> L
  1  SELECT table_name
  2  SELECT table_name
  3  FROM dba_tables
  4* WHERE owner='SYS'
SQL> DEL 1
SQL> L
  1  SELECT table_name
  2  FROM dba_tables
  3* WHERE owner='SYS'
DEL deletes one line or a range of lines from the buffer. Unlike every other command in this lesson, DEL has no abbreviated form; it must be typed out in full.

INPUT

The INPUT command, abbreviated to I, inserts new lines into the buffer 
I - INPUT

Syntax
    I
    I text
Example 
SQL> L
  1  SELECT table_name
  2* FROM dba_tables
SQL> I
  3  WHERE owner='SYS'
  4
SQL> L
  1  SELECT table_name
  2  FROM dba_tables
  3* WHERE owner='SYS'
INPUT, abbreviated to I, inserts one or more new lines after the current line, the line marked with an asterisk. This command is genuinely named INPUT, not INSERT; older material sometimes gets this wrong, but I is short for INPUT.
INPUT has two behaviors worth knowing beyond the basic single-line case. Enter INPUT with no text at all, and SQL*Plus switches into a multi-line entry mode, prompting for each new line until you type a blank line or a single period to finish. And if you specify line number 0 followed by text, SQL*Plus inserts that text as a brand new line 1, ahead of everything already in the buffer, rather than after the current line.

APPEND

The APPEND command, abbreviated to A, appends text to the end of the current line 
A - APPEND

Syntax
    A text
Example 
SQL> L
  1  SELECT table_name
  2  FROM
  3* WHERE owner='SYSTEM'
SQL> L 2
  2* FROM
SQL> A dba_tables
  2* FROM dba_tables
APPEND, abbreviated to A, adds text to the end of the current line. Leave two spaces after the A command if you want a leading space before the appended text.
One gotcha worth knowing before you rely on APPEND for anything ending in a semicolon: SQL*Plus treats a single trailing semicolon as an optional command terminator, not literal text. If you actually need the appended text to end with a semicolon, type two semicolons at the end instead of one; otherwise your semicolon disappears rather than landing in the buffer.

/ (Forward Slash)

The forward-slash command executes the SQL statement currently in the buffer 
/

Syntax
    /
Example 
SQL> L
  1  SELECT table_name
  2  FROM dba_tables
  3* WHERE owner='SYSTEM'
SQL> /

TABLE_NAME
------------------------------
AQ$_QUEUES
AQ$_QUEUE_TABLES
A forward slash by itself executes whatever SQL command or PL/SQL block is currently held in the buffer.

EDIT: A Full-Editor Alternative

For a genuinely long or complicated statement, working line by line with LIST, CHANGE, APPEND, INPUT, and DEL can feel slower than it needs to be. EDIT, abbreviated ED, offers a different path: it invokes your operating system's own text editor, either on a named file or, if you omit the filename, directly on the current contents of the SQL buffer. Once you save and close the editor, the edited content becomes the new buffer content, ready to run with a slash. Worth noting: EDIT's file search uses a variable called ORACLE_PATH, which is distinct from the SQLPATH variable covered earlier in this module for the @ and @@ commands; the two look similar but serve different commands.

Summary

  1. LIST, abbreviated L, lists lines in the buffer; a bare semicolon does the same for the whole buffer.
  2. CHANGE, abbreviated C, replaces or deletes text on the current line, case-insensitively, with optional wildcard-style matching.
  3. DEL, which cannot be abbreviated, deletes one line or a range of lines.
  4. INPUT, abbreviated I, inserts one or more new lines into the buffer, either after the current line or, using line 0, at the very beginning.
  5. APPEND, abbreviated A, adds text to the end of the current line, with special handling required for a trailing semicolon.
  6. The forward slash executes whatever is currently in the buffer.
  7. EDIT, abbreviated ED, hands the buffer off to a full external text editor for anything too involved for line-by-line editing.
One habit worth building from day one: the buffer holds exactly one statement at a time, and it lasts only until you execute another one. Type a hundred-line statement into the buffer, then absentmindedly type COMMIT and press Enter, and that hundred-line statement is simply gone, replaced by the single word you just ran. If you're editing anything substantial, copy it to a text file first, or use EDIT directly on a named file, so a stray command doesn't cost you the work.

SQL*Plus Line Editing - Quiz

Here is a short quiz to test your knowledge of the line-editing commands.
SQL Plus Line Editing - Quiz

[1]SQL buffer: A memory area used by SQL*Plus to hold the most recently entered SQL statement or PL/SQL block.

[1] SQL buffer: A memory area used by SQL*Plus to hold the most recently entered SQL statement or PL/SQL block.

SEMrush Software