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 .

11.3.1 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 table > project_hours INDEX_NAME COLUMN_NAME ------------------------------ ------------------------------ PROJECT_HOURS_PK PROJECT_ID EMPLOYEE_ID TIME_LOG_DATE Next table > Goodbye!

It sure does look like a loop, but it's not. Example 11-27 shows the script that is being run.

Example 11-27. Using recursion to simulate a loop

SET ECHO OFF SET VERIFY OFF COLUMN index_name FORMAT A30 COLUMN column_name FORMAT A30 BREAK ON index_name NODUPLICATES SELECT index_name, column_name FROM user_ind_columns WHERE table_name = UPPER('&1'); --Ask the user if he wants to do this again. PROMPT ACCEPT s_next_table PROMPT 'Next table >' --Execute either list_indexes.sql or empty.sql, --depending on the user's response. COLUMN next_script NOPRINT NEW_VALUE s_next_script SET TERMOUT OFF SELECT DECODE ('&&s_next_table', '','ex11-27_empty.sql', 'ex11-27.sql ' UPPER('&&s_next_table')) next_script FROM dual; SET TERMOUT ON @&&s_next_script

The key to the looping is in the last part of the script, following the ACCEPT statement. If you enter another table name, the SELECT statement will return another call to the ex11-27.sql script. So, when you type "project" in response to the prompt, the s_next_script substitution variable ends up being this:

ex11-27.sql PROJECT

The only thing missing is the at sign, and that is supplied by the command at the bottom of Example 11-27. In this case, the command:

@&&s_next_script

will be translated to:

@ex11-27.sql PROJECT

If you don't enter a table name at the prompt, the s_next_table variable will be null, and the DECODE statement will return "ex11-27_empty.sql". The ex11-27_empty.sql script is necessary because the @ command must be executed. ex11-27_empty.sql gives you a clean way out of the recursion. In this case, ex11-27_empty.sql displays a goodbye message:

PROMPT PROMPT Goodbye! PROMPT

Recursive execution is a limited technique. You can't nest scripts forever. You can only go 20 levels deep, and on some older versions of SQL*Plus the limit may be as low as 5. Exceed that limit, and you will get the following message:

SQL*Plus command procedures may only be nested to a depth of 20.

Still, recursion can be useful. What are the odds that you will want to type in 20 table names in one sitting? In this case, the convenience may outweigh any chance of exceeding that limit on nesting scripts. And if you do exceed the limit, so what? You can rerun the script.

11.3.2 Looping Within PL/SQL

You should consider PL/SQL when you need to implement any type of complex procedural logic, and that includes looping. Because PL/SQL executes in the database, you can't use it for any loop that requires user interaction. Example 11-27, which repeatedly prompts for another table name, could never be implemented in PL/SQL. It's also impossible to call another SQL*Plus script from PL/SQL. However, if you can get around those two limitations, PL/SQL may be the best choice for the task. Example 11-28 shows a script that uses a PL/SQL block to display indexes on all tables that you own, having names matching a pattern that you specify.

Example 11-28. Looping is often best done in PL/SQL

SET ECHO OFF SET VERIFY OFF SET SERVEROUTPUT ON ACCEPT table_name PROMPT 'Show indexes on what table >' DECLARE BEGIN IF '&table_name' IS NOT NULL THEN --Loop for each table and index selected by the user FOR xtable IN ( SELECT table_owner, table_name, index_name FROM user_indexes WHERE table_name LIKE UPPER('&table_name') ORDER BY table_owner, table_name, index_name) LOOP --Display the table and index names DBMS_OUTPUT.PUT_LINE(CHR(9)); DBMS_OUTPUT.PUT_LINE('Index ' xtable.index_name ' on ' xtable.table_owner '.' xtable. table_name); --Loop through each column in the index FOR xcolumn IN ( SELECT column_name FROM user_ind_columns WHERE index_name = xtable.index_name ORDER BY column_position) LOOP DBMS_OUTPUT.PUT_LINE(' ' xcolumn.column_name); END LOOP; END LOOP; END IF; END; /

Output from Example 11-28 looks like this:

SQL> @ex11-28 Show indexes on what table > employee Index EMPLOYEE_BY_NAME on GENNICK.EMPLOYEE EMPLOYEE_NAME Index EMPLOYEE_PK on GENNICK.EMPLOYEE EMPLOYEE_ID

Example 11-28 prompts once at the beginning of the script. Then control falls into a PL/SQL block that uses an outer loop to go through each matching table and index, and an inner loop to display each column from each index. PL/SQL can't write to your display. Instead, Example 11-28 uses the DBMS_OUTPUT package to write output to an in-memory buffer on the database server. When the block completes, SQL*Plus reads and displays the contents of that buffer.