Sql*Plus User’s Guide and Reference

4-12
SQL*Plus User's Guide and Reference

---

5
Using Scripts in SQL*Plus
5-1
5
 Using Scripts in SQL*Plus
This chapter helps you learn to write and edit scripts containing SQL*Plus commands, 
SQL commands, and PL/SQL blocks. It covers the following topics:
■
Editing Scripts
■
Editing Scripts in SQL*Plus Command-Line
■
Placing Comments in Scripts
■
Running Scripts
■
Nesting Scripts
■
Exiting from a Script with a Return Code
■
Defining Substitution Variables
■
Using Predefined Variables
■
Using Substitution Variables
■
Passing Parameters through the START Command
■
Communicating with the User
■
Using Bind Variables
■
Fetching Iterative Results from a SELECT inside a PL/SQL Block
Read this chapter while sitting at your computer and try out the examples shown. 
Before beginning, make sure you have access to the sample schema described in 
SQL*Plus Overview
 on page xix.
Editing Scripts
In SQL*Plus command-line, the use of an external editor in combination with the @, 
@@ or START commands is an effective method of creating and executing generic 
scripts. You can write scripts which contain SQL*Plus, SQL and PL/SQL commands, 
which you can retrieve and edit. This is especially useful for storing complex 
commands or frequently used reports. 
Writing Scripts with a System Editor
Your operating system may have one or more text editors that you can use to write 
scripts. You can run your operating system's default text editor without leaving the
SQL*Plus command-line by entering the EDIT command. 

---

Editing Scripts in SQL*Plus Command-Line
5-2
SQL*Plus User's Guide and Reference
You can use the SQL*Plus DEFINE command to define the variable, _EDITOR, to hold 
the name of your preferred text editor. For example, to define the editor used by EDIT 
to be vi, enter the following command:
DEFINE _EDITOR = vi
You can include an editor definition in your user or site profile so that it is always 
enabled when you start SQL*Plus. See 
SQL*Plus Configuration
 on page 2-3, the 
DEFINE
 command on page 12-43, and the 
EDIT
 command on page 12-57 for more 
information.
To create a script with a text editor, enter EDIT followed by the name of the file to edit 
or create, for example:
EDIT SALES
EDIT adds the filename extension .SQL to the name unless you specify the file 
extension. When you save the script with the text editor, it is saved back into the same 
file. EDIT lets you create or modify scripts.
You must include a semicolon at the end of each SQL command and a slash (/) on a 
line by itself after each PL/SQL block in the file. You can include multiple SQL 
commands and PL/SQL blocks in a script.