Line Editing

The concept of line-editing goes way back to the days when all many people had to work with were dumb terminals that didn't allow full-screen editing, and connection speeds were so slow that full-screen editing would have been very painful anyway. A good line editor will allow you to work productively at connection speeds as low as 300 bits per second. While working at that speed isn't much of a concern today, it accurately reflects the environment at the time SQL*Plus was first conceived.

The line-editing process in SQL*Plus follows these steps:

1. Enter a SQL statement or PL/SQL block, which SQL*Plus stores in the buffer.
2. List the contents of the buffer to the screen.
3. Enter SQL*Plus commands telling SQL*Plus to make changes to the statement or block in the buffer.
4. List the buffer again.
5. If you like what you see, execute the statement or block; otherwise , you go back to step 3 and make some more changes.

I can remember that in my younger days my fellow programmers and I always took great pride in the number of line-editing changes we could make and visualize in our heads before we had to break down and list our code again.

2.7.1 The Current Line

When working with the line editor in SQL*Plus, you must understand the concept of the current line . The current line is the one that you have most recently "touched." When you are entering a statement, the most recently entered line is the current line.

The statement shown in Example 2-9 is six lines long. Line 7 doesn't count and is not added to the buffer because that's where the blank line is used to terminate entry of the statement. In this case, the last line "touched" happens to be the last line entered, so line 6 is the current line.

Example 2-9. Line 5 is the current line

SQL> SELECT employee_name, project_name 2 FROM employee JOIN project_hours 3 ON employee.employee_id = project_hours.employee_ id 4 JOIN project 5 ON project_hours.project_id = project.project_id 6 GROUP BY employee_name, project_name 7 SQL>

Most line-editing commands, by default, operate on the current line. Some commands, such as LIST and DEL, allow you to specify a line number. When you specify a line number for an editing command, the command is executed, and that line then becomes the new current line. You'll see how this works as you read through the examples that follow.

2.7.2 Line-Editing Commands

SQL*Plus implements a number of useful line-editing commands, some of which have several variations. Most of these commands may be abbreviated to one letter. Table 2-1 describes each of these commands and shows the abbreviations and variations for each one.

Table 2-1. SQL*Plus line-editing commands

Notice that two of the commands, LIST and DEL, allow you to specify a line number or a range of line numbers . For these two commands, there are two special keywords you can use in place of a number. These keywords are * and LAST, and have the following meanings:

*

An asterisk always refers to the current line.

LAST

The keyword LAST refers to the last line in the buffer.

You will see examples of how these elements are used as you read more about each of the commands.

2.7.2.1 Getting a statement into the buffer

To put a SQL statement into the buffer, enter the statement and terminate it with a blank line, as shown in Example 2-10.

Example 2-10. Entering a SQL statement into the buffer

SQL> SELECT * 2 FROM project 3 SQL>

The statement is inserted into the buffer one line at a time as you enter it. Pressing Enter on a blank line tells SQL*Plus to leave the statement in the buffer without transmitting it to the server. PL/SQL blocks are entered the same way except that you terminate them by entering a period on the last line. Example 2-11 shows one of the shortest PL/SQL block you can write.

Example 2-11. A very short PL/SQL block

SQL> BEGIN 2 NULL; 3 END; 4 . SQL>

Terminating the block with a period tells SQL*Plus not to send it to the database, but to keep it in the buffer.

2.7.2.2 LIST

The LIST command shows you the current contents of the buffer. It is fundamental to the use of the other line-editing commands. Use LIST to view your SQL statement as it currently exists to see if any changes need to be made. Use LIST after making changes to be sure that they were made correctly.

Look at Example 2-12, which shows a SQL statement being entered into SQL*Plus, and then shows the LIST command being used to display it again.

Example 2-12. Listing the buffer

SQL> SELECT employee_name, time_log_name, project_name 2 FROM employee JOIN 3 ON employee.employee_num = time_log.employee_num 4 JOIN project 5 ON time_log.project_id = project.project_num 6 HAVING employee_num = project_name 7 GROUP BY employee_name, project_name 8 SQL> LIST 1 SELECT employee_name, time_log_name, project_name 2 FROM employee JOIN 3 ON employee.employee_num = time_log.employee_num 4 JOIN project 5 ON time_log.project_id = project.project_num 6 HAVING employee_num = project_name 7* GROUP BY employee_name, project_name

Notice the asterisk marking line 7. The asterisk indicates the current line, which LIST always sets to be the last line displayed. You can display just the current line by using LIST *, as in the following example:

SQL> LIST * 7* GROUP BY employee_name, project_name

You can display one specific line by specifying the line number as an argument to the LIST command. The next example shows how to list line 3:

SQL> LIST 3 3* ON employee.employee_num = time_log.employee_num

Notice the asterisk. By listing line 3 you have made it the current line for editing purposes.

The keyword LAST may be used to display the last line in the buffer:

SQL> LIST LAST 7* GROUP BY employee_name, project_name

You may specify a range of lines to be displayed. Do this by specifying the starting and ending lines as arguments to the LIST command. Either or both of these arguments may be the keyword LAST or *. Following are several different ways to display a range of lines using LIST:

SQL> LIST 1 3 List lines 1 through 3 1 SELECT employee_name, time_log_name, project_name 2 FROM employee JOIN 3* ON employee.employee_num = time_log.employee_num SQL> LIST * LAST List everything beginning from the current line 3 ON employee.employee_num = time_log.employee_num 4 JOIN project 5 ON time_log.project_id = project.project_num 6 HAVING employee_num = project_name 7* GROUP BY employee_name, project_name SQL> LIST 4 * List from line 4 through 7 (the current line) 4 JOIN project 5 ON time_log.project_id = project.project_num 6 HAVING employee_num = project_name 7* GROUP BY employee_name, project_name SQL> LIST * * A one-line range, same effect as LIST * 7* GROUP BY employee_name, project_name SQL> LIST LAST LAST A one-line range, same as LIST LAST 7* GROUP BY employee_name, project_name

As a shortcut to using the LIST command, if you are only interested in one line, you can list it by entering the line number and then pressing Enter. This won't work for a range of lines, but it will work for just one. Here's an example:

SQL> 3 3* ON employee.employee_num = time_log.employee_num

On a seven-line statement, you might wonder why you would ever bother to list just one line or a range of lines. Remember, line speeds were slow when SQL*Plus was first developed. In addition, SQL statements and PL/SQL blocks are often much longer than seven lines. Listing a range allows you to focus on one area at a time while you fix it.

Keep the SQL statement from Example 2-12 in the buffer (or at least in mind) as you read about the rest of the line-editing commands. It has several mistakes that we'll fix using the other commands.

2.7.2.3 APPEND

Use the APPEND command to add text onto the end of a line. It works on the current line, so you must first decide which line you want to change and then make that line current. Use the LIST command to review the SQL statement currently in the buffer:

SQL> LIST 1 SELECT employee_name, time_log_name, project_name 2 FROM employee JOIN 3 ON employee.employee_num = time_log.employee_num 4 JOIN project 5 ON time_log.project_id = project.project_num 6 HAVING employee_num = project_name 7* GROUP BY employee_name, project_name

I intended this SELECT statement to join all three sample tables, but if you look at line 2, you will see that I forgot to include the project_hours table. This can be corrected by first making line 2 the current line and then using the APPEND command to add the third table to the join. The first step is to LIST line 2 in order to make it current:

SQL> L 2 2* FROM employee JOIN

Now that line 2 is the current line, the APPEND command may be used to add project_hours to the join:

SQL> A project_hours 2* FROM employee JOIN project_hours

It's a bit difficult to see from the example, but two spaces follow the A (for APPEND) command. The first space separates the command from the text you wish to append. SQL syntax requires a space following the keyword JOIN, so my text to append consisted of a space followed by the table name . Now the SELECT statement in the buffer joins all three tables.

2.7.2.4 CHANGE

The CHANGE command searches the current line for a specified string and replaces that string with another. CHANGE replaces only the first occurrence it finds, so if you need to change multiple occurrences of a string in the same line, you will need to execute the same CHANGE command several times. CHANGE may also be used to simply delete text from a line.

List the contents of the buffer again. Your output should match that shown below:

SQL> LIST 1 SELECT employee_name, time_log_name, project_name 2 FROM employee JOIN project_hours 3 ON employee.employee_num = time_log.employee_num 4 JOIN project 5 ON time_log.project_id = project.project_num 6 HAVING employee_num = project_name 7* GROUP BY employee_name, project_name

Line 1 references a column that does not exist. A little later you will see how to remove that column reference with the CHANGE command. Next, the two ON clauses contain four mistakes: the table name time_log is used twice instead of project_hours , and employee_num is used twice when it really should be employee_id . The CHANGE command can be used to fix these problems. To start with, here's how to change time_log to project_hours :

SQL> L 3 3* ON employee.employee_num = time_log.employee_num SQL> C /time_log/project_hours/ 3* ON employee.employee_num = project_hours.employee_num SQL> L 5 5* ON time_log.project_id = project.project_num SQL> c /time_log/project_hours/ 5* ON project_hours.project_id = project.project_num

In this example, the LIST command is first used to make line 3 the current line. Then the CHANGE command, abbreviated to C, is used to change the table name. After the edit is complete, the line is automatically redisplayed so you can see the effects of the change. The process is repeated to make the same change to line 5. You can change only one line at a time.

Next, the employee_num field name needs to be corrected. It should be employee_id . Although the two occurrences of employee_num are in the same line, CHANGE will have to be executed twice. The following example shows this:

SQL> L 3 3* ON employee.employee_num = project_hours.employee_num SQL> c /employee_num/employee_id/ 3* ON employee.employee_id = project_hours.employee_num SQL> c /employee_num/employee_id/ 3* ON employee.employee_id = project_hours.employee_id

Notice that the CHANGE command searched the current line from left to right. The leftmost occurrence of employee_num was the first to be changed. Notice also that the CHANGE command had to be retyped each time. SQL*Plus does not have any command-recall capability.

Now that line 3 is fixed up, take another look at line 1. This time, omit the L command, and just type the line number in order to list the line:

SQL> 1 1* SELECT employee_name, time_log_name, project_name

Line 1 contains a bad column name, which needs to be deleted. A variation of the CHANGE command, where you don't supply any replacement text, can be used to do this. The following example shows how:

SQL> C /time_log_name, // 1* SELECT employee_name, project_name

At first glance, the use of the CHANGE command to delete text may not seem very intuitive. Think in terms of searching for a string, in this case for " time_log_name , " and replacing it with nothing.

With the CHANGE command, you can use delimiters other than the forward slash character. You simply need to be consistent within the command. SQL*Plus interprets the first non-space character following the CHANGE command as the delimiter character. The following commands, for example, are all equivalent:

C /FRUB/FROM/ C *FRUB*FROM* C XFRUBXFROMX

The only time you would ever need to use a delimiter other than / is if you need to include a / as part of the text to be searched for or replaced . You have the option of leaving off the trailing delimiter as long as you aren't trying to include trailing spaces in your substitution string. The following two commands are equivalent:

C /FRUB/FROM/ C /FRUB/FROM

However, if your substitution strings contain spaces, you do need to include the trailing delimiter. The following two commands will not produce equivalent results:

C / FRUB / FROM / C / FRUB / FROM

It's probably easiest to be in the habit of including the trailing delimiter all the time. You'll make fewer mistakes that way.

2.7.2.5 DEL

Use the DEL command to erase one or more lines from the buffer. Used by itself, DEL erases the current line. You may specify a line, or a range of lines, as an argument to the DEL command. Unlike the other line-editing commands, DEL cannot be abbreviated. This is perhaps a safety measure to keep you from accidentally deleting a line.

If you have been following along through all the line-editing examples, use the L command to list the buffer. You should see the following output:

SQL> L 1 SELECT employee_name, project_name 2 FROM employee JOIN project_hours 3 ON employee.employee_id = project_hours.employee_id 4 JOIN project 5 ON project_hours.project_id = project.project_num 6 HAVING employee_num = project_name 7* GROUP BY employee_name, project_name

Line 6, with its HAVING clause, is completely spurious . It can be erased by specifying the DEL command as follows:

SQL> DEL 6 SQL> L * 6* GROUP BY employee_name, project_name

SQL*Plus doesn't echo anything back at you, but line 6 has been erased. Notice that L * was used following the delete to list the current line, which is now line 6. Why line 6? Because 6 was the number of the line most recently touched by an editing command. In this case, the original line 6 was erased, what was line 7 became line 6, and the new line 6 became current.

The DEL command may be used to erase a range of lines. As with LIST, the keywords LAST and * may be used to specify the last line in the buffer and the current line, respectively. The following example shows how to erase lines 4 through the current line, which is line 6:

SQL> DEL 4 * SQL> L * 3* ON employee.employee_id = project_hours.employee_id

Because line 6 was current, the DEL command just shows erased lines 4 through 6. The new current line would ordinarily still be line 4 because that was the last number line touched (erased) but, in this case, because only three lines are left in the buffer, the last line becomes current.

2.7.2.6 INPUT

The INPUT command is used to insert one or more lines of text into the buffer. The INPUT command with a text argument allows you to insert only one line, which is placed into the buffer following the current line. The INPUT command with no arguments puts you into a multiline input mode where you can type as many lines as desired, ending with a blank line. These lines are inserted into the buffer following the current line.

List the buffer again. You can see that we have done serious damage to our SELECT statement by our most recent, and evidently careless, deletion:

SQL> L 1 SELECT employee_name, project_name 2 FROM employee JOIN project_hours 3* ON employee.employee_id = project_hours.employee_id

The original intent was to list each employee together with all projects to which the employee actually charged hours. To do that, the join to project and the GROUP BY clause need to be put back in. The following example shows how to insert the GROUP BY clause by using the INSERT command with a text argument:

SQL> L Make line 3 current, in order to insert after it 1 SELECT employee_name, project_name 2 FROM employee JOIN project_hours 3* ON employee.employee_id = project_hours.employee_id SQL> I GROUP BY employee_name, project_name SQL> L 1 SELECT employee_name, project_name 2 FROM employee JOIN project_hours 3 ON employee.employee_id = project_hours.employee_id 4* GROUP BY employee_name, project_name

An easier alternative, when you have several lines to insert, would be to use the INPUT command with no arguments. This places you into input mode , in which you can type as many lines as you like. Pressing a blank line exits input mode, and terminates the entry. Here's how to put back the join to the project table using this method:

SQL> L 3 3* ON employee.employee_id = project_hours.employee_id SQL> I 4i JOIN project 5i ON project_hours.project_id = project.project_id 6i SQL> L 1 SELECT employee_name, project_name 2 FROM employee JOIN project_hours 3 ON employee.employee_id = project_hours.employee_id 4 JOIN project 5 ON project_hours.project_id = project.project_id 6* GROUP BY employee_name, project_name

The LIST command was used to make line 3 current, so that new lines will be inserted after it. Then the I (for INPUT) command was used by itself to enter input mode, and the two lines defining the join to project were entered into the buffer. The prompt included an "i" following the line number to remind you that you were inserting lines into an existing statement.

If you are picky about formatting, use the second form of the INPUT command shown above. That will let you enter leading spaces to make things line up nicely . INPUT text will trim off leading spaces before text is inserted.

To add lines at the end of a buffer, first do a LIST or a LIST LAST to make the last line current. Then use the INPUT command to put yourself into input mode. Any lines you type will be appended onto the end of the buffer.

To add a line to the beginning of the buffer, prior to the first line, add it as line 0:

SQL> SELECT * FROM dually 2 SQL> L 1* SELECT * FROM dually SQL> 0 WITH dually AS (SELECT * FROM dual) SQL> L 1 WITH dually AS (SELECT * FROM dual) 2* SELECT * FROM dually

2.7.2.7 Retyping a line

Using the line editor, you can completely replace a line in the buffer by entering the desired line number followed by a new version of the line. Following is our now executable statement:

SQL> L 1 SELECT employee_name, project_name 2 FROM employee JOIN project_hours 3 ON employee.employee_id = project_hours.employee_id 4 JOIN project 5 ON project_hours.project_id = project.project_id 6* GROUP BY employee_name, project_name

Suppose that for reasons of aesthetics, or perhaps to follow your site's coding standards, you wish to make the JOIN keyword flush-left. You can do that by retyping the entire line in one go, as follows:

SQL> 4 JOIN project SQL> l 1 SELECT employee_name, project_name 2 FROM employee JOIN project_hours 3 ON employee.employee_id = project_hours.employee_id 4 JOIN project 5 ON project_hours.project_id = project.project_id 6* GROUP BY employee_name, project_name

Notice that line 4 has been replaced by the text that was typed after the numeral 4 on the first line of this example. You can replace any line in this way. If you want to preserve the indenting, you can insert extra spaces following the line number.