Oracle SQL*Plus: The Definitive Guide

Saving Your Work

Consider saving all the commands to produce a report in a script file. I can’t remember ever producing an ad-hoc report that was truly generated just once. Often there are formatting issues I don’t see until after the first printing. Other times a client will look at a report and immediately think of one or two things to add. By saving your work in a file, you will be prepared when the request for that same report, or one similar to it, comes again.

The only time I ever print a report by typing the commands interactively into SQL*Plus is when the query is very simple and I don’t care how the output is formatted. In such a case, if I’m in a hurry, I may simply spool off the output from a SELECT statement, print it, and live with the incorrect pagination, lack of titles, and lack of column formatting.

Saving your work in a file also protects you from simple mistakes and minor catastrophes. If you’ve typed 20 formatting commands into SQL*Plus and your computer locks up, you’ve lost your work. You’ll have to restart your computer and then attempt to remember all those commands. If you are working on a long SQL query, and that query exists only in the SQL buffer, a simple thing like typing COMMIT will clobber it — something I’ve done many times myself. Save yourself some work by saving your work in a file.