Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

Chapter 11. Advanced Scripting

SQL*Plus was not designed to be a tool used for
writing complex scripts. Its capabilities can't
compare to those of your typical Unix shell, such as the Korn shell
or the Bourne shell. Nor does it have anywhere near the capabilities
of an advanced scripting tool such as Perl. Most noticeably,
SQL*Plus suffers from the
following limitations:

It lacks an IF statement.

There are no looping constructs.

It has limited error handling.

There is only marginal support for validating user input.

Because of these limitations, SQL*Plus is best suited to executing
top-down scripts that don't require any branching,
looping, or error handling. Most of the scripts you have seen so far
in this book fall into this category. Many are reports that set up
some column and page formatting and then execute a query. If
something goes wrong, you may not see any data in the report, or you
may see some SQL or SQL*Plus error messages.

This limited scripting support is fine when it comes to producing a
report. After all, if a report fails, you can fix the problem and
rerun the report. But what if you are performing a more complex and
critical task? What if you are summarizing some data, posting the
summary results to a summary table, and then deleting the underlying
detail? In that case, you certainly don't want to
delete the data if the summarization failed. You need some kind of
error-handling mechanism.

If you need to write scripts of any significant complexity, I strongly encourage you to investigate the use of PL/SQL in your script. PL/SQL is a powerful programming language in its own right and includes support for error handling, branching, and looping, which are important features that SQL*Plus lacks. Steven Feuerstein's and Bill Pribyl's book, Oracle PL/SQL Programming , Third Edition (O'Reilly), is an excellent resource.

This chapter describes some specific ways to work around these
limitations of SQL*Plus. Believe it or not, it is possible, using
only SQL*Plus, to implement branching and to validate user input.
There are even ways to deal with repetitive tasks without resorting
to a loop. You will learn about bind variables and see how they
better enable you to mix PL/SQL code into your SQL*Plus scripts. You
will see how bind variables can make the job of developing queries
for application programs easier.