Oracle SQL*Plus: The Definitive Guide

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 second-best bet is to look into using your operating system’s scripting language, if there is one. Having said this, I’ll point out that there are a couple of things you can do in SQL*Plus that might get you the same result as writing a loop. These are:

• Recursive execution

• Generating a file of commands, and then executing it

The first option has some severe limitations, and I don’t recommend it too strongly. 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. Say 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> @list_indexes employee

INDEX_NAME                     COLUMN_NAME
------------------------------ ------------------------------
EMPLOYEE_PK                    EMPLOYEE_ID

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