Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

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

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

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.

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:

Opened SQL*Plus window #1, entered select * from
project.

Opened SQL*Plus window #2, entered select * from
employee.

Issued EDIT command from window #1. The file
afiedt.buf now contains
select * from project.

Issued EDIT command from window #2. The file
afiedt.buf now contains
select * from employee.

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.

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.

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.