The SQL*Plus Buffer

Using SQL*Plus is the classic way of creating and managing Oracle databases. It is available with all installations on all operating systems. So, if you know how to use it, you'll be able to process an Oracle database on any operating system.

To use SQL*Plus, find the icon under the Start/Programs/Oracle.. and click it. Sign onto your database using the username and password. You should see a window similiar to the one shown below:

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Feb 26 13:51:58 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL>

Among the many features of SQL*Plus is a text editor. As you type into SQL*Plus, your keystrokes are placed into a buffer. When you press Enter, SQL*Plus will save what you typed into a line in the buffer and go to a new line, but it will neither finish the statement not execute it.

To see the contents of the buffer, type LIST. The line shown with an asterisk is the current line. You can change the current line by entering LIST followed by the line number such as LIST 1. To change the contents of the current line, enter change/astring/bstring, where astring is the string you want to change, and bstring is the replacement string.

The Change command is fine for making small changes, but it could be unworkable for editing longer expressions such as stored procedures. For such purposes, you can set up SQL*Plus to connect to your text editor. Before doing this, however, you should create a directory for your Oracle code, say C:\MyDirectory\OracleCode. Find the SQL*Plus icon your C drive or desktop and right click to reveal properties and enter the name of the new directory in the Start In text box. Click OK and restart SQL*Plus.

Next, click the Edit menu in the SQL*Plus window menu and then select Editor/Define Editor. You can enter the name of your editor here. Notepad is the default editor and works just fine for editing your SQL code. Click OK. Now you have defined your text editor for SQL*Plus and set it to point to your directory. Now whenever you type Edit, SQL*Plus will invoke Notepad or whichever editor you selected.

You can now create,save,and edit files of code in that directory. For example, if you enter the following:

SELECT Table_Name
FROM USER_TABLES;

After the contents appear, type Edit, SQL*Plus will bring up your editor with the contents of the buffer. Use SAVE AS to give the file a new name, such as EX1.txt. Close your editor and you will return to SQL*Plus. To edit the file you just created, type EditEX1.txt. and you will enter your editor with that file. When you exit your editor and return to SQL*Plus, EX1.txt will be stored in the SQL*Plus buffer. To cause the buffer contents to execute, enter the right-leaning slash(/).