Oracle SQL*Plus: The Definitive Guide, 2nd Edition

Chapter 8. Writing SQL*Plus Scripts

In the previous chapter, you saw how to write a script to produce a report. This chapter delves more deeply into the subject of scripting, and shows you how to write interactive scripts. You will learn how to use substitution variables, which allow you to supply values dynamically to a script at runtime. You will learn how to prompt the user for those values and how to display other messages for the user to see. Finally, you will learn how to package your script for easy access when you need it.

Why Write Scripts?

The most compelling reason to write scripts, in my mind, is to encapsulate knowledge. Suppose, for example, that you have developed a query that returns index definitions for a table. You certainly don't want to have to think through the entire process of developing that query each time you need to see an index. If you have a good script available, you just run it. Likewise, if someone asks you how to see index definitions for a table, you can give them a copy of the script.

Another reason for developing scripts is that they save time and effort, making it easy to run a series of commands repeatedly. Look at the script in Example 5-5 used to produce the Project Hours and Dollars Report. It contains 16 commands, some quite long. Who wants to retype all that each time they generate a report? I sure don't, do you?

Finally, scripts can simplify tasks for you and others. When you know you have a good, reliable script, you can run it, answer ...