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.
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:
SQL> edit Wrote file afiedt.buf 21
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:
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:
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.
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:
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.
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:
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.
Introduction to SQL*Plus
A Lightning SQL Tutorial
Generating Reports with SQL*Plus
Creating HTML Reports
Writing SQL*Plus Scripts
Extracting and Loading Data
Exploring Your Database
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 (Definitive Guides)
Authors: Jonathan Gennick