You invoke the editor with the EDIT command. The syntax looks like this:

ED[IT] [filename]

where:

The typical use of the EDIT command is to edit a statement currently contained in the buffer. The following example shows a query being entered and the editor being invoked:

SQL> SELECT project_name
  2    FROM projects
  3   WHERE project_id in (
  4           SELECT DISTINCT project_id
  5             FROM project_hours)
  6  
SQL> EDIT
Wrote file afiedt.buf

Upon entering the EDIT command, the contents of the buffer are written to a file named AFIEDT.BUF , and the editor is invoked. Figure 2.7 shows what your screen would now look like on a Windows 95 or NT system. On Unix systems, the filename is lowercase, and will be afiedt.buf.

The filename AFIEDT.BUF is simply a work file used by SQL*Plus to hold your command while it is being edited. The name is a throwback to the very early days of SQL*Plus when it was briefly known as AUFI, which stood for Advanced User Friendly Interface.

Figure 2-7. Results of the EDIT command under Windows 95/NT

SQL*Plus will not invoke the editor if the buffer is empty; instead you will see the message shown below:

SQL> EDIT
Nothing to save.

If you have an empty buffer and wish to enter a new query, you must type something, perhaps just the first line, into SQL*Plus before using the EDIT command.

Another use for the EDIT command is to edit an existing text file. You can edit any text file you like, whether it contains a query or not. The following EDIT command for example, lets you edit your Unix Korn shell profile:

EDIT .profile

When you edit a file in this way, the contents of the file are not loaded into the buffer. This is just a convenient way for you to edit a file without having to exit SQL*Plus first.

Take another look at Figure 2.7. Pay attention to the way in which the SQL statement is terminated. Notice that there is no trailing semicolon. Notice also that the statement is terminated by a forward slash on a line by itself. When you edit the statement in the editor, do not attempt to terminate it with a semicolon. Semicolons only work as terminators when you are executing a script file, or when you are typing a SQL statement in from the command prompt. Adding a semicolon to the end of a statement being edited with the EDIT command will result in an invalid character error when you try and execute it. The easiest thing to do is to leave the statement as it is, terminated by a forward slash on a line by itself. Strictly speaking, though, the forward slash is optional, and may be omitted.

Although SQL statements do not require a trailing semicolon, a PL/SQL block does. That’s because the trailing semicolon is part of the PL/SQL syntax, but not part of the SQL syntax.

Here are the rules to follow when editing the SQL buffer with an external text editor:

Once you are finished editing your statement, you need to exit the editor in order to return to SQL*Plus. If you are using Notepad under Windows NT or 95, you do this by going to the File menu and choosing Exit.

When you exit the editor, control returns to SQL*Plus. The contents of the work file are loaded into the buffer and displayed on the screen for you to see. You may then execute your revised statement by using either the RUN command or the / command.