SQL* Plus CLI  «Prev  Next»

Lesson 15Line editing
ObjectiveUse SQL*Plus line-editing commands.

SQL Plus Line Editing Commands

SQL*Plus provides a set of line-editing commands to use in editing SQL statements. While these line-editing commands are crude by today's standards, they do allow you to correct minor mistakes without having to retype the entire SQL statement. There are just a few commands to learn, and they can save you a lot of time.

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 in the buffer, you can make changes to it, execute it, and make changes again. The line-editing commands all operate on the current statement in the buffer. The following series of images illustrates their use:

Oracle Line Editing Commands

1) Use the LIST command, abbreviated to L, to list lines in the buffer
1) Use the LIST command, abbreviated to L, to list lines in the buffer. By itself, L lists the entire buffer. You may optionally supply a line number to list, or a range of line numbers to list. You can list a single line, by simply typing the number.

2) The CHANGE, abbreviated to C, command may be used to change test on line
2) The CHANGE, abbreviated to C, command may be used to change test on line, or to delete text. To delete text, change it without supplying a new value.

3) Use the DEL command, which may not be abbreviated, to delete one line or a range of lines
3) Use the DEL command, which may not be abbreviated, to delete one line or a range of lines.

4) SQL Line Editing 4
4) Use the INSERT command, abbreviated to I, to insert lines into the buffer. The insertion occurs after the line marked with an asterisk, which is the line most recently listed.

5) Use the APPEND command, abbreviated to A, to append text onto the end of a line.
5) Use the APPEND command, abbreviated to A, to append text onto the end of a line. Leave two spaces after the A command if you want one to precede the text that you are appending.

6) Use the forward-slash command to execute the SQL statement currently in the buffer.
6) Use the forward-slash command to execute the SQL statement currently in the buffer.

  1. Use the LIST command, abbreviated to L, to list lines in the buffer.
  2. The CHANGE, abbreviated to C, command may be used to change test on line, or to delete text.
  3. Use the DEL command, which may not be abbreviated, to delete one line or a range of lines.
  4. Use the INSERT command, abbreviated to I, to insert lines into the buffer.
  5. Use the APPEND command, abbreviated to A, to append text onto the end of a line.
  6. Use the forward-slash command to execute the SQL statement currently in the buffer.


SQL statements remain in the buffer only until you execute another SQL statement. If you have a 100-line statement in the buffer and you inadvertently type COMMIT, the 100-line statement will be replaced by the word COMMIT. If you are editing long statements, make sure to copy them to a text file before you begin.

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.