Editing Commands

Because SQL*Plus does not store SQL*Plus commands in the buffer, you edit a SQL*Plus command entered directly to the command prompt by using [Backspace] or by re-entering the command.

You can use a number of SQL*Plus commands to edit the SQL command or PL/SQL block currently stored in the buffer. Alternatively, you can use a host operating system editor to edit the buffer contents.

Table 3-1 lists the SQL*Plus commands that allow you to examine or change the command in the buffer without re-entering the command.

Table 3-1 SQL*Plus Editing Commands

APPEND text

A text

CHANGE /old/new

C /old/new

CHANGE /text

C /text

CLEAR BUFFER

CL BUFF

DEL

(none)

DEL n

(none)

DEL * 

(none)

DEL n *

(none)

DEL LAST

(none)

DEL m n

(none)

DEL * n

(none)

INPUT

I

INPUT text

I text

LIST

L

LIST n

L n  or  n

LIST * 

L *

LIST n *

L n *

LIST LAST

L LAST

LIST m n

L m n

LIST * n

L * n

You will find these commands useful if you mistype a command or wish to modify a command you have entered.

Listing the Buffer Contents

Any editing command other than LIST and DEL affects only a single line in the buffer. This line is called the current line. It is marked with an asterisk when you list the current command or block.

Example 3-1 Listing the Buffer Contents

Suppose you want to list the current command. Use the LIST command as shown below. (If you have EXITed SQL*Plus or entered another SQL command or PL/SQL block since following the steps in Example 2-3, perform the steps in that example again before continuing.)

SQL> LIST
  1  SELECT EMPNO, ENAME, JOB, SAL
  2* FROM EMP WHERE SAL < 2500

Notice that the semicolon you entered at the end of the SELECT command is not listed. This semicolon is necessary to mark the end of the command when you enter it, but SQL*Plus does not store it in the SQL buffer. This makes editing more convenient, since it means you can add a new line to the end of the buffer without removing a semicolon from the line that was previously the last.

Editing the Current Line

The SQL*Plus CHANGE command allows you to edit the current line. Various actions determine which line is the current line:

• LIST a given line to make it the current line.

• When you LIST or RUN the command in the buffer, the last line of the command becomes the current line. (Note, that using the slash (/) command to run the command in the buffer does not affect the current line.)

• If you get an error message, the line containing the error automatically becomes the current line.

Example 3-2 Making an Error in Command Entry

Suppose you try to select the DEPTNO column but mistakenly enter it as DPTNO. Enter the following command, purposely misspelling DEPTNO in the first line:

SQL> SELECT DPTNO, ENAME, SAL
  2 FROM EMP
  3 WHERE DEPTNO = 10;

You see this message on your screen:

SELECT DPTNO, ENAME, SAL
*
ERROR at line 1:
ORA-0904:  invalid column name

Examine the error message; it indicates an invalid column name in line 1 of the query. The asterisk shows the point of error--the mistyped column DPTNO.

Instead of re-entering the entire command, you can correct the mistake by editing the command in the buffer. The line containing the error is now the current line. Use the CHANGE command to correct the mistake. This command has three parts, separated by slashes or any other non-alphanumeric character:

• the word CHANGE or the letter C

• the sequence of characters you want to change

• the replacement sequence of characters

The CHANGE command finds the first occurrence in the current line of the character sequence to be changed and changes it to the new sequence. If you wish to re-enter an entire line, you do not need to use the CHANGE command: re-enter the line by typing the line number followed by a space and the new text and pressing [Return].

Example 3-3 Correcting the Error

To change DPTNO to DEPTNO, change the line with the CHANGE command:

SQL> CHANGE /DPTNO/DEPTNO

The corrected line appears on your screen:

1* SELECT DEPTNO, ENAME, SAL

Now that you have corrected the error, you can use the RUN command to run the command again:

SQL> RUN

SQL*Plus lists the command, and then runs it:

1  SELECT  DEPTNO, ENAME, SAL
2  FROM    EMP
3* WHERE   DEPTNO = 10
DEPTNO ENAME     SALARY
------ --------  ----------
10     CLARK     $2,450
10     KING      $5,000
10     MILLER    $1,300

Note that the column SAL retains the format you gave it in Example 2-4. (If you have left SQL*Plus and started again since performing Example 2-4, the column has reverted to its original format.)

For information about the significance of case in a CHANGE command and on using wildcards to specify blocks of text in a CHANGE command, refer to the CHANGE command in Chapter 8.

Adding a New Line

To insert a new line after the current line, use the INPUT command.

To insert a line before line 1, enter a zero ("0") and follow the zero with text. SQL*Plus inserts the line at the beginning of the buffer and that line becomes line 1.

SQL> 0 SELECT EMPNO

Example 3-4 Adding a Line

Suppose you want to add a fourth line to the SQL command you modified in Example 3-3. Since line 3 is already the current line, enter INPUT (which may be abbreviated to I) and press [Return]. SQL*Plus prompts you for the new line:

SQL> INPUT
  4

Enter the new line. Then press [Return]. SQL*Plus prompts you again for a new line:

4  ORDER BY SAL
5

Press [Return] again to indicate that you will not enter any more lines, and then use RUN to verify and re-run the query.

Appending Text to a Line

To add text to the end of a line in the buffer, use the APPEND command:

1. Use the LIST command (or just the line number) to list the line you want to change.

2. Enter APPEND followed by the text you want to add. If the text you want to add begins with a blank, separate the word APPEND from the first character of the text by two blanks: one to separate APPEND from the text, and one to go into the buffer with the text.

Example 3-5 Appending Text to a Line

To append a space and the clause DESC to line 4 of the current query, first list line 4:

SQL> LIST 4
  4* ORDER BY SAL

Next, enter the following command (be sure to type two spaces between APPEND and DESC):

SQL> APPEND  DESC
  4* ORDER BY SAL DESC

Use RUN to verify and re-run the query.

Deleting Lines

To delete lines in the buffer, use the DEL command:

1. Use the LIST command (or just the line numbers) to list the lines you want to delete.

2. Enter DEL with an optional clause.

Suppose you want to delete the current line to the last line inclusive. Use the DEL command as shown below.

SQL> DEL * LAST

DEL makes the following line of the buffer (if any) the current line.

For more information, see the DELETE command in Chapter 8.

Editing Commands with a System Editor

Your computer's host operating system has one or more text editors that you can use to create and edit host system files. Text editors perform the same general functions as the SQL*Plus editing commands, but you may find them more familiar.

You can run your host operating system's default text editor without leaving SQL*Plus by entering the EDIT command:

SQL> EDIT

EDIT loads the contents of the buffer into your system's default text editor. You can then edit the text with the text editor's commands. When you tell the text editor to save edited text and then exit, the text is loaded back into the buffer.

To load the buffer contents into a text editor other than the default, use the SQL*Plus DEFINE command to define a variable, _EDITOR, to hold the name of the editor. For example, to define the editor to be used by EDIT as EDT, enter the following command:

SQL> DEFINE _EDITOR = EDT

You can also define the editor to be used by EDIT in your user or site profile. See "Setting Up Your SQL*Plus Environment" later in this chapter and the DEFINE and EDIT commands in Chapter 8 for more information.