Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Training

    Home      .Services     Login       Start Learning     Certification      .                 .Share your BELIEF(s)...


. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       Email2aFriend    |


Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

Basics - SQL 


Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 |


Lesson 07

"The artist is nothing without the gift, but the gift is nothing without work." - Emile Zola (1840-1902)


Read first then play the video:

   SQL-VIDEO -SQL and PL/SQL Notepad editor

Notepad Editing

Why notepad editing and use of afiedt.buf

Oracle makes provisions for you to utilize your favorite text editor to edit the statement created in "afiedt.buf," the file into which SQL*PLUS stores the most recently executed SQL statement. You simply type edit (abbreviated "ed"). This action causes Oracle to bring up the SQL statement in "afiedt.buf" into the operating system's default text editor.



To change the text editor, issue the define_editor='your editor' statement from the SQL*PLUS prompt. Try to use a text editor rather than the Command Line editor.


Go to MS-DOS and change the directory to the iself folder.

SQL> cd ..


Login to "sqlplus" as "iself/schooling".

SQL> sqlplus iself/schooling


Get the dept file.

SQL> get dept


"ed" command

Your default editor is notepad. Use the "ed" command, to call notepad. On notepad, you can modify the query. Then close the window and click on Yes to replace the changes to the buffer.


Notice that when you use the "ed" command in the sqlplus, your defined editor will bring the last SQL statement from Oracle buffer. Now, you should be able to modify the SQL statement. After you modify your query, you can save and exit from the defined editor. By default that is NOTEPAD.


Remember that when you bring your SQL statement into your editor the ";" character will be disappeared and instead a "/" character will be replaced. Leave the SQL statement with "/" character and don"t enter ";" character at the end of your SQL statement.


Also, you should not work in the defined editor one SQL statement at a time. You can not have multiple SQL statements.


Notepad as an independent tool

If you want to use multiple SQL statements or to write your SQL script, you may want to open your own independent editor from the SQLPLUS tool.


And the technique you may want to use is to open the notepad as an independent tool.

To open the dept script from the iself directory, open notepad as a separate window and then open the "dept" file from the "iself" folder.


Add a column heading. Change the statement to query only the department number 10.

Save the file. Save the file at the iself folder.


Go to "SQLPLUS."

Get the "dept" file.

SQL> get dept


Notice that the changes are in the buffer, run the query.

SQL> run


Use the "Start" command to run the query.

Go to notepad


Remove the department number column.

Save the changes.

Go back to "SQLPLUS."

Use the @ sign to run the file.

No department number this time.


"This book fills a much-needed gap." - Moses Hadas (1900-1966) in a review



Q: How do you use the notepad editor?

Q: What is afiedt.buf?

Q: How do you change your text editor in the SQLPLUS tool?

Q: What does the ed command in the SQLPLUS tool?

Q: Can you have multiple SQL statements in the afiedt.buf file?

Q: How do you use the notepad editor as an independent tool in the SQLPLUS utility?

Q: How do you execute or run a SQL script?