The EDIT Command

You don't like line-editing ? SQL*Plus does not have a built-in full-screen editor, but it does have the EDIT command. The SQL*Plus EDIT command allows you to invoke the text editor of your choice to use in editing SQL statements and PL/SQL blocks.

2.10.1 Choosing Your Editor

Although you issue the EDIT command, SQL*Plus invokes the editor named in a SQL*Plus user variable named _EDITOR . You can view the current editor choice by issuing the command DEFINE _EDITOR , as follows :

SQL> DEFINE _EDITOR DEFINE _EDITOR = "ed" (CHAR)

If you don't like the default choice (and I'm reasonably certain you won't), you can change the editor, but only for the duration of your current session, using another variation of the DEFINE command:

DEFINE _editor = "vi"

Now, SQL*Plus will invoke the vi editor in response to the EDIT command.

If you're using the Windows version of SQL*Plus, you can change the value of EDITOR from the GUI, using Edit EditorDefine Editor, as shown in Figures Figure 2-6 and Figure 2-7. The default editor choice under Windows is Notepad.

Figure 2-6. The Define Editor menu option

Figure 2-7. Specifying the executable to invoke in response to the EDIT command

To make an editor choice permanent, you can place a DEFINE _EDITOR command in either your global or local login file. SQL*Plus login files are executed whenever SQL*Plus starts, or, beginning with Oracle Database 10 g , whenever you connect to a database. See Chapter 14 for more information on login files and other aspects of SQL*Plus configuration.

2.10.2 Invoking the Editor

You invoke the editor with the EDIT command, which may be abbreviated ED. SQL*Plus then invokes your external editor to let you edit the statement currently contained in the buffer. Example 2-16 shows a query being entered and the editor being invoked.

Example 2-16. Invoking an external editor

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

When you execute the EDIT command, the contents of the buffer are written to a file named afiedt.buf , and your editor is invoked. The filename afiedt.buf is passed as the first argument in the editor's invocation (e.g., vi afiedt.buf , or Notepad afiedt.buf ). Figure 2-8 shows what your screen would now look like on a Windows system.

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 AFI, which stood for Advanced Friendly Interface.

Figure 2-8. Results of the EDIT command under Windows

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

SQL> edit SP2-0107: 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.

2.10.3 Beware Editing Conflicts!

There is the potential for conflict if you and another user happen to be sharing the same current working directory, and you both invoke an external editor to edit the contents of your SQL buffer. After all, only one afiedt.buf file can be in a directory.

The following editing sequence was generated on a Windows system with SQL*Plus set to use Windows Notepad as the external editor:

SQL> select * from project 2 SQL> edit Wrote file afiedt.buf 1* select * from employee

The original statement was not changed in the external editor. The result statement, which only appears to be an edited version of the first, is a statement I edited in another window. I used the following sequence of events to generate this example:

1. Opened SQL*Plus window #1, entered select * from project .
2. Opened SQL*Plus window #2, entered select * from employee .
3. Issued EDIT command from window #1. The file afiedt.buf now contains select * from project .
4. Issued EDIT command from window #2. The file afiedt.buf now contains select * from employee .
5. Closed window #2's Notepad instance. No change to afiedt.buf . SQL*Plus in window #2 reads back the same statement that it wrote out.
6. Closed window #1's Notepad instance. SQL*Plus in window #1 reads back the statement written out from window #2.

Of course, on Windows you usually have only one user per system, so this scenario is unlikely . However, it's common to have many concurrent users on Linux and Unix systems, so the possibility of this scenario is something you should remember.

2.10.4 Formatting Your Command

Take another look at Figure 2-8. Pay attention to the way in which the SQL statement is terminated. No trailing semicolon exists, and the statement is terminated by a forward slash on a line by itself. You can include or omit the trailing forward slash, but do not attempt to terminate a SQL statement with a semicolon when editing the SQL buffer using an external editor.

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

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

• Do not end SQL statements with a semicolon.
• End PL/SQL blocks with a semicolon.
• Optionally, terminate the file with a forward slash on a line by itself.
• Include only one SQL statement or PL/SQL block.

2.10.5 Getting Back to SQL*Plus

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, 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 or / command.