Teaching Oracle to write SQL*Plus Scripts

As many experienced users of SQL are well aware, SQL can be used to generate SQL scripts. The benefit to the SQL programmer is that for just one SQL script, multiple scripts can be generated and subsequently run. These multiple scripts are all run under spool file control, ensuring minimal involvement by the programmer.

The experience of careful preparation for conference presentations has emphassised just how powerful the technique is. I now use it on a daily basis. In fact, major applications have been written using the method, including trigger generation for audit control, and most recently, an asynchronous full replication, backup and recovery suite.

So who is going to be most interested in this technique? Obviously experienced Oracle programmers, most particularly DBAs, who are likely to require all manner of scripts at short notice. But it is also possible that any person writing standard SQL against any relational database system may be interested, as the technique is not restricted to Oracle alone.

Lets start with some Introductory Remarks of what its all about,

... then consider a small Tutorial should you wish a refresher course,

... then remind ourselves of various basic Rules for building scripts of this nature,

.. then finally lets have a look at some Example scripts.

For each script presented, a brief description will also be included within the body of the script.

Should you also have any scripts you would like to see presented here, I should be more than happy to include them, properly acknowledged, of course!
 

Check back to this site from time to time, as I shall be adding new scripts.