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 :




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.

Neither the command nor the user variable name is case-sensitive, so define _editor will work just as well as DEFINE _EDITOR.


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


SELECT project_name


FROM project


WHERE project_id IN (




FROM project_hours)




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:



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.

Beware of ed!

The default editor for SQL*Plus on Unix and Linux systems is, unfortunately , not vi . Rather, it is ed , which is a line-oriented text editor along the lines of SQL*Plus's built-in editing functionality. You'll know you've dropped into ed when you issue an EDIT command with results like the following:



Wrote file afiedt.buf


Press Enter at this point, and the only feedback you'll get is a question mark (?). It's not at all obvious how to exit ed and return to SQL*Plus. If you don't happen to know how to use ed , you might feel trapped in an editor that you can't get out of. When that happens, just press Ctrl-D. That key sequence will exit the ed editor and return you to SQL*Plus, from which you can define EDITOR to point to vi , or any other editor you're comfortable with, as described in "Choosing Your Editor."


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:


select * from project




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.

The Linux/Unix vim editor will detect the conflict I've just described, through the existence of a swap file that vim attempts to create based on the name of the file you are editing. However, the vim editor can't determine whether two users are trying to edit the same file, or whether the swap file is left over from a previous editing session that crashed.

Editing Specific Files

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

This technique will not work for files without extensions, as SQL*Plus will always attempt to add .sql to any filename you supply to the EDIT command that does not already have a period in the name.


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.

When you type a SQL statement directly into SQL*Plus and terminate it with a semicolon, SQL*Plus strips off that semicolon, which is not properly part of SQL syntax. However, if you include a terminating semicolon while editing a SQL statement with an external editor, that semicolon gets loaded into the SQL buffer as part of the statement, and the result will be an invalid character error when you go to execute the statement.

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.

Be sure to save the file before leaving the editor. To make SQL*Plus see your changes, they must be written back to the work file. Most editors, including Notepad, will remind you to save your changes before you exit, but vi will not. You should explicitly save your changes unless you want to throw them away.

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.

The work file ( afiedt.buf ) is not deleted. Its contents remain undisturbed until your next use of the EDIT command.


Introduction to SQL*Plus

Command-Line SQL*Plus

Browser-Based SQL*Plus

A Lightning SQL Tutorial

Generating Reports with SQL*Plus

Creating HTML Reports

Advanced Reports

Writing SQL*Plus Scripts

Extracting and Loading Data

Exploring Your Database

Advanced Scripting

Tuning and Timing

The Product User Profile

Customizing Your SQL*Plus Environment

Appendix A. SQL*Plus Command Reference

Appendix B. SQL*Plus Format Elements

Oracle SQL Plus The Definitive Guide, 2nd Edition
Oracle SQL*Plus: The Definitive Guide (Definitive Guides)
ISBN: 0596007469
EAN: 2147483647
Year: N/A
Pages: 151 © 2008-2020.
If you may any questions please contact us: