To put a SQL statement into the buffer, simply enter the statement and terminate it with a blank line, as shown here:

SQL> SELECT * 
  2    FROM project
  3

The statement is actually inserted into the buffer one line at a time as you enter it. Pressing ENTER on a blank line tells SQL*Plus to leave it there 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. Following is an example of the shortest PL/SQL block one can write:

SQL> BEGIN
  2    NULL;
  3  END;
  4  .

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

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 the following example, which shows a SQL statement being entered into SQL*Plus, and then shows the LIST command being used to display it again:

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

Notice the asterisk marking line number 6. 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 *
  6*  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*  WHERE 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. For example:

SQL> LIST LAST
  6*  GROUP BY employee_name, project_name

You may also specify a range of lines to be displayed. This is done by specifying both the starting and ending lines as arguments to the LIST command. Either or both of these arguments may be the keyword LAST or *. The next example shows 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, project
  3*  WHERE employee.employee_num = time_log.employee_num  Line 3 is now current

SQL> LIST * LAST	
                  List everything beginning from the current line

  3   WHERE employee.employee_num = time_log.employee_num
  4  HAVING employee_name = project_name
  5     AND time_log.project_num = project.project_num
  6*  GROUP BY employee_name, project_name  Line 6 is now current

SQL> LIST 4 *	
                  List from line 4 through 6 (the current line)

  4  HAVING employee_name = project_name
  5     AND time_log.project_num = project.project_num
  6*  GROUP BY employee_name, project_name
SQL> LIST * *	
                  A one-line range, same effect as LIST *

  6*  GROUP BY employee_name, project_name
SQL> LIST LAST LAST	
                  A one-line range, same as LIST LAST

  6*  GROUP BY employee_name, project_name

As a shortcut to using the LIST command, if there is just one line you are interested in, you can list it by entering the line number itself 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*  WHERE employee.employee_num = time_log.employee_num

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

Keep the above SQL statement in the buffer as you read on about the rest of the line-editing commands. It has several mistakes that will be fixed using the other commands.

The APPEND command is used 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> L
  1  SELECT employee_name, time_log_name, project_name
  2    FROM employee, project
  3   WHERE employee.employee_id = time_log.employee_id
  4  HAVING employee_name = project_name
  5     AND time_log.project_id = project.project_id
  6*  GROUP BY employee_name, project_name

I really 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 easily 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, project

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, project, project_hours

Notice that APPEND took all the text following the command, appended it to the current line, then redisplayed that line to show the results of the edit. Now the SELECT statement in the buffer joins all three tables.

The CHANGE command searches the current line for a specified string and replaces that string with another. CHANGE only replaces 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> L
  1  SELECT employee_name, time_log_name, project_name
  2    FROM employee, project, project_hours
  3   WHERE employee.employee_num = time_log.employee_num
  4  HAVING employee_name = project_name
  5     AND time_log.project_num = project.project_num
  6*  GROUP BY employee_name, project_name

Notice that line 1 references a column that does not exist. In just a bit you will see how to remove that column reference with the CHANGE command. Next, notice that the WHERE clause has three mistakes: the table name TIME_LOG is used 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 all these problems. To start with, here’s how to change TIME_LOG to PROJECT_HOURS:

SQL> L 3
  3*  WHERE employee.employee_num = time_log.employee_num
SQL> C /time_log/project_hours/
  3*  WHERE employee.employee_num = project_hours.employee_num

In the above 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.

Next, the employee_num field name needs to be corrected. It should be employee_id. Remember, CHANGE only does one substitution at a time. Since there are two occurrences of employee_num that need to be changed, CHANGE will have to be executed twice. The following example shows this:

SQL> L 3
  3*  WHERE employee.employee_num = project_hours.employee_num
SQL> C /employee_num/employee_id/
  3*  WHERE employee.employee_id = project_hours.employee_num
SQL> C /employee_num/employee_id/
  3*  WHERE 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, is 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. It may help to 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 just need to be consistent within the command. SQL*Plus interprets the first nonspace 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 needed to include a / as part of the text to be searched for or replaced. You also have the option of leaving off the trailing delimiter, so 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.

The DEL command is used to erase one or more lines from the buffer. Used by itself, it erases the current line. You may also specify a line, or a range of lines, as arguments 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.

List the buffer. If you have been following along through all the line-editing examples, you should see the following output:

SQL> L
  1  SELECT employee_name,  project_name
  2    FROM employee, project, project_hours
  3   WHERE employee.employee_id = project_hours.employee_id
  4  HAVING employee_name = project_name
  5     AND time_log.project_num = project.project_num
  6*  GROUP BY employee_name, project_name

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

SQL> DEL 4
SQL> L *
  4*    AND time_log.project_num = project.project_num

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

The DEL command may also 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 next example shows how to erase lines 3 through the current line. Remember that line 4 is current.

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

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

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 also 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 deleting the entire WHERE clause.

SQL> L
  1  SELECT employee_name,  project_name
  2    FROM employee, project, project_hours
  3*  GROUP BY employee_name, project_name

The statement listed above is actually syntactically correct and would execute. However, it would simply return all possible combinations of employees and projects. The original intent was to list each employee together with all projects to which the employee actually charged hours. In order to do that, the WHERE clause deleted earlier needs to be put back in. The following example shows how to insert the first line of the WHERE clause by using INSERT with a text argument:

SQL> L 2	
                  Make line 2 current, so we can insert after it
  2*   FROM employee, project, project_hours
SQL> I WHERE employee.employee_id = project_hours.employee_id
SQL> L
  1  SELECT employee_name,  project_name
  2    FROM employee, project, project_hours
  3  WHERE 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 throws you into input mode, where you can type as many lines as you like. Pressing a blank line exits input mode, terminating entry. Here’s how to put in the second line of the WHERE clause using this method:

SQL> L 3	
                  Make line 3 current, so new lines follow it
  3* WHERE employee.employee_id = project_hours.employee_id
SQL> I			
                  Enter input mode and type the new line

4i   AND time_log.project_num = project.project_num
5i
SQL> L
  1   SELECT employee_name,  project_name
  2     FROM employee, project, project_hours
  3   WHERE employee.employee_id = project_hours.employee_id
  4    AND time_log.project_num = project.project_num
  5*  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 INPUT command was used by itself to enter input mode, and a new line was entered to be added to the WHERE clause. Notice that 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 in order to make things line up nicely. INSERT 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.

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. Assume that you have the following SELECT statement in the buffer:

SQL> L
  1    SELECT employee_name,  project_name
  2      FROM employee, project, project_hours
  3     WHERE employee.employee_id = project_hours.employee_id
  4       AND time_log.project_num = project.project_num
  5*    GROUP BY employee_name, project_name

With respect to the sample tables used for this book, you can see that line 4 has several mistakes. There is no TIME_LOG table, and the primary key for a project is project_id, not project_num. The CHANGE command could be used to correct these items, but you might find it faster and easier just to retype the entire line. Here’s how you can do that:

SQL> 4    AND project_hours.project_id = project.project_id
SQL> L
  1    SELECT employee_name,  project_name
  2      FROM employee, project, project_hours
  3     WHERE employee.employee_id = project_hours.employee_id
  4     AND project_hours.project_id = project.project_id
  5*    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.