Teaching Oracle to write SQL*Plus Scripts:

A minimalist Tutorial...

SQL>
SQL>
SQL>

We start from the position that the fastest way to learn a new technique is by looking at and understanding real-world examples. So a few elementary examples will be presented below, but the real stuff will be on the pages of Script Examples. These will all be fully documented.

However, if you would like something more formal, mail me to request a copy of the paper I submitted to the UK Oracle User Group conference in December, 2000.

Example 1:

This is a simple SQL statement -

Select sysdate from dual;

Example 2:

Now embed that script within another, as follows:

select
 ‘Select sysdate from dual;’
from dual;

 The second example is a 2-Level Embedded script. The inner, Level-1 script is identical to Example 1.

 Note what is happening to the quotes. This is a 4-Level embedded script, with the outermost script being described as the Level-4 script. The output of this script is a 3-Level one, which produces a 2-Level script, and so on.

The automatic implementation of all four scripts requires the use of the 'spool' and the '@spool_file' commands.

Note how the above Example 3 script can be implemented with spool commands. First, place the entire script into a text file, call it FULL.SQL, and then wrap it in a succession of spool files. Run this for yourself, and watch carefully what happens.

SPOOL A1.SQL
 @FULL.SQL
 SPOOL OFF
 SPOOL A2.SQL
 @A1.SQL
 SPOOL OFF
 SPOOL A3.SQL
 @A2.SQL
 SPOOL OFF
 @A3.SQL

 A simple rule emerges from this example - when a 4-Level nested query is used, 3 spool files are required. The rule is therefore:

for an 'n' nested query, 'n-1' spool files will be required.

A word of warning - switch off headers, echos and the SQLPROMPT, like this:

set echo off
set pagesize 0
set sqlprompt ""

...and switch them all back on again afterwards. You may have to make other 'set' changes depending on the default condition of your own SQL*Plus environment.

Now you are ready to consider some more realistic examples.

SQL>
SQL>
SQL>