Oracle SQL*Plus: The Definitive Guide, 2nd Edition

Looping in SQL*Plus

There is no way to write a real loop using SQL*Plus. Your best option, if you need to do something iteratively, is to use PL/SQL. PL/SQL, however, doesn't allow you any interaction with the user, so it's not always suitable for the task at hand. Your next bet is to look into using your operating system's scripting language, if there is one.

This said, you can do a couple of things in SQL*Plus that might get you the same result as writing a loop:

• Using recursive execution

• Generating a file of commands, and then executing it

The first option has some severe limitations, and I don't recommend it. The second option I use all the time, especially when performing database maintenance tasks.

Recursive Execution

You can't loop, but you can execute the same script recursively. Suppose you have a script that displays some useful information, and you want to give the user the option of running it again. You can do that by recursively executing the script. Take a look at the following interaction, in which the user is looking at indexes for various tables. It looks like a loop. Each time through, the user is prompted for another table name, and the indexes on that table are displayed.

SQL> @ex11-27 employee

INDEX_NAME                     COLUMN_NAME
------------------------------ ------------------------------
EMPLOYEE_PK                    EMPLOYEE_ID
EMPLOYEE_BY_NAME               EMPLOYEE_NAME

Next table >project INDEX_NAME COLUMN_NAME ------------------------------ ------------------------------ PROJECT_PK PROJECT_ID Next ...