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

Command	Abbreviation	Purpose
APPEND text	A text	adds text at the end of a line
CHANGE /old/new	C /old/new	changes old to new in a line
CHANGE /text	C /text	deletes text from a line
CLEAR BUFFER	CL BUFF	deletes all lines
DEL	(none)	deletes the current line
DEL n	(none)	deletes line n
DEL *	(none)	deletes the current line
DEL n *	(none)	deletes line n through the current line
DEL LAST	(none)	deletes the last line
DEL m n	(none)	deletes a range of lines (m to n)
DEL * n	(none)	deletes the current line through line n
INPUT	I	adds one or more lines
INPUT text	I text	adds a line consisting of text
LIST	L	lists all lines in the SQL buffer
LIST n	L n or n	lists line n
LIST *	L *	lists the current line
LIST n *	L n *	lists line n through the current line
LIST LAST	L LAST	lists the last line
LIST m n	L m n	lists a range of lines (m to n)
LIST * n	L * n	lists the current line through line n

You will find these commands useful if you mis-type 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.)

LIST




SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID, SALARY
  2  FROM EMP_DETAILS_VIEW
  3* WHERE SALARY>12000

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 append a new line to the end of the buffer without removing a semicolon.

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 JOB_ID column but mistakenly enter it as JO_ID. Enter the following command, purposely misspelling JOB_ID in the first line:

SELECT EMPLOYEE_ID, LAST_NAME, JO_ID, SALARY
FROM EMP_DETAILS_VIEW
WHERE JOB_ID='SA_MAN';

You see this message on your screen:

SELECT EMPLOYEE_ID, LAST_NAME, JO_ID, SALARY
                               *
ERROR at line 1:
ORA-00904: 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 mis-typed column JOB_ID.

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. You do not need to use the CHANGE command to re-enter an entire line. 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 JO_ID to JOB_ID, change the line with the CHANGE command:

CHANGE /JO_ID/JOB_ID

The corrected line appears on your screen:

1* SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, SALARY

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

RUN

SQL*Plus correctly displays the query and its result:

  1  SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID, SALARY
  2  FROM EMP_DETAILS_VIEW
  3* WHERE JOB_ID='SA_MAN'

EMPLOYEE_ID LAST_NAME                 JOB_ID     MONTHLY SALARY
----------- ------------------------- ---------- --------------
        145 Russell                   SA_MAN            $14,000
        146 Partners                  SA_MAN            $13,500
        147 Errazuriz                 SA_MAN            $12,000
        148 Cambrault                 SA_MAN            $11,000
        149 Zlotkey                   SA_MAN            $10,500

Note that the column SALARY 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.

0 SELECT EMPLOYEE_ID

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.

INPUT

SQL*Plus prompts you for the new line:

4

Enter the new line. Then press Return.

4 ORDER BY SALARY

SQL*Plus prompts you again for a new line:

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.

  1  SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID, SALARY
  2  FROM EMP_DETAILS_VIEW
  3  WHERE JOB_ID='SA_MAN'
  4* ORDER BY SALARY

EMPLOYEE_ID LAST_NAME                 JOB_ID     MONTHLY SALARY
----------- ------------------------- ---------- --------------
        149 Zlotkey                   SA_MAN            $10,500
        148 Cambrault                 SA_MAN            $11,000
        147 Errazuriz                 SA_MAN            $12,000
        146 Partners                  SA_MAN            $13,500
        145 Russell                   SA_MAN            $14,000

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:

LIST 4


4* ORDER BY SALARY

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

APPEND  DESC


4* ORDER BY SALARY DESC

Type RUN to verify the query:

  1  SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID, SALARY
  2  FROM EMP_DETAILS_VIEW
  3  WHERE JOB_ID='SA_MAN'
  4* ORDER BY SALARY DESC

EMPLOYEE_ID LAST_NAME                 JOB_ID     MONTHLY SALARY
----------- ------------------------- ---------- --------------
        145 Russell                   SA_MAN            $14,000
        146 Partners                  SA_MAN            $13,500
        147 Errazuriz                 SA_MAN            $12,000
        148 Cambrault                 SA_MAN            $11,000
        149 Zlotkey                   SA_MAN            $10,500

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.

DEL * LAST

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

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

Editing Commands with a System Editor

Your computer's host operating system may have 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:

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:

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.