Sql*Plus User’s Guide and Reference

Example 5–1
Using a System Editor to Write a SQL Script
Suppose you have composed a query to display a list of salespeople and their 
commissions. You plan to run it once a month to keep track of how well each 
employee is doing. 
To compose and save the query using your system editor, invoke your editor and 
create a file to hold your script:
EDIT SALES
Enter each of the following lines in your editor. Do not forget to include the semicolon 
at the end of the SQL statement:
COLUMN LAST_NAME HEADING 'LAST NAME'
COLUMN SALARY HEADING 'MONTHLY SALARY' FORMAT $99,999
COLUMN COMMISSION_PCT HEADING 'COMMISSION %' FORMAT 90.90
SELECT LAST_NAME, SALARY, COMMISSION_PCT
FROM EMP_DETAILS_VIEW
WHERE JOB_ID='SA_MAN';
The format model for the column COMMISSION_PCT tells SQL*Plus to display an 
initial zero for decimal values, and a zero instead of a blank when the value of 
COMMISSION_PCT is zero for a given row. Format models and the COLUMN 
command are described in more detail in the 
COLUMN
 command on page 12-26 and 
in the Oracle Database SQL Language Reference. 
Now use your editor's save command to store your query in a file called SALES.SQL.
Editing Scripts in SQL*Plus Command-Line
You can use a number of SQL*Plus commands to edit the SQL command or PL/SQL 
block currently stored in the buffer. 
Table 5–1, " SQL*Plus Editing Commands"
 lists the SQL*Plus commands that allow 
you to examine or change the command in the buffer without re-entering the 
command.

---

Editing Scripts in SQL*Plus Command-Line
Using Scripts in SQL*Plus
5-3
These are useful if you want to correct or modify a command you have entered.
Listing the Buffer Contents
The SQL buffer contains the last SQL or PL/SQL command. Any editing command 
other than LIST and DEL affects only a single line in the buffer. This line is called the 
current line. It is marked with an asterisk when you list the current command or block.