RelationalDBDesign RelationalDBDesign

SQL Extensions   «Prev  Next»
Lesson 4Using a text editor with SQL*Plus
Objective Change a Query by using a Text Editor

Using a Text Editor with SQL*Plus

An alternative way to edit the SQL*Plus buffer is to use the text editor within your operating system. The exact editor used depends on which operating system you are using. For example, if you are using Windows, the editor invoked is Microsoft Notepad or download Notepad++.
Invoke the editor by typing the following command and pressing Enter.

When the editor is invoked, SQL*Plus creates a file named AFIEDT.BUF and writes the SQL*Plus buffer into the file. After making changes, when you are saving and closing the file, SQL*Plus copies the modified file back into the SQL*Plus buffer and prints it on the screen. Go through the following Slide Show for a quick demonstration of invoking and using the text editor.

  1. Here is the query that you created in the simulation in the previous lesson. This time the query will be changed quite a bit, so it is better to use the text editor method.
  2. To invoke the text editor, type EDIT and press ENTER
  3. SQL*Plus places the current query into a file and opens the file with the text editor.
  4. After adding some lines to the query and modifying the SELECT clause, the query is ready to test. Clicking the X in the top right corner of Windows Notepad closes the editor.
  5. Notepad displays a window asking you to save changes. Clicking the Yes button saves the changes.
  6. SQL*Plus writes the contents of the file into the current buffer and displays it in the SQL* Plus window.
  7. Type a forward slash and press ENTER to execute the query.
  8. You have an error in your code. Type EDIT and press ENTER to go back to the text editor.
  9. The corrections have been made using the text editor. Once again, click the X in the top right corner to close the text editor.
  10. Notepad asks about saving your changes. Click the Yes button to save changes and return to SQL* plus.
  11. The modified query is now ready to execute in SQL* Plus. Type a forward slash and press ENTER to execute.
  12. The query successfully executes and displays the results

SQL Style

First, some comments on style. SQL*Plus does not care whether the SQL commands you type are in uppercase or lowercase.For example, the command
SeLeCt feaTURE, section, PAGE FROM newsPaPeR;

will produce exactly the same result as this one:
select Feature, Section, Page from NEWSPAPER;

Case matters only when SQL*Plus or Oracle is checking an alphanumeric value for equality. Aside from this usage, case is completely irrelevant. As a matter of style, this module follows certain conventions about case to make the text easier to read:
select, from, where, order by, having, and group by will always be lowercase and boldface in the body of the text.
SQL*Plus commands also will be lowercase and boldface (for example, column, set, save, ttitle, and so on). IN, BETWEEN, UPPER, and other SQL operators and functions will be uppercase and boldface.
Column names will be mixed uppercase and lowercase without boldface (for example, Feature, EastWest, Longitude, and so on).
Table names will be uppercase without boldface (for example, NEWSPAPER, WEATHER, LOCATION, and so on).
You may want to follow similar conventions in creating your own queries, or your company already may have standards it would like you to use. The goal of any such standards should always be to make your work simple to read and understand.

Using Line Text Editors

The link below contains information with respect to editing commands for saving and retrieving your own files in SQL*Plus.
Using Line Text Editors