Editing Commands

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 shows several SQL*Plus commands that allow you to examine or change the command in the buffer without re-entering the command.

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 LAST	(none)	deletes the last line
DEL m n	(none)	deletes a range of lines (m to 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 LAST	L LAST	lists the last line
LIST m n	L m n	lists a range of lines (m to n)
Table 3 - 1. SQL*Plus Editing Commands

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

Listing the Buffer Contents

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

• 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. (Using the slash (/) command to run the command in the buffer does not affect the current line, however.)

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

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

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 CHANGE.

Adding a New Line

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 rerun the query.

Appending Text to a Line

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.

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 rerun the query.

Deleting Lines

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.

SQL> DEL * LAST

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

For more information, see DEL.

Editing Commands with a System Editor

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" and DEFINE and EDIT for more information.