O'Reilly logo

Oracle SQL*Plus: The Definitive Guide by Jonathan Gennick

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ------------------------------ ------------------------------ ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required