Chapter 4. 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 the user to dynamically supply values 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. Say, 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, just give them a copy of the script.

A second reason for developing scripts is that they save time. Look at the script to produce the first report in Chapter 3. It contains 17 separate commands, some quite long. By placing those commands in a script, you save yourself the time and effort involved in retyping all of them each time you run the report.

Lastly, scripts can simplify tasks both for you and for others. When you know you have a good, reliable script, you can just run it, answer the questions, then sit back while it does all the work. You don’t need to worry, thinking “did I enter the correct command?”, “did I log on as the correct user?”, or “did I get that query just right?”

Anytime you find yourself performing a task over and over, think about writing a script to do it for you. You’ll save yourself time. You’ll save yourself stress. You’ll be able to share your knowledge more easily.

Tip

A good source of ready-to-run scripts for Unix users is Oracle Scripts by Brian Lomasky and David C. Kreines, O’Reilly & Associates, 1998.

Get Oracle SQL*Plus: The Definitive Guide now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.