The SQL Editor
The SQL buffer is a work area assigned to the SQL*Plus environment. This buffer only contains SQL or PL/SQL syntax. The contents of this buffer can be loaded, saved, and manipulated with the following commands.
TIP
PL/SQL (pronounced P - L - S - Q - L) is an Oracle9i procedural language with SQL imbedded in it. This gives the user the ability to perform various tasks based on row-at-a-time or result set processing of the SQL. PL/SQL is useful for performing looping functions and in-depth calculations based on a variety of variables and other tables.
A or APPEND new text
Appends text to the end of the current line of the SQL buffer. In Figure 2.4, the L is used to show the current line in the SQL buffer as well as change the current line in the SQL buffer.
Figure 2.4. SQL buffer APPEND command.
C or CHANGE/target text/new text/
Changes the target text to the new text on the current line in the SQL buffer. Figure 2.5 shows a misspelling of a column name and how L and C are used to quickly correct the problem.
Figure 2.5. SQL buffer CHANGE command.
DEL
Deletes the current line in the SQL buffer.
EDIT filename
Utilizes an operating-system-dependent text editor. To edit the SQL buffer with an operating-system dependent text editor (see Figures 2.6 and 2.7), simply leave off the filename. The default editor in WindowsNT/95/98 is Notepad; in all Unix environments the default editor is vi. This default editor can be changed in the Windows environments with the regedit command (REGEDIT, HKEY_LOCAL_MACHINE, SOFTWARE, Oracle) or by editing the .profile file on Unix.
Figure 2.6. SQL buffer EDIT and GET commands.
Figure 2.7. SQL buffer NOTEPAD editing session.
GET filename
Reads an operating-system-dependent file into the SQL buffer. Figure 2.8 demonstrates how this command can be used in conjunction with the EDIT command (see Figure 2.9) to put the contents of the operating-system file editing back into the SQL Buffer.
Figure 2.9. SQL buffer START command.
I or INPUT text
Adds the text after the current line in the SQL buffer. Figure 2.8 shows how to use INPUT to insert an additional line to the SQL statement in the SQL buffer.
L or LIST number OR nn nn
Displays the contents of the SQL buffer. When the number syntax is used, LIST will display the line number and make that line the current line in the SQL buffer. The nn nn option will list the range of lines between the two numbers. LIST has been repeatedly demonstrated in this section.
SAVE filename
Saves the contents of the SQL buffer to an operating-system–dependent file with the contents of the SQL buffer.
TIP
When creating SQL*Plus command files, utilize these editing features to arrive at the query results desired: SAVE to the operating system, then edit that file with EDIT to add the formatting and other desired features.
 | START filename param1 param2 … or @filename param1 param2 … |
START will execute the contents of the SQL*Plus command file named in filename and pass any input parameters to the SQL*Plus command file. The difference between START and GET is that GET reads the contents of an operating-system file and puts the contents in the SQL buffer. START does the same thing but also executes the contents of the SQL buffer. @ has the same functionality as START. Figure 2.9 illustrates the START command use.
TIP
This START feature is utilized when creating the Sales Tracking tablespaces and the Sales Tracking tables and indexes covered in-depth in Chapter 4, “Building an Oracle9i Database.” I have used this command to create INSTALL.SQL scripts used to implement application objects.
/ (forward slash)
Slash is used to execute whatever is in the SQL buffer. This is a convenient way to execute SQL statements in a SQL script file where START is used to run the whole script. The / will immediately follow the SQL statement on the next line.